NexusDB WebScripts (Part 3) - Connect to a Database
Welcome to part 3. Here we will learn how to access a database hosted from the nxServer and display some data. We will also cover including other nxScript files.
First things first: we need a database to use. Feel free to create one for yourself, or you can use the one I provide [below], which I will be using for part 3 (and parts thereafter). |
// START SQL - Run this in an SQL query window in Enterprise Manager in your blank test database
// Comment out this alias creation code if you have your own database you wish to use
IF NOT EXISTS (SELECT * FROM #SCHEMAS WHERE UPPER(SCHEMA_NAME) = 'WILLTESTDB') THEN
CREATE ALIAS "WillTestDB" FROM 'C:\TEMP\WillTestDB' ENFORCE;
END IF;
DROP TABLE IF EXISTS "WillTestDB"."MyTable1";
CREATE TABLE "WillTestDB"."MyTable1" (
ID AUTOINC
,field1 VARCHAR(140)
);
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 1');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 2');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 3');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 4');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 5');
// END SQL
// Comment out this alias creation code if you have your own database you wish to use
IF NOT EXISTS (SELECT * FROM #SCHEMAS WHERE UPPER(SCHEMA_NAME) = 'WILLTESTDB') THEN
CREATE ALIAS "WillTestDB" FROM 'C:\TEMP\WillTestDB' ENFORCE;
END IF;
DROP TABLE IF EXISTS "WillTestDB"."MyTable1";
CREATE TABLE "WillTestDB"."MyTable1" (
ID AUTOINC
,field1 VARCHAR(140)
);
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 1');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 2');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 3');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 4');
INSERT INTO "WillTestDB"."MyTable1"(field1) VALUES ('value 5');
// END SQL
The [above] SQL script will create a new database alias WillTestDB in C:\TEMP\WillTestDB.
Because we are either running this SQL from a different alias, or no alias at all (in Enterprise Manager the SQL query window will just have "SQL:" in the title bar instead of "SQL:<alias>"), I've had to define the alias in which I want my SQL code to happen.
The table I'm creating here is very simple on purpose as the aim of this part is to show basic connectivity to a database table, not complicate things with complex table structures...we can do that later ;)
Because we are either running this SQL from a different alias, or no alias at all (in Enterprise Manager the SQL query window will just have "SQL:" in the title bar instead of "SQL:<alias>"), I've had to define the alias in which I want my SQL code to happen.
The table I'm creating here is very simple on purpose as the aim of this part is to show basic connectivity to a database table, not complicate things with complex table structures...we can do that later ;)
In order to access database data we need to create a connection chain in a similar way as we would in a Delphi application containing an embedded server, with the main differences being our nxServer (TnxServerEngine) is already defined with the reserved word ServerEngine.
<%
var
aSession: TnxSession;
aDB: TnxDatabase;
...
begin
...
aSession := TnxSession.Create(nil);
aDB := TnxDatabase.Create(nil);
...
aSession.ServerEngine := ServerEngine;
aDB.Session := aSession;
aDB.AliasName := 'WillTestDB';
...
aSession.Open;
aDB.Open;
...
end.
%>
var
aSession: TnxSession;
aDB: TnxDatabase;
...
begin
...
aSession := TnxSession.Create(nil);
aDB := TnxDatabase.Create(nil);
...
aSession.ServerEngine := ServerEngine;
aDB.Session := aSession;
aDB.AliasName := 'WillTestDB';
...
aSession.Open;
aDB.Open;
...
end.
%>
The above .nxScript snippet shows basic connecting to the WillTestDB database, though we're not doing much with it at this time.
Notable things to point out about the above snippet:
Notable things to point out about the above snippet:
- ServerEngine is a reserved variable name being utilised by the nxServer
- It appears to be the TnxBaseServerEngine used by the nxServer
- There are no TnxTransports (TnxWinsockTransport, TnxNamedPipeTransport, etc.)
- This is because we are connecting to the local nxServer also hosting the WebScript
- This means that WebScript content is independent of network status i.e. the WebScript page will function locally even if there is no network connection
From here, we can incorporate a TnxQuery to pull data from the database:
<%
/* Disclaimer: For anyone out there with OCD over my colouring of the code, please don't get too obsessive about it. It is mainly just to allow you to have better visual distinction. I colour these manually so there will be inconsistencies */
var
aSession: TnxSession;
aDB: TnxDatabase;
aQuery: TnxQuery;
...
begin
...
aSession := TnxSession.Create(nil);
aDB := TnxDatabase.Create(nil);
aQuery := TnxQuery.Create(nil);
aQuery.Database := aDB;
...
aSession.ServerEngine := ServerEngine;
aDB.Session := aSession;
aDB.AliasName := 'WillTestDB';
...
aSession.Open;
aDB.Open;
...
aQuery.SQL.Text := 'select * from MyTable1 order by ID';
aQuery.Open;
...
if not aQuery.IsEmpty then
begin
aQuery.First;
Output.Writeln('<table>');
while not aQuery.Eof do
begin
Output.Writeln('<tr>');
Output.Writeln('<td>' + IntToStr(aQuery.FieldByName('ID').AsInteger) + '</td>');
Output.Writeln('<td>' + aQuery.FieldByName('Field1').AsString + '</td>');
Output.Writeln('</tr>');
aQuery.Next;
end;
Output.Writeln('<table>');
end;
...
end.
%>
/* Disclaimer: For anyone out there with OCD over my colouring of the code, please don't get too obsessive about it. It is mainly just to allow you to have better visual distinction. I colour these manually so there will be inconsistencies */
var
aSession: TnxSession;
aDB: TnxDatabase;
aQuery: TnxQuery;
...
begin
...
aSession := TnxSession.Create(nil);
aDB := TnxDatabase.Create(nil);
aQuery := TnxQuery.Create(nil);
aQuery.Database := aDB;
...
aSession.ServerEngine := ServerEngine;
aDB.Session := aSession;
aDB.AliasName := 'WillTestDB';
...
aSession.Open;
aDB.Open;
...
aQuery.SQL.Text := 'select * from MyTable1 order by ID';
aQuery.Open;
...
if not aQuery.IsEmpty then
begin
aQuery.First;
Output.Writeln('<table>');
while not aQuery.Eof do
begin
Output.Writeln('<tr>');
Output.Writeln('<td>' + IntToStr(aQuery.FieldByName('ID').AsInteger) + '</td>');
Output.Writeln('<td>' + aQuery.FieldByName('Field1').AsString + '</td>');
Output.Writeln('</tr>');
aQuery.Next;
end;
Output.Writeln('<table>');
end;
...
end.
%>
Displaying the query contents we are simply iterating through the resultset from first to last.
Here is the full source containing simple code to display a query.
Here is the full source containing simple code to display a query.
<%
var
aSession: TnxSession;
aDB: TnxDatabase;
aQuery: TnxQuery;
begin
aSession := TnxSession.Create(nil);
aDB := TnxDatabase.Create(nil);
aQuery := TnxQuery.Create(nil);
aQuery.Database := aDB;
aSession.ServerEngine := ServerEngine;
aDB.Session := aSession;
aDB.AliasName := 'WillTestDB';
try
Output.Writeln('<HTML><HEAD><TITLE>PART 3 - Connect a Database</TITLE></HEAD><BODY>');
Output.Writeln('<H1>Part 3 - Connect a Database</H1>');
aSession.Open;
Output.Writeln('<P>Session open...</P>');
aDB.Open;
Output.Writeln('<P>Database open...</P>');
aQuery.SQL.Text := 'select * from MyTable1 order by ID';
aQuery.Open;
Output.Writeln('<P>Query open...</P>');
if not aQuery.IsEmpty then
begin
Output.Writeln('<P>Query contains data...</P>');
aQuery.First;
Output.Writeln('<TABLE>');
Output.Writeln('<TR><TH COLSPAN="'+IntToStr(aQuery.Fields.FieldCount)+'">MyTable1</TH></TR>');
while not aQuery.Eof do
begin
Output.Writeln('<TR>');
Output.Writeln('<TD>' + IntToStr(aQuery.FieldByName('ID').AsInteger) + '</TD>');
Output.Writeln('<TD>' + aQuery.FieldByName('Field1').AsString + '</TD>');
Output.Writeln('</TR>');
aQuery.Next;
end;
Output.Writeln('</TABLE>');
end else
Output.Writeln('<P>No data found in query</P>');
Output.Writeln('</BODY></HTML>');
finally
if aQuery.Active then
aQuery.Close;
aQuery.Free;
aDB.Free;
aSession.Free;
end;
end.
%>
var
aSession: TnxSession;
aDB: TnxDatabase;
aQuery: TnxQuery;
begin
aSession := TnxSession.Create(nil);
aDB := TnxDatabase.Create(nil);
aQuery := TnxQuery.Create(nil);
aQuery.Database := aDB;
aSession.ServerEngine := ServerEngine;
aDB.Session := aSession;
aDB.AliasName := 'WillTestDB';
try
Output.Writeln('<HTML><HEAD><TITLE>PART 3 - Connect a Database</TITLE></HEAD><BODY>');
Output.Writeln('<H1>Part 3 - Connect a Database</H1>');
aSession.Open;
Output.Writeln('<P>Session open...</P>');
aDB.Open;
Output.Writeln('<P>Database open...</P>');
aQuery.SQL.Text := 'select * from MyTable1 order by ID';
aQuery.Open;
Output.Writeln('<P>Query open...</P>');
if not aQuery.IsEmpty then
begin
Output.Writeln('<P>Query contains data...</P>');
aQuery.First;
Output.Writeln('<TABLE>');
Output.Writeln('<TR><TH COLSPAN="'+IntToStr(aQuery.Fields.FieldCount)+'">MyTable1</TH></TR>');
while not aQuery.Eof do
begin
Output.Writeln('<TR>');
Output.Writeln('<TD>' + IntToStr(aQuery.FieldByName('ID').AsInteger) + '</TD>');
Output.Writeln('<TD>' + aQuery.FieldByName('Field1').AsString + '</TD>');
Output.Writeln('</TR>');
aQuery.Next;
end;
Output.Writeln('</TABLE>');
end else
Output.Writeln('<P>No data found in query</P>');
Output.Writeln('</BODY></HTML>');
finally
if aQuery.Active then
aQuery.Close;
aQuery.Free;
aDB.Free;
aSession.Free;
end;
end.
%>
This .nxScript should open a table showing the fields from MyTable1.
That is how we connect to a database, and display some data, in its most basic form.
Uses for this would be simply to display data from a query or table. There is no two-way interaction here though.
For that we need to make use of session data.
That is how we connect to a database, and display some data, in its most basic form.
Uses for this would be simply to display data from a query or table. There is no two-way interaction here though.
For that we need to make use of session data.