myspool.pc

PROC*C OCI 程序

/*---------------------------------------------------------
 -- FUNCTION :ORACLE CSV SPOOL
 -- DATE     :2009-05-14 16:42:11
 ---------------------------------------------------------*/

#include<stdio.h>
#include<stdlib.h>
#include<ctype.h>
#include<string.h>

#include<oratypes.h>
#include<ocidfn.h>
#include<ociapr.h>
#include<ocidem.h>
#include<oci.h>

#define MAX_BINDS 12
#define MAX_ITEM_BUFFER_SIZE 100
#define MAX_SELECT_LIST_SIZE 200
#define MAX_SQL_IDENTIFIER 31
#define MAXCOLNAMELEN 40
#define PARSE_NO_DEFER 0
#define PARSE_V7_LNG 2

/* define LDA,CDA and HDA */
Lda_Def   lda;
ub1       hda[256];
Cda_Def   cda;
Cda_Def   cda1;
Cda_Def   cda2;

text bind_values[MAX_BINDS][MAX_ITEM_BUFFER_SIZE];

struct describe
{
  sb4 dbsize;
  sb2 dbtype;
  sb1 buf[MAX_ITEM_BUFFER_SIZE];
  sb4 buflen;
  sb4 dsize;
  sb2 precision;
  sb2 scale;
  //sb4 precision;
  //sb4 scale;
  sb2 nullok;
  sb2 nullok2;
  sb2 nullok3;
  sb2 displen;
};



struct col_info
{
  sb4 dbsize;
  sb2 Type;
  sb1 Name[MAX_ITEM_BUFFER_SIZE];
  sb4 buflen;
  sb4 dsize;
  sb2 MaxLength;
  sb2 Precision;
  sb2 Scale;
  //sb4 precision;
  //sb4 scale;
  sb2 nullok;
  sb2 nullok2;
  sb2 nullok3;
  sb2 displen;
};


struct define
{
  ub1 buf[MAX_ITEM_BUFFER_SIZE];
  float flt_buf;
  sword int_buf;
  sb2 indb;
  sb2 col_retlen,col_retcode;
 };
 
 struct describe desc[MAX_SELECT_LIST_SIZE];
 struct define def[MAX_SELECT_LIST_SIZE];
 
 sword connect(void);
 sword describe_define(Cda_Def *);
 sword do_binds(Cda_Def *,text *);
 
 void do_exit(sword);
 void oci_error(Cda_Def *);
 sword get_sql_statement(void);
 void print_header(sword);
 void print_rows(Cda_Def *,sword);
 
 /* public valiabe */
 static text sql_statement[4096];
 static sword sql_function;
 static sword numwidth=8;
 
 static sword m_colCount;
 struct col_info m_colInfo[MAX_SELECT_LIST_SIZE];
 
static  text   *username=(text   *)"aa";   
static  text   *password=(text   *)"aa";   
static  text   *selectemp=(text*)"SELECT role_id,role_name,created_by,creation_date FROM sys_role";    
 
int main()
{
  sword col,errno,n,ncols;
  text *cp;
  

    
  if ( connect())
    return -10;
    
  if (oopen(&cda,&lda,(text *)0,-1,-1,(text *) 0,-1))
  {
  	  oci_error(&cda);
  	  do_exit(EXIT_FAILURE);
  }
  
    
  get_sql_statement();
    
  //printf("step1-1\n");  
  if (oparse (&cda,(text *) sql_statement,(sb4)-1,(sword) PARSE_NO_DEFER,(ub4) PARSE_V7_LNG))
  //if (oparse (&cda,(text *) selectemp,(sb4)-1,(sword) PARSE_NO_DEFER,(ub4) PARSE_V7_LNG))
  //if   (oparse(&cda,selectemp,(sb4)-1,(sword)PARSE_NO_DEFER,(ub4)PARSE_V7_LNG))   
  //if (oparse(&cda, (text *) "select * FROM sys_role", -1, 1, 2))
  {
     //printf("step1-2\n");  
    oci_error(&cda);
    //return -1;
  }
  
  //printf("step1-3\n");  
  sql_function=cda.ft;
  
  if (sql_function==FT_SELECT)
    if ((ncols=describe_define(&cda))==-1)
      return -1;
    
  //get_meta(&cda);    
  
  get_col_info(&cda);
  
  //printf("step1-4\n");  
  
  if (oexec(&cda))
  {
    //printf("step1-5\n");  
    oci_error(&cda);
    return -1;
  }
  
  if (sql_function!=FT_SELECT)
  {
    return -1;
  }
  
  //printf("step1-6\n");  
  
  //print_header(ncols);

  //printf("step1-7\n");  
  print_rows(&cda,ncols);
  
  printf("\n%d row%c processed.\n",cda.rpc,cda.rpc==1?'\0':'s');

}


sword connect()
{ 
   text username[132];
   text password[132];
   sword n;
   
   strcpy(username,"pas");
   strcpy(password,"pas0616");
   
   //if (orlon(&lda,hda,username,-1,password,-1,-1))
   if (olog(&lda,   (ub1   *)hda,   (text   *)"pas",   -1,   (text   *)"pas0616",   -1,   
                        (text   *)0,   -1,   (ub4)OCI_LM_DEF))   
   {
     printf("Cannot connect as %s.\n",username);
     return -1;
   }
   
   return;
}


//odsc(cda, col+1,&desc[col].dbsize,(sb2 *) -1,(sb2 *) -1,
//     &desc[col].dbtype, &desc[col].buf,&desc[col].precision,&desc[col].displen)


sword get_col_info(Cda_Def * cda)
{
OCIError     *m_errhp;
void* m_colhp = NULL; 
text  p[1000];
ub4 nNameLen; 
ub4 i;
sword status;   

OCIAttrGet( cda, OCI_HTYPE_STMT, &m_colCount, 0, OCI_ATTR_PARAM_COUNT, m_errhp );

printf("m_colCount=%d",m_colCount);

//然后，依次去取列的信息
for(  i = 1; i <= 5; i++ )
{
    m_colInfo[i-1].MaxLength = 0;
    memset( m_colInfo[i-1].Name, 0, sizeof(m_colInfo[i-1].Name) );
    
    //得到列信息
   //status = OCIParamGet( cda, OCI_HTYPE_STMT, m_errhp, (    void**)&m_colhp, i );
   status = OCIParamGet( sql_statement, OCI_HTYPE_STMT, m_errhp, (    void**)&m_colhp, i );

  
    //得到列名
   status = OCIAttrGet( m_colhp, OCI_DTYPE_PARAM, (text**)&p, &nNameLen, OCI_ATTR_NAME, m_errhp );
   if( nNameLen > MAXCOLNAMELEN )
        nNameLen = MAXCOLNAMELEN;
   strncpy( m_colInfo[i-1].Name, (const char*)p, nNameLen );
   
   printf("FNAME%d=%s ",i,m_colInfo[i-1].Name);
   
   //字段信息放在m_colInfo里面，而数据在 m_data 中
   // status = OCIDefineByPos( cda, &m_defhp[i-1], m_errhp, i, (ub1*)m_data[i-1], m_colInfo[i-1].MaxLength+1,SQLT_STR, &m_ind[i-1], 0, (ub2*)0, OCI_DEFAULT );
 }

}



sword get_meta(Cda_Def * cda)
{
OCIParam     *mypard;
OCIError     *errhp;
ub2          dtype;
text         *col_name;
ub4          counter, col_name_len, char_semantics, col_width,col_char_len;
sb4          parm_status;


text objptr[] = "tablename";
ub4 objp_len = strlen("tablename");
OCIParam *parmh;         /* parameter handle */
OCIParam *collsthd;      /* handle to list of columns */
OCIParam *colhd;         /* column handle */
OCIDescribe *dschp;      /* describe handle */


/* get the describe handle for the table */
//if (OCIDescribeAny(svch, errh, objptr, objp_len, OCI_OTYPE_NAME, 0, 
// OCI_PTYPE_TABLE, dschp))
 //return error;


/* Request a parameter descriptor for position 1 in the select-list */
counter = 1;
printf("step2-1\n");  
parm_status = OCIParamGet(sql_statement, OCI_HTYPE_STMT, errhp, &mypard, 
           (ub4) counter);
/* Loop only if a descriptor was successfully retrieved for
   current position, starting at 1 */
while (parm_status == OCI_SUCCESS) {
   /* Retrieve the data type attribute */
    OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
           (dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, 
           (OCIError *) errhp) ;
   /* Retrieve the column name attribute */
    OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
           (dvoid**) &col_name, (ub4 *) &col_char_len, (ub4) OCI_ATTR_NAME,
           (OCIError *) errhp );
   /* Retrieve the length semantics for the column */
    OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
           (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED, 
           (OCIError *) errhp  );
   if (char_semantics)
       /* Retrieve the column width in characters */
        OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &col_width, (ub4 *) 0, (ub4) OCI_ATTR_CHAR_SIZE, 
               (OCIError *) errhp  );
   else
       /* Retrieve the column width in bytes */
       OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &col_width,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, 
               (OCIError *) errhp  );
   
   printf("step2-2\n");  
               
   printf("column=%s  datatype=%d, char semantics=%d, width=%d\n\n", col_name,
               dtype, char_semantics, col_width);
   fflush(stdout);
   /* increment counter and get next descriptor, if there is one */
   counter++;
   parm_status = OCIParamGet(sql_statement, OCI_HTYPE_STMT, errhp, &mypard,
                 (ub4) counter);
   }

}


sword describe_define(Cda_Def * cda)
{
  sword col,deflen,deftyp;
  static ub1 * defptr;
  
  for (col=0;col<MAX_SELECT_LIST_SIZE;col++)
  {
    desc[col].buflen=MAX_ITEM_BUFFER_SIZE;
    
    /*if (odescr(cda,col+1,desc[col].dbsize,
               &desc[col].dbtype,&desc[col].buf[0],
               &desc[col].precision,&desc[col].scale,
               &desc[col].nullok))
*/ 
/*     if odescr(cda,
 *               col+1,
 *               &enamelen,
 *               (sb2   *)&db_type,
 *               name_buf,
 *               (sb4*)&len,
 *               (sb4*)&dsize,
 *               (sb2   *)0,
 *               (sb2   *)0,
 *               (sb2   *)0)
*/
/*    if (odescr(cda,
               col+1,
	             &desc[col].dbsize,
               &desc[col].dbtype,
	             desc[col].buf,
               &desc[col].precision,
	             &desc[col].scale,
               &desc[col].nullok,
               &desc[col].nullok2,
               &desc[col].nullok3
	       ))
	       
odsc(&cursor, 2, &sal_len, (sb2 *) -1, (sb2 *) -1,
     &sal_type, col_name, &col_name_len, &max_disp_len);
	       
	       */
   if (odsc(cda, col+1,&desc[col].dbsize,(sb2 *) -1,(sb2 *) -1,
     &desc[col].dbtype, &desc[col].buf,&desc[col].precision,&desc[col].displen))
    { 
      if (cda->rc==VAR_NOT_IN_LIST) 
       break;
     else
     {
       oci_error(cda);
       return -1;
     }

   }
   
   
   switch(desc[col].dbtype)
   {
     case NUMBER_TYPE:
     
     desc[col].dbsize=numwidth;
     
     if (desc[col].scale!=0)
     {
       defptr=(ub1*)&def[col].flt_buf;
       deflen=(sword) sizeof(float);
       deftyp=FLOAT_TYPE;
       desc[col].dbtype=FLOAT_TYPE;
      }
      
     else
     {
      defptr=(ub1 *)&def[col].int_buf;
      deflen = (sword) sizeof(sword);
      deftyp=INT_TYPE;
      desc[col].dbtype=INT_TYPE;
     }
     break;
     default:
     
     if(desc[col].dbtype==DATE_TYPE)
       desc[col].dbsize=18;
     
     if(desc[col].dbtype=ROWID_TYPE)
       desc[col].dbsize=18;
       
    
    defptr=def[col].buf;
    deflen=desc[col].dbsize>MAX_ITEM_BUFFER_SIZE?MAX_ITEM_BUFFER_SIZE:desc[col].dbsize+1;
    
    deftyp=STRING_TYPE;
    break;
  }
  
  if(odefin(cda,col+1,
    defptr,deflen,deftyp,
    -1,&def[col].indb,(text *)0,-1,-1,
    &def[col].col_retlen,
    &def[col].col_retcode))
   {
     oci_error(cda);
     return -1;
   }
   
  }
  return col;
}


void do_exit(sword rv)
{
  if (oclose(&cda))
    fputs("Error closing cursor!\n",stdout);
    
    if (ologof(&lda))
      fputs("Error logging off!\n",stdout);
    
    exit(rv);
}  


void oci_error(Cda_Def *cur)
{
 
 /* text msg[512];
  sword n;
  
  fputs("\n --ORACLE ERROR--\n",stderr);
  n=oerhms(&lda,cda->rc,msg,(sword) sizeof(msg));
  fprintf(stderr,"msg:%s\n",msg);
  if (cda->fc>0)
	  fprintf(stderr,"Processing OCI function %s",
			  oci_func_tab[cda->fc]);
  fputc('\n',stdout);
  */
  
  
  char msg_buf[71];

  printf("Oracle error: code is %d, op is %d\n",
         (int) cur->rc, (int) cur->fc);
  oermsg(cur->rc, msg_buf);
  printf("%s\n", (char *) msg_buf);

  
}

     
void print_header(sword ncols)
{
  sword col,n;
  
  fputc('\n',stdout);
  
  for (col=0;col<ncols;col++)
  {
    printf("%s ",desc[col].buf);
   
    n=desc[col].dbsize-desc[col].buflen;
    if (desc[col].dbtype==FLOAT_TYPE||
      desc[col].dbtype==INT_TYPE)
    {
      printf("%*c",n,' ');
      printf("%*.*s",desc[col].buflen,
         desc[col].buflen,desc[col].buf);
         
    }
    else
    {
      printf("%*.*s",desc[col].buflen,
        desc[col].buflen,desc[col].buf);
      
      printf("%*c",n,' ');
    }
    
    fputc(' ',stdout);
  }
  fputc('\n',stdout);
  
  
  for (col=0;col<ncols;col++)
  {
     for (n=desc[col].dbsize;--n>=0;)
       fputc('-',stdout);
       
     fputc(' ',stdout);
  }
  fputc('\n',stdout);
  
}
 
 
void print_rows(Cda_Def * cda,sword ncols)
{

  sword col,n;
   
  for(;;)
  {
    fputc('\n',stdout);
    
    if (ofetch(cda))
    {
      if(cda->rc==NO_DATA_FOUND)
        break;
       
      if(cda->rc==NULL_VALUE_RETURNED)
        oci_error(cda);
       
    }
    
    /*
    
    for (col=0;col<ncols;col++)
    {
       if(def[col].indb<0)
         printf("% *c",desc[col].dbsize,' ');
       else
       { 
	 switch(desc[col].dbtype)
	 {
         case FLOAT_TYPE:
           printf("%*.*f",numwidth,2,def[col].int_buf);
           break;
	 case INT_TYPE:
	   printf("%*d",numwidth,def[col].int_buf);
	   break;
         default:
           n=printf("%s",def[col].buf);
           n=desc[col].dbsize-n;
           if (n>0)
             printf("%*c",n,' ');
           break;
          }
          
        }
        fputc(' ',stdout);
      }
      
      */


    ////////////////////////////////////////    
    for (col=0;col<ncols;col++)
    {
    	 if ( col>0 ) printf("\t");
    	 
			 switch(desc[col].dbtype)
			 {
		        case FLOAT_TYPE:
		           printf("%f",def[col].int_buf);
		           break;
			      case INT_TYPE:
			        printf("%d",def[col].int_buf);
			         break;
		         default:
		           n=printf("\"%s\"",def[col].buf);
            break;
		    }
          
     }
     ////////////////////////////////////////    
     
     
   }
}

sword get_sql_statement()
{
  text cbuf[1024];
  text * cp;
  sword stmt_level;
  strcpy(sql_statement,"SELECT * FROM sys_role where rownum<10");
  //strcpy(sql_statement,"SELECT * FROM mtl_material_transactions where rownum<=10000");
}