How to list a database's tables in a HTML page?
lazarus / 38-Networking and Web Programming

----------------------------------------------------------------------
tjpren · 2012-05-23 11:37

Hello Again,

I'm using FreeSpider to query a MS SQL database.

The first Spiderpage allows an operator to enter a SQL query string.

I'd like to present to the operators with a list of available tables
for the select statement.

I'm wondering if anyone knows how to retrieve the database table names
as a list into an HTML page.

I can bring back a a table list in a desktop app using GetTableNames
as:
sa.GetTableNames('',ComboBox1.Items);

But in the Freespider there is no comboboxes.

Any thoughts?

----------------------------------------------------------------------
Leledumbo · 2012-05-23 15:51

What's wrong with outputing one with direct HTML? Or use the template
system to separate data formatting.

----------------------------------------------------------------------
tjpren · 2012-05-24 08:49

Hi leledumbo,

Not really sure what you mean.

I'm not sure how to make the connection with direct HTML.  I've done
it once with asp, and seen it done with PHP; but thought there might
be a method of mixing pascal with HTML.

I thought that somehow I could get the output from the GetTablenames
into an array, and direct that to a Response.Content.Add() routine?

Thoughts?

Regards

----------------------------------------------------------------------
Leledumbo · 2012-05-24 09:32

QuoteI thought that somehow I could get the output from the
GetTablenames into an array, and direct that to a
Response.Content.Add() routine?Yes, that's what I mean.

----------------------------------------------------------------------
tjpren · 2012-05-25 09:51

I think I'm close but no solution

When I run SQLConnector1.GetTableNames(Variable2);

I get an error:
Transaction of connection not set

I've tried adding
SQLTransaction1.commit;
SQLTransaction.active;

But the error is still there.

Any thoughts?


Code: [Select]procedure TDataModule1.saDatasettableRequest(Sender:
TObject;
  Request: TSpiderRequest; var Response: TSpiderResponse);

Var
    Variable1 : String;
    Variable2 : TStrings;
    i: Integer;
begin
    SpiderForm1.Action:= ExePath + '/datasettable';
    SQLConnector1.Connected:=False;

    with SdfDataSet1 do
    begin
      Active:=false;
      ClearFields;
      FileName:='connector.txt';
      FirstLineAsSchema:=True;
      Active:=True;
    end;

    SdfDataSet1.First;
    while not SdfDataSet1.EOF do
    begin
       Variable1 :=sdfDataSet1.FieldByName('hostname').AsString;
       SQLConnector1.DatabaseName
:=sdfDataSet1.FieldByName('databasename').AsString;
       SQLConnector1.HostName
:=sdfDataSet1.FieldByName('HostName').AsString;
       SQLConnector1.UserName
:=sdfDataSet1.FieldByName('username').AsString;
       SQLConnector1.Password
:=sdfDataSet1.FieldByName('password').AsString;
       SQLConnector1.Connected:=True;
       SdfDataSet1.Next;
    end;
       SdfDataSet1.Active:=False;
       SdfDataSet1.ClearFields;
       //SQLConnector1.GetTableNames(Variable2);
       SQLTransaction1.Active:=False;

    Response.Add('<h2><font color=red>Filter Form for MS SQL database
</font></h2>');
    Response.Content.Add('Database server:');
    Response.Content.Add(Variable1);
    Response.Add('<h2><font color=blue>Add SQL Query here
</font></h2>');
    SpiderForm1.AddInput(itText, 'State', '', 'size=50');
    SpiderForm1.AddInput(itSubmit, 'add', 'Add');

    Response.Add(SpiderForm1.Contents);

  if FileExists('sub.dat') then
     DeleteFile('sub.dat');
     MemDataset1.Open;

  if Request.ContentFields.IndexOfName('add') <> -1 then
  begin
    Response.Content.Add('Saving data..');
    MemDataset1.Append;
    MemDataset1.FieldByName('State').AsString:= Request.Form('State');
    MemDataset1.Post;
    MemDataset1.SaveToFile('sub.dat');
  end;
    Response.Add(SpiderTable1.Contents);
    Response.Add('<hr><a href="./">Main</a>');

    Memdataset1.Close;
end;

----------------------------------------------------------------------
ludob · 2012-05-25 12:36

When dropping SQLTransaction1 on the datamodule you have to set it's
database property to SQLConnector1. When doing so you will see that
the transaction property of SQLConnector1 is also set to
SQLTransaction1

----------------------------------------------------------------------
tjpren · 2012-05-26 08:51

Thanks Ludob,
This got rid of my error.
Code: [Select]SQLConnector1.Transaction :=SQLTransaction1;
       SQLTransaction1.DataBase :=SQLConnector1;   I've also stopped
with the GetTableNames, as I don't think it's supported in SQLdb.  I'm
now trying
Code: [Select]SQLQuery1.sql.text:= 'SELECT * FROM sys.Tables';
       SQLQuery1.Open; but can't figure out how to execute it within
HTML.

I've tried
Response.Add('<td><%Response.Write(SQLQuery1.ExecSQL)%></td>');
But that doesn't work.

Does anyone know how to execute the query within HTML (or is it
possible?).

I've seen it done in examples, inserting PHP, but my IIS server
doesn't support PHP.

Java might be OK, but I don't know Java code.

Regards

----------------------------------------------------------------------
Leledumbo · 2012-05-26 13:54

Quotebut can't figure out how to execute it within HTML.

I've tried
Response.Add('<td><%Response.Write(SQLQuery1.ExecSQL)%></td>');
But that doesn't work.
Don't use your PHP brain to code in Pascal
In short, you have to execute it, grab the result and stringify, then
add to response. Something like:
Code: [Select]SQLQuery1.sql.text:= 'SELECT * FROM sys.Tables';
SQLQuery1.Open;
Response.Add('<table>');
while not SQLQuery1.EOF do begin
  Response.Add('<tr>');
  Response.Add('<td>' + SQLQuery1.FieldByName('fieldname1').AsString +
'</td>');
  Response.Add('<td>' + SQLQuery1.FieldByName('fieldname2').AsString +
'</td>');
  Response.Add('</tr>');
  SQLQuery1.Next;
end;
Response.Add('</table>');

----------------------------------------------------------------------
tjpren · 2012-05-26 15:53

Fantastic, that did the job.

Many thanks Leledumbo.

What I've got now, is a form which opens to the operator, and lists
the available tables he can use to build a query.  When a form opens
it uses a csv file containing all the logins, so he doesn't have to
worry about passwords etc,

Once he builds his query, and executes, a new form appears with a
table of the data.

He can go back, and refine his query if necessary.

I'll tidy it up a bit and post the code for others.

You made it seem so easy - again thanks.

----------------------------------------------------------------------
tjpren · 2012-05-27 07:39

Hello,

This is my CGI web app for querying an MS SQL database.  Works OK on
IIS.

Thanks for the help from Leledumbo, and Ludob.

It lists the database tables for selection.

The database connection info is stored in csv file on the server.

Regards