Re: [SQL] Questions about embedded-sql!

Anna Langer wrote:
>
> Hi!
>
> We are having some problems with writing embedded-sql. We want to
> write like this but it doesn't work. test1 is our table in our
> database.
>
> res = PQexec(conn, "select * from test1");
> if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> {
> fprintf(stderr, "DECLARE CURSOR command failed\n");
> PQclear(res);
> exit_nicely(conn);
> }
> PQclear(res);

"select * from test1" is not a 'command', at least not as libpq is
concerned. A select statement is some 'data-returning' thingie, so
you'll want to check for PGRES_TUPLES_OK instead of PGRES_COMMAND_OK.

> In the example that we have get this part from are they using CURSORS.
> Do you have to use them? Does anybody know where we can find some

Yes, cursors are very handy. It allows you to get to your datarows one
at a time without having to load them all into the memory of your
application. Basically:

BEGIN; --- cursors only work inside a transaction
DECLARE c CURSOR FOR select * from test1;
FETCH IN c; --- gets the first row
FETCH IN c; --- gets the second row
END; --- gets rid of the cursor *and* the transaction

> examples anbout embedded-sql in C or C++? Does anybody know where we
> can find anyting about CURSOR?

btw. what you are doing is not embedded SQL. You are using the Call
Level API (CLI). Embedded SQL enables you to put SQL statements inside
your program without having to bother with this CLI (i.e. the stuff you
do above). It does however require a pre-processor. You run this
preprocessor over your source code before you run the compiler. The
preprocessor converts all the SQL statements into the appropriate CLI
instructions. Postgresql has such a preprocessor for embedded SQL, it's
called ecpg. Read all about it in its documentation.

Maarten

--

Maarten Boekhold, boekhold(at)tibco(dot)com
TIBCO Finance Technology Inc.
The Atrium
Strawinskylaan 3051
1077 ZX Amsterdam, The Netherlands
tel: +31 20 3012158, fax: +31 20 3012358
http://www.tibco.com