Stephen T.
Posts: 114
|
« on: October 25, 2013, 11:49:39 am » |
|
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.
|