Lazarus

Programming => Databases => Topic started by: Vodnik on March 06, 2018, 02:47:19 pm

Title: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 06, 2018, 02:47:19 pm
Hello,
This fragment of code cause exception:

  try
    FormDB.DBquery.Close;
    FormDB.DBquery.SQL.text:='select (INTERVAL(0:0:0) HOUR TO second + callend UNITS second)  from callrecord;';
    FormDB.DBquery.Open;
    StatusBar.SimpleText:='Data read from DB';
  except
    on E: Exception do begin
      FormDB.DBquery.Close;
      CheckBoxConnected.Checked:=False;
      StatusBar.SimpleText:=E.message;
      ShowMessage(E.message);
    end;
  end;

while the same SQL query
select (INTERVAL(0:0:0) HOUR TO second + callend UNITS second)  from callrecord;
works fine from SQL query tool with the same DSN.

Error text:

Could not prepare statement. ODBC error details: LastReturnCode: SQL_ERROR; Could not get error message: An invalid parameter was passed to SQLGetDiagRec/Field

Lazarus 1.8.0, FPC 3.0.4 x32, Windows 7, DB Informix via ODBC.
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 06, 2018, 03:09:24 pm
Did you setup the TODBCConnection correctly?
Could you press "Connected" in the object inspector of that ODBCConnection1 and make it active?
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 06, 2018, 03:48:14 pm
Yes, it work fine with other SQL queries, e.g.

    FormDB.DBquery.SQL.text:='select callid as Call_ID, callstart as Call_Start, callend as Duration, origin as Calling_Num, origindestination as Called_Num, (callstart + sitetzoffset units minute) as Call_Start, callend as Duration  from callrecord';

successfully produce output for DBGrid
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 06, 2018, 04:37:32 pm
Ok, then it must be the statement.

I find INTERVAL(0:0:0) a really strange SQL-term (but I'm not familiar with DB Informix).

If FPC is trying to parse that, it could stumble too.
You could try setting ParseSQL to false.

Code: Pascal  [Select][+][-]
  1. FormDB.DBquery.ParseSQL := false;
  2. FormDB.DBquery.Open;
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: taazz on March 06, 2018, 04:54:11 pm
Quote from: rvk on March 06, 2018, 04:37:32 pm
Ok, then it must be the statement.

I find INTERVAL(0:0:0) a really strange SQL-term (but I'm not familiar with DB Informix).

If FPC is trying to parse that, it could stumble too.
You could try setting ParseSQL to false.

Code: Pascal  [Select][+][-]
  1. FormDB.DBquery.ParseSQL := false;
  2. FormDB.DBquery.Open;
+1
The colon character <:> is used to define parameters in sql by SQLDB is it possible to enclose the 0:0:0 in quotes or something along those lines? Setting ParseSQL to false might help but I'm not sure.
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 06, 2018, 08:51:01 pm
Well, setting FormDB.DBquery.ParseSQL := false; didn't help.
I have tried another query, just to check that Lazarus can deal with INTERVAL:
   
Code: Pascal  [Select][+][-]
  1. FormDB.DBquery.SQL.text:='select (disconnecttime - callstart) from callrecord;';
(this is subtraction of two DATETIME values => INTERVAL)
but this have caused exception, too:
Column has an unknown or unsupported column type.
Datasource dependent type name:INTERVAL(8 ) TO FRACTION(3).
ODBC SQL data type code: 110
while the same query performs OK to the same DSN from SQL query tool.
I have a bad feeling that Lazarus can't deal with Informix INTERVALs.
That will be a tragedy.
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 06, 2018, 09:48:48 pm
I have found a workaround:
Code: Pascal  [Select][+][-]
  1.     FormDB.DBquery.SQL.text:='select TO_CHAR(TODAY::DATETIME YEAR TO SECOND + callend UNITS SECOND, ''%T'') from callrecord;';
  2.  
This produce the same result as my initial example, with no exception rising.
As I understand, this query results to string, that Lazarus can process.
Not what I want, but something.
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 07, 2018, 10:00:05 am
Code snippet below is from fpc/packages/fcl-db/src/sqldb/odbc/odbcconn.pas
It seems like the SQL_INTERVAL_* are not fully implemented (yet). It's also on the wiki:
Quote
What is left to be implemented:
proper transaction support; currently each connection corresponds to one transaction some field types
SQL_TYPE_UTC* (these are mentioned in the ODBC docs, but seem not to be used in implementations)
SQL_INTERVAL_* (what would be the corresponding TFieldType?)
SQL_GUID (TGUIDField was not implemented, until recently)
(http://wiki.freepascal.org/ODBCConn)

So the question would be what kind of fieldtype this should be. I think they could be put in a ftDateTime field.

Code: Pascal  [Select][+][-]
  1. //...
  2.       SQL_LONGVARBINARY: begin FieldType:=ftBlob;       FieldSize:=BLOB_BUF_SIZE; end; // is a blob
  3.       SQL_TYPE_DATE:     begin FieldType:=ftDate;       FieldSize:=0; end;
  4.       SQL_SS_TIME2,
  5.       SQL_TYPE_TIME:     begin FieldType:=ftTime;       FieldSize:=0; end;
  6.       SQL_TYPE_TIMESTAMP:begin FieldType:=ftDateTime;   FieldSize:=0; end;
  7. {      SQL_TYPE_UTCDATETIME:FieldType:=ftUnknown;}
  8. {      SQL_TYPE_UTCTIME:    FieldType:=ftUnknown;}
  9. {      SQL_INTERVAL_MONTH:           FieldType:=ftUnknown;}
  10. {      SQL_INTERVAL_YEAR:            FieldType:=ftUnknown;}
  11. {      SQL_INTERVAL_YEAR_TO_MONTH:   FieldType:=ftUnknown;}
  12. {      SQL_INTERVAL_DAY:             FieldType:=ftUnknown;}
  13. {      SQL_INTERVAL_HOUR:            FieldType:=ftUnknown;}
  14. {      SQL_INTERVAL_MINUTE:          FieldType:=ftUnknown;}
  15. {      SQL_INTERVAL_SECOND:          FieldType:=ftUnknown;}
  16. {      SQL_INTERVAL_DAY_TO_HOUR:     FieldType:=ftUnknown;}
  17. {      SQL_INTERVAL_DAY_TO_MINUTE:   FieldType:=ftUnknown;}
  18. {      SQL_INTERVAL_DAY_TO_SECOND:   FieldType:=ftUnknown;}
  19. {      SQL_INTERVAL_HOUR_TO_MINUTE:  FieldType:=ftUnknown;}
  20. {      SQL_INTERVAL_HOUR_TO_SECOND:  FieldType:=ftUnknown;}
  21. {      SQL_INTERVAL_MINUTE_TO_SECOND:FieldType:=ftUnknown;}
  22.       SQL_GUID:          begin FieldType:=ftGuid;       FieldSize:=38; end; //SQL_GUID defines 36, but TGuidField requires 38
  23.       SQL_SS_VARIANT:    begin FieldType:=ftVariant;    FieldSize:=0; end;
  24.     else
  25.       begin FieldType:=ftUnknown; FieldSize:=ColumnSize; end
  26.     end;
  27. //...
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 13, 2018, 10:25:43 am
RVK, thank you for clarification of situation!
FieldType of dtDateTime  seems good idea, but here is the source of my doubts:
Code: MySQL  [Select][+][-]
  1. select datetime (2018-03-14 12:00:00) year to second - datetime (2018-03-12 12:00:00) year to second  from sites;
This SQL query results in interval (2 00:00:00) day to second. Can it be interpreted with dtDateTime? My understanding is that dtDateTime interpretation correlates with century and can't simultaneously hold 2018-03-14 12:00:00 and 2 00:00:00 (different centures)
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 13, 2018, 11:45:26 am
Quote from: Vodnik on March 13, 2018, 10:25:43 am
My understanding is that dtDateTime interpretation correlates with century and can't simultaneously hold 2018-03-14 12:00:00 and 2 00:00:00 (different centures)
It can. One will be 2018-03-14 12:00:00 and the other will be 1900-01-01 00:00:00 (usually 1890-12-30 00:00:00 is date "zero").

If I set TField.Value := 2; (2 is the day with fractional part as time) I get 1900-01-01 00:00:00 in my database.

There is no other way to do it because "interval" is a foreign subject to many programming languages. Otherwise you need to cast it to some other variable yourself (like seconds from a certain date/time).
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 14, 2018, 07:55:34 am
Well, it seems that there is no other way to deal with INTERVAL but to write my implementation for it, that is not a good idea for beginner. Or either switch my project development to Delphi... :(
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 14, 2018, 10:08:40 am
Quote from: Vodnik on March 14, 2018, 07:55:34 am
Well, it seems that there is no other way to deal with INTERVAL but to write my implementation for it, that is not a good idea for beginner.
You could also request a change in the bug-tracker (feature request). (not sure if it's already in there)

Quote
Or either switch my project development to Delphi... :(
Doesn't Delphi interpret INTERVAL as just a TStringField?
In that case you're just as well off using a CAST to string in your queries.
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 14, 2018, 12:46:55 pm
Quote
Doesn't Delphi interpret INTERVAL as just a TStringField?
In that case you're just as well off using a CAST to string in your queries.

Can it be?! Then I have to find this out.
If so, how it can be possible to INSERT value of type INTERVAL into TABLE?
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 14, 2018, 01:05:27 pm
Quote from: Vodnik on March 14, 2018, 12:46:55 pm
If so, how it can be possible to INSERT value of type INTERVAL into TABLE?
In Delphi the string should be converted back to INTERVAL by the database.
Quote
Oracle recognizes literals for other ANSI interval types and converts the values to Oracle interval values.
(I guess it would be the same for DB Informix ODBC)
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: Vodnik on March 27, 2018, 10:43:07 pm
Quote from: rvk on March 14, 2018, 01:05:27 pm
Quote from: Vodnik on March 14, 2018, 12:46:55 pm
If so, how it can be possible to INSERT value of type INTERVAL into TABLE?
In Delphi the string should be converted back to INTERVAL by the database.

Then why this is not working in Lazarus?
This command I expected to work:
Code: XML  [Select][+][-]
  1. FormDB.DBquery.SQL.text:='select TODAY::DATETIME YEAR TO SECOND + callend UNITS SECOND from callrecord;';
But I have to write:
Code: Pascal  [Select][+][-]
  1. FormDB.DBquery.SQL.text:='select TO_CHAR(TODAY::DATETIME YEAR TO SECOND + callend UNITS SECOND, ''%T'') from callrecord;';
  2.  
Title: Re: SQL query to ODBC fails from my application, while fine from ODBC query tool
Post by: rvk on March 27, 2018, 11:32:29 pm
Quote from: Vodnik on March 27, 2018, 10:43:07 pm
Quote from: rvk on March 14, 2018, 01:05:27 pm
Quote from: Vodnik on March 14, 2018, 12:46:55 pm
If so, how it can be possible to INSERT value of type INTERVAL into TABLE?
In Delphi the string should be converted back to INTERVAL by the database.
Then why this is not working in Lazarus?
This command I expected to work:
Code: XML  [Select][+][-]
  1. FormDB.DBquery.SQL.text:='select TODAY::DATETIME YEAR TO SECOND + callend UNITS SECOND from callrecord;';
But I have to write:
Code: Pascal  [Select][+][-]
  1. FormDB.DBquery.SQL.text:='select TO_CHAR(TODAY::DATETIME YEAR TO SECOND + callend UNITS SECOND, ''%T'') from callrecord;';
  2.  
Because that's not an INSERT statement and we were talking about insert automatically converting string to INTERVAL. At least that's what you asked.
TinyPortal © 2005-2018