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;
}

Answer 1

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;
}

Answer 2

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.

Answer 3

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;