TSQLQuery.SQL.Text enquiry
lazarus / 16-Databases

----------------------------------------------------------------------
jshand2010 · 2013-02-03 05:16

Hi there, i have having trouble with the TSQLQuery where it comes to
updating records.

I have included my source code so you can see where i have gone wrong
in my code.

i have postgresql properly set up and i am currently able to add
records, but i use lazarus to do the bulk of my information checking.

so would appreciate all the help i can get.

thanks again

----------------------------------------------------------------------
jamhitz · 2013-02-03 09:14

Your code is too long winded for me to pinpoint exactly what the
problem is. However, if you want to update a record in SQLdb, you must
have a TSQLTransaction linked to your TSQLQuery.

http://wiki.freepascal.org/SQLdb_Tutorial2

JamHitz

----------------------------------------------------------------------
exdatis · 2013-02-03 12:39

Error message(or screen shots)?
Regards.

----------------------------------------------------------------------
BigChimp · 2013-02-03 12:44

Also useful to know would be operating system, lazarus version,
indication of what code does not work as needed (actual function/line
number)
You can even put the laz/operating system version in your forum
message footer (see the forum control panel) so you don't need to
repeatedly type it...

and preferably postgresql DB DDL, possibly less 1 letter variable
names...

Edit: thanks for including a sample project, that already helps a lot,
but is specifying some more quite obvious info really too much to ask?
What would you ask for if somebody came to you with a similar
question?

----------------------------------------------------------------------
jshand2010 · 2013-02-03 21:00

i hope this helps you more.

thanks again

----------------------------------------------------------------------
goodname · 2013-02-03 21:21

Wow that is a lot of code. Since this is your project could you narrow
down your question to the specific line your having trouble with
instead of asking us to debug your entire project and make guesses at
where we think you might be having problems.

----------------------------------------------------------------------
jshand2010 · 2013-02-03 21:29

yeah i know it's a lot of code  

below is the code i am having trouble with

procedure updateloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                           d: TDatasource; user: string; pass: string;
                           selection: string);
var
  e: EPQDatabaseError;

begin
  try
    q.Close;
    q.SQL.Text:='update empl_login set uname=:user, pword=:pass '+
                        'where uname=:selection;'; //this is line i'm
having trouble with because it complains that the column selection
doesn't exist and i'm trying to do is look for that uname and change
it with :user
    q.Params.ParamByName('user').AsString:=user;
    q.Params.ParamByName('pass').AsString:=pass;
    q.ExecSQL;
    t.Commit;
  except
    on e: Exception do begin
      ShowMessage(e.Message);
    end;
  end;
end;

----------------------------------------------------------------------
goodname · 2013-02-03 21:42

try setting the selection parm.
Code: [Select]q.Params.ParamByName('pass').AsString:=pass;
q.Params.ParamByName('selection').AsString:=selection;
q.ExecSQL;

----------------------------------------------------------------------
jshand2010 · 2013-02-03 21:50

i added q.params.parambyname('selection').asstring:=selection but it
hung when i tried execute and it didn't come up with an error.

----------------------------------------------------------------------
jshand2010 · 2013-02-03 22:04

i think i've got the code correct, but there is a chance that i
don't.  not too sure if it is a bug with lazarus/fpc or my program at
the moment, but it is frustrating, as the update is carried out in
postgresql, but lazarus hangs in the process.

----------------------------------------------------------------------
BigChimp · 2013-02-04 08:30

1. You might want to try adding something like
q.Params.ParamByName('selection').DataType:=ftString; before
q.Params.ParamByName('selection').AsString:=selection;
See if that helps. If it does, IMO it should be reported as a bug.
2. So give the DDL to the database (the definition of the tables) so
we can properly test it...

----------------------------------------------------------------------
BigChimp · 2013-02-04 08:31

Quote from: jshand2010 on February 03, 2013, 10:50:30 pmi added
q.params.parambyname('selection').asstring:=selection but it hung when
i tried execute and it didn't come up with an error.

Haven't looked at the code again but why
q.params.parambyname('selection').asstring:=selection
instead
of
q.params.parambyname('selection').asstring:=user
... you want to update a certain user, don't you?

Edit: mmm, going to have to look at the code - you're updating the
username as well in that SQL statement!?!?

Edit:
Had a look at the code. No idea what you want to do exactly but I'm
assuming you want to change a user's password in that function.
Apparently you get the user name from a combobox and put that into the
selection parameter.

Ok... but in procedure Tfloginmanager.btnEditClick(Sender: TObject);
you do
updateloginrec(conn, query, trans, datas, ul.username, ul.password,
                           ts.combotext);
why not pass
updateloginrec(conn, query, trans, datas, ul.username, ul.password,
                           ul.username);
(note: I might be misunderstanding your code here)

Also, you give t.Commit in that function. I'm assuming the transaction
has already been started elsewhere (e.g. t.StartTransaction)?

In any case, could you explain what you mean by "hang" exactly? Does
the GUI still show? Can you close the application normally? Are there
any message boxes?

----------------------------------------------------------------------
jshand2010 · 2013-02-04 09:17

i've worked out everything now.  thanks for all the help given.  i
have found a solution on adding, editing and deleting a record.  it
all work without any problems.

----------------------------------------------------------------------
BigChimp · 2013-02-04 09:20

Great. Could you share that solution? I'm very interested what you
found... and I'm sure the others are too.

----------------------------------------------------------------------
jshand2010 · 2013-02-04 10:05

unit provars;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
  pqconnection, sqldb, db;

type
  UserLogin=record
    username, password, combotext: string;
  end;

var
  ul: UserLogin;

  h: string[1];
  a, b: integer;

procedure cp; //close program

procedure cloginmem; //clear login memory

procedure loadlogindata(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                           d: TDatasource); //load login database

procedure addloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                         d: TDatasource; user, pass: string); //add
record

procedure updateloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                            d: TDatasource; user, pass: string;
change: string); //update record

procedure deleteloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                            d: TDatasource; user: string);

implementation

procedure cp; //close program
begin
  cloginmem;

  Application.Terminate;
end;

procedure cloginmem; //clear login memory
begin
  ul.username:='';
  ul.password:='';
  ul.combotext:='';
end;

procedure loadlogindata(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                           d: TDatasource); //load login database
var
  e: EPQDatabaseError;

begin
  try
    q.Close;
    q.SQL.Clear;
    q.SQL.Add('select * from empl_login;');
    q.ExecSQL;
    q.Open;
    t.Active:=true;
  except
    on e: Exception do begin
      ShowMessage(e.Message);
    end;
  end;
end;

procedure addloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                         d: TDatasource; user, pass: string);
var
  e: EPQDatabaseError;

begin
  try
    q.Close;
    q.SQL.Clear;
    q.SQL.Add('insert into empl_login (uname, pword) values (:uname,
:pword);');
    q.Params.ParamByName('uname').AsString:=user;
    q.Params.ParamByName('pword').AsString:=pass;
    q.ExecSQL;
    t.Commit;
  except
    on e: Exception do begin
      ShowMessage(e.Message);
    end;
  end;
end;

procedure updateloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                            d: TDatasource; user, pass: string;
change: string); //update record
var
  e: EPQDatabaseError;

begin
  try
    q.Close;
    q.SQL.Clear;
    q.SQL.Add('update empl_login set uname=:uname, pword=:pword where
uname=:change;');
    q.Params.ParamByName('uname').AsString:=user;
    q.Params.ParamByName('pword').AsString:=pass;
    q.Params.ParamByName('change').AsString:=change;
    q.ExecSQL;
    t.Commit;
  except
    on e: Exception do begin
      ShowMessage(e.Message);
    end;
  end;
end;

procedure deleteloginrec(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                            d: TDatasource; user: string);
var
  e: EPQDatabaseError;

begin
  try
    q.Close;
    q.SQL.Clear;
    q.SQL.Add('delete from empl_login where uname=:uname;');
    q.Params.ParamByName('uname').AsString:=user;
    q.ExecSQL;
    t.Commit;
  except
    on e: Exception do begin
      ShowMessage(e.Message);
    end;
  end;
end;

end.