Delphi Pages Forums

Delphi Pages Forums (http://www.delphipages.com/forum/index.php)
-   General (http://www.delphipages.com/forum/forumdisplay.php?f=2)
-   -   Building SQL string (http://www.delphipages.com/forum/showthread.php?t=217725)

Jung 01-30-2018 04:51 PM

Building SQL string
 
Hello,

I build SQL string in the fly which is well executed but with no data I need please, someone to tell me is my sql string is correct I use UIB to connect Firebird DB:

Code:

V1:='SELECT FIRST :NBROW * FROM VW_MVT_LIV_FOURNISSEUR WHERE MVT_ID>=:NextRow AND MVT_ID<=:EndRow';
Qry.SQL.Clear;
Qry.SQL.Text:=V1;
Qry.Params.ByNameAsInteger['NBROW']:=NbRow;
Qry.Params.ByNameAsInteger['NEXTROW']:=NextRow;
Qry.Params.ByNameAsInteger['ENDROW']:=EndRow;
Qry.Open;

Is executed without data

But if I do that without using parameters :

Code:

V1:='SELECT FIRST '+INTTOSTR(NBROW)+' * FROM VW_MVT_LIV_FOURNISSEUR WHERE MVT_ID>='+INTTOSTR(NextRow)+ ' AND MVT_ID<='+INTTOSTR(EndRow);
I get data
Can you please help me ?

Marsheng 01-30-2018 09:21 PM

You might try

V1:='SELECT FIRST '+NBROW+' * FROM VW_MVT_LIV_FOURNISSEUR WHERE MVT_ID>='+NextRow+'AND MVT_ID<='+EndRow;

or something along this line. V1 must be a text line. Can't have embedded values within it.

Also your NBROW etc must be declared before you assign V1.

Jung 01-31-2018 09:10 AM

Hello

The problem is to use query parameters which doesn't work for me using your string need the ':' for parameters. NBROW, NEXTROW, ENDROW are already declared

rojam 01-31-2018 04:00 PM

You can't use parameters in the SELECT portion of a query. You'll have to concatenate that portion of the query, then you can use parameters for the WHERE portion of the query.
Code:

V1 := 'SELECT FIRST ' + INTTOSTR(NBROW) + ' * FROM VW_MVT_LIV_FOURNISSEUR WHERE MVT_ID >= :NextRow AND MVT_ID <= :EndRow';
You can also rewrite the query using the BETWEEN condition
Code:

V1 := 'SELECT FIRST ' + INTTOSTR(NBROW) + ' * FROM VW_MVT_LIV_FOURNISSEUR WHERE MVT_ID BETWEEN :NextRow AND :EndRow';

Jung 01-31-2018 06:10 PM

Thanks rojam but your solution doesn't work
Anyway I left this method for another because I haven't much time.
Thank you very for all

Marsheng 01-31-2018 10:32 PM

I have been using PHP prepared statements. Not sure if anything can be transposed into Delphi.

https://www.w3schools.com/php/php_my...statements.asp

Jung 02-01-2018 09:04 AM

I will compare it with delphi thank Marsheng


All times are GMT. The time now is 12:47 AM.

Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.