Recent

Author Topic: Error on executing sqlquery with parameters (:param)  (Read 20504 times)

mojontp

  • New member
  • *
  • Posts: 6
Error on executing sqlquery with parameters (:param)
« on: January 11, 2009, 03:36:13 pm »
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

Quote
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!
« Last Edit: January 11, 2009, 03:47:51 pm by mojontp »

tatamata

  • Hero Member
  • *****
  • Posts: 736
    • ZMSQL - SQL enhanced in-memory database
Re: Error on executing sqlquery with parameters (:param)
« Reply #1 on: January 11, 2009, 04:34:45 pm »
:=self.somevalue; ???

mojontp

  • New member
  • *
  • Posts: 6
Re: Error on executing sqlquery with parameters (:param)
« Reply #2 on: January 11, 2009, 04:45:55 pm »
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

  • Full Member
  • ***
  • Posts: 165
    • Lazarus Support website
Re: Error on executing sqlquery with parameters (:param)
« Reply #3 on: January 15, 2009, 11:53:07 am »
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

  • New member
  • *
  • Posts: 6
Re: Error on executing sqlquery with parameters (:param)
« Reply #4 on: January 20, 2009, 02:11:04 pm »
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

  • Full Member
  • ***
  • Posts: 165
    • Lazarus Support website
Re: Error on executing sqlquery with parameters (:param)
« Reply #5 on: January 22, 2009, 04:48:57 pm »
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

  • New member
  • *
  • Posts: 6
Re: Error on executing sqlquery with parameters (:param)
« Reply #6 on: January 22, 2009, 06:15:33 pm »
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

  • Full Member
  • ***
  • Posts: 165
    • Lazarus Support website
Re: Error on executing sqlquery with parameters (:param)
« Reply #7 on: January 23, 2009, 10:39:48 am »
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

  • New member
  • *
  • Posts: 6
Re: Error on executing sqlquery with parameters (:param)
« Reply #8 on: January 23, 2009, 10:48:31 am »
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

  • Newbie
  • Posts: 1
Re: Error on executing sqlquery with parameters (:param)
« Reply #9 on: January 26, 2009, 08:18:48 am »
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

  • Full Member
  • ***
  • Posts: 165
    • Lazarus Support website
Re: Error on executing sqlquery with parameters (:param)
« Reply #10 on: February 03, 2009, 10:46:28 pm »
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

  • Sr. Member
  • ****
  • Posts: 275
Re: Error on executing sqlquery with parameters (:param)
« Reply #11 on: April 30, 2009, 10:48:03 pm »
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

  • Newbie
  • Posts: 1
Re: Error on executing sqlquery with parameters (:param)
« Reply #12 on: July 14, 2009, 10:01:51 am »
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

   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;               



 

TinyPortal © 2005-2018