[SOLVED - missing {$H+} dirctv] TQuery.SQL.Text strangly cut off
lazarus / 16-Databases

----------------------------------------------------------------------
T90 · 2011-08-25 22:02

Hello,

I want to Open this SQL query and I have written it in Lazarus 0.9.30
like so:

QueryObjects.SQL.Text := 'SELECT Object.ID AS ObjID, AccountNumber, '
+
                    'ObjectName, RegionID, Region.ID, '+
                    'Region.Name AS RegionName, ObjectSS.ObjectNumber
AS ObjectNum '+
                    'FROM Object ' +
                    'LEFT JOIN Region ON Object.RegionID = Region.ID
'+
                    'LEFT JOIN ObjectSS ON Object.ID =
ObjectSS.ObjectID '+
                    'WHERE Object.ID IN ('+ SQLText +');';

It does not Opens because of an error. I debuged a little bit and
found out that the query text is cutted off and the text reaching the
mysql server is:

SELECT Object.ID AS ObjID, AccountNumber, ObjectName, RegionID,
Region.ID, Region.Name AS RegionName, ObjectSS.ObjectNumber AS
ObjectNum FROM Object LEFT JOIN Region ON Object.RegionID = Region.ID
LEFT JOIN ObjectSS ON Object.ID = ObjectSS.ObjectID WHERE

And that's it - there is nothing after the WHERE clause. What am I
doing wrong.

Thanks.

----------------------------------------------------------------------
BigChimp · 2011-08-25 22:52

Could be that either MySQL or the FPC database components have a
maximum length for an SQL statement - seems like the cutoff is after
255 characters, so that might well be the case...

----------------------------------------------------------------------
fredycc · 2011-08-26 01:25

Try using "Add", for me works for a complex query's.

This is a little example

        SQLQuery2.SQL.Clear;
        SQLQuery2.SQL.Add('INSERT INTO TRANCAJ');
        SQLQuery2.SQL.Add('(CLAVE_SUC,CLAVE_SOC,TIPO_OPER_TRCA,NUM_CTA
_TCAJ,IMPORTE_TCAJ)');
        SQLQuery2.SQL.Add(Format('VALUES
(%d,%d,''%s'',%d,%d,%d,%d,%d,%f)',
                                       
[SQLQuery1.FieldByName('CLAVE_SUC').AsInteger,
                                       
 SQLQuery1.FieldByName('CLAVE_SOC').AsInteger,
                                       
 SQLQuery1.FieldByName('TIPO_OPER_TRCA').AsInteger,
                                       
 SQLQuery1.FieldByName('NUM_CTA_TCAJ').AsInteger,
                                       
 SQLQuery1.FieldByName('IMPORTE_TCAJ').AsFloat]));

        SQLQuery2.ExecSQL; 


Regards

----------------------------------------------------------------------
Lacak2 · 2011-08-26 05:22

Interesting.
Can you provide some test application, which can we run and test.

----------------------------------------------------------------------
T90 · 2011-08-26 07:48

Quote from: BigChimp on August 26, 2011, 12:52:14 amCould be that
either MySQL or the FPC database components have a maximum length for
an SQL statement - seems like the cutoff is after 255 characters, so
that might well be the case...

The component I am using is TDBGrid. The SQL I provided is used in
another form with the same components. There this code executes
normally which is the strangest thing. I am begining to wonder if my
installation of Lazarus is corrupted...

Fredycc, I used to try with Add, but there are errors as well.

Lacak2, sorry I can not upload appropriate test app.

----------------------------------------------------------------------
BigChimp · 2011-08-26 07:57

Quote from: T90 on August 26, 2011, 09:48:31 amLacak2, sorry I can not
upload appropriate test app.
Shame... a lot less chance of a fix that way

----------------------------------------------------------------------
Lacak2 · 2011-08-26 08:32

May be, when thinking about 255 limit, that problem is related to
using shortstrings instead of longstrings.

Look please at your units, if they have in the begining :
{$H+}

Or check if you have in Project options
"Use Ansi strings (-Sh)"

----------------------------------------------------------------------
T90 · 2011-08-26 09:10

Quote from: Lacak2 on August 26, 2011, 10:32:40 amMay be, when
thinking about 255 limit, that problem is related to using
shortstrings instead of longstrings.

Look please at your units, if they have in the begining :
{$H+}

Or check if you have in Project options
"Use Ansi strings (-Sh)"

It worked. I didn't have the {$H+} directive. Thank you, Lacak2. Thank
you, all.