next »

[Stephen T.]

BDS - 3.55.01.40 (driver dbmmsv80) Genero 2.40.03 (driver dbmsncA0)

This issue is more specific to the setup. SQL Server was (is) configured with the date in 'American' format - ie MM/DD/YYYY. The DBDATE env var is defined as DMY4/ in both the BDS and Genero environments.

The issue appeared in prepared statements where a date was explicitly defined - ie
 DEFINE l_count,l_d,l_m                 INTEGER       
 DEFINE l_string                            VARCHAR(5000)
 DEFINE l_date                              DATE


whenever any error continue       

    LET l_string =
        'SELECT COUNT(*) ',
        'FROM some_table ',
        'WHERE some_date_field = ? '

    PREPARE p_count_dates FROM l_string
    DECLARE c_count_dates CURSOR FOR p_count_dates

   
for l_m = 1 to 12
   for l_d = 1 to 31
       LET l_date   = MDY(l_m,l_d,'2006')
       IF STATUS = 0 THEN
           FOREACH c_count_dates USING l_date INTO l_count
           END FOREACH
           IF STATUS = 0 THEN           
               DISPLAY 'Date (',l_date, ') Got (',l_count       ,')'
           ELSE
               DISPLAY 'Got STATUS (',STATUS,') for Date (',l_date,')'
           END IF                   
       END IF         
  end for           
end for           
   
for l_m = 1 to 12
   for l_d = 1 to 31
 
       LET l_date   = MDY(l_m,l_d,'2006')
       IF STATUS = 0 THEN
           LET l_string =
                'SELECT COUNT(*) ',
                'FROM some_table ',
                'WHERE some_date_field = \'',l_date,'\' '

        PREPARE p_count_dates2 FROM l_string
        DECLARE c_count_dates2 CURSOR FOR p_count_dates2
           FOREACH c_count_dates2  INTO l_count
           END FOREACH
           IF STATUS = 0 THEN           
               DISPLAY 'Embedded: Date (',l_date, ') Got (',l_count       ,')'
           ELSE
               DISPLAY 'Embedded: Got STATUS (',STATUS,') for Date (',l_date,')'
           END IF                   
       END IF         
  end for           
end for
whenever any error stop


Here, the first loop works fine in both BDS and Genero as the SQL prepare appears to cater for the fact that the DB expects the date in MM/DD/YYYY format and adjusts accordingly - as constructed statements also appear to do.
In the second loop, BDS again appears to work, but Genero gets a -6372, as it seems not to adjust the date to the MM/DD/YYYY format, so any date above the 12th of the month causes an error.

Again, I am not saying this is an issue with Genero, just a difference between BDS and Genero and this is presumably an odd set of circumstances where the DBDATE field then doesn't match the date format required by the database.

[Sebastien F.]

Hello Stephen,

Yes, BDS and Genero SQL Server drivers behave differently regarding date literals in SQL.

BDS drivers were converting any date-looking string literal to a native target database date literal, but this was a mistake, because we cannot arbitrary change a string literal without knowing the semantic context. Some may want to store exactly something like "23/12/2004" in a CHAR column, and that literal was also converted in BDS.

Please read this:
https://4js.com/techdocs/fjs-fglgws-manual/#c_fgl_sql_programming_081.html

If you can't review the code, you must adapt the date formatting settings in SQL Server to match DBDATE...

Note that CONSTRUCT generates database-specific literals (set FGLSQLDEBUG to see what is generated), so there is not issue with that case.

Seb

[Stephen T.]

Seb,
I agree that Genero can't (shouldn't) do anything other than what it is doing with date 'literals' as I can't see how you would know what someone intended with sayu '01/07/2013'. As it turned out we scanned all the prepared pieces of code and have so far only found a dozen or so cases where a change was required and created a function that looks at DBDATE and the DBASEDATE (new env var defaulting here to MDY4/) formats and convert accordingly.

As I said before, the issues in the migration all appear (so far) to be Genero doing the 'right' thing - but the time has been in working out what is going on and then proving the issues. So I hope this little section gives others a helping hand - and helps in shortening that time.

I'm just about to raise another - but in this one I'm not so sure now that this is a migration issue. I'll raise it anyway (it's pretty obscure) and highly unlikely that anyone else will hopefully hit it. Let me know if you think it should be removed!

[Sebastien F.]

Any feedback is welcome please do not hesitate.
Seb