Re: Date Problem

>Date: Thu, 30 Jan 1997 10:03:00 -0800 >From: Amita Shrivastava <bflams@uranium.bfl.soft.net> > >Hi Jonathan > >I am sorry I didn't give you complete picture. Thanks for the extra information -- maybe we start to get somewhere. >Well this problem we are facing only at our Customer place. I only have what >they have sent me. I'll reproduce it again. [You are right INTO comes before >FROM and after SELECT..sorry..]. > >1. If a field is declared as DATE in a table, and when we read it from >table to a program variable, the program variable is NULL. > >select f_date into :ws_date from tableA We don't have the table schema, nor the declaration for ws_date, nor an indication of how the errors are handled, nor an indication of how the null-ness is being tested. But there is more info below, so we'll ignore this for the time being. If you still need help from me after this, then you are going to have to provide the table schema and working ESQL/C code which illustrates the problem -- without this, I do not have the time to spend chasing red herrings. >In this query ws-date is NULL eventhouh f_date has some value. > >2. Here we have some funny ESQL/C >/* Declare Cursor to get Trailer Details */ > EXEC SQL > DECLARE trailer_location CURSOR FOR > SELECT tl_carrier, tl_trailer_no, tl_dflag, tl_truck_type,tl_date_emptied, > trail_rep.cur_row = 6; This DECLARE must be failing to compile -- the SELECT statement shown is not complete (no FROM clause, and the '=' is illegal syntax too). And why do you have the same cursor redeclared below? > create_line ( trail_bodywin, 2, 1, 75 ); >/* Call Initialise Screen */ > initialise_trailer_loc ( trail_bodywin ); > create_line ( trail_bodywin, 5, 1, 75 ); > >/* Declare Cursor to get Trailer Details */ > EXEC SQL > DECLARE trailer_location CURSOR FOR > SELECT tl_carrier, tl_trailer_no, tl_dflag, tl_truck_type,tl_date_emptied, > tl_time_emptied, tl_arrival_date, tl_arrival_time > FROM > truckmaster > WHERE > date(tl_arrival_date) BETWEEN date(from_date) AND date(to_date);> > EXEC SQL > OPEN trailer_location; > if ( sqlca.sqlcode != 0 ) > { > .................. > >tl_arrival_date is defined as char(9) in the table.[This was suggested by >our Customer] Why is tl_arrival_date a CHAR(9) field? It should be a DATE! Even if you insist on it being a CHAR field, why 9? CHAR(6) allows 970127; CHAR(8) allos 19970127 or 97/01/27, and CHAR(10) allows 1997/01/27, but what format exploits CHAR(9)? Any CHAR field uses far more storage than a DATE field (6-10 bytes vs 4 bytes). And you lose any possibility of using DBDATE (for internationalized applications), and unless you are using 4 digits for the year, your program will collapse in a heap in 2 and a bit years. Don't come running to me for help then. Are from_date and to_date table columns too? Or are they program variables? If the latter, then you're missing the colons in front of them. If the former, what data types are they? And what data types are the variables in the FETCH statement which collects the data from the trailer_location cursor? ** General commentary on submitting questions to c.d.i ** As should be clear from my questioning, you have to provide complete information for people in the c.d.i newsgroup to be able to help you, and if the problem is a run-time problem, you need to provide the minimal, compilable code that will demonstrate the problem. For example, the calls to create_line() and initialize_trailer_loc() are not material to the problem (or, if they are, they are also needed in the example code). It often takes effort to produce the minimal reproduction (and sometimes the effort of producing it will reveal the answer anyway), but even so, you should expend that effort because you are asking other people to help you at no cost to you apart from the time it takes to prepare the question and an accurate, detailed illustration of the problem. ** End General Commentary ** >if This is the condition the date( tl_arrival_date ) is NULL. How are you testing this? In the SQL, or in the ESQL/C? What format is the data in tl_arrival_date? Since it is a CHAR(9) field, it must be in a format that can be converted to a date with your current value of DBDATE. >From these queries it is very clear that date as data type and date >function as such is not a problem. Where is the problem ? Should we >define a date field as char(9) at all? No! >Background is - INFORMIX online 5.05, ESQL/C running on AIX 3.2.5 Thank you for this information. I'm reasonably confident that the problem is 'operator error' rather than 'product error'. Certainly, on the information available to us, I do not expect to find the problem is a product bug. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>