How to do a begin work, commit work in esql ?

[joseph v d'silva]

Hi,
I have to write an application (in C) where I have to pick up some
data by joining a few tables, and write some data to a file and some
to another table. This table basically serves as a info table as what
information was generated to the file last time, so has to be
updated/inserted each time a file is generated. Now if some how
writing to the file fails in between, I want the entire transaction
rolled back so that I can start the file generation afresh. So what I
am looking for is a "BEGIN WORK" at the begining of the program and a
"COMMIT WORK" at the end of the program. In between, If something goes
wrong I can do a "ROLLBACK WORK" and start allover again as the
database hasn't been changed.

What I want to know is whether this is possible in embedded sql in
Informix. If so how do I write embedded sql statements for such things
( what is the syntax ? some sample code could help !)

[Art S. Kagel]

Read the ESQL C manual (www.informix.com/answers) and look at the sample
programs provided in the SDK distribution as well as those posted to the
IIUG Software Repository. Short answer, if your database is logged then
YES you can do what you want to do, if not change its status to one of
the logged statuses. Note that ANSI style logged databases do not
require a BEGIN WORK statement.

Art S. Kagel

[007]

I recommend the ESQL/C Programmer's Manual. Over here, we RTFM when we
want to learn something.

http://www.informix.com/answers/english/alpha.thm#E

Good luck,

James

[joseph v d'silva]

I am not quite done with it , I get an error code of -256 (Transaction
not available) and the sqlca.sqlwarn.sqlwarn1 is a space character ('
'). I tried to follow the code in informix/demo/esqlc/unload.ec

here is my source code

#include <stdio.h>
#include <string.h>

$include sqlca;

int main(int argc, char** argv)
{
$ char query[512];
$ char name[21];
char ch;
int i;

$ database "joseph";
printf("%d\n", sqlca.sqlcode); /* error -256 */
printf(":%c:\n", sqlca.sqlwarn.sqlwarn1);

$ set lock mode to wait ;
printf("%d\n", sqlca.sqlcode);

sprintf(query,"%s","insert into my_temp1(name) values(?)");

$ begin work;
printf("%d\n", sqlca.sqlcode);

printf("Press any key to continue:");
getchar();

$ prepare ins_query from $query;
printf("%d\n", sqlca.sqlcode);

for(i=1; i<argc; i++)
{
strncpy(name, argv[i], sizeof(name)-1);
$ execute ins_query using $name;
printf("%d\n", sqlca.sqlcode);
}

printf("Press any key to continue:");
ch = getchar();

if(ch = 'c')
$ commit work;
else
$ rollback work;

printf("%d\n", sqlca.sqlcode); /* error -256 */

$ close database ;
printf("%d\n", sqlca.sqlcode);

return 0;
}

[Obnoxio The Clown]

joseph v d'silva wrote:

> I am not quite done with it , I get an error code of -256 (Transaction
> not available) and the sqlca.sqlwarn.sqlwarn1 is a space character ('
> '). I tried to follow the code in informix/demo/esqlc/unload.ec

You need to have database logging switched on. Set your TAPEDEV to
/dev/null or nul and run

ontape -s -L 0 -B yourdatabasename

[joseph v d'silva]

j_v_d...@www.com (joseph v d'silva) wrote in message news:<25bda1a5.02070...@posting.google.com>...

I am not quite done with it , I get an error code of -256 (Transaction

not available) and the sqlca.sqlwarn.sqlwarn1 is a space character ('
'). I tried to follow the code in informix/demo/esqlc/unload.ec

here is my source code

[Shetty Manjunath-W3085C]

Hi,

May be the problem with ANSI Compliant database. Remove Begin work and try. The BEGIN WORK statement is not needed because transactions are implicit. A warning is generated if you use a BEGIN WORK statement immediately after one of the following statements:

DATABASE
COMMIT WORK
CREATE DATABASE
ROLLBACK WORK
An error is generated if you use a BEGIN WORK statement after any other statement.

Check now.

Cheers,
Manjunath Shetty S.

[Art S. Kagel]

See below

joseph v d'silva wrote:

> I am not quite done with it , I get an error code of -256 (Transaction
> not available) and the sqlca.sqlwarn.sqlwarn1 is a space character ('
> '). I tried to follow the code in informix/demo/esqlc/unload.ec
>
> here is my source code
>
> #include <stdio.h>
> #include <string.h>
>
> $include sqlca;
>
> int main(int argc, char** argv)
> {
> $ char query[512];
> $ char name[21];
> char ch;
> int i;
>
> $ database "joseph";
> printf("%d\n", sqlca.sqlcode); /* error -256 */

I seriously doubt that you got -256 from a DATABASE statement.

> printf(":%c:\n", sqlca.sqlwarn.sqlwarn1);

OK that means that sqlca.sqlwarn.sqlwarn2 is not set to 'W' meaning that
your database was created without logging and therefore does not
support transactions. Either drop the database and create it again with
logging (preferably UNBUFFERED log) or use ontape or onbar to change the
logging status of the database to BUFFERED or UNBUFFERED (preferred).
THEN you will have transaction support.

>
> $ set lock mode to wait ;
> printf("%d\n", sqlca.sqlcode);
>
> sprintf(query,"%s","insert into my_temp1(name) values(?)");
>
> $ begin work;
> printf("%d\n", sqlca.sqlcode);

Here's where you likely got the -256 error.

>
> printf("Press any key to continue:");
> getchar();
>
> $ prepare ins_query from $query;
> printf("%d\n", sqlca.sqlcode);
>
> for(i=1; i<argc; i++)
> {
> strncpy(name, argv[i], sizeof(name)-1);
> $ execute ins_query using $name;
> printf("%d\n", sqlca.sqlcode);
> }
>
> printf("Press any key to continue:");
> ch = getchar();
>
> if(ch = 'c')
> $ commit work;
> else
> $ rollback work;
>
> printf("%d\n", sqlca.sqlcode); /* error -256 */
>
> $ close database ;
> printf("%d\n", sqlca.sqlcode);
>
> return 0;
> }
>

BTW using the dollar sign ($) to introduce SQL is Informix specific.
For portablility I prefer to use the EXEC SQL ANSI Embedded SQL standard.

Art S. Kagel