TSQLQuery.SQL.Text enquiry

[jshand2010]

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]

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]

Error message(or screen shots)?
Regards.

[BigChimp]

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]

i hope this helps you more.

thanks again

[goodname]

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]

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]

try setting the selection parm.

Code: [Select]

q.Params.ParamByName('pass').AsString:=pass;
q.Params.ParamByName('selection').AsString:=selection;
q.ExecSQL;


[jshand2010]

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]

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]

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]

i 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]

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]

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

[jshand2010]

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.