Re: Preparing UPDATE statement in ESQL/C
This is becoming a FAQ.
This is a modified version of what I sent to c.d.i at the beginning of
August on the subject. I've edited it (a lot) to make it more coherent
(and I've effectively removed the original question) because, in the
previous discussion, all sorts of amendments were made along the way and
various side-alleys were explored.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>From: bal@dpx.dpx.courier.kiev.ua (Bespaly Alex)
>Date: 25 Oct 1995 12:46:55 +0200
>X-Informix-List-Id: <news.18224>
>
> Is there any way in ESQL/C after $PREPAREing an
>UPDATE statement like:
>"update table1 set (field1,...) = (?,...,?) where <condition>"
> to find out the next info:
> - how many columns are being updated;
> - sqlvar entries for each of them;
>that is fully determined sqlda structure?
> When preparing INSERT/SELECT statements, sure, I have it, but
>what can I do with UPDATE?
===========================================================================
The DESCRIBE statement does not give you the description of the input
parameters of an UPDATE statement -- neither those on the RHS of the SET
clause, nor those in the condition part of the WHERE clause. It gives you
the description of the output data from the database for two statements,
SELECT and EXECUTE PROCEDURE, and even for these, you do not get a
description of the input parameters! The only statement for which you do
get a description of the input parameters is the INSERT INTO Table VALUES
statement, but this does not apply to either the UPDATE or the DELETE
statement (nor to the input parameters for SELECT or EXECUTE PROCEDURE).
Consider:
SELECT *
FROM SomeTable
WHERE SomeColumn IN (SELECT T2.AnotherColumn
FROM SomeOtherTable T2
WHERE T2.SomeColumn = ?)
If this statement is prepared and described, ESQL/C will give you an sqlda
structure with the result of expanding the "*", but it won't contain any
description of SomeColumn. Similarly, all the values in the UPDATE
statement are input parameters, and you have to know their types ahead of
time by some other mechanism than DESCRIBE.
If you really need to know what those types are, you can arrange to prepare
and describe a SELECT statement which lists the columns in the SELECT-list.
This will give you an sqlda structure with the information you require. In
my example, I would prepare and describe a SELECT statement, but it is
hardly what could be called convenient.
SELECT AnotherColumn FROM SomeOtherTable
SQL-92 mandates the DESCRIBE OUTPUT and DESCRIBE INPUT statements to
describe the input or output parameters of a statement. The information
you require in an UPDATE statement would be available via DESCRIBE INPUT.
Informix does not claim full compliance with SQL-92 with version 7.10 (only
with entry-level SQL-92, which is close to SQL-89, and this statement is
subject to correction by those in the know) and as of the 7.1 release of
ESQL/C, Informix does not support DESCRIBE INPUT, so you can't find the
information out directly.
FWIW, I am told that Sybase does support DESCRIBE INPUT -- I have
neither version information nor personal experience or documentation
to substantiate this, but I have no reason to disbelieve it.
===========================================================================
/* Sample code testing the behaviour of DESCRIBE */
#include <sqlda.h>
#include <stdio.h>
main()
{
struct sqlda *p_sqlda;
EXEC SQL DATABASE stores;
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
EXEC SQL PREPARE p_select FROM
"SELECT Customer_num FROM Customer WHERE lname = ?";
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
EXEC SQL DESCRIBE p_select INTO p_sqlda;
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
printf("sqlda.sqld = %ld\\n", p_sqlda->sqld);
EXEC SQL PREPARE p_insert FROM
"INSERT INTO Customer(Customer_num) VALUES(?)";
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
EXEC SQL DESCRIBE p_insert INTO p_sqlda;
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
printf("sqlda.sqld = %ld\\n", p_sqlda->sqld);
EXEC SQL PREPARE p_update FROM
"UPDATE Customer SET Lname = ? WHERE Customer_num = ?";
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
EXEC SQL DESCRIBE p_update INTO p_sqlda;
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
printf("sqlda.sqld = %ld\\n", p_sqlda->sqld);
EXEC SQL PREPARE p_delete FROM
"DELETE FROM Customer WHERE Customer_num = ?";
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
EXEC SQL DESCRIBE p_delete INTO p_sqlda;
printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode);
printf("sqlda.sqld = %ld\\n", p_sqlda->sqld);
return(0);
}
Output from sample code:
sqlca.sqlcode = 0
sqlda.sqld = 1
sqlca.sqlcode = 0
sqlca.sqlcode = 6
sqlda.sqld = 1
sqlca.sqlcode = 0
sqlca.sqlcode = 4
sqlda.sqld = 0
sqlca.sqlcode = 0
sqlca.sqlcode = 5
sqlda.sqld = 0