using execSQL with SQLdb
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2013-11-24 19:36

I still have problems with SQLdb.
Code: [Select]procedure TDMGebruikers.SavePassword(const user,
password : string);
var MyQuery : TSQLQuery;
begin
  MyQuery := TSQLQuery.Create(nil);
  try
    MyQuery.DataBase := DBConnection;       //TIBConnection
    MyQuery.Transaction := DBTransaction;   //TSQLTransaction
    MyQuery.SQL.Text := 'UPDATE users set password = :password' +
                        'WHERE usernaam = :name';
    MyQuery.ParamByName('name').AsString := user;
    MyQuery.ParamByName('password').AsString := password;
    MyQuery.ExecSQL;
    MyQuery.ApplyUpdates;
    DBTransaction.Commit;
  finally
    MyQuery.free;
  end;
end;
I've searched in several topics for using execSQL and SQLdb. This code
above is the result. But it occurs an error:
QuoteIBConnection : Execute :
 -Dynamic SQL Error
 -SQL error code = -303
 -arithmetic exception, numeric overflow, or string truncation
 -string right truncation.What does this error means. I can't find on
the internet what's wrong.

(I really want to get SQLdb work)

----------------------------------------------------------------------
taazz · 2013-11-24 20:24

That sound extremely like a columns results were truncated please
provide the sql command executed and of course meta info of the tables
and columns touched by the sql.

Haven't done any DB programming lately so the above comes from memory,
take care.

----------------------------------------------------------------------
luca · 2013-11-24 20:50

In my experience I use .value instead of .asString to assign string
value to parameter.

MyQuery.ParamByName('password').Value := password

Hope this help.

Luca

----------------------------------------------------------------------
BigChimp · 2013-11-24 22:52

In your SQL statement, you're missing a space after :password

Notes/questions:
1 I would use .AsString for text fields, not AsValue: that involves a
slow variant conversion
2 Why do you need to call ApplyUpdates?
3 Your commit closes the transaction. Do you want to add a
starttransaction afterwards? Or add it at the beginning of your
function(s)?

Groeten,
BigChimp

----------------------------------------------------------------------
mangakissa · 2013-12-03 19:41

@BigChimp

Quote1 I would use .AsString for text fields, not AsValue: that
involves a slow variant conversionI'm aware of that. It's even better
for developping, because you can see which value you're using in the
table.
Quote2 Why do you need to call ApplyUpdates?I thought I needed it
everytime, but execSQL is writing directly to the database. Now I'm
smarter (hopefully   )
Quote3 Your commit closes the transaction. Do you want to add a
starttransaction afterwards? Or add it at the beginning of your
function(s)?I've changed the rules a little bit (for myself). I've
created a new transaction for every execSQL. Now a commit is saving my
changes to the database without interfering other transactions.

I'm learning a lot of SQLdb and his ways. It's not the same way what I
used to be working.

Groeten,
mangakissa

----------------------------------------------------------------------
BigChimp · 2013-12-05 07:47

Goedemorgen,

My point about the .asstring was more to Luca, really, sorry.

Quote from: mangakissa on December 03, 2013, 08:41:10 pmI've created a
new transaction for every execSQL. Now a commit is saving my changes
to the database without interfering other transactions.
Seems like a good plan

Quote from: mangakissa on December 03, 2013, 08:41:10 pmI'm learning a
lot of SQLdb and his ways. It's not the same way what I used to be
working.
So am I - and don't get me started about how datasets work.. I never
went that deep when doing .Net... but it is fun... mostly

Groeten
de aap