Literal Date Selection From Database

[Stephen T.]

FGL 3.00.04 GDC 3.00.04 Postgresql 9.4.6

I have an old piece of code that has most likely been around since version 1.1. The code built a record area from one table for inserting into another. Within the select, one of the fields was a literal date - such as '01/01/1900'. This must have worked fine at the time, as the target table was populated.
Now, using 3.00 and Postgresql 9.4, if I select a literal into a date field, the date field is 'blanked' - BUT if I select a literal date field cast as a date it works ok. So I have a workaround.

The question then is, was there a change in this behaviour at some point and is what I am seeing then expected behaviour? I did look back through the docs for 3.00 that detail the changes but missed this change.

The code to show the issue:
DEFINE l_string                        STRING
DEFINE l_rec                           RECORD
        someDate                        DATE,
        someOtherDate                   DATE
        END RECORD

    CALL ext_open_database('','')

    LET l_string                        =
        'SELECT \'01/01/1900\',\'01/01/2016\'::DATE ',
        'FROM blankTable ',
        'WHERE recno    = 1'

    PREPARE p_readDate                  FROM l_string
    DECLARE c_readDate                  CURSOR FOR p_readDate

    FOREACH c_readDate                  INTO l_rec.*
        DISPLAY 'date (',l_rec.someDate,') other Date (',l_rec.someOtherDate,')'
    END FOREACH

    CLOSE DATABASE

....someDate is blank, but someOtherDate is populated OK. No error is thrown.

[Sebastien F.]

Hello Stephen,

What do you mean by version "1.1"?

As far as I can remember, with PostgreSQL, fetching a string representing a date into a DATE variable always required the date string to be in ISO format "YYYY-MM-DD".

Try with "1900-01-01" in your sample.

Yes, maybe the fglrun should complain when the string cannot be converted to a DATE...

Obviously, it's always better to make sure that you fetch a "real" date value from the DB server, and be only dependent from server date format settings (in your sample you do this with a ::CAST operator)

Just use real date columns or cast to ::DATE as you did.

This is not a workaround, it's safe programming.

Seb

[Stephen T.]

Version 1.1 - should have been Genero 1.10. IE code from the very early days.

I did try reversed date format and double checked the postgresql.conf file for the datestyle setting - iso,dmy. A literal date, both dmy and ymd  format, in the source SQL returns blank  - see (FGLSQLDEBUG):
SQL: OPEN
| 4gl source      : tester.4gl line=4256
| pgs.c:01513(3)  : Nat stmt1 = declare cu1 cursor with hold for select '01/01/1900','1900/01/01','01/01/2016'::DATE from   blankTable where recno    = 1
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| Execution time  :   0 00:00:00.00038
SQL: FETCH
| 4gl source      : tester.4gl line=4256
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| into(tmp): 3
|  t:                          DATE f:04 v:""
|  t:                          DATE f:04 v:""
|  t:                          DATE f:04 v:"01/01/2016"
| Execution time  :   0 00:00:00.00008
date (          ) YYYYMMDD Date (          ) other Date (01/01/2016)
SQL: FETCH
| 4gl source      : tester.4gl line=4256
| sqlcode         : 100
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| into(tmp): 3
|  t:                          DATE f:04 v:""
|  t:                          DATE f:04 v:""
|  t:                          DATE f:04 v:"01/01/2016"
| Execution time  :   0 00:00:00.00007

If the above is what you would expect now, then no issue. I just thought I'd report it in case it wasn't expected behaviour.


Steve

[Sebastien F.]

Steve,
Please try "1900-01-01" using (-) minus as separator instead of (/) slash.
Seb

[Stephen T.]

Seb,
Sorry - yes 1900-01-01 works ok.

The original code (that appears to have worked at some point and with some mix of Genero/Postgresql versions) though used a d/m/yyyy format.

Steve

[Sebastien F.]

ok...!
Maybe old Genero versions accepted both DBDATE and ISO formatted dates to fill the DATE variable from the fetched string...
Anyway, avoid using strings representing dates in SQL, just use real date values and you are no longer dependent from date format.
Seb

[Reuben B.]

Stephen,

If you are curious, feel free to download the old versions of Genero from the web-site and see if you can track what version the behaviour changed, if it did.  Going back as far as Genero 1.1 you are also looking at different versions of the database as well and perhaps your datestyle setting was different back then, there is potentially more than just Genero changing between now and then.

If you can show the change was between 2.5 and 3.0, then we would most likely look more closely, even though the code is doing what we'd expect.

Reuben

[Stephen T.]

Reuben,
No, I'm not that fussed. It only cropped up as I was using an old piece of code to create and populate a table, and what I assumed was working code, then failed - but without an error being thrown.

Just out of idle curiosity, I have just tried select a literal into a non compatible typed target ('A' into INT) and that also appears to just return blank rather than throw an error - so am I right in assuming that any miscast literal will simply return blank from the SQL interface?

[Sebastien F.]

Stephen,

I cannot reproduce your issue.

When fetching a string to an integer, I get a conversion error -1203.

See attachment (please try this code)

Result with Informix:

Code (genero) Select Expand

varchar to integer  :       -1213
varchar to date     :       -1205
varchar to decimal  :       -1213
date to integer     :           0
date to decimal     :           0
decimal to date     :           0
decimal to integer  :           0

Result with PostgreSQL:

Code (genero) Select Expand


varchar to integer  :       -1213
varchar to date     :           0
varchar to decimal  :       -1213
date to integer     :           0
date to decimal     :       -1213
decimal to date     :           0
decimal to integer  :           0

However, varchar to date should raise an error => please contact you support to register a bug in the PostgreSQL driver.

Seb

[Stephen T.]

Seb,
I've probably done something wrong then!
The code was as per the date test - but with a an 'A' being read into an integer:
DEFINE l_string                        STRING
DEFINE l_rec                           RECORD
        someInt                         INTEGER,
        SOMEDollarDate,
        someDate,
        someDateYYYYMMDD,
        someOtherDate                   DATE
        END RECORD

    CALL ext_open_database('','')

WHENEVER ANY ERROR STOP
    LET l_string                        =
        'SELECT \'A\',$$1900-01-01$$,\'01-01-1900\',\'1900-01-01\',\'01-01-2016\'::DATE ',
        'FROM blankTable ',
        'WHERE recno    = 1'

    PREPARE p_readDate                  FROM l_string
    DECLARE c_readDate                  CURSOR FOR p_readDate


    FOREACH c_readDate                  INTO l_rec.*
        DISPLAY 'Status (',status,') SQL Status (',sqlca.sqlerrd[2],') integer (',l_rec.someInt,') dollarDate (',l_rec.someDollarDate,') date (',l_rec.someDate,') YYYYMMDD Date (',l_rec.someDateYYYYMMDD,') other Date (',l_rec.someOtherDate,')'
    END FOREACH

    CLOSE DATABASE


....which gave the following SQL DEBUG....
SQL: PREPARE
| 4gl source      : tester.4gl line=4256
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu0' (fglname='p_readdate',module='tester')
|   fgl stmt      : SELECT 'A',$$1900-01-01$$,'01-01-1900','1900-01-01','01-01-2016'::DATE FROM blankTable WHERE recno    = 1
|   sql stmt      : SELECT 'A',$$1900-01-01$$,'01-01-1900','1900-01-01','01-01-2016'::DATE FROM blankTable WHERE recno    = 1
| Execution time  :   0 00:00:00.00005
SQL: DECLARE PREPARED
| 4gl source      : tester.4gl line=4257
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
|   scroll cursor : 0
|   with hold     : 0
| Execution time  :   0 00:00:00.00003
SQL: OPEN
| 4gl source      : tester.4gl line=4260
| pgs.c:01513(3)  : Nat stmt1 = declare cu1 cursor with hold for select 'A',$$1900-01-01$$,'01-01-1900','1900-01-01','01-01-2016'::DATE from   blankTable where recno    = 1
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| Execution time  :   0 00:00:00.00036
SQL: FETCH
| 4gl source      : tester.4gl line=4260
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| into(tmp): 5
|  t:                       INTEGER f:04 v:""
|  t:                          DATE f:04 v:"01/01/1900"
|  t:                          DATE f:04 v:""
|  t:                          DATE f:04 v:"01/01/1900"
|  t:                          DATE f:04 v:"01/01/2016"
| Execution time  :   0 00:00:00.00012
Status (          0) SQL Status (          0) integer (           ) dollarDate (01/01/1900) date (          ) YYYYMMDD Date (01/01/1900) other Date (01/01/2016)
SQL: FETCH
| 4gl source      : tester.4gl line=4260
| sqlcode         : 100
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| into(tmp): 5
|  t:                       INTEGER f:04 v:""
|  t:                          DATE f:04 v:"01/01/1900"
|  t:                          DATE f:04 v:""
|  t:                          DATE f:04 v:"01/01/1900"
|  t:                          DATE f:04 v:"01/01/2016"
| Execution time  :   0 00:00:00.00008
SQL: CLOSE
| 4gl source      : tester.4gl line=4261
| pgs.c:01473(2)  : Nat: close cu1
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[dev_gyb])
| sql cursor      : ident='cu1' (fglname='c_readdate',module='tester')
| Execution time  :   0 00:00:00.00004
SQL: CLOSE DATABASE


Steve

[Sebastien F.]

Please contact the support channel so we can properly investigate and follow up with this case.
Seb