posgresql:  how to insert, update, view and delete records?
lazarus / 16-Databases

----------------------------------------------------------------------
jshand2010 · 2013-01-14 20:23

I have set up postgres to connect to a database without an error. 
what i need to know how to insert a record, view a record, update a
record and delete a record.

i have a table called empl_login (empl_id, username, password) which
are the names of the columns or fields.

another thing, how do you check for duplicates?

one more thing.  if you need to read data from the database into
dynamic memory...what is the correct method for doing this?  the
reason i want to do this is so that i only have to make changes to the
database on closing a form.  loading a form will open the database for
changes.

i appreciate any help given

----------------------------------------------------------------------
denver · 2013-01-14 20:43

Well, you are taking the basic method to handle database by using
lazarus.
You better check this out first
http://wiki.freepascal.org/SQLdb_Tutorial1

Anyway, welcome to lazarus

----------------------------------------------------------------------
jshand2010 · 2013-01-14 22:25

yeah i've already gone through those tutorials.  i found it very
confusing.  i just need straight forward instructions on how to
execute these functions....please help me out

----------------------------------------------------------------------
denver · 2013-01-15 09:47

Assume that you are using sqldb with the following components on a
form :

    PQConnection1: TPQConnection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;

Try to config the parameters in PQConnection1 and let it connect to
PostgreSQL Server :

Once the connection is done, here is an example base on your table
layout to insert data : ( Assume a fields are string )

Code: [Select]procedure insert_empl_login( fempl_id, fusername,
fpassword : String ) ;
begin
  SQLQuery1.Database := PQConnection1 ;
  SQLQuery1.SQL.Clear ;
  SQLQuery1.SQL.Add( 'insert into empl_login ( empl_id, username,
password ) values ( :empl_id, :username, :password ) ' ) ;
  SQLQuery1.Params.ParamByName( 'empl_id' ).AsString := fempl_id ;
  SQLQuery1.Params.ParamByName( 'username' ).AsString := fusername ;
  SQLQuery1.Params.ParamByName( 'password ' ).AsString := fpassword ;
  SQLQuery1.ExecSQL ;  //  record inserted
end ;

I like to use Parameters in SQL statement cause it can more easy to
handle some special characters in string.

for edit  any record with given empl_id

Code: [Select]
procedure edit_empl_login( fempl_id, fusername, fpassword : String ) ;
begin
  SQLQuery1.Database := PQConnection1 ;
  SQLQuery1.SQL.Clear ;
  SQLQuery1.SQL.Add( 'update empl_login set username =:username,
password = : password  where empl_id = :empl_id' ) ;
  SQLQuery1.Params.ParamByName( 'empl_id' ).AsString := fempl_id ;
  SQLQuery1.Params.ParamByName( 'username' ).AsString := fusername ;
  SQLQuery1.Params.ParamByName( 'password ' ).AsString := fpassword ;
  SQLQuery1.ExecSQL ;  //  record updated ;
end ;



To Delete any record with given empl_id :

Code: [Select]
procedure delete_empl_login( fempl_id : String ) ;
begin
  SQLQuery1.Database := PQConnection1 ;
  SQLQuery1.SQL.Clear ;
  SQLQuery1.SQL.Add( 'delete from empl_login where empl_id = :empl_id'
) ;
  SQLQuery1.Params.ParamByName( 'empl_id' ).AsString := fempl_id ;
  SQLQuery1.ExecSQL ;
end ;



A few lines of coding example is better than a thousand of words !
Denver