Error on executing sqlquery with parameters (:param)

[mojontp]

Hi forum,
i discovered recently a problem with the TSqlQuery and its parameters.
I just that its my fault of usage. Otherwise i will run into serious problems with this bug.
ok. here my case:
I use a sql code like

Code: [Select]

query.sql.text:='INSERT INTO TABLENAME (FIELD1, FIELD2) VALUES (:VAL1, :VAL2)';
query.Params.ParamByName('VAL1').AsString:=self.somevalue;
query.ExecSql; //<<<<<<< Access violation


my lazarus is 0.9.24 beta
fpc: 2.2.0
component sqldb from the package sqldblaz: version 1.0

if i avoid to use parameters, everything goes well. But otherwise i dont know how to store a long text into a blob field.

i hope there is some patch or work-around or even just some hints!
thank you!

[tatamata]

:=self.somevalue; ???

[mojontp]

yes. just some variable.
its safe and has a value. for sure....

even if i use a hard coded string, the error occurs afterwards.


thanks

[Loesje]

fpc 2.2.0? Try 2.2.2. Or try to add spaces before and after the parameter-names:

Code: [Select]

uery.sql.text:='INSERT INTO TABLENAME (FIELD1, FIELD2) VALUES ( :VAL1 , :VAL2 )';

[mojontp]

ok. i updated to fpc 2.2.2 after moneying around with lazarus and alle the paths.... i actually hate it to install fpc and lazarus... but its another topic...

i found that if i use parameters, the transaction connected to my TSQLQuery, has to be connected to db TIbconnection object.

here an example

Code: [Select]

ibDb:=TIBConnection.Create(nil);
    ibDb.Dialect:=3;
    ibDb.DatabaseName:=ConData.folder;
    ibDb.Password:=conData.password;     
    ibDB.UserName:=conData.username;

    SQLTRANS:=TSQLTransaction.Create(nil);
    SQLTRANS.DataBase:=ibDb;

    ibDb.Transaction:=SQLTRANS; //<<<<<<<<<<<< THIS ONE!

    ibSQL:=TSQLQuery.Create(nil);
    ibSQL.DataBase:=ibDB;
    ibSQL.Transaction:=SQLTRANS; 
if i unset the market line, everything works fine until i use parameters in my queries....

its not so pleasant, because i created JUST one instance of TIbconnectino in my application and many transactions and tsqlQueries (every action that wants to use the db, creates a new transaction and a new tsqlquery).

ok my actual work around is now to set the TIbconnection instance to global and assign the used transaction to it, just before query.execSql.

Meanwhile ill hope, that this strange behaviour will be fixed some time...

thanks for help!!!

greetings

[Loesje]

Sounds strange to me, what's the error-message you got?

And you're sure that the problem isn't that you have ParseSQL to true or UsePrimaryKeyAsKey to true?

[mojontp]

well, the workaround helped to avoid the problem.

what do you  mean by parsesql and useasprimarykey? how could i use them to avoid the error?

thanks a lot!

[Loesje]

Those are properties of TSQLQuery. Just give them another value. (I think that you already did that, and that that was what solved your problem, and not the transaction-line.)

So please try, and tell what the error message is that you got. You only told that it 'didn't work'

[mojontp]

Hi Loesje,
i wrote the error message in the source here.
its "just" an "Access violation". .... 

i will try the tricks today.
but i remember, that i tried the prepare method before. but maybe it works together with parse.
i will post the result here as soon i get something.

thank you!

[Bahman Movaqar]

Those are properties of TSQLQuery. Just give them another value. (I think that you already did that, and that that was what solved your problem, and not the transaction-line.)

So please try, and tell what the error message is that you got. You only told that it 'didn't work'

Exactly right!  I faced the same problem and your hint was helpful.  Thanks.

I wonder what's the reason?

Bahman

[Loesje]

Blob-parameters were indeed using the transaction of the connection instead of the transaction of the query itself. It's fixed in fpc-trunk, r12679

[clauslack]

try with "add" and Lazarus 0.9.26.3

query.sql.clear;
query.sql.add:='INSERT INTO TABLENAME (FIELD1, FIELD2) VALUES (:VAL1, :VAL2)';
query.Params.ParamByName('VAL1').AsString:='aaaa';
query.Params.ParamByName('VAL1').AsString:='bbbb';
query.ExecSql;

[v00d00]

Hi everyone. Have the same problem. I am using Lazarus 0.9.26-4.

this code throws the access violation exception


^{procedure TRL.SaveToDB
var db:TDbZugriff;
berL:String;
begin
db:=TdbZugriff.Create(nil);
 db.query.SQL.Clear;
    showLog('TRl.SaveToDb update clear');
    db.query.SQL.Add ('UPDATE RL_LISTE SET PLZLISTE = :PLZLISTE1 ,'+
     ' BERLEITER= '+berL+', '+
     ' VPUID= '+IntToStr(self.vp.uid)+' '+
     ' WHERE UID= '+IntToStr(self.UID));
    showLog('TRl.SaveToDb update add');
    db.Query.Prepare;
    showLog('TRl.SaveToDb update prepare');
    //db.Query.Transaction := SQLTRANS;
    db.Query.Params.ParamByName('PLZLISTE1').AsBlob:=self.encodePlzListe;
    showLog('TRl.SaveToDb update param');
    db.query.ExecSQL; // << this line throws the exception
    showLog('TRl.SaveToDb update exec');}     


The query is executed and data stores to db. But exception still rises.
There is a TDBZugriff constructor code

^{Constructor TDbZugriff.Create(Owner:TComponent);
  begin
  inherited Create;  //TObject erstellen und Speicher zuweisen
  self.Query:=TSQLQuery.Create(Owner);
  self.Trans:=TSQLTransaction.Create(Owner);
  self.Trans.Database:=dmData.ibDb;
  self.Query.Transaction:=self.Trans;
  self.Query.DataBase:=dmData.ibDb;
  //test:
  //dmData.ibDb.  Transaction:=self.Trans;
  if not dmData.ibDb.Connected then dmData.ibDb.Connected:=true;
  end;}


and there is a idDB global variable code

   <sup>ibDb:=TIBConnection.Create(nil);
    ibDb.Dialect:=3;
    ibDb.DatabaseName:=ConData.folder; {'/home/psadmin/fpc/svn/DB/NATURBALANCE.gdb';  }
    ibDb.Password:=conData.password; {'951186';}
    ibDB.UserName:=conData.username; {'SYSDBA';}


    SQLTRANS:=TSQLTransaction.Create(nil);
    SQLTRANS.DataBase:=ibDb;

    ibDb.Transaction:=SQLTRANS;

    ibSQL:=TSQLQuery.Create(nil);
    ibSQL.DataBase:=ibDB;
    ibSQL.Transaction:=SQLTRANS;</sup>