Create a DBExpress-Connection at Runtime

Problem/Question/Abstract:

If you have a Webservice or a nonvisual component, you can't put a TSQLConnection on a form so you have to call the connection at runtime

Answer:

The normal way for Delphi and Kylix is just to check dbExpress, put a TSQLConnection on a form then double-click the TSQLConnection to display the Connection Editor and set parameter values (database path, connection name etc.) to indicate the settings.

But in our example, all goes by runtime (path and login) with dbExpress we don't need an alias or the BDE either.

procedure TVCLScanner.PostUser(const Email, FirstName, LastName: WideString);
var
Connection: TSQLConnection;
DataSet: TSQLDataSet;
begin
Connection := TSQLConnection.Create(nil);
with Connection do
begin
ConnectionName := 'VCLScanner';
DriverName := 'INTERBASE';
LibraryName := 'dbexpint.dll';
VendorLib := 'GDS32.DLL';
GetDriverFunc := 'getSQLDriverINTERBASE';
Params.Add('User_Name=SYSDBA');
Params.Add('Password=masterkey');
Params.Add('Database=milo2:D:\frank\webservices\umlbank.gdb');
LoginPrompt := False;
Open;
end;
DataSet := TSQLDataSet.Create(nil);
with DataSet do
begin
SQLConnection := Connection;
CommandText := Format('INSERT INTO kings VALUES("%s","%s","%s")',
[Email, FirstN, LastN]);
try
ExecSQL;
except
end;
end;
Connection.Close;
DataSet.Free;
Connection.Free;
end;


Sending commands to the server

Another possibilities is to send commands like CreateTable to the Server. For TSQLConnection, Execute takes three parameters: a string that specifies a single SQL statement that you want to execute, a TParams object that supplies any parameter values for that statement, and a pointer that can receive a TCustomSQLDataSet that is created to return records.

Note: Execute can only execute one SQL statement at a time. It is not possible to execute multiple SQL statements with a single call to Execute, as you can with SQL scripting utilities. To execute more than one statement, call Execute repeatedly.

It is relatively easy to execute a statement that does not include any parameters. For example, the following code in our example executes a CREATE TABLE statement (DataDefinitionLanguage) without any parameters on a TSQLConnection component:

procedure createUserTable;
var
Connection: TSQLConnection;
SQLstmt: string;
begin
Connection := TSQLConnection.Create(nil);
with Connection do
begin
ConnectionName := 'VCLScanner';
DriverName := 'INTERBASE';
LibraryName := 'dbexpint.dll';
VendorLib := 'GDS32.DLL';
GetDriverFunc := 'getSQLDriverINTERBASE';
Params.Add('User_Name=SYSDBA');
Params.Add('Password=masterkey');
with TWebModule1.create(nil) do
begin
getFile_DataBasePath;
Params.Add(dbPath);
free;
end;
LoginPrompt := False;
Connected := True;
SQLstmt := 'CREATE TABLE NewMaxCusts ' +
'( ' +
'  CustNo INTEGER NOT NULL, ' +
'  Company CHAR(40), ' +
'  State CHAR(2), ' +
'  PRIMARY KEY (CustNo) ' +
')';
try
Execute(SQLstmt, nil, nil);
except
raise
end;
Close;
Free;
end; //end Connection
end;