testdb2.sqc

转发交易接口

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlca.h>
#include <sqlutil.h>
#include <db2ApiDf.h>
#include <ctype.h>

void SqlInfoGet(char *Msg, struct sqlca *pSqlca);

EXEC SQL BEGIN DECLARE SECTION;
  sqlint32 Records;
  long id;
  char name[21];
  char strStmt[256];
EXEC SQL END DECLARE SECTION;

int main(int argc, char *argv[])
{
  int rc = 0;
  char nodeName[SQL_INSTNAME_SZ + 1];

  struct sqlca sqlca;
  char errorMsg[1024];

  EXEC SQL BEGIN DECLARE SECTION;
    char dbAlias[15];
    char user[128 + 1];
    char pswd[15];
  EXEC SQL END DECLARE SECTION;

  strcpy(dbAlias, "DBEBRS");
  strcpy(user, "ebcs");
  strcpy(pswd, "ebcs");

  if (strlen(user) > 0)
  {
    EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
  }
  else
  {
    EXEC SQL CONNECT TO :dbAlias;
  }

  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("connect false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  }
  else
  {
  	printf("connect succ!\n");  
  }


  EXEC SQL SELECT id, name
    INTO :id, :name
    FROM tb1
    WHERE id = 1;
  
  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("single select false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  }
  else
  {
        printf("single select: id = %-d\n", id);
  }

/* 
  EXEC SQL DECLARE c1 CURSOR FOR
    SELECT id, name
      FROM tb1;

  EXEC SQL OPEN c1;
  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("mult select false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  }
  else
  {

    EXEC SQL FETCH c1 INTO :id, :name;
    if (sqlca.sqlcode != 0)                             
    {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("mult select fetch false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
    }
    else
    {
      while (sqlca.sqlcode != 100)
      {
        printf("mult select: id = %-d name = %s\n", id, name);
        EXEC SQL FETCH c1 INTO :id, :name;
        if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100 )                             
        {                                                  
            SqlInfoGet(errorMsg, &sqlca);
  	    printf("mult select fetch false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  	    break;
        }
      }
    }

    EXEC SQL CLOSE c1;
  }
  
  strcpy(strStmt,
         "INSERT INTO tb1 VALUES(3, '33')");

  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("insert false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
        EXEC SQL ROLLBACK;
  }
  else
  {
    EXEC SQL COMMIT;
    if (sqlca.sqlcode != 0)                             
    {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("insert commit false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
    }
    printf("insert succ\n");
  }
  
  //mult select 
  EXEC SQL DECLARE c2 CURSOR FOR
    SELECT id, name
      FROM tb1;

  EXEC SQL OPEN c2;
  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("mult select false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  }
  else
  {

    EXEC SQL FETCH c2 INTO :id, :name;
    if (sqlca.sqlcode != 0)                             
    {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("mult select fetch false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
    }
    else
    {
      while (sqlca.sqlcode != 100 && sqlca.sqlcode != 100)
      {
        printf("mult select: id = %-d name = %s\n", id, name);
        EXEC SQL FETCH c2 INTO :id, :name;
        if (sqlca.sqlcode != 0)                             
        {                                                  
            SqlInfoGet(errorMsg, &sqlca);
  	    printf("mult select fetch false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  	    break;
        }
      }
    }

    EXEC SQL CLOSE c2;
  }
  
  //delete
  strcpy(strStmt,
         "delete from tb1 where id = 3");

  EXEC SQL EXECUTE IMMEDIATE :strStmt;
  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("delete false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
        EXEC SQL ROLLBACK;
  }
  else
  {
    EXEC SQL COMMIT;
    if (sqlca.sqlcode != 0)                             
    {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("delete commit false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
    }
    printf("delete succ\n");
  }
  
  EXEC SQL DECLARE c3 CURSOR FOR
    SELECT id, name
      FROM tb1;

  EXEC SQL OPEN c3;
  if (sqlca.sqlcode != 0)                             
  {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("mult select false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  }
  else
  {

    EXEC SQL FETCH c3 INTO :id, :name;
    if (sqlca.sqlcode != 0)                             
    {                                                  
        SqlInfoGet(errorMsg, &sqlca);
  	printf("mult select fetch false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
    }
    else
    {
      while (sqlca.sqlcode != 100 && sqlca.sqlcode != 100)
      {
        printf("mult select: id = %-d name = %s\n", id, name);
        EXEC SQL FETCH c3 INTO :id, :name;
        if (sqlca.sqlcode != 0)                             
        {                                                  
            SqlInfoGet(errorMsg, &sqlca);
  	    printf("mult select fetch false SQLCODE = [%-d]%s\n", sqlca.sqlcode, errorMsg);
  	    break;
        }
      }
    }

    EXEC SQL CLOSE c3;
  }
*/  
  EXEC SQL CONNECT RESET;

}

void SqlInfoGet(char *Msg, struct sqlca *pSqlca)
{
  char sqlInfo[1024];
  char sqlstateMsg[1024];
  char errorMsg[1024];

  int rc = 0;
  strcpy(Msg, "");
  
  if (pSqlca->sqlcode != 0 && pSqlca->sqlcode != 100)
  {
    if (pSqlca->sqlcode != 0)
    {
      sprintf(sqlInfo, "error report");
    }
    else
    {
      sprintf(sqlInfo, "warning report");
    } 

    /* get error message */
    rc = sqlaintp(errorMsg, 1024, 80, pSqlca);
    if (rc > 0) /* return code is the length of the errorMsg string */
    {
      strcat(sqlInfo, errorMsg);
    }

    /* get SQLSTATE message */
    rc = sqlogstt(sqlstateMsg, 1024, 80, pSqlca->sqlstate);
    if (rc > 0)
    {
      strcat(sqlInfo, sqlstateMsg);
    }
    strcpy(Msg, sqlInfo);
  } 
}