Queries about TSQLDataset

[Enquiring Mind]

Hi,

Can anyone help with the following questions about TSQLDataset?

1. When executing a SQL 'SELECT' command, is each row downloaded separately
when SQLDataset.Open or SQLDataset.Next is called, or are all the rows
downloaded into a temporary variable when SQLDataset.Open is called, and
SQLDataset.Next just moves the cursor to the next row in the temporary
variable? For example, the purpose of the following code is to download all
the rows from a table into a custom data structure:

SQLDataset.CommandText:= 'SELECT * FROM ' + FTableName;
SQLDataset.Open;
SQLDataset.First;
while not SQLDataset.Eof do
begin
RecordI:= FBaseRecordClass.Create(Self);
RecordI.ReadFromSQLDataset(SQLDataset);
AddNewRecord(RecordI, Done);
SQLDataset.Next;
end;
If the table contains say 100 records, will there be 100 downloads, or just
one download?

2.Can NULL values be uploaded in parameters in SQL INSERT or UPDATE
statements? I tried the following code:

{Assign SQL command:}
SQLDataset.CommandText:= 'UPDATE Projects SET DateTimeModified=
:DatetimeModified '
+ ', Name= :Name, ParentProjectIndex= :ParentProjectIndex'
+ ' WHERE ProjectIndex= :ProjectID';
{Set input parameters:}
with SQLDataset.Params do
begin
{.}ParamByName('ProjectID').AsInteger:= FID;
{.}ParamByName('DatetimeModified').AsSQLTimestamp:=
FDatetimeModified;
{.}ParamByName('Name').AsString:= FName;
with {.}ParamByName('ParentProjectIndex') do
begin
if FParent= nil then
begin
{.}Clear; {Set parameter to Null}
{.}Bound:= True;
end
else
{.}AsInteger:= FParent.ID;
end;
end;
{Prepare and execute SQL command:}
SQLDataset.ExecSQL(False);
SQLDataset.Close;

When the condition FParent=nil applies, I was expecting the above code to
cause a NULL value to be uploaded for parameter 'ParentProjectIndex' - but
an error seems to be generated when this condition occurs. Can anyone
suggest any reasons for this?

3. If a sub-query is used to assign an auto-incremented value to a primary
key field when a new record is inserted in the database, is there a way of
getting the insertion command to return the field generated on the server?
Consider the following example:

SQLDataset.CommandText:= 'INSERT INTO Projects (ProjectIndex,'
+ 'DateTimeModified, Name, ParentProjectIndex)'
+ ' values ((select max(ProjectIndex) from Projects)+1,'
+ ' current_timestamp, :Name, :ParentProjectIndex)';
The sub-query assigns the required auto-incremented value to field
ProjectIndex on the server, but how can the client get this value returned
to it?

4. If a SQL command contains an IN predicate, can the IN item list be
uploaded in a parameter?

5. If the answer to 4 is that it's not possible, then to achieve the same
effect using a query parameter one might envisage doing something along the
following lines:

SQLDataset.CommandText:= 'Select * from Projects where ProjectIndex=
:ProjectIndex;

for i:= 0 to High(RecordArray) do
begin
RecordI:= RecordArray[i];
SQLDataset.Close;
SQLDataset.Params.ParamByName('ProjectIndex').AsInteger:=
RecordI.FIndex;
SQLDataset.Open;
if not SQLDataset.EOF then
RecordI.ReadFromSQLDataset(SQLDataset);
end;
end;

Would this work? Is there a more efficient way of achieving the same effect?
Should the TSQLDataset.Prepared parameter be set to True before the loop
starts?

Thanks in advance for any tips.

EM

[Bill Todd [TeamB]]

When you execute a query the SQL statement is sent to the database
server which parses and executes the SQL statement. In the case of a
SELECT the database server creates a result set and waits for the
client to request the results. Each time the client sends a FETCH
command to the server the server returns the next row in the result set.

TSQLDataSet is a forward only read only dataset. As you move the cursor
through the dataset by calling TSQLDataSet.Next it fetches the rows one
at a time. It does not buffer multiple rows on the client. That is the
function of TClientDataSet.

[Enquiring Mind]

Post by Bill Todd [TeamB]
When you execute a query the SQL statement is sent to the database
server which parses and executes the SQL statement. In the case of a
SELECT the database server creates a result set and waits for the
client to request the results. Each time the client sends a FETCH
command to the server the server returns the next row in the result set.
TSQLDataSet is a forward only read only dataset. As you move the cursor
through the dataset by calling TSQLDataSet.Next it fetches the rows one
at a time. It does not buffer multiple rows on the client. That is the
function of TClientDataSet.

Many thanks for that. So I take it that SQLDataset.Open causes the SQL
command to be executed on the server and the first row to be returned. Each
successive SQLDataset.Next call causes the next row to be fetched from the
server. What action exactly does the SQLDataset.Close call have?

When calling SQLDataset.ExecSQL repeatedly, is it advisable to call
SQLDataset.Close as well, or is this unnecessary?

If there are 2 TSQLDataset objects, SQLDatasetMaster for a master datset,
and SQLDatasetDetail for a Detail dataset, there is presumably no problem
fetching rows from each in nested do loops. For example:
SQLDatasetMaster.CommandText:= 'select * from Invoices where
Date>:StartDate';
SQLDatasetDetail.CommandText:= 'select * from InvoiceItems where InvoiceID=
:InvoiceID';
...
SQLDatasetMaster.Open;
while not SQLDatasetMaster.EOF do
begin
InvoiceI:= TInvoice.Create(Invoices);
InvoiceI.FID:= SQLDatasetMaster.FieldByName('InvoiceID').AsSmallInt;
Invoices.Add(InvoiceI);
SQLDatasetDetail.Params.ParamByName('InvoiceID').AsSmallInt:=
InvoiceI.FID;
SQLDatasetDetail.Open;
while not SQLDatasetDetail.EOF do
begin
InvoiceItemI:= TInvoiceItem.Create(InvoiceI.InvoiceItems);
InvoiceItemI.Date:=
SQLDatasetDetail.FieldByName('Description').AsString;
InvoiceI.InvoiceItems.Add(InvoiceItemI);
SQLDatasetDetail.Next;
end;
SQLDatasetDetail.Close;
SQLDatasetMaster.Next;
end;
SQLDatasetMaster.Close;

Do SQLDataset:= TSQLDataset.Create, SQLDataset.SQLConnection:=
SQLConnection1 and SQLDataset.Destroy only involve computations in the
client program?

Regards,

EM

[Bill Todd [TeamB]]

Post by Enquiring Mind
Many thanks for that. So I take it that SQLDataset.Open causes the
SQL command to be executed on the server and the first row to be
returned. Each successive SQLDataset.Next call causes the next row to
be fetched from the server. What action exactly does the
SQLDataset.Close call have?

SQLDataSetClose closes the cursor on the server.

Post by Enquiring Mind
When calling SQLDataset.ExecSQL repeatedly, is it advisable to call
SQLDataset.Close as well, or is this unnecessary?

It is not necessary. Close is required for SELECT statements so the
SQLDataSet can close the cursor on the server. This is how the server
knows that the client is finished with the result set returned by the
SELECT.

Post by Enquiring Mind
If there are 2 TSQLDataset objects, SQLDatasetMaster for a master
datset, and SQLDatasetDetail for a Detail dataset, there is
presumably no problem fetching rows from each in nested do loops.

That is correct.

Post by Enquiring Mind
Do SQLDataset:= TSQLDataset.Create, SQLDataset.SQLConnection:=
SQLConnection1 and SQLDataset.Destroy only involve computations in
the client program?

That is essentially correct. If you want to know in detail look at the
soruce code.