SQL query to ODBC fails from my application, while fine from ODBC
query tool
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2018-03-06 13:47

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.

----------------------------------------------------------------------
rvk · 2018-03-06 14:09

Did you setup the TODBCConnection correctly?
Could you press "Connected" in the object inspector of that
ODBCConnection1 and make it active?

----------------------------------------------------------------------
Vodnik · 2018-03-06 14:48

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 · 2018-03-06 15:37

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FormDB.DBquery.ParseSQL := false;FormDB.DBquery.Open;

----------------------------------------------------------------------
taazz · 2018-03-06 15:54

Quote from: rvk on March 06, 2018, 04:37:32 pmOk, 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FormDB.DBquery.ParseSQL := false;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.

----------------------------------------------------------------------
Vodnik · 2018-03-06 19:51

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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.

----------------------------------------------------------------------
Vodnik · 2018-03-06 20:48

I have found a workaround:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
FormDB.DBquery.SQL.text:='select TO_CHAR(TODAY::DATETIME YEAR TO
SECOND + callend UNITS SECOND, ''%T'') from callrecord;'; 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 · 2018-03-07 09:00

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:
QuoteWhat 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //...     
SQL_LONGVARBINARY: begin FieldType:=ftBlob;      
FieldSize:=BLOB_BUF_SIZE; end; // is a blob      SQL_TYPE_DATE:    
begin FieldType:=ftDate;       FieldSize:=0; end;      SQL_SS_TIME2, 
   SQL_TYPE_TIME:     begin FieldType:=ftTime;       FieldSize:=0;
end;      SQL_TYPE_TIMESTAMP:begin FieldType:=ftDateTime;  
FieldSize:=0; end;{      SQL_TYPE_UTCDATETIME:FieldType:=ftUnknown;}{
    SQL_TYPE_UTCTIME:    FieldType:=ftUnknown;}{    
 SQL_INTERVAL_MONTH:           FieldType:=ftUnknown;}{    
 SQL_INTERVAL_YEAR:            FieldType:=ftUnknown;}{    
 SQL_INTERVAL_YEAR_TO_MONTH:   FieldType:=ftUnknown;}{    
 SQL_INTERVAL_DAY:             FieldType:=ftUnknown;}{    
 SQL_INTERVAL_HOUR:            FieldType:=ftUnknown;}{    
 SQL_INTERVAL_MINUTE:          FieldType:=ftUnknown;}{    
 SQL_INTERVAL_SECOND:          FieldType:=ftUnknown;}{    
 SQL_INTERVAL_DAY_TO_HOUR:     FieldType:=ftUnknown;}{    
 SQL_INTERVAL_DAY_TO_MINUTE:   FieldType:=ftUnknown;}{    
 SQL_INTERVAL_DAY_TO_SECOND:   FieldType:=ftUnknown;}{    
 SQL_INTERVAL_HOUR_TO_MINUTE:  FieldType:=ftUnknown;}{    
 SQL_INTERVAL_HOUR_TO_SECOND:  FieldType:=ftUnknown;}{    
 SQL_INTERVAL_MINUTE_TO_SECOND:FieldType:=ftUnknown;}      SQL_GUID: 
       begin FieldType:=ftGuid;       FieldSize:=38; end; //SQL_GUID
defines 36, but TGuidField requires 38      SQL_SS_VARIANT:    begin
FieldType:=ftVariant;    FieldSize:=0; end;    else      begin
FieldType:=ftUnknown; FieldSize:=ColumnSize; end    end;//...

----------------------------------------------------------------------
Vodnik · 2018-03-13 09:25

RVK, thank you for clarification of situation!
FieldType of dtDateTime  seems good idea, but here is the source of my
doubts:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2018-03-13 10:45

Quote from: Vodnik on March 13, 2018, 10:25:43 amMy 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 · 2018-03-14 06:55

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 · 2018-03-14 09:08

Quote from: Vodnik on March 14, 2018, 07:55:34 amWell, 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)

QuoteOr 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 · 2018-03-14 11:46

QuoteDoesn'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 · 2018-03-14 12:05

Quote from: Vodnik on March 14, 2018, 12:46:55 pmIf 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.
QuoteOracle 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 · 2018-03-27 20:43

Quote from: rvk on March 14, 2018, 01:05:27 pmQuote from: Vodnik on
March 14, 2018, 12:46:55 pmIf 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FormDB.DBquery.SQL.text:='select TODAY::DATETIME YEAR TO SECOND +
callend UNITS SECOND from callrecord;';But I have to write:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FormDB.DBquery.SQL.text:='select TO_CHAR(TODAY::DATETIME YEAR TO
SECOND + callend UNITS SECOND, ''%T'') from callrecord;';

----------------------------------------------------------------------
rvk · 2018-03-27 21:32

Quote from: Vodnik on March 27, 2018, 10:43:07 pmQuote from: rvk on
March 14, 2018, 01:05:27 pmQuote from: Vodnik on March 14, 2018,
12:46:55 pmIf 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FormDB.DBquery.SQL.text:='select TODAY::DATETIME YEAR TO SECOND +
callend UNITS SECOND from callrecord;';But I have to write:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
FormDB.DBquery.SQL.text:='select TO_CHAR(TODAY::DATETIME YEAR TO
SECOND + callend UNITS SECOND, ''%T'') from callrecord;'; 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.