- 论坛徽章:
- 15
|
本帖最后由 yulihua49 于 2010-04-02 17:00 编辑
在设计表结构时,往往一个表没有合适的主键,常设计一个自增列,或default uuid列,插入数据时可以不插这个列。这个列往往用于外连接,可是插入后可能就找不到哪个是刚插入的了。此时必须使用带RETURNING子句的插入语句,在插入完成后立即得到键值。
- tuxticket@jgbticket:~/test> cat t_ret.c
- /**********************************************************
- * test RETURNING
- **********************************************************/
- #include <sqli.h>
- /*****************************************
- create table ttype (
- "id" number(2),
- "i64" number(20),
- "i2" number(4),
- "d8" number(12,2),
- "d4" number(4,2),
- "t1" timestamp default systimestamp,
- "r5" raw(128),
- "date1" date default sysdate,
- primary key ("t1")
- )
- *****************************************/
- typedef struct {
- char id;
- char i64[22];
- short i2;
- double d8;
- double d4;
- INT64 t1;
- char r5[128];
- char date1[YEAR_TO_SEC_LEN];
- } TTYPE_stu;
- main(int argc,char **argv)
- {
- T_SQL_Connect SQL_Connect;
- TTYPE_stu tt;
- int ret,sth;
- char stmt[4096],timestamp[30];
- struct timeval ts;
- INT64 now;
- if(argc>1) ret=envcfg(argv[1]);
- gettimeofday(&ts,0);
- ret=db_open(&SQL_Connect);
- if(ret) {
- printf("oprn database error!\n");
- return 1;
- }
- ___SQL_Transaction__(&SQL_Connect,TRANBEGIN);
- tt.id=14;
- tt.i2=0;
- tt.d8=1234567890.12;
- tt.d4=12.34;
- rsecstrfmt(tt.date1,now_sec(),"YYYY-MM-DD HH24:MI:SS");
- strcpy(stmt,"INSERT INTO TICKET.TTYPE (\"id\",\"i2\",\"d8\",\"d4\",\"date1\") VALUES(:1,:2,:3,:4,TO_DATE(:5,'YYYY-MM-DD HH24:MI:SS')) RETURNING TO_CHAR(\"t1\",'YYYY-MM-DD HH24:MI:SS.FF6') INTO :6");
- sth=sqlo_prepare(SQL_Connect.dbh,stmt);
- if(sth<0) {
- ___SQL_GetError(&SQL_Connect);
- printf("prepare %s err=%d,%s\n",stmt,
- SQL_Connect.Errno,
- SQL_Connect.ErrMsg);
- ___SQL_CloseDatabase__(&SQL_Connect);
- return 1;
- }
- if(SQLO_SUCCESS !=
- (sqlo_bind_by_pos(sth, 1, SQLOT_INT, &tt.id, sizeof(tt.id), NULL, 0)) ||
- (sqlo_bind_by_pos(sth, 2, SQLOT_INT, &tt.i2, sizeof(tt.i2), NULL, 0)) ||
- (sqlo_bind_by_pos(sth, 3, SQLOT_FLT, &tt.d8, sizeof(tt.d8), NULL, 0)) ||
- (sqlo_bind_by_pos(sth, 4, SQLOT_FLT, &tt.d4, sizeof(tt.d4), NULL, 0)) ||
- (sqlo_bind_by_pos(sth, 5, SQLOT_STR, tt.date1, sizeof(tt.date1), NULL, 0)) ||
- (sqlo_bind_by_pos(sth, 6, SQLOT_STR, timestamp, sizeof(timestamp), NULL, 0))) {
- ___SQL_GetError(&SQL_Connect);
- printf("bind %s err=%d,%s\n",stmt,
- SQL_Connect.Errno,
- SQL_Connect.ErrMsg);
- ___SQL_Close__(&SQL_Connect,sth);
- ___SQL_CloseDatabase__(&SQL_Connect);
- return 2;
- }
- ret=sqlo_execute(sth,1);
- if(SQLO_SUCCESS != ret) {
- ___SQL_GetError(&SQL_Connect);
- printf("execute %s err=%d,%s\n",stmt,
- SQL_Connect.Errno,
- SQL_Connect.ErrMsg);
- ___SQL_Close__(&SQL_Connect,sth);
- ___SQL_CloseDatabase__(&SQL_Connect);
- return 3;
- }
- printf("exec=%d,timestamp=%s\n",ret,timestamp);
- ___SQL_Close__(&SQL_Connect,sth);
- ___SQL_Transaction__(&SQL_Connect,TRANROLLBACK);
- ___SQL_CloseDatabase__(&SQL_Connect);
- return 0;
- }
- tuxticket@jgbticket:~/test> ./t_ret ld.ini
- exec=0,timestamp=2010-04-02 16:44:43.625854
复制代码 参考:
http://blog.chinaunix.net/u3/92831/showart_2205055.html |
|