Thread: ECPG and EXEC SQL GET DIAGNOSTICS

Hi
I am currently thinking about porting
Embedded esql/c code from informix to
PostgreSQL. It there any plans on incorporating
the "EXEC SQL GET DIAGNOSTICS" functionality?
Thanks to all.
ken

kjw wrote:
> I am currently thinking about porting
> Embedded esql/c code from informix to
> PostgreSQL. It there any plans on incorporating
> the "EXEC SQL GET DIAGNOSTICS" functionality?

It's already there.  What is missing?

To all,
Thanks for responding to my question and sorry for the delay
in answering your reply.

Informix ESQLC (9.2.1 ) has functionality that allows you to get the
status of
a previously excecuted sql statement.

/* there can be more than 1 exception, the "1" in the following example
refers to the 1st exception" */
EXEC SQL get diagnostics  exception 1
:_srvrname = SERVER_NAME, :_sqlstate_code = RETURNED_SQLSTATE,
:_message = MESSAGE_TEXT, :_messlen = MESSAGE_LENGTH;

This would place the DB Server name, Error message text (if any) and the
length
of the error message into the respective host variables.
This statement would compile and run under informix but fail
when compiled with ecpg (3.1.0/PostgreSQL 7.4.1) with the following
error code.
"test.pgc:19: ERROR: syntax error at or near "diagnostics" "

Compilation command: "ecpg -c -C INFORMIX test.pgc"

I have attached test.pgc

Thanks to all,
ken



Peter Eisentraut wrote:

>kjw wrote:
>
>
>>I am currently thinking about porting
>>Embedded esql/c code from informix to
>>PostgreSQL. It there any plans on incorporating
>>the "EXEC SQL GET DIAGNOSTICS" functionality?
>>
>>
>
>It's already there.  What is missing?
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>.
>
>
>

/*
 * compiles using informix esql 9.2.1 compiler but does not compile
 * using ecpg 3.1.0(PostgreSQL 7.4.1)
 */

int main (int argc, char **argv)
{

    /* Declare host variables */
    EXEC SQL BEGIN DECLARE SECTION;
        char _ix_database_name[30];
        int exception_count;
        char _message[100];
    EXEC SQL END DECLARE SECTION;

    /* Connect to the database. */
    EXEC SQL CONNECT TO 'test_db';

    EXEC SQL get diagnostics :exception_count = NUMBER ;

    /*
     * The GET DIAGNOSTICS statement also returns information about the
     * exceptions that the most-recently executed SQL statement has generated.
     */
    EXEC SQL get diagnostics  exception 1
    :_message = MESSAGE_TEXT;

    return (0);
}

kjw schrieb:
> To all,
> Thanks for responding to my question and sorry for the delay
> in answering your reply.
> 
> Informix ESQLC (9.2.1 ) has functionality that allows you to get the 
> status of
> a previously excecuted sql statement.
> 
> /* there can be more than 1 exception, the "1" in the following example 
> refers to the 1st exception" */
> EXEC SQL get diagnostics  exception 1
> :_srvrname = SERVER_NAME, :_sqlstate_code = RETURNED_SQLSTATE,
> :_message = MESSAGE_TEXT, :_messlen = MESSAGE_LENGTH;
> 
> This would place the DB Server name, Error message text (if any) and the 
> length
> of the error message into the respective host variables.
> This statement would compile and run under informix but fail
> when compiled with ecpg (3.1.0/PostgreSQL 7.4.1) with the following 
> error code.
> "test.pgc:19: ERROR: syntax error at or near "diagnostics" "
> 
> Compilation command: "ecpg -c -C INFORMIX test.pgc"

I really like this syntax! It's a nice way to get at information which 
historically had been accessed by SQLCA. Just like GET DESCRIPTOR 
encapsulates access to the SQLDA structure.

About two years ago, when I implemented get descriptor, get diagnostics 
was not there (and the error suggests it's still the case).

Supporting this feature would be not that difficult to add: you can 
follow the get descriptor design. [add keywords to scanner, add syntax 
and generated code to yacc, add function to library] If you want to 
implement it and need more information just ask me (or Michael Meskes).

But beware: You might soon run into the fact that SET DESCRIPTOR is not 
yet there at all (as are query parameters). Or perhaps you might need 
SQLDA (which never was implemented, too). But these are all shortcomings 
that I'm aware of. Sadly C embedded SQL is not as popular as JDBC or 
ODBC, so less people are enhancing it.
   Christof