|
Title: Literal Date Selection From Database Post by: Stephen T. on March 11, 2016, 08:30:09 am 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. Title: Re: Literal Date Selection From Database Post by: Sebastien F. on March 11, 2016, 11:20:44 am 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 Title: Re: Literal Date Selection From Database Post by: Stephen T. on March 11, 2016, 12:40:02 pm 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 Title: Re: Literal Date Selection From Database Post by: Sebastien F. on March 11, 2016, 01:42:02 pm Steve,
Please try "1900-01-01" using (-) minus as separator instead of (/) slash. Seb Title: Re: Literal Date Selection From Database Post by: Stephen T. on March 11, 2016, 02:03:23 pm 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 Title: Re: Literal Date Selection From Database Post by: Sebastien F. on March 11, 2016, 02:17:35 pm 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 Title: Re: Literal Date Selection From Database Post by: Reuben B. on March 13, 2016, 11:16:32 pm 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 Title: Re: Literal Date Selection From Database Post by: Stephen T. on March 14, 2016, 09:56:58 am 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? Title: Re: Literal Date Selection From Database Post by: Sebastien F. on March 15, 2016, 10:37:58 am 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
Result with PostgreSQL: Code
However, varchar to date should raise an error => please contact you support to register a bug in the PostgreSQL driver. Seb Title: Re: Literal Date Selection From Database Post by: Stephen T. on March 15, 2016, 11:37:28 am 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 Title: Re: Literal Date Selection From Database Post by: Sebastien F. on March 15, 2016, 11:50:46 am Please contact the support channel so we can properly investigate and follow up with this case.
Seb |