Hi,

> I have just begun programming using interbase SQL, and got several
> questions, so please bear with me :o)
>
> 1- I have declared a variable in a stored procedure:
> DECLARE VARIABLA EOL CHAR(2);
> EOL=ASCII_CHAR(10)||ASCII_CHAR(13);
> but i keep getting the message: 'Arithmetic exception,
> numeric overflow, or
> string truncation'
> Even using EOL=ASCII_CHAR(10); produces the error, which
> leads me to think
> that the problem is with the ASCII_CHAR UDF. What could be wrong?

Which UDF library do you use? Have you tried other functions: CHR from
FUDLibrary or CHARACTER or CRLF (both from FreeUDFLib)?

> 2- I have 2 tables as follows:
> CREATE TABLE CURRENCIES (
> ID INTEGER,
> CURRENCY_NAME CHAR(30),
> PRIMARY KEY(ID));
>
> CREATE TABLE COUNTRIES (
> ID INTEGER,
> COUNTRY_NAME CHAR(30),
> CURRENCY1 INTEGER,
> CURRENCY2 INTEGER,
> PRIMARY KEY(ID),
> FOREIGN KEY (CURRENCY1) REFERENCES CURRENCIES (ID),
> FOREIGN KEY (CURRENCY2) REFERENCES CURRENCIES (ID));
>
> What's the correct SQL SELECT statement that will give me a list of
> countries with the names of both currencies?
> The statement:
> SELECT C.COUNTRY_NAME, D.CURRENCY_NAME FROM COUNTRIES C,
> CURRENCIES D WHERE
> C.CURRENCY1=D.ID;
> lists the countries with the name of the 1st currency. How to go about
> showing both names?

SELECT C.COUNTRY_NAME, D.CURRENCY_NAME, E.CURRENCY_NAME
FROM COUNTRIES C, CURRENCIES D, CURRENCIES E
WHERE C.CURRENCY1 = D.ID AND C.CURRENCY2 = E.ID;

> 3- The CREATE TABLE statement supports an EXTERNAL FILE option. Is it
> possible to set the external file name from a stored
> procedure at run-time?

Hmmm. You could try:

UPDATE RDB$RELATIONS
SET RDB$EXTERNAL_FILE = 'YOUR_NEW_FILE_PATH'
WHERE RDB$RELATION_NAME = 'YOUR_EXTERNAL_TABLE_NAME';

IMHO, your external table *must not* be opened at the moment of updating.
And your procedure must have efficient privileges to update system tables.

> 4- The INSERT statement supports a SELECT clause such as:
> INSERT INTO TABLE1 SELECT COL1 FROM TABLE2 WHERE COL2=0;
> The above statement will copy COL1 from TABLE2 to TABLE1 for
> all rows in
> TABLE2 where COL2=0. What's the best way to set COL2 to 1 for
> these records
> (and only these records) which have been copied, noting that
> in the meantime
> other users might be adding records to TABLE2?

Just implement this stuff as separate SQL commands and isolate them in a
snapshot transaction.

Cheers,
Dmitry