At 01:38 AM 29/01/2004 +0000, you wrote:

> Thanks for your comments with the code for the Stored Procedure.
> I remain uncertain, however, how exactly to do the following:
>(1) Create the Stored Procedure, and
>(2) Invoke the SP with the required values.

I told you *exactly* how to do (2) in the posting that you just replied
to. I gave you a sample of (1) in the posting before that.

>(1)
>To create the SP, do I issue a set of commands like the below:
>
> IB_Q.insertSQL.add('create procedure InsertOrUpdate (');
> IB_Q.insertSQL.add(' ID1 VARCHAR(12), ID2 VARCHAR(4),');
> IB_Q.insertSQL.add(' D1 INTEGER, D2 INTEGER, V1 DOUBLE)');
> IB_Q.insertSQL.add('as');
> IB_Q.insertSQL.add('declare variable PKValue integer;');
> IB_Q.insertSQL.add('begin');
> IB_Q.insertSQL.add(' Select ThePKey from T1');
> etc.

No, you are totally lost here. A stored procedure is a database object -
just as a table or an index or a trigger or a view is a database
object. It has to exist before you can use it.

DDL (Data Definition Language) commands are used to create, alter and drop
(delete) database objects. DDL creates metadata ("data about data").

DML (Data Manipulation Language) commands are used to create, alter and
delete data. DML statements start with SELECT, INSERT, UPDATE, DELETE or
EXECUTE. A DML statement to execute a procedure starts with EXECUTE PROCEDURE.

DSQL (Dynamic SQL) is a term used in IB and Fb to distinguish the API
flavour of SQL from the original InterBase SQL language (nowadays referred
to as ESQL, or embedded SQL) which is "static" - you precompile SQL
statements (embed them) in the client code and you don't use the API
directly through a client program like gds32.dll or fbclient.dll. PSQL is
a variant of static SQL specifically for writing triggers and stored
procedures.

You use the complex DDL command CREATE PROCEDURE to create a stored
procedure. At your stage of knowledge, don't even try to do this from your
own application. Use an admin tool that knows how to do it
properly. Committing the DDL statement to the database causes the SP to be
compiled. After that, it is available to be called from your client
applications using EXECUTE PROCEDURE or (if it's a selectable procedure,
which it won't be for your current task) using a SELECT statement.

Either use an admin tool like IB_SQL to create and compile your
SP interactively; or write a DDL script to do it using IB_SQL's script
utility.

>If so, do I end it with any call (like ExecSQL)?

Committing a stored procedure declaration doesn't execute it, it compiles
it into an executable database object, so you can use it in a DSQL statement.

ExecSQL is a Delphi method for executing a statement. PSQL (Firebird's
programming language) is not Delphi. The last thing in a stored procedure
declaration is the keyword END.

>Also, you use the variable "ThePKey". Is this actually a variable
>introduced into the SP, or was your intention that I replace that
>string with the actual name of the primary key (Constraint) I defined
>when I created the table?

the latter.

>(2)
>Regarding using the SP:
>I will be updating (or inserting) records in (or into) the table
>using data read from a text file. Each text line (record to update or
>add) will be parsed into a structure, MyRec, that will hold the
>values for that record. I will read each line, parse into MyRec,
>call the SP, and read again until the (large) text file has been
>exhausted.
>
>To use the SP, do I do the following:
>
> { read a text line }
> { parse the line into MyRec }
> IB_Q.sql.add('EXECUTE PROCEDURE InsertOrUpdate (');
> IB_Q.sql.add(':ID1, :ID2, :D1, :D2, :V1 )');
> IB_Q.sql.param[0].asString := MyRec.ID1;
> IB_Q.sql.param[1].asString := MyRec.ID2;
> IB_Q.sql.param[2].asInteger := MyRec.D1;
> IB_Q.sql.param[3].asInteger := MyRec.D2;
> IB_Q.sql.param[4].asFloat := MyRec.V1;
> IB_Q.execSQL;

Not really. For one thing, since you are not working with a dataset (which
you have failed to mention previously), you would not use an ib_query. An
ib_dsql or an ib_cursor would be used for this sort of operation - in which
case, we are not talking about the xxxxSQL methods of a dataset, but a
parameterised DML statement in its own right.

>You had referred to calling insert and post; aren't those Table (a la
>the BDE) methods? I'm not sure how to use them here, or with what
>component (I have only an IB_Connection and an IB_Query).

ib_query is the wrong component. And, no, insert *is* a method available
to any dataset. And Post is a method available (and essential) to any
statement, dataset or not.

As a generality, any data access object whose SQL property begins with the
word SELECT is a dataset. Any that begins with EXECUTE is
not. (tib_cursor can act for both).

>Do I call "Prepare" anywhere?? Any special settings to turn on or
>off in IB_Q that I normally would either ignore or leave at their
>default??

Get your head around the task first. You've chosen to use this rather
awkward method of reading your text records into a record structure. Your
task is to grab each field of that structure and pass it to a DML
statement. You begin by creating a container for the DML statement. (a
tib_dsql or a tib_cursor). There is no good reason not to do that part in
the IDE and to install the SQL property at design time, complete with
placeholder parameters.

Then, at run-time, you start a transaction, prepare your statement and then
loop through your input structure, assigning the appropriate values to the
parameters and calling execute when the record is populated. Call Execute
and loop back to the next input record. When you've come to the end of your
input, you fall out of the loop and commit the transaction.

IB/Fb have a far better way to read text records than this old-fashioned
stone-age record approach - the external virtual table - known in our
jargon as "external files". If you can get your input file into a
fixed-length format, you can just open it as a table and read the fields as
column values. Then you can use the TIB_Datapump and do this thing by magic.

>Sorry for asking these little details. The IB Developers Guide gives
>a rather confusing discussion of this topic and brings in additional
>components, so I felt I had no choice but to ask.

Forget the IB Developers Guide. It was written by tech writers, not
programmers. And it was written for a data access model that's different
in many ways from IBO.

Seriously, the reference you *really* need at the moment is the Language
Reference. You can't do IB/Fb without knowing SQL, specifically the
various flavours of IB's and Firebird's SQL statements. Once you
understand how that works, you'll understand how to use data access
components.

This list specialises in using IB Objects components with Delphi to write
client applications. By nature, it assumes you know all the SQL statements
and what they are for...and, conversely, what statements you need to use
for the tasks you want to do. For help with SQL and designing and creating
database objects, use the firebird-support list and the various doccos
relating to Firebird and IB.

Helen
p.s. this is the last ad hoc manual I plan to write today. I'm trying to
finish a book. :-|