Solved

Accessing database through HTTP/CGI

Posted on 1998-03-08
7
261 Views
Last Modified: 2010-04-06
This is a complement to a previous question about accessing a database through the Internet.  From the answer I got, I have decided that the simplest way to do it would be to use HTTP and a CGI program on the server.

I already purchased the IMS component from Argosoft which contains an HTTP implementation.  From what I understand, I need to write a CGI program that will access the database on the server.  

Question 1:  Should I use Delphi to write the CGI program ? If so, is the CGIExpert component a good choice ?

Question 2: Does that mean that if the server is running Windows NT then I can use a single user version of Interbase as a database (what comes with Delphi Professional) ?

Question 3: What about BDE ? If I use Interbase, will I have to install BDE on the server that runs my CGI written in Delphi ?

I am new with CGI so if you could direct me to an example that would be great.
0
Comment
Question by:ymailhot
7 Comments
 
LVL 5

Expert Comment

by:julio011597
ID: 1360280
I've written some CGI programs in Delphi accessing local tables, i.e. Paradox/dBase tables through the BDE.

Tell me if you are interested in some code.

BTW, i've downloaded but never used "CGI Expert", so cannot tell how it works; but CGI is a much or less an easy protocol, so i better like writing my own code... :)
0
 
LVL 3

Expert Comment

by:mirek071497
ID: 1360281
1.Yes you can use delphi and all other languages. You must only create good response in html. If your program can execute on the server side then it can be used for cgi. You can't use Delphi for CGI on linux but for Windows NT you can. CGI Expert is good start point however I thinnk so you can write better CGI without this componnent.
2.You can't because license will be broken.
3.Yes BDE is a god solution.

p.s. Try to read CGI specyfication for your server. You must understand ServerParsed CGI and how to use CGI in forms. You can pass parameters in two ways.......... read this !
0
 

Author Comment

by:ymailhot
ID: 1360282
Thank you Julio and Mirek.

Julio, your code would be greatly appreciated. Please send iy to Yves.Mailhot@skylink.ca.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ymailhot
ID: 1360283
My Internet provider has a dedicated MS-SQL server which I could access via ODBC.  Would this be a better solution than using a CGI ? Would I need to install the BDE on each machine which accesses the database ?
0
 

Author Comment

by:ymailhot
ID: 1360284
My Internet provider has a dedicated MS-SQL server which I could access via ODBC.  Would this be a better solution than using a CGI ? Would I need to install the BDE on each machine which accesses the database ?
0
 
LVL 5

Expert Comment

by:julio011597
ID: 1360285
It is not an alternative to CGI; you need CGI to access the MS-SQL server itself; i.e. you need CGI as a bridge between the web server and the database engine.

Any ODBC compliant database engine may be accessed from Delphi; since you are going to write your CGIs with Delphi, then they can be able to access ODBC driven databases (MS-SQL in your case).

As to the machines which accesses the database, do not forget that database access happens only on the server side, no matter where the request comes from.

0
 
LVL 3

Accepted Solution

by:
williams2 earned 150 total points
ID: 1360286
I have been programming severel different Delphi CGI applications using both MS-SQL and simple querying. In The final result, I've programmed an example using DDE with a CGI-Service and a CGI-Client. All examples are provided with simple explanations and necessary files.
The problem is, that you cannot keep the Database open with one application. You have to open and close it each time, and opening a Database and closing it, is rather ressource-demanding. In between, the INTRBASE service provides multiuser access to the Service application, security for hacking and fasten up the querying all in two simple applications.
You can have it, if necessary.

Here's an example of a simple Delphi CGI-application:
1. The HTML file first:
<html>
<title>Testing CGI - with Delphi Interbase Database</title>

<body>
<body background = http://mis.lamar.edu/mis/Grundy/parchmen.gif>
<form action="http://localhost/cgi-win/test.exe" method=post>
<h2><Center>My Search Person CGI</Center></h2>
<HR>Word to search:
<input type= text name= searchfield1 size=20><br>
(with automatic wildcard selection)<br>
Kategori:
<select name=formfield1>
<option value="Forname">Forename
<option value="Surname">Surname
<option value="Username">Username
<option value="Age">Age
</select><BR>
<HR><select name=formfield3>
<option value="IGNORE">ignore
<option value="AND">and
<option value="OR">or
</select>
Second search:
<input type= text name= searchfield2 size=20><br>
(med automatisk wildcard selektion)<br>
Kategori:
<select name=formfield2>
<option value="Forname">Forename
<option value="Surname">Surname
<option value="Username">Username
<option value="Age">Age
</select><BR>

<HR>

<input type=submit value="Submit">
</form>
</body>
</html>


       [CGI]
       Request Protocol=HTTP/1.0
       Request Method=POST
       Request Keep-Alive=No
       Document Root=C:\WEBSITE\HTDOCS\
       Executable Path=/cgi-win/test.exe
       Server Software=WebSite/1.1e
       Server Name=10.2.100.138
       Server Port=80
       Server Admin=Williams@aabc.dk
       CGI Version=CGI/1.3 (Win)
       Remote Address=127.0.0.1
       Referer=file://www.aabc.dk/Test.htm
       User Agent=Mozilla/3.01 [da] (Win95; I)
       Content Type=application/x-www-form-urlencoded
       Content Length=81
         
       [System]
       GMT Offset=3600
       Debug Mode=No
       Output File=C:\Website\CGI-Temp\43ws.htm
       Content File= C:\Website\CGI-Temp\43ws.inp
       
       [Form Literal]
       searchfield1=Michael
       searchfield2=Boldsson
       ...
 
       [Accept]
       image/gif=Yes
       image/x-xbitmap=Yes
       image/jpeg=Yes
       image/pjpeg=Yes
       */*=Yes

       [Extra Headers]
       Host=localhost

      The Outputfile will contain the string: "C:\Website\CGI-Temp\43ws.htm"
      This means, that you have to write to axctly that filename. The Server program will handle it afterwards.
      The "43ws.INP" contains the inputinformation from the user, but you allready got them here under the [Form
      Litteral] topic.

      And now to the program example:

Unit TestUnit;

  Uses IniFiles, StdCtrls, DB, DBTables;

Const
  PicDir = [String containing public path to pictures of users];
var
  db1: Tdatabase;
  dBase: TQuery;
  PicDir: String;

Implementation
 
  procedure InitDB;
  begin
    db1 := TDatabase.create(nil);
    db1.databasename := 'MyBase';
    db1.drivername   := 'INTRBASE';

    db1.params.clear;
    db1.params.add('SERVER NAME=C:\MYBASE.GDB');
    db1.params.add('USER NAME=SYSDBA');
    db1.params.add('OPEN MODE=READ/WRITE');
    db1.params.add('SCHEMA CACHE SIZE=8');
    db1.params.add('LANGDRIVER=');
    db1.params.add('SQLQRYMODE=');
    db1.params.add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
    db1.params.add('SCHEMA CACHE TIME=-1');
    db1.params.add('MAX ROWS=-1');
    db1.params.add('BATCH COUNT=200');
    db1.params.add('ENABLE SCHEMA CACHE=FALSE');
    db1.params.add('SCHEMA CACHE DIR=');
    db1.params.add('PASSWORD=masterkey');

    db1.loginprompt  := false;
    db1.connected    := true;

    DBase:= TQuery.create(nil);
    DBase.databaseName:= 'MyBase';
    DBase.requestLive:= true
  end;

  function Correct(s: String): String;
  var // Makes the first letter uppercase on each word
    i: Integer;
    oldc: Char;
  begin
    oldc:=' ';
    For i:=1 to length(s) do
    begin
      if (oldc=' ') and (s[i]<>' ') then
        s[i]:=UpperCase(s[i])[1];
      oldc:=s[i];
    end;
    Correct:=s;
  end;

  Procedure GetTabel(var IniFile: TIniFile);
  var
    s: String; // Search String
    t: String; // Search type
    DoubleSearch: String;
    Statement: String; //SQL Statement
    Bool: String;
  begin
    dbase.sql.clear;
    t := IniFile.ReadString('Form Literal','formfield1','');
    s := IniFile.ReadString('Form Literal','searchfield1','""');
    s:= LowerCase(s);
    Statement:='select * from MyTabel where ('+t+' Like ' + s + ')';
    dbase.sql.add(Statement);
    Bool := IniFile.ReadString('Form Literal','formfield3','');
    if Bool<>'IGNORE'then
    begin
      t := IniFile.ReadString('Form Literal','formfield2','');
      s := IniFile.ReadString('Form Literal','searchfield2','""');
      S := LowerCase(s); // SQL is Case sensitive
      Statement:=Bool+' ('+t+' Like ' + s + ')';
      dbase.sql.add(Statement);
    end;
    dbase.open;
  end;

  Procedure WriteResult(var f: textFile);
  var
    Fname, Sname, User: String;
    Age, Tlf, PicID: String;
  begin
    DBase.first;
    If DBase.RecordCount>0 then
    begin
      while not DBase.eof do
      begin
        Fname:= DBase.FieldByName('Forename').asString;
        Sname:= DBase.FieldByName('Surname').asString;
        User:= DBase.FieldByName('UserName').asString;
        Age:= DBase.FieldByName('Age').asString;
        Tlf:= DBase.FieldByName('Tlf').asString;
        PicID:= DBase.FieldByName('PicID').asString;

        Writeln(f,'<B>Forename: </B>'+Correct(Fname)+'<BR>');
        Writeln(f,'<B>SurName: </B>'+Correct(Sname)+'<BR>');
        Writeln(f,'<B>User Ident.: </B>'+Uppercase(User)+'<BR>');
        Writeln(f,'<B>Age: </B>'+Age+'<BR>');
        Writeln(f,'<B>Telephone #: </B>'+Tlf+'<BR>');
        Writeln(f,'<CENTER><IMG SRC="'+PicDir+PicID+'"></CENTER><br><hr>');

        dBase.next;
      end;
    end else
    begin
      Writeln(f,'<BR><HR><BR><B>The query returned no result </B><BR>');
    end;
  end;

  Procedure ProcessScript;
  var
    IniFile: TInifile;
    filename: string;
    f: Textfile; //Same as 'File of Text'
  begin
    IniFile:= TInifile.create(Paramstr(1)); // Paramstr(1)

    // *** header begin ***

    filename:= IniFile.Readstring('System','Output File','');
    assignfile(f,filename);
    rewrite(f); // Create output file
    writeln(f,'HTTP/1.0 200 OK');
    writeln(f,'Date: Friday, 28 Nov-97, 13:36:12 GMT');
    writeln(f,'Server:Website/1.1e');
    writeln(f,'Allow-ranges: bytes');
    writeln(f,'Content-type: text/html');
    writeln(f,'Content-length: 0');
    writeln(f);
    writeln(f,'<HTML>');
    writeln(f,'<Title>Result</Title>');
    writeln(f,'<Body>');
    writeln(f,'<body background = http://mis.lamar.edu/mis/Grundy/parchmen.gif>');
    writeln(f,'The following matched the query: <BR><BR>');

    // *** header end ***

   GetData(IniFile); // Hand over the Inifile to the 'GetData' procedure, to finish the query.
   WriteResult(f); // Write the resulting attributes

   writeln(f,'</body>');
   writeln(f,'</HTML>');
   Closefile(f);
 end;

 Begin
   InitDB;
   ProcessScript;
 end.

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now