linux下在C中嵌入SQL连接免费数据库PostgreSQL的问题
我想在linux下用在C语言中应用嵌入SQL的方法连接PostgreSQL数据库，看了一些书最后通过PQconnectdb的方式连上了但是没弄明白怎么用嵌入SQL语法的方式来连接和操作数据库
谁能给我一个具体的能成功编译运行的例子啊？最好是有详细注释的
[解决办法]
先编写.pgc文件如下:
#include <stdlib.h>
exec sql include sqlca;
main ()
{
exec sql connect to tcp:postgresql://127.0.0.1:5432/andy as conn user postgres;
exec sql BEGIN WORK;
exec sql UPDATE children SET fname = 'Gavin ' WHERE childno = 10;
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
然后用ecpg预处理为.c程序,再编译,就ok了
[解决办法]
/*建数据库过程略*/
/*结构*/
/*数据库名:wangxsDB
表名：t
a b c d e
1 1 a 1 1
2 2 b 2 2
3 3 c 3 3
4 4 d 4 4
5 5 e 5 5
6 6 f 6 6
*/
/*代码*/
#include <stdio.h>
#include <stdlib.h>
EXEC SQL include sqlca;
main()
{
EXEC SQL BEGIN DECLARE SECTION;
char result[3];
char database[20];
char username[20];
char password[20];
EXEC SQL END DECLARE SECTION;
strcpy(database, "wangxsDB ");
strcpy(username, "wangxs ");
EXEC SQL CONNECT TO :database USER :username;
EXEC SQL SELECT a INTO :result FROM t WHERE a=1;
printf( "result = %s\n ", result );
EXEC SQL DISCONNECT;
}
/*编译*/
/*?.dbc
ecpg ?.dbc -> ?.c*/
ecpg esqltest.dbc
[wangxs@localhost ~]$ cc ?.c -I /usr/local/postgre/include/ -L /usr/local/p
ostgre/lib/ -lecpg -lpq -o ?
/*或者*/
ecpg esqltest.dbc
[root@localhost wangxs]# cc ?.c -I /var/lib/pgsql/include -L /var/lib/pgsql
/lib -lecpg -lpq -o ?
/*makefile的编写*/
INCLUDE=/usr/local/postgre/include/
LIB=/usr/local/postgre/lib/
FILEDBC=esqltest.dbc
FILEC=esqltest.c
FILE=esqltest
ESQL=ecpg
CC=cc
.exe:$(FILEC)
$(CC) -I $(INCLUDE) -L $(LIB) -lecpg -lpq -o $(FILE)
.c:$(FILEDBC)
$(ESQL) $(FILEDBC)
[解决办法]
//File:mypgsql.h
#include <iostream>
#include <stdio.h>
#include <libpq-fe.h>
#include <stdlib.h>
#include <unistd.h>
#include <vector>
#include <string>
using namespace std;
//static pthread_mutex_t hci_mutex=PTHREAD_MUTEX_INITIALIZER;
class mypgsql{
private:
PGconn *conn;
PGresult *res;
int row;
int col;
public:
vector <string> vcol_head;//表头名
vector <string> vdata;//表数据,按行优先
mypgsql(string hostaddr= "127.0.0.1 ",string dbmane=NULL,string user= "usr "):vcol_head(),vdata()
{
string coninfo;
coninfo= "hostaddr= "+hostaddr+ " "+ "dbname= "+dbmane+ " user= "+user;
//sprintf(coninfo, "hostaddr=%s dbname=%s ", hostaddr, dbmane);
conn = PQconnectdb(coninfo.c_str());
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s \n ", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
//pthread_mutex_lock(&hci_mutex);
row = col = 0;
}
int exe(string s_exe)
{
res = PQexec(conn, s_exe.c_str());
if (PQresultStatus(res) == PGRES_TUPLES_OK)//成功执行一个返回数据的查询查询（比如 SELECT 或者 SHOW）
{
row = PQntuples(res);
col = PQnfields(res);
for(int i=0; i <col; i++)
vcol_head.push_back(PQfname(res, i));
for(int i=0; i <row; i++)
{
for(int j=0; j <col; j++)
vdata.push_back(PQgetvalue(res, i, j));
}
PQclear(res);
}
else if(PQresultStatus(res) == PGRES_COMMAND_OK)//成功完成一个不返回数据的命令
{
PQclear(res);
}
else
{
printf( "PgSql operation error! ");
PQclear(res);
return -1;//操作不成功，返回-1
}
return 0;//操作成功，返回0
}
~mypgsql()
{
PQfinish(conn);
//pthread_mutex_unlock(&hci_mutex);
}
};
//------------------------------------------------
//File:contest.cpp
#include <pthread.h>
#include "mypgsql.h "
#include <unistd.h>
#include <stdlib.h>
static int FNUM = 0;
int operatedb()
{
char sqlstr[100];
mypgsql pgsql( "192.168.1.3 ", "test ", "user ");
sprintf(sqlstr, "insert into filelist values( 'temp%d.exe ') ", FNUM);
pgsql.exe(sqlstr);
printf( "insert ----%d \n ",FNUM);
//pgsql.exe( "select * from filelist ");
//cout < < "-- " < <pgsql.vcol_head[0] < < "--thread " < <endl;
//for(int i=0; i <pgsql.vdata.size(); i++)
//cout < <pgsql.vdata[i] < <endl;
}
int deltb(void)
{
mypgsql pgsql( "192.168.1.3 ", "test ", "user ");
pgsql.exe( "delete from filelist ");
}
void *thread_function(void *arg)
{
for(int i=0; i <10; i++)
{
operatedb();
FNUM++;
}
return NULL;
}
int main(void)
{
deltb();
int pnum=300;
int i = 0;
pthread_t mythread[pnum];
for(i=0; i <pnum; i++)
{
if( pthread_create( &mythread[i], NULL, thread_function, NULL) )
{
printf( "error creating thread. ");
abort();
}
}
//sleep(1000);
for(i=0; i <pnum; i++)
{
//sleep(1);
printf( "i=%d \n ",i);
//if(mythread[i] == NULL)
//printf( "NULL \n ");
if ( pthread_join ( mythread[i], NULL ) )
{
printf( "error joining thread. ");
abort();
}
}
exit(0);
}
//-------------------------------------------
//File:Makefile
all:
cc -c -I/usr/local/pgsql/include contest.cpp
g++ -o contest contest.o -L/usr/local/pgsql/lib -lpq -lpthread
