PostgreSQL ecpg program doesn't save changes to the database

Question

I am using using postgresql server on my laptop, and trying to connect with the database through my C program, using epcg. I wrote the following commands to precompile, compile and run the my .pgc program.

PRE-COMPIILE - epcg sql.pgc  
COMPILE -   gcc -c sql.c -I/usr/include/postgresql   
RUN -  gcc -o sql sql.o -L/usr/lib -lecpg

My program is getting compiled and run successfully, i.e., it is showing no errors.
But, when I try to retrieve my database at the command line prompt, using command,
COMMAND - psql database

No updates are there in the tables,i.e., the commands which I have written in the program, are not getting updated in the database.

The following is the code in .pgc file :

#include<stdio.h>


int main()
{


    EXEC SQL CONNECT TO database;

    EXEC SQL create table player(player_id int,player_name varchar(255),team varchar(10));
    EXEC SQL create table player1(player_id int,player_name varchar(255),team varchar(10));
    EXEC SQL INSERT INTO player VALUES(1,'ram','a');

    EXEC SQL COMMIT;

    EXEC SQL DISCONNECT database;

    return 0;
}

The following is the code in C, after precompilation :

/* Processed by ecpg (4.7.0) */
/* These include files are added by the preprocessor */
#include <ecpglib.h>
#include <ecpgerrno.h>
#include <sqlca.h>
/* End of automatic include section */

#line 1 "sql.pgc"
#include<stdio.h>

int main()
{
    { ECPGconnect(__LINE__, 0, "vidisha@localhost:5432" , NULL, NULL , NULL, 0); }
#line 8 "sql.pgc"
    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table player ( player_id int , player_name varchar ( 255 ) , team varchar ( 10 ) )", ECPGt_EOIT, ECPGt_EORT);}
#line 10 "sql.pgc"
    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table player1 ( player_id int , player_name varchar ( 255 ) , team varchar ( 10 ) )", ECPGt_EOIT, ECPGt_EORT);}
#line 11 "sql.pgc"
    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into player values ( 1 , 'ram' , 'a' )", ECPGt_EOIT, ECPGt_EORT);}
#line 12 "sql.pgc"
    { ECPGtrans(__LINE__, NULL, "commit");}
#line 14 "sql.pgc"
    { ECPGdisconnect(__LINE__, "vidisha");}
#line 16 "sql.pgc"
    return 0;
}

are you sure the program is in fact connecting to the db? http://www.postgresql.org/docs/7.4/static/ecpg-connect.html — vector, Sep 04 '12 at 15:12

Nicolás Ozimica · Answer 1 · 2012-09-04T19:00:34.010

1

Using the script you wrote I didn't get any changes in my test DB too.

So, using @vector advice, if I gave the username of my test DB, everything works:

Using this makefile:

default:
    ecpg sql.pgc
    gcc -c sql.c -I/usr/include/postgresql
    gcc -o sql sql.o -L/usr/lib -lecpg

And, as an example, I'll use:

testdb as the database name
9876 as the port
testuser as the username
testpass as the password

with this sql.pgc:

#include <stdio.h>

int main() {
    EXEC SQL CONNECT TO testdb@localhost:9876 USER 'testuser' IDENTIFIED BY 'testpass';

    EXEC SQL create table player(player_id int,player_name varchar(255),team varchar(10));
    EXEC SQL create table player1(player_id int,player_name varchar(255),team varchar(10));
    EXEC SQL INSERT INTO player VALUES(1,'ram','a');

    EXEC SQL COMMIT;

    EXEC SQL DISCONNECT;

    return 0;
}

everything worked well.

edited Sep 04 '12 at 19:00

answered Sep 04 '12 at 15:52

Nicolás Ozimica

9,481
5
38
51

Thanks for the response, I tried your proposed solution, but its still not working. I guess, its not connecting to the database. What should I do now? – vidzi Sep 04 '12 at 18:38
I didn't put in my example the use of passwords. Perhaps that's what is missing in your case. I was using a testing db without it... – Nicolás Ozimica Sep 04 '12 at 18:56
That suggests that there's some really important error handling and status checking being omitted here. The program shouldn't just blindly try to continue on error. – Craig Ringer Sep 04 '12 at 22:06
Being this just a test program, the error handling and status checking is the next step to pursue. – Nicolás Ozimica Sep 04 '12 at 22:16

score 1 · Answer 2 · answered Sep 04 '12 at 19:04

You also have to provide the password and the default port is 5432. This code works fine for me. testdb- databaseName, postgres - username, 123321 - password

_Also after once executing this program you have to remove both the create table lines else your code will not work (as the tables are alredy created). You will get no error as you have not done error handling. To learn about error handling and more click here. _

#include<stdio.h>


int main()
{


    EXEC SQL CONNECT TO testdb@localhost:5432 USER 'postgres' USING '123321';

    EXEC SQL create table player(player_id int,player_name varchar(255),team varchar(10));
    EXEC SQL create table player1(player_id int,player_name varchar(255),team varchar(10));
    EXEC SQL INSERT INTO player VALUES(1,'ram','a');

    EXEC SQL COMMIT;

    EXEC SQL DISCONNECT database;

    return 0;
}

score 0 · Answer 3 · edited Apr 14 '17 at 15:50

0

You have to set search path to your current database.

EXEC SQL SET SEARCH_PATH TO "your database";

Also, at the end, don't forget to commit.

EXEC SQL COMMIT;

edited Apr 14 '17 at 15:50

Floern

33,559
24
104
119

answered Apr 14 '17 at 15:45

Prakash2403

1
2

PostgreSQL ecpg program doesn't save changes to the database

3 Answers3