Recent

Author Topic: SQL query to ODBC fails from my application, while fine from ODBC query tool  (Read 2506 times)

Vodnik

  • New member
  • *
  • Posts: 40
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.
« Last Edit: March 06, 2018, 03:05:31 pm by Vodnik »

rvk

  • Hero Member
  • *****
  • Posts: 3505
Did you setup the TODBCConnection correctly?
Could you press "Connected" in the object inspector of that ODBCConnection1 and make it active?

Vodnik

  • New member
  • *
  • Posts: 40
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

rvk

  • Hero Member
  • *****
  • Posts: 3505
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;

taazz

  • Hero Member
  • *****
  • Posts: 5362
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Vodnik

  • New member
  • *
  • Posts: 40
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.
« Last Edit: March 06, 2018, 09:10:45 pm by Vodnik »

Vodnik

  • New member
  • *
  • Posts: 40
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.

rvk

  • Hero Member
  • *****
  • Posts: 3505
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. //...

Vodnik

  • New member
  • *
  • Posts: 40
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)

rvk

  • Hero Member
  • *****
  • Posts: 3505
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).

Vodnik

  • New member
  • *
  • Posts: 40
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... :(

rvk

  • Hero Member
  • *****
  • Posts: 3505
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.

Vodnik

  • New member
  • *
  • Posts: 40
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?

rvk

  • Hero Member
  • *****
  • Posts: 3505
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)

Vodnik

  • New member
  • *
  • Posts: 40
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.