在linux服务器上,建立几张表,然后将一个数据插入一张事先存在的表。写了一个pc文件---------------------------------------------------------
一些常用头文件EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;int ConnectDB();//连接始据库 成功返回0 失败返回1
void DisconnectDB() //断开数据库
{
EXEC SQL COMMIT RELEASE;
}int create_CA_Register_Key_table(char *Table_name);
int create_CA_Revoke_Key_table(char *Table_name);
int create_CA_Log_Manage_table(char *Table_name);
int insert_CA_into_Server_Cert_Table(char *CA_Name,char *sn,char *CAcert);int create_CA_Register_Key_table(char *Table_name)
{
EXEC SQL BEGIN DECLARE SECTION;
char table_name[60];
EXEC SQL END DECLARE SECTION;
memset(table_name, 0, 60);
strcpy(table_name, Table_name);
char qstr[4000];
memset(qstr,0,4000);
sprintf(qstr,"CREATE TABLE %s_Regist_Key(userCertNo varchar(32) NOT NULL,KRTime varchar(32) NOT NULL,taskNo varchar(32),Su_CommonName varchar(64),Is_CountryName varchar(64),Is_OrgName varchar(64),Is_OrgUnitName varchar(64),Is_CommonName varchar(64) NOT NULL,Is_IPAddress varchar(64),Is_ExtendInfo1 varchar(128),Is_ExtendInfo2 varchar(128),Is_ExtendInfo3 varchar(128),NotBefore varchar(14) NOT NULL,NotAfter varchar(14) NOT NULL,requestProof varchar(2048),dsCode varchar(128),extendInfo varchar(128),PrKLen smallint NOT NULL,appKeyType varchar(32) NOT NULL,CPrK varchar(2048) NOT NULL,MAC varchar(32) NOT NULL)",table_name); EXEC SQL EXECUTE IMMEDIATE :qstr;
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}int create_CA_Revoke_Key_table(char *Table_name)
{
EXEC SQL BEGIN DECLARE SECTION;
char table_name[60];
EXEC SQL END DECLARE SECTION;
memset(table_name, 0, 60);
strcpy(table_name, Table_name);
char qstr[4000];
memset(qstr,0,4000);
sprintf(qstr,"CREATE TABLE %s_Revoke_Key(userCertNo varchar(32) NOT NULL,KRTime varchar(32) NOT NULL,KVTime varchar(32) NOT NULL,taskNo varchar(32),Su_CommonName varchar(64),Is_CountryName varchar(64),Is_OrgName varchar(64),Is_OrgUnitName varchar(64),Is_CommonName varchar(64) NOT NULL,Is_IPAddress varchar(64),Is_ExtendInfo1 varchar(128),Is_ExtendInfo2 varchar(128),Is_ExtendInfo3 varchar(128),NotBefore varchar(14) NOT NULL,NotAfter varchar(14) NOT NULL,requestProof varchar(2048),dsCode varchar(128),extendInfo varchar(128),PrKLen smallint NOT NULL,appKeyType varchar(32) NOT NULL,CPrK varchar(2048) NOT NULL,MAC varchar(32) NOT NULL)",table_name);
EXEC SQL EXECUTE IMMEDIATE :qstr;
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}int create_CA_Log_Manage_table(char *Table_name)
{
EXEC SQL BEGIN DECLARE SECTION;
char table_name[60];
EXEC SQL END DECLARE SECTION;
memset(table_name, 0, 60);
strcpy(table_name, Table_name);
char qstr[4000];
memset(qstr,0,4000);
sprintf(qstr,"CREATE TABLE %s_Log_Manage(EvTime varchar(32) NOT NULL,userCertNo varchar(32) NOT NULL,Su_ComN varchar(128) NOT NULL,Event varchar(128) NOT NULL,Result varchar(128) NOT NULL,MAC varchar(32) NOT NULL)",table_name);
//printf ("%s\n",qstr);
EXEC SQL EXECUTE IMMEDIATE :qstr;
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
} EXEC SQL COMMIT WORK;
return 0;
}
int insert_CA_into_Server_Cert_Table(char *CA_Name,char *sn,char *CAcert)
{ EXEC SQL BEGIN DECLARE SECTION;
char ca_name[128] ={0};
char serial_num[32] ={0};
char ca_cert[2000] ={0};
char mac[32] ={0};
char sql[1500] = {0};
EXEC SQL END DECLARE SECTION; strcpy(ca_name,CA_Name);
strcpy(serial_num,sn);
strcpy(ca_cert,CAcert);
int len = 0;
len += sprintf(sql, "insert into %s ", "Server_Cert");
len += sprintf(sql+len, "values('%s',",ca_name);
len += sprintf(sql+len, "'%s',", ca_cert);
len += sprintf(sql+len, "'%s')", serial_num); EXEC SQL execute immediate :sql;
if (sqlca.sqlcode < 0)
{
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}
----------------------------------------------------------------------
在一个C文件里调用***********************************************************************常用头文件int main(int argc,char *argv[])
{
int retv;
retv=ConnectDB();
if(retv!=0)
{
printf("连接数据库失败.\n");
return 1;
} char CA_name[16]={0};
char Cert_Num[32]={0};
char Cert[2048]={0};
strcpy(CA_name,argv[1]);
strcpy(Cert_Num,argv[2]);
strcpy(Cert,argv[3]); retv=create_CA_Register_Key_table(CA_name);
if(retv!=0)
{
printf("创建CA_Register_Key_table失败 retv:%d\n",retv);
return 1;
}
retv=create_CA_Revoke_Key_table(CA_name);
if(retv!=0)
{
printf("创建CA_Revoke_Key_table失败 retv:%d\n",retv);
return 1;
} retv=create_CA_Log_Manage_table(CA_name);
if(retv!=0)
{
printf("创建CA_Log_Manage_table失败retv:%d\n",retv);
return 1;
} retv=insert_CA_into_Server_Cert_Table(CA_name,Cert_Num,Cert);
if(retv!=0)
{
printf("数据插入失败retv:%d\n",retv);
return 1;
} DisconnectDB();
return 0;
}
***************************************************************
在服务器上gdb 生成的可执行文件没问题 可是放到网页上调用的时候 显示建立第二张表的时候 出错 返回值为sqlca.sqlcode-1012
没登陆?! 这是怎么回事?更怪的是,查询数据库,三张表都已经建好了,数据也插好了。怪事。。
一些常用头文件EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;int ConnectDB();//连接始据库 成功返回0 失败返回1
void DisconnectDB() //断开数据库
{
EXEC SQL COMMIT RELEASE;
}int create_CA_Register_Key_table(char *Table_name);
int create_CA_Revoke_Key_table(char *Table_name);
int create_CA_Log_Manage_table(char *Table_name);
int insert_CA_into_Server_Cert_Table(char *CA_Name,char *sn,char *CAcert);int create_CA_Register_Key_table(char *Table_name)
{
EXEC SQL BEGIN DECLARE SECTION;
char table_name[60];
EXEC SQL END DECLARE SECTION;
memset(table_name, 0, 60);
strcpy(table_name, Table_name);
char qstr[4000];
memset(qstr,0,4000);
sprintf(qstr,"CREATE TABLE %s_Regist_Key(userCertNo varchar(32) NOT NULL,KRTime varchar(32) NOT NULL,taskNo varchar(32),Su_CommonName varchar(64),Is_CountryName varchar(64),Is_OrgName varchar(64),Is_OrgUnitName varchar(64),Is_CommonName varchar(64) NOT NULL,Is_IPAddress varchar(64),Is_ExtendInfo1 varchar(128),Is_ExtendInfo2 varchar(128),Is_ExtendInfo3 varchar(128),NotBefore varchar(14) NOT NULL,NotAfter varchar(14) NOT NULL,requestProof varchar(2048),dsCode varchar(128),extendInfo varchar(128),PrKLen smallint NOT NULL,appKeyType varchar(32) NOT NULL,CPrK varchar(2048) NOT NULL,MAC varchar(32) NOT NULL)",table_name); EXEC SQL EXECUTE IMMEDIATE :qstr;
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}int create_CA_Revoke_Key_table(char *Table_name)
{
EXEC SQL BEGIN DECLARE SECTION;
char table_name[60];
EXEC SQL END DECLARE SECTION;
memset(table_name, 0, 60);
strcpy(table_name, Table_name);
char qstr[4000];
memset(qstr,0,4000);
sprintf(qstr,"CREATE TABLE %s_Revoke_Key(userCertNo varchar(32) NOT NULL,KRTime varchar(32) NOT NULL,KVTime varchar(32) NOT NULL,taskNo varchar(32),Su_CommonName varchar(64),Is_CountryName varchar(64),Is_OrgName varchar(64),Is_OrgUnitName varchar(64),Is_CommonName varchar(64) NOT NULL,Is_IPAddress varchar(64),Is_ExtendInfo1 varchar(128),Is_ExtendInfo2 varchar(128),Is_ExtendInfo3 varchar(128),NotBefore varchar(14) NOT NULL,NotAfter varchar(14) NOT NULL,requestProof varchar(2048),dsCode varchar(128),extendInfo varchar(128),PrKLen smallint NOT NULL,appKeyType varchar(32) NOT NULL,CPrK varchar(2048) NOT NULL,MAC varchar(32) NOT NULL)",table_name);
EXEC SQL EXECUTE IMMEDIATE :qstr;
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}int create_CA_Log_Manage_table(char *Table_name)
{
EXEC SQL BEGIN DECLARE SECTION;
char table_name[60];
EXEC SQL END DECLARE SECTION;
memset(table_name, 0, 60);
strcpy(table_name, Table_name);
char qstr[4000];
memset(qstr,0,4000);
sprintf(qstr,"CREATE TABLE %s_Log_Manage(EvTime varchar(32) NOT NULL,userCertNo varchar(32) NOT NULL,Su_ComN varchar(128) NOT NULL,Event varchar(128) NOT NULL,Result varchar(128) NOT NULL,MAC varchar(32) NOT NULL)",table_name);
//printf ("%s\n",qstr);
EXEC SQL EXECUTE IMMEDIATE :qstr;
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
} EXEC SQL COMMIT WORK;
return 0;
}
int insert_CA_into_Server_Cert_Table(char *CA_Name,char *sn,char *CAcert)
{ EXEC SQL BEGIN DECLARE SECTION;
char ca_name[128] ={0};
char serial_num[32] ={0};
char ca_cert[2000] ={0};
char mac[32] ={0};
char sql[1500] = {0};
EXEC SQL END DECLARE SECTION; strcpy(ca_name,CA_Name);
strcpy(serial_num,sn);
strcpy(ca_cert,CAcert);
int len = 0;
len += sprintf(sql, "insert into %s ", "Server_Cert");
len += sprintf(sql+len, "values('%s',",ca_name);
len += sprintf(sql+len, "'%s',", ca_cert);
len += sprintf(sql+len, "'%s')", serial_num); EXEC SQL execute immediate :sql;
if (sqlca.sqlcode < 0)
{
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}
----------------------------------------------------------------------
在一个C文件里调用***********************************************************************常用头文件int main(int argc,char *argv[])
{
int retv;
retv=ConnectDB();
if(retv!=0)
{
printf("连接数据库失败.\n");
return 1;
} char CA_name[16]={0};
char Cert_Num[32]={0};
char Cert[2048]={0};
strcpy(CA_name,argv[1]);
strcpy(Cert_Num,argv[2]);
strcpy(Cert,argv[3]); retv=create_CA_Register_Key_table(CA_name);
if(retv!=0)
{
printf("创建CA_Register_Key_table失败 retv:%d\n",retv);
return 1;
}
retv=create_CA_Revoke_Key_table(CA_name);
if(retv!=0)
{
printf("创建CA_Revoke_Key_table失败 retv:%d\n",retv);
return 1;
} retv=create_CA_Log_Manage_table(CA_name);
if(retv!=0)
{
printf("创建CA_Log_Manage_table失败retv:%d\n",retv);
return 1;
} retv=insert_CA_into_Server_Cert_Table(CA_name,Cert_Num,Cert);
if(retv!=0)
{
printf("数据插入失败retv:%d\n",retv);
return 1;
} DisconnectDB();
return 0;
}
***************************************************************
在服务器上gdb 生成的可执行文件没问题 可是放到网页上调用的时候 显示建立第二张表的时候 出错 返回值为sqlca.sqlcode-1012
没登陆?! 这是怎么回事?更怪的是,查询数据库,三张表都已经建好了,数据也插好了。怪事。。
if(sqlca.sqlcode < 0)
{
EXEC SQL COMMIT RELEASE;
return (sqlca.sqlcode);
}
EXEC SQL COMMIT WORK;
return 0;
}
我想这里操作前是否应该先连接数据库?
2. 另外,创建表前先检查是否要drop table