Using TSQLQuery or TSQTransaction to create tables in a database
lazarus / 16-Databases

----------------------------------------------------------------------
jshand2010 · 2015-01-13 03:53

Hi there, how's it going?

I would like some help on finding out how to add tables within your
application.  i am using TPQConnection TSQLQuery TSQLTransaction and
TDatasource.  so this requirement is for a postgresql database.

Any help will be appreciated.

----------------------------------------------------------------------
LacaK · 2015-01-13 06:11

Are you asking how to create table on remote PostgreSQL database ?
You can use your TSQLQuery object:
  SQLQuery1.ExecSQL('create table mytable (column1 integer,...)');
or
  PQConnection1.ExecuteDirect(...);
Then:
  SQLTransaction1.Commit;

----------------------------------------------------------------------
jshand2010 · 2015-01-13 08:02

hey thanks lacak,

unfortunately i have tried both the ExecSQL and ExecuteDirect and it
doesn't seem to work as i get a crash with the error not supported on
gdb < 5.3 and an sql state of 42P01

Here are the lines of code related to this problem

procedure createlogintb(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                           ds: TDatasource);
begin
  try
    if (p.Connected=true) then begin
      p.ExecuteDirect('create table login (username varchar(20) not
null, password varchar(20) not null);');
      t.Commit;

      loadlogintb(p, q, t, ds);
    end
    else begin
      p.Connected:=true;
      createlogintb(p, q, t, ds);
    end;
  except
    on e: Exception do begin
      ShowMessage('Error in class '+e.ClassName);
      ShowMessage('Error Message Is '+e.Message);
    end;
  end;
end;

procedure loadlogintb(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction;
                         ds: TDatasource);
begin
  a:=0;
  clearul(a);
  cleartl;

  try
    if (p.Connected=true) then begin
      q.Close;
      q.SQL.Clear;
      q.SQL.Add('select * from login;');
      q.ExecSQL;
      q.Open;
      q.First;

      a:=q.RecordCount;

      clearul(a);

      if (a>=1) then begin
        for d:=1 to a do begin
          ul[d-1].username:=q.Fields[0].AsString;
          ul[d-1].password:=q.Fields[1].AsString;
          q.Next;
        end;
      end;

      q.Close;
    end
    else begin
      p.Connected:=true;
      loadlogintb(p, q, t, ds);
    end;
  except
    on e: Exception do begin
      createlogintb(p, q, t, ds);
    end;
  end;
end;

for the tsqlquery my settings are:

q.sql.clear;
q.sql.add('select * from login;');
q.execsql;
q.open;
q.first;

it the gui settings they are the fefault with the sql command ' select
* from login; '

and q.is then activated.  this is where the error starts

many thanks

----------------------------------------------------------------------
jshand2010 · 2015-01-13 08:17

Here is my source code for anyone to take a look at

thanks again

----------------------------------------------------------------------
jshand2010 · 2015-01-13 08:49

another thought is how would you check to see if a table exists before
you load the table information?

----------------------------------------------------------------------
engkin · 2015-01-13 09:44

Quote from: jshand2010 on January 13, 2015, 09:49:36 amanother thought
is how would you check to see if a table exists before you load the
table information?
The connection provides GetTableNames method. Check if the table you
want is included in the list it returns.

----------------------------------------------------------------------
jshand2010 · 2015-01-13 10:03

thanks engkin, i tried that command, but can't get it to work as i
have no idea where to place it and how to use it properly.  tried
looking through the wiki, but no luck.

----------------------------------------------------------------------
engkin · 2015-01-13 10:10

Something similar to this, maybe:
Code: [Select]function TableExists(vConnection: TSQLConnection;
vTableName: String): boolean;
var
  ListOfTables: TStringList;
begin
  Result := False;
  ListOfTables := TStringList.Create;
  try
    vConnection.GetTableNames(ListOfTables, False);
    Result := ListOfTables.IndexOf(vTableName)>=0;
  finally
    ListOfTables.Free;
  end;
end;

I did not try the code.

----------------------------------------------------------------------
jshand2010 · 2015-01-13 10:41

thanks engkin:

this is the code i put into my program:

function CheckTables(vConnection: TPQConnection; tname: string):
boolean;
var
  List: TStringList;
begin
  Result:=False;
  List:=TStringList.Create;
  try
    vConnection.GetTableNames(List, false);
    Result:=List.IndexOf(tname)>=0;
  finally
    List.Free;
  end;
end;

in loadlogintb i also put this code:

if (p.Connected=true) then begin
      tabletrue:=CheckTables(p, 'login');

      if (tabletrue=false) then begin
        p.ExecuteDirect('create table login (username varchar(20) not
null, password varchar(20) not null);');
        t.Commit;
      end;

      q.Close;
      q.SQL.Clear;
      q.SQL.Add('select * from login;');
      q.ExecSQL;
      q.Open;
      q.First;

      a:=q.RecordCount;

      clearul(a);

      if (a>=1) then begin
        for d:=1 to a do begin
          ul[d-1].username:=q.Fields[0].AsString;
          ul[d-1].password:=q.Fields[1].AsString;
          q.Next;
        end;
      end;

      q.Close;
    end
    else begin
      p.Connected:=true;
      loadlogintb(p, q, t, ds);
    end;

not too sure what else i need to do to get it working.

----------------------------------------------------------------------
LacaK · 2015-01-13 12:31

Quote from: jshand2010 on January 13, 2015, 09:02:14 amit the gui
settings they are the fefault with the sql command ' select * from
login; '

and q.is then activated.  this is where the error starts

It seems to me, that you are trying Open SQLQuery (select * from
login) before you create table "login"
Check your code and Active property of SQLQuery (may be you have set
it to True in IDE)

----------------------------------------------------------------------
jshand2010 · 2015-01-13 21:51

hey there lacak, many thanks for your help and patience.

i have enclosed the changes i have made to my provars.txt file to get
this working and so far no joy.

Where am i going wrong??

i have even turned off the TPQConnection.active.
TSQLQuery.SQL has nothing in the list.

Thanks again

----------------------------------------------------------------------
jshand2010 · 2015-01-14 00:04

I have made major changes to the code and still doesn't work.  take a
look at the setting i have made on the gui part of the program on the
plogin.frm

Any further assistance is appreciated

----------------------------------------------------------------------
LacaK · 2015-01-14 06:25

At least I see error in:

Code: [Select]function CheckTable(p: TPQConnection; tname: string):
boolean;
var
  List: TStringList;

begin
  Result:=False;
  try
   List:=TStringList.Create; // <-- ADD THIS
    p.GetTableNames(List, False);
    Result:=List.IndexOf(tname)>=0;
  finally
    List.Free;
  end;
end;

----------------------------------------------------------------------
jshand2010 · 2015-01-14 09:24

now this is getting slightly frustrating.

i have added that line and i now get the error of gdb <5.3 Unknown
error.  i don't know where this is coming from

----------------------------------------------------------------------
jshand2010 · 2015-01-14 09:33

it looks like the function CheckTable is the problem here.  i have
commented it out and i no longer get this error i stated above