免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2113 | 回复: 7
打印 上一主题 下一主题

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢? [复制链接]

论坛徽章:
0
跳转到指定楼层
1 [收藏(0)] [报告]
发表于 2003-02-23 16:14 |只看该作者 |倒序浏览
请问能不能提供一个和ORACLE打交道得程序呀,包括连接,执行SQL语句,和断开得,谢谢各位了

论坛徽章:
0
2 [报告]
发表于 2003-02-23 16:41 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

up  最好有测试oracle 的负载的代码   :(

论坛徽章:
0
3 [报告]
发表于 2003-02-23 19:41 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

下面这个够复杂的,不过功能比较强大。
摘自:如何跨越数据库平台。
全文: http://cwqu.3322.org/itf_db.htm



  1. #include <itf_db.h>;
  2. #include <stdio.h>;
  3. #include <tbase.h>;

  4. #include <sqlca.h>;
  5. #include <sqlda.h>;
  6. #include <oraca.h>;

  7. EXEC ORACLE OPTION (ORACA=YES);

  8. #include <ctype.h>;
  9. #include <unistd.h>;

  10. int vabs(int id)
  11. {
  12.   if(id<0) return 0-id;
  13.   else
  14.   return id;
  15. }

  16. typedef struct
  17. {
  18.   int  Initlized;
  19.   char DbName[100];
  20.   char UserName[100];
  21.   char PassWord[100];
  22. }DbMgrStru;

  23. extern "C"
  24. {
  25.   void sqlclu(SQLDA*);
  26.   struct SQLDA * sqlald(int, unsigned int, unsigned int);
  27.   void sqlnul(unsigned short*, unsigned short*, int*);
  28. };

  29. DbMgrStru DbMgr={0};

  30. int _ShowRunSql_Switch=1;

  31. /* 连接数据库 */
  32. int DbConnect(char * dbname,char * username,char * password)
  33. {
  34. EXEC SQL BEGIN DECLARE SECTION;
  35.   char * pusername=username;
  36.   char * ppassword=password;
  37. EXEC SQL END DECLARE SECTION;

  38.   strcpy(DbMgr.UserName,pusername);
  39.   strcpy(DbMgr.PassWord,ppassword);
  40.   EXEC SQL CONNECT :pusername IDENTIFIED BY :ppassword;
  41.   if(sqlca.sqlcode!=0)
  42.     printf("connect to database error : sqlcode is %d u: %s p: %s\n",sqlca.sqlcode,pusername,ppassword);
  43.   puts("\n");
  44.   puts("**************************************************************");
  45.   printf("             SIG ORA ITF IS STARTING: MaxConnection: %d\n",1);
  46.   puts("**************************************************************");
  47.   puts("\n");
  48.   return 1;
  49. }

  50. int DbBeginTran(DBDataMgr * ddm)
  51. {
  52.   DbExecuteSql(ddm,"begintran");
  53.   return 1;
  54. }

  55. int DbCommit(DBDataMgr * ddm)
  56. {
  57.   DbExecuteSql(ddm,"commit");
  58.   return 1;
  59. }

  60. int DbRollback(DBDataMgr * ddm)
  61. {
  62.   DbExecuteSql(ddm,"rollback");
  63.   return 1;
  64. }

  65. int DbDisconnect()
  66. {
  67.   DbExecuteSql(NULL,"commit");
  68.   EXEC SQL COMMIT WORK RELEASE;
  69.   return 1;
  70. }

  71. #define MAX_ITEMS         140
  72. #define MAX_VNAME_LEN     130
  73. #define MAX_INAME_LEN     130

  74. char * GetErrorMsg()
  75. {
  76. static char buf[1024];
  77.         sprintf(buf,"\n%.*s\n", sqlca.sqlerrm.sqlerrml,
  78.                                                  sqlca.sqlerrm.sqlerrmc);
  79.   return buf;
  80. }

  81. int DbShowError()
  82. {
  83.   printf("sqlerr: sqlcode is '%d' errmsg: '%s'\n",
  84.     sqlca.sqlcode,GetErrorMsg());
  85.   return 0;
  86. };

  87. int DbBatchInsert(DBDataMgr * ddm)
  88. {
  89. SQLDA * bind_dp=NULL;
  90. SQLDA * select_dp=NULL;
  91. int     id;
  92. char    value[120];
  93. int     preallocateflag=1;
  94. char    sqlcmd[2048];
  95. int     haveerror=0;

  96. EXEC SQL BEGIN DECLARE SECTION;
  97.   int     mtsid=0;
  98.   char *  psqlcmd=sqlcmd;
  99. EXEC SQL END DECLARE SECTION;

  100. struct   RowListStru * row,* next;
  101. ColListStru * col;
  102. char     info[120];
  103. int      valsize;

  104.   sprintf(sqlcmd,"insert into %s( ",ddm->;TableName);
  105.   for(id=0;id<ddm->;FieldCount;id++)
  106.   {
  107.     strcat(sqlcmd,ddm->;FieldName[id]);
  108.     if(id!=ddm->;FieldCount-1)
  109.       strcat(sqlcmd,", ");
  110.   };
  111.   strcat(sqlcmd,") values(");
  112.   for(id=0;id<ddm->;FieldCount;id++)
  113.   {
  114.     sprintf(value,":v%d",id);
  115.     strcat(sqlcmd,value);
  116.     if(id!=ddm->;FieldCount-1)
  117.       strcat(sqlcmd,", ");
  118.   };
  119.   strcat(sqlcmd,")");
  120.   printf("To run sqlcmd is '%s'\n",sqlcmd);

  121.   if(ddm!=NULL)
  122.     DD_SetSqlCode(ddm,0,"");
  123.   if ((bind_dp=sqlald(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN)) == (SQLDA *) 0)
  124.   {
  125.     printf("Cannot allocate memory for bind descriptor.");
  126.     if(ddm!=NULL)
  127.       DD_SetSqlCode(ddm,-2001,"Cannot allocate memory for bind descriptor.");
  128.     haveerror=1;
  129.     goto err_ret_lab;
  130.   }
  131.   for (id = 0; id < MAX_ITEMS; id++)
  132.   {
  133.     bind_dp->;I[id] = NULL;
  134.     bind_dp->;V[id] = NULL;
  135.   }
  136.   if ((select_dp=sqlald (MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN)) == (SQLDA *) 0)
  137.   {
  138.     printf("Cannot allocate memory for select descriptor.");
  139.     if(ddm!=NULL)
  140.       DD_SetSqlCode(ddm,-2001,"Cannot allocate memory for select descriptor.");
  141.     haveerror=1;
  142.     goto err_ret_lab;
  143.   }

  144.   select_dp->;N = MAX_ITEMS;
  145.   for (id = 0; id < MAX_ITEMS; id++)
  146.   {
  147.     bind_dp->;I[id] = (short *) IMem.Malloc(sizeof(short));
  148.     bind_dp->;V[id] = (char *) IMem.Malloc(1);

  149.     select_dp->;I[id] = (short *) IMem.Malloc(sizeof(short));
  150.     select_dp->;V[id] = (char *) IMem.Malloc(1);
  151.   }
  152.   bind_dp->;N = MAX_ITEMS;
  153.   EXEC SQL PREPARE S1 FROM :psqlcmd;

  154.   if(sqlca.sqlcode!=0)
  155.   {
  156.     if(ddm!=NULL)
  157.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  158.     haveerror=1;
  159.     goto err_ret_lab;
  160.   };

  161.   EXEC SQL DECLARE C1 CURSOR FOR S1;
  162.   if(sqlca.sqlcode!=0)
  163.   {
  164.     if(ddm!=NULL)
  165.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  166.     haveerror=1;
  167.     goto err_ret_lab;
  168.   };
  169.   EXEC SQL DESCRIBE BIND VARIABLES FOR S1 INTO bind_dp;
  170.   if(sqlca.sqlcode!=0)
  171.   {
  172.     if(ddm!=NULL)
  173.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  174.     haveerror=1;
  175.     goto err_ret_lab;
  176.   };
  177.   if (bind_dp->;F < 0)
  178.   {
  179.     printf ("\nToo many bind variables (%d), maximum is %d\n.",-bind_dp->;F, MAX_ITEMS);
  180.     if(ddm!=NULL)
  181.       DD_SetSqlCode(ddm,-2001,"Too many bind variables ");
  182.     haveerror=1;
  183.     goto err_ret_lab;
  184.   }
  185.   bind_dp->;N = bind_dp->;F;
  186.   row = ddm->;Head;
  187.   while(1)
  188.   {
  189.     if(row==NULL)
  190.       break;
  191.     for(id=0;id<ddm->;FieldCount;id++)
  192.     {
  193.       col=row->;Col + id;
  194.       valsize = (int)strlen(col->;Value);
  195.       bind_dp->;L[id] = valsize;
  196.       IMem.Free(bind_dp->;V[id]);

  197.       bind_dp->;V[id] = (char *) IMem.Malloc((bind_dp->;L[id] + 1));
  198.       strncpy(bind_dp->;V[id], col->;Value, valsize);
  199.       if ((strncmp(bind_dp->;V[id], "NULL", 4) == 0) ||
  200.          (strncmp(bind_dp->;V[id], "null", 4) == 0) || valsize==0)
  201.         *bind_dp->;I[id] = -1;
  202.       else
  203.         *bind_dp->;I[id] = 0;
  204.       bind_dp->;T[id] = 1;
  205.     };
  206.     EXEC SQL OPEN C1 USING DESCRIPTOR bind_dp;
  207.     if(sqlca.sqlcode!=0)
  208.     {
  209.       if(ddm!=NULL)
  210.         DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  211.       printf("sqlerr: sqlcode is '%d' errmsg: '%s'\n",sqlca.sqlcode,GetErrorMsg());
  212.       for(id=0;id<ddm->;FieldCount;id++)
  213.       {
  214.         col=row->;Col + id;
  215.         valsize = (int)strlen(col->;Value);
  216.         printf("id: %2d col: '%s' size: %d value: '%s'\n",id,ddm->;FieldName[id],valsize,col->;Value);
  217.       };
  218.       EXEC SQL ROLLBACK;
  219.       haveerror=1;
  220.       goto err_ret_lab;
  221.       //return 0;
  222.     };
  223.     next = row->;Next;
  224.     row = next;
  225.   };
  226.   EXEC SQL CLOSE C1;

  227. err_ret_lab:
  228.   for (id = 0; id < MAX_ITEMS; id++)
  229.   {
  230.     if (bind_dp->;V[id] != NULL)
  231.       IMem.Free(bind_dp->;V[id]);
  232.     if(bind_dp->;I[id] !=NULL)
  233.       IMem.Free(bind_dp->;I[id]);
  234.     if (select_dp->;V[id] != NULL)
  235.       IMem.Free(select_dp->;V[id]);
  236.     if(select_dp->;I[id] != NULL)
  237.       IMem.Free(select_dp->;I[id]);
  238.   }
  239.   if(bind_dp!=NULL)
  240.     sqlclu(bind_dp);

  241.   if(select_dp!=NULL)
  242.     sqlclu(select_dp);

  243.   if(haveerror)
  244.     return 0;

  245.   return 1;
  246. }

  247. int DbExecuteSql(DBDataMgr * ddm,char * sqlcmd)
  248. {
  249. SQLDA * bind_dp=NULL;
  250. SQLDA * select_dp=NULL;
  251. int     id;
  252. int     null_ok, precision, scale;
  253. char    value[8192],fname[120],head[300],fieldtype;
  254. int     mtsid=0,preallocateflag=1,fieldsize=0,fielddecsize=0;
  255. EXEC SQL BEGIN DECLARE SECTION;
  256.   char *  psqlcmd=sqlcmd;
  257. EXEC SQL END DECLARE SECTION;
  258. int haveerror=0;
  259. int isnumber[200];

  260.   if(ddm!=NULL)
  261.   {
  262.     DD_Free(ddm);
  263.     DD_Initlize(ddm);
  264.   };

  265.   if(_ShowRunSql_Switch)
  266.     printf("want to run sql: '%s'\n",sqlcmd);
  267.   sscanf(sqlcmd,"%s",head);
  268.   scale = (int)strlen(head);
  269.   IString.Allt(head);

  270.   for(id=0;id<scale;id++)
  271.     head[id]=toupper(head[id]);

  272.   if(!strcmp(head,"BEGINTRAN"))
  273.     return 1;

  274.   DD_SetSqlCode(ddm,0,"");

  275.   if ((strncmp(head,"SELECT",6)!=0))
  276.   {
  277.     DD_SetRecordCount(ddm,0);
  278.     DD_SetFieldCount(ddm,0);

  279.     EXEC SQL EXECUTE IMMEDIATE :psqlcmd;

  280.     if(vabs(sqlca.sqlcode)==1403) sqlca.sqlcode=0;
  281.     if(vabs(sqlca.sqlcode)==1405) sqlca.sqlcode=0;

  282.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());

  283.     if(sqlca.sqlcode!=0)
  284.       DbShowError();

  285.     return 1;
  286.   };

  287.   if ((bind_dp=sqlald(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN)) == (SQLDA *) 0)
  288.   {
  289.     printf("Cannot allocate memory for bind descriptor.");
  290.     DD_SetSqlCode(ddm,-2001,"Cannot allocate memory for bind descriptor.");
  291.     haveerror=1;
  292.     goto err_ret_lab_sql;
  293.   }
  294.   for (id = 0; id < MAX_ITEMS; id++)
  295.   {
  296.     bind_dp->;I[id] = NULL;
  297.     bind_dp->;V[id] = NULL;
  298.   };
  299.   if ((select_dp=sqlald (MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN)) == (SQLDA *) 0)
  300.   {
  301.     printf("Cannot allocate memory for select descriptor.");
  302.     DD_SetSqlCode(ddm,-2001,"Cannot allocate memory for select descriptor.");
  303.     haveerror=1;
  304.     goto err_ret_lab_sql;
  305.   }
  306.   select_dp->;N = MAX_ITEMS;
  307.   for (id = 0; id < MAX_ITEMS; id++)
  308.   {
  309.     bind_dp->;I[id] = (short *) IMem.Malloc(sizeof(short),"binddp_i");
  310.     bind_dp->;V[id] = (char *) IMem.Malloc(1,"binddp_v");
  311.     select_dp->;I[id] = (short *) IMem.Malloc(sizeof(short),"selectdp_i");
  312.     select_dp->;V[id] = (char *) IMem.Malloc(1,"selectdp_v");
  313.   }
  314.   bind_dp->;N = MAX_ITEMS;
  315.   EXEC SQL PREPARE S0 FROM :psqlcmd;
  316.   if(sqlca.sqlcode!=0)
  317.   {
  318.     printf("prepare error %d\n",sqlca.sqlcode);
  319.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  320.     haveerror=1;
  321.     goto err_ret_lab_sql;
  322.   };
  323.   EXEC SQL DECLARE C0 CURSOR FOR S0;
  324.   if(sqlca.sqlcode!=0)
  325.   {
  326.     printf("declare error %d\n",sqlca.sqlcode);
  327.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  328.     haveerror=1;
  329.     goto err_ret_lab_sql;
  330.   };
  331.   EXEC SQL DESCRIBE BIND VARIABLES FOR S0 INTO bind_dp;
  332.   if(sqlca.sqlcode!=0)
  333.   {
  334.     printf("describe error %d\n",sqlca.sqlcode);
  335.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  336.     haveerror=1;
  337.     goto err_ret_lab_sql;
  338.   };
  339.   if (bind_dp->;F < 0)
  340.   {
  341.     printf ("\nToo many bind variables (%d), maximum is %d\n.",-bind_dp->;F, MAX_ITEMS);
  342.     DD_SetSqlCode(ddm,-2001,"Too many bind variables ");
  343.     haveerror=1;
  344.     goto err_ret_lab_sql;
  345.   }
  346.   bind_dp->;N = bind_dp->;F;
  347.   EXEC SQL OPEN C0 USING DESCRIPTOR bind_dp;

  348.   /* 无记录 */
  349.   if(vabs(sqlca.sqlcode)==1403) sqlca.sqlcode=0;

  350.   /* 提取的值为空 */
  351.   if(vabs(sqlca.sqlcode)==1405) sqlca.sqlcode=0;

  352.   if(sqlca.sqlcode!=0)
  353.   {
  354.     printf("open error %d\n",sqlca.sqlcode);
  355.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  356.     haveerror=1;
  357.     goto err_ret_lab_sql;
  358.   };

  359.   select_dp->;N = MAX_ITEMS;
  360.   EXEC SQL DESCRIBE SELECT LIST FOR S0 INTO select_dp;

  361.   if (select_dp->;F < 0)
  362.   {
  363.     printf ("\nToo many select - list items (%d), maximum is %d\n",
  364.              -(select_dp->;F), MAX_ITEMS);
  365.     DD_SetSqlCode(ddm,-2001,"Too many select - list items");
  366.     haveerror=1;
  367.     goto err_ret_lab_sql;
  368.   }
  369.   if(sqlca.sqlcode!=0)
  370.   {
  371.     printf("describe select list error %d\n",sqlca.sqlcode);
  372.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  373.     haveerror=1;
  374.     goto err_ret_lab_sql;
  375.   };
  376.   DD_SetRecordCount(ddm,0);
  377.   DD_SetFieldCount(ddm,select_dp->;F);
  378.   select_dp->;N = select_dp->;F;
  379.   for (id = 0; id < select_dp->;F; id++)
  380.   {
  381.     isnumber[id]=0;
  382.     sqlnul ((unsigned short *)&(select_dp->;T[id]), (unsigned short *)&(select_dp->;T[id]), &null_ok);
  383.     switch (select_dp->;T[id])
  384.     {
  385.       /* 这里是字符类型 */
  386.       case  1 :
  387.         break;

  388.       /* 这里是数值类型,要确定小数点,强行转为字符型了 */
  389.       case  2 :
  390.         isnumber[id]=1;
  391.         select_dp->;T[id] = 1;
  392.         select_dp->;L[id] = 100;
  393.         precision = 50;
  394.         scale = 6;
  395.         #ifdef MODIFIED_BY_PROGRAM_NOTE
  396.           sqlprc (&(select_dp->;L[id]), &precision, &scale);
  397.           if (precision == 0) precision = 40;
  398.           if (scale >; 0)
  399.             select_dp->;L[id] = sizeof(float);
  400.           else
  401.             select_dp->;L[id] = sizeof(int);
  402.         #endif
  403.         break;

  404.       /* LONG类型 */
  405.       case  8 :
  406.         select_dp->;L[id] = 240;
  407.         break;

  408.       /* ROWID 类型 */
  409.       case 11 :
  410.         select_dp->;L[id] = 18;
  411.         break;

  412.       /* 日期类型 */
  413.       case 12 :
  414.         select_dp->;L[id] = 9;
  415.         break;

  416.       /* RAW 类型 */
  417.       case 23 :
  418.         break;

  419.       /* LONG RAW 类型 */
  420.       case 24 :
  421.         select_dp->;L[id] = 240;
  422.         break;
  423.     }
  424.     if (select_dp->;T[id] != 2)    /* 不是数字类型 */
  425.     {
  426.       fieldtype = 'C';
  427.       IMem.Free(select_dp->;V[id]);
  428.       select_dp->;V[id] = (char *) IMem.Malloc(select_dp->;L[id] + 1,"selectdp_vrel");
  429.       //select_dp->;V[id] = (char *) realloc(select_dp->;V[id],select_dp->;L[id] + 1);
  430.     }
  431.     else
  432.     {
  433.       fieldtype = 'N';
  434.       IMem.Free(select_dp->;V[id]);

  435.       select_dp->;V[id] = (char *) IMem.Malloc(select_dp->;L[id] + 1,"selectdp_vrel");
  436.       //select_dp->;V[id] = (char *) realloc(select_dp->;V[id],select_dp->;L[id]);
  437.     };

  438.     /* 不是LONGRAW 与数字类型,均转为字符类型 */
  439.     if (select_dp->;T[id] != 24 && select_dp->;T[id] != 2)
  440.       select_dp->;T[id] = 1;

  441.     /* 数字类型,判断小数点 */
  442.     if (select_dp->;T[id] == 2)
  443.       if (scale >; 0)
  444.         select_dp->;T[id] = 4;
  445.       else
  446.         select_dp->;T[id] = 3;

  447.     /* 字段名称长度要限制 */
  448.     if((int)strlen(select_dp->;S[id])>;4096)
  449.       *(select_dp->;S[id] + 4096) = 0;

  450.     /* 取字段名称 */
  451.     strcpy(value,select_dp->;S[id]);
  452.     IString.Allt(value);
  453.     sscanf(value,"%s",fname);
  454.     /*
  455.     printf("Field %d is '%s' type %d\n",id,fname,select_dp->;T[id]);
  456.     */
  457.     DD_SetFieldName(ddm,id,fname);
  458.     DD_SetFieldType(ddm,id,fieldtype);
  459.     DD_SetFieldSize(ddm,id,123);
  460.     DD_SetFieldDecSize(ddm,id,789);
  461.   }
  462.   while(1)
  463.   {
  464.     if(sqlca.sqlcode!=0)
  465.     {
  466.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  467.       DbShowError();
  468.       break;
  469.     };
  470.     EXEC SQL FETCH C0 USING DESCRIPTOR select_dp;
  471.     /* 无记录了 */
  472.     if(vabs(sqlca.sqlcode)==1403)
  473.       break;
  474.     /* 提取的值为空 */
  475.     if(vabs(sqlca.sqlcode)==1405) sqlca.sqlcode=0;
  476.     /* 有错误了 */
  477.     if(vabs(sqlca.sqlcode)!=0)
  478.     {
  479.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  480.       DbShowError();
  481.       break;
  482.     };
  483.     /* printf("select_dp_F is %d\n",select_dp->;F); */
  484.     DD_AddRow(ddm);
  485.     for (id = 0; id < select_dp->;F; id++)
  486.     {
  487.       if (*select_dp->;I[id] < 0)
  488.       {
  489.         if (select_dp->;T[id] == 4)
  490.         {
  491.           sprintf (value,"%-*c ",(int)select_dp->;L[id]+3, ' ');
  492.         }
  493.         else
  494.         {
  495.           sprintf (value,"%-*c ",(int)select_dp->;L[id], ' ');
  496.         };
  497.       }
  498.       else
  499.       {
  500.         if (select_dp->;T[id] == 3)
  501.         {
  502.           sprintf (value,"%*d ", (int)select_dp->;L[id],*(int *)select_dp->;V[id]);
  503.         }
  504.         else
  505.           if (select_dp->;T[id] == 4)
  506.           {
  507.             sprintf (value,"%*.2f ", (int)select_dp->;L[id],*(float *)select_dp->;V[id]);
  508.           }
  509.           else
  510.           {
  511.             if(select_dp->;L[id]>;1500)
  512.             {
  513.               memcpy(value,select_dp->;V[id],1500);
  514.               value[1500]=0;
  515.             }
  516.             else
  517.               sprintf (value,"%-*.*s ", (int)select_dp->;L[id],(int)select_dp->;L[id], select_dp->;V[id]);
  518.           }
  519.       };
  520.       if(isnumber[id])
  521.         IString.Allt(value);
  522.       else
  523.         IString.AlltRight(value);
  524.       DD_SetValue(ddm,id,value);
  525.       /*
  526.       printf("field %2d type: %d value is '%s' - mtsid: %d - %d\n",
  527.         id,select_dp->;T[id],value,mtsid,thr_self());
  528.       */
  529.     }
  530.   }
  531.   EXEC SQL CLOSE C0;

  532. err_ret_lab_sql:

  533.   for (id = 0; id < MAX_ITEMS; id++)
  534.   {
  535.     if (bind_dp->;V[id] != NULL)
  536.       IMem.Free(bind_dp->;V[id]);

  537.     if (bind_dp->;I[id] != NULL)
  538.       IMem.Free(bind_dp->;I[id]);

  539.     if (select_dp->;V[id] != NULL)
  540.       IMem.Free(select_dp->;V[id]);

  541.     if(select_dp->;I[id] != NULL)
  542.       IMem.Free(select_dp->;I[id]);
  543.   }
  544.   if(bind_dp!=NULL)
  545.     sqlclu(bind_dp);
  546.   if(select_dp!=NULL)
  547.     sqlclu(select_dp);

  548.   if(haveerror)
  549.     return 0;

  550.   return 1;
  551. }

  552. int DbSelectRow(DBDataMgr * ddm,char * sqlcmd,int (* data_handle)(void *,void *),void * dbioptr,void * ass_ptr)
  553. {
  554. SQLDA * bind_dp;
  555. SQLDA * select_dp;
  556. int     id;
  557. int     null_ok, precision, scale;
  558. char    value[8192],fname[120],head[300];
  559. int     mtsid=0,preallocateflag=1;
  560. EXEC SQL BEGIN DECLARE SECTION;
  561.   char *  psqlcmd=sqlcmd;
  562. EXEC SQL END DECLARE SECTION;
  563. int isnumber[200];

  564.   if(ddm!=NULL)
  565.   {
  566.     DD_Free(ddm);
  567.     DD_Initlize(ddm);
  568.   };
  569.   if(_ShowRunSql_Switch)
  570.     printf("want to run sql: '%s'\n",sqlcmd);

  571.   sscanf(sqlcmd,"%s",head);
  572.   scale = (int)strlen(head);
  573.   IString.Allt(head);
  574.   for(id=0;id<scale;id++)
  575.     head[id]=toupper(head[id]);
  576.   if(!strcmp(head,"BEGINTRAN"))
  577.     return 1;

  578.   DD_SetSqlCode(ddm,0,"");

  579.   if ((strncmp(head,"SELECT",6)!=0))
  580.     return 0;

  581.   if ((bind_dp=sqlald(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN)) == (SQLDA *) 0)
  582.   {
  583.     printf("Cannot allocate memory for bind descriptor.");
  584.     DD_SetSqlCode(ddm,-2001,"Cannot allocate memory for bind descriptor.");
  585.     return 0;
  586.   }
  587.   if ((select_dp=sqlald (MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN)) == (SQLDA *) 0)
  588.   {
  589.     printf("Cannot allocate memory for select descriptor.");
  590.     DD_SetSqlCode(ddm,-2001,"Cannot allocate memory for select descriptor.");
  591.     return 0;
  592.   }
  593.   select_dp->;N = MAX_ITEMS;
  594.   for (id = 0; id < MAX_ITEMS; id++)
  595.   {
  596.     bind_dp->;I[id] = (short *) IMem.Malloc(sizeof(short));
  597.     bind_dp->;V[id] = (char *) IMem.Malloc(1);
  598.     select_dp->;I[id] = (short *) IMem.Malloc(sizeof(short));
  599.     select_dp->;V[id] = (char *) IMem.Malloc(1);
  600.   }
  601.   bind_dp->;N = MAX_ITEMS;
  602.   EXEC SQL PREPARE S0 FROM :psqlcmd;
  603.   if(sqlca.sqlcode!=0)
  604.   {
  605.     printf("prepare error %d\n",sqlca.sqlcode);
  606.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  607.     return 0;
  608.   };
  609.   EXEC SQL DECLARE C5 CURSOR FOR S0;
  610.   if(sqlca.sqlcode!=0)
  611.   {
  612.     printf("declare error %d\n",sqlca.sqlcode);
  613.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  614.     return 0;
  615.   };
  616.   EXEC SQL DESCRIBE BIND VARIABLES FOR S0 INTO bind_dp;
  617.   if(sqlca.sqlcode!=0)
  618.   {
  619.     printf("describe error %d\n",sqlca.sqlcode);
  620.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  621.     return 0;
  622.   };
  623.   if (bind_dp->;F < 0)
  624.   {
  625.     printf ("\nToo many bind variables (%d), maximum is %d\n.",-bind_dp->;F, MAX_ITEMS);
  626.     DD_SetSqlCode(ddm,-2001,"Too many bind variables ");
  627.     return 0;
  628.   }
  629.   bind_dp->;N = bind_dp->;F;
  630.   EXEC SQL OPEN C5 USING DESCRIPTOR bind_dp;

  631.   /* 无记录 */
  632.   if(vabs(sqlca.sqlcode)==1403) sqlca.sqlcode=0;

  633.   /* 提取的值为空 */
  634.   if(vabs(sqlca.sqlcode)==1405) sqlca.sqlcode=0;

  635.   if(sqlca.sqlcode!=0)
  636.   {
  637.     printf("open error %d\n",sqlca.sqlcode);
  638.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  639.     return 0;
  640.   };

  641.   select_dp->;N = MAX_ITEMS;
  642.   EXEC SQL DESCRIBE SELECT LIST FOR S0 INTO select_dp;

  643.   if (select_dp->;F < 0)
  644.   {
  645.     printf ("\nToo many select - list items (%d), maximum is %d\n",
  646.              -(select_dp->;F), MAX_ITEMS);
  647.     DD_SetSqlCode(ddm,-2001,"Too many select - list items");
  648.     return 0;
  649.   }
  650.   if(sqlca.sqlcode!=0)
  651.   {
  652.     printf("describe select list error %d\n",sqlca.sqlcode);
  653.     DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  654.     return 0;
  655.   };
  656.   DD_SetRecordCount(ddm,0);
  657.   DD_SetFieldCount(ddm,select_dp->;F);
  658.   select_dp->;N = select_dp->;F;
  659.   for (id = 0; id < select_dp->;F; id++)
  660.   {
  661.     sqlnul ((unsigned short *)&(select_dp->;T[id]), (unsigned short *)&(select_dp->;T[id]), &null_ok);
  662.     isnumber[id]=0;
  663.     switch (select_dp->;T[id])
  664.     {
  665.       /* 这里是字符类型 */
  666.       case  1 :
  667.         break;

  668.       /* 这里是数值类型,要确定小数点,强行转为字符型了 */
  669.       case  2 :
  670.         isnumber[id]=1;
  671.         select_dp->;T[id] = 1;
  672.         select_dp->;L[id] = 100;
  673.         precision = 50;
  674.         scale = 6;
  675.         #ifdef MODIFIED_BY_PROGRAM_NOTE
  676.           sqlprc (&(select_dp->;L[id]), &precision, &scale);
  677.           if (precision == 0) precision = 40;
  678.           if (scale >; 0)
  679.             select_dp->;L[id] = sizeof(float);
  680.           else
  681.             select_dp->;L[id] = sizeof(int);
  682.         #endif
  683.         break;

  684.       /* LONG类型 */
  685.       case  8 :
  686.         select_dp->;L[id] = 240;
  687.         break;

  688.       /* ROWID 类型 */
  689.       case 11 :
  690.         select_dp->;L[id] = 18;
  691.         break;

  692.       /* 日期类型 */
  693.       case 12 :
  694.         select_dp->;L[id] = 9;
  695.         break;

  696.       /* RAW 类型 */
  697.       case 23 :
  698.         break;

  699.       /* LONG RAW 类型 */
  700.       case 24 :
  701.         select_dp->;L[id] = 240;
  702.         break;
  703.     }
  704.     IMem.Free(select_dp->;V[id]);
  705.     if (select_dp->;T[id] != 2)    /* 不是数字类型 */
  706.       select_dp->;V[id] = (char *) IMem.Malloc(select_dp->;L[id] + 1);
  707.     else
  708.       select_dp->;V[id] = (char *) IMem.Malloc(select_dp->;L[id]);

  709.     /* 不是LONGRAW 与数字类型,均转为字符类型 */
  710.     if (select_dp->;T[id] != 24 && select_dp->;T[id] != 2)
  711.       select_dp->;T[id] = 1;

  712.     /* 数字类型,判断小数点 */
  713.     if (select_dp->;T[id] == 2)
  714.       if (scale >; 0)
  715.         select_dp->;T[id] = 4;
  716.       else
  717.         select_dp->;T[id] = 3;

  718.     /* 字段名称长度要限制 */
  719.     if((int)strlen(select_dp->;S[id])>;4096)
  720.       *(select_dp->;S[id] + 4096) = 0;

  721.     /* 取字段名称 */
  722.     strcpy(value,select_dp->;S[id]);
  723.     if(isnumber[id])
  724.       IString.Allt(value);
  725.     else
  726.       IString.AlltRight(value);

  727.     sscanf(value,"%s",fname);
  728.     /*
  729.     printf("Field %d is '%s' type %d\n",id,fname,select_dp->;T[id]);
  730.     */
  731.     DD_SetFieldName(ddm,id,fname);
  732.   }
  733.   while(1)
  734.   {
  735.     if(sqlca.sqlcode!=0)
  736.     {
  737.       printf("1-sqlcode is %d\n",sqlca.sqlcode);
  738.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  739.       break;
  740.     };
  741.     EXEC SQL FETCH C5 USING DESCRIPTOR select_dp;
  742.     /* 无记录了 */
  743.     if(vabs(sqlca.sqlcode)==1403)
  744.       break;
  745.     /* 提取的值为空 */
  746.     if(vabs(sqlca.sqlcode)==1405) sqlca.sqlcode=0;
  747.     /* 有错误了 */
  748.     if(vabs(sqlca.sqlcode)!=0)
  749.     {
  750.       DD_SetSqlCode(ddm,sqlca.sqlcode,GetErrorMsg());
  751.       printf("2-sqlcode is %d\n",sqlca.sqlcode);
  752.       break;
  753.     };
  754.     /* printf("select_dp_F is %d\n",select_dp->;F); */
  755.     if(ddm->;RecordCount==0)
  756.       DD_AddRow(ddm);
  757.     for (id = 0; id < select_dp->;F; id++)
  758.     {
  759.       if (*select_dp->;I[id] < 0)
  760.       {
  761.         if (select_dp->;T[id] == 4)
  762.         {
  763.           sprintf (value,"%-*c ",(int)select_dp->;L[id]+3, ' ');
  764.         }
  765.         else
  766.         {
  767.           sprintf (value,"%-*c ",(int)select_dp->;L[id], ' ');
  768.         };
  769.       }
  770.       else
  771.       {
  772.         if (select_dp->;T[id] == 3)
  773.         {
  774.           sprintf (value,"%*d ", (int)select_dp->;L[id],*(int *)select_dp->;V[id]);
  775.         }
  776.         else
  777.           if (select_dp->;T[id] == 4)
  778.           {
  779.             sprintf (value,"%*.2f ", (int)select_dp->;L[id],*(float *)select_dp->;V[id]);
  780.           }
  781.           else
  782.           {
  783.             if(select_dp->;L[id]>;1500)
  784.             {
  785.               memcpy(value,select_dp->;V[id],1500);
  786.               value[1500]=0;
  787.             }
  788.             else
  789.               sprintf (value,"%-*.*s ", (int)select_dp->;L[id],(int)select_dp->;L[id], select_dp->;V[id]);
  790.           }
  791.       };
  792.       IString.Allt(value);
  793.       DD_SetValue(ddm,id,value);
  794.     }
  795.     if(data_handle!=NULL)
  796.       if(!data_handle(dbioptr,ass_ptr)) break;
  797.   }
  798.   EXEC SQL CLOSE C5;
  799.   for (id = 0; id < MAX_ITEMS; id++)
  800.   {
  801.     if (bind_dp->;V[id] != (char *) 0)
  802.       IMem.Free(bind_dp->;V[id]);
  803.     IMem.Free(bind_dp->;I[id]);
  804.     if (select_dp->;V[id] != (char *) 0)
  805.       IMem.Free(select_dp->;V[id]);
  806.     IMem.Free(select_dp->;I[id]);
  807.   }
  808.   sqlclu(bind_dp);
  809.   sqlclu(select_dp);
  810.   return 1;
  811. }
复制代码

论坛徽章:
0
4 [报告]
发表于 2003-02-23 20:52 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

pro*C

论坛徽章:
0
5 [报告]
发表于 2003-02-24 10:13 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

看看以前的贴子,我曾经发过一篇介绍UNIX环境下如何使用PRO*C进行ORACLE数据库编程的文章,里面有详细的介绍,还有step by step得例子

论坛徽章:
0
6 [报告]
发表于 2003-02-24 10:13 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

使用pro*c比较简单
前几天有多人问pRO*C问题
并且写上源码
你可以自己看看

论坛徽章:
0
7 [报告]
发表于 2003-02-24 10:23 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

我用过OCI,想过还不错

论坛徽章:
0
8 [报告]
发表于 2003-02-24 10:57 |只看该作者

请问各位老鸟能不能提供一个和ORACLE数据库打交道得程序呢?

oci的性能应该是会比pro*c好

但是使用pro*c会更简单
使用pro*c后oracle的编译器会把pro*c编译成oci
不用那么复杂的语法
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP