📄 esql-c资料(完全版)三_unix_操作系统_网络学院_天新网.htm
字号:
(bind_dp->V[i] != (char *)
0)<BR>free(bind_dp->V[i]);<BR>free(bind_dp->I[i]); /*
MAX_ITEMS were allocated. */<BR>if (select_dp->V[i] != (char *)
0)<BR>free(select_dp->V[i]);<BR>free(select_dp->I[i]); /*
MAX_ITEMS were allocated. */<BR>}
<P>SQLSQLDAFree(SQL_SINGLE_RCTX,
bind_dp);<BR>SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);
<P>EXEC SQL WHENEVER SQLERROR CONTINUE;<BR>/* 关闭游标*/<BR>EXEC SQL
CLOSE C;
<P>EXEC SQL COMMIT WORK RELEASE;<BR>puts("\nHave a good day!\n");
<P>EXEC SQL WHENEVER SQLERROR DO sql_error();<BR>return;<BR>}
<P>/*连接数据库函数*/<BR>oracle_connect()<BR>{<BR>EXEC SQL BEGIN DECLARE
SECTION;<BR>VARCHAR username[128];<BR>VARCHAR pass<A
href="http://school.21tx.com/office/word/"
target=_blank>Word</A>[32];<BR>EXEC SQL END DECLARE
SECTION;<BR>/提示用户输入用户名*/<BR>printf("\nusername: ");<BR>fgets((char
*) username.arr, sizeof username.arr,
stdin);<BR>fflush(stdin);<BR>username.arr[strlen((char *)
username.arr)-1] = '\0';<BR>username.len = strlen((char *)
username.arr);<BR>/*提示用户输入口令*/<BR>printf("password:
");<BR>fgets((char *) password.arr, sizeof password.arr,
stdin);<BR>fflush(stdin);<BR>password.arr[strlen((char *)
password.arr) - 1] = '\0';<BR>password.len = strlen((char *)
password.arr);<BR>EXEC SQL WHENEVER SQLERROR GOTO
connect_error;<BR>/*连接数据库*/<BR>EXEC SQL CONNECT :username IDENTIFIED
BY :password;<BR>printf("\nConnected to ORACLE as user %s.\n",
username.arr);<BR>return
0;<BR>/*连接错误处理*/<BR>connect_error:<BR>fprintf(stderr, "Cannot
connect to ORACLE as user %s\n",<BR>username.arr);<BR>return
-1;<BR>}<BR>/*为SQLDA分配空间*/<BR>alloc_descriptors(size, max_vname_len,
max_iname_len)<BR>int size;<BR>int max_vname_len;<BR>int
max_iname_len;<BR>{<BR>int
i;<BR>/*SQLSQLDAAlloc的第一个参数是SQL语句的最大列数或输入宿主变量的最大个数。<BR>*第二个参数，是指列名的最大长度，或参数名的最大长度。<BR>*第三个参数，是指指示符变量名的最大长度。*/<BR>/*给SQLDA分配空间，下面这个SQLDA用于输入参数*/<BR>if
((bind_dp =<BR>SQLSQLDAAlloc(SQL_SINGLE_RCTX,
size,<BR>max_vname_len, max_iname_len)) == (SQLDA *)
0)<BR>{<BR>fprintf(stderr, "Cannot allocate memory for bind
descriptor.");<BR>return -1; /* Have to exit in this case.
*/<BR>}<BR>/*给SQLDA分配空间，下面这个SQLDA用于动态查询*/<BR>if ((select_dp
=<BR>SQLSQLDAAlloc (SQL_SINGLE_RCTX, size,<BR>max_vname_len,
max_iname_len)) == (SQLDA *) 0)<BR>{<BR>fprintf(stderr,"Cannot
allocate memory for select descriptor.");<BR>return
-1;<BR>}<BR>/*设置最大的列数，或最大的变量数*/<BR>select_dp->N = MAX_ITEMS;
<P>/* 给存放指示符变量值和存放数据的变量申请空间。*/<BR>for (i = 0; i < MAX_ITEMS; i++)
{<BR>bind_dp->I[i] = (short *) malloc(sizeof
(short));<BR>select_dp->I[i] = (short *)
malloc(sizeof(short));<BR>bind_dp->V[i] = (char *)
malloc(1);<BR>select_dp->V[i] = (char *)
malloc(1);<BR>}<BR>return
0;<BR>}<BR>/*获得SQL语句，可略看*/<BR>get_dyn_statement()<BR>{<BR>char *cp,
linebuf[256];<BR>int iter, plsql;<BR>int help();<BR>for (plsql = 0,
iter = 1; ;)<BR>{<BR>if (iter == 1)<BR>{<BR>printf("\nSQL>
");<BR>dyn_statement[0] = '\0';<BR>}<BR><BR>fgets(linebuf, sizeof
linebuf, stdin);<BR>fflush(stdin);
<P>cp = strrchr(linebuf, '\n');<BR>if (cp && cp !=
linebuf)<BR>*cp = ' ';<BR>else if (cp == linebuf)<BR>continue;<BR>if
((strncmp(linebuf, "EXIT", 4) == 0) ||<BR>(strncmp(linebuf, "exit",
4) == 0))<BR>{<BR>return -1;<BR>}<BR>else if (linebuf[0] == '?'
||<BR>(strncmp(linebuf, "HELP", 4) == 0) ||<BR>(strncmp(linebuf,
"help", 4) == 0))<BR>{<BR>help();<BR>iter =
1;<BR>continue;<BR>}<BR>if (strstr(linebuf, "BEGIN")
||<BR>(strstr(linebuf, "begin")))<BR>{<BR>plsql =
1;<BR>}<BR>strcat(dyn_statement, linebuf);<BR>if ((plsql &&
(cp = strrchr(dyn_statement, '/'))) ||<BR>(!plsql && (cp =
strrchr(dyn_statement, ';'))))<BR>{<BR>*cp =
'\0';<BR>break;<BR>}<BR>else<BR>{<BR>iter++;<BR>printf("%3d ",
iter);<BR>}<BR>}<BR>return
0;<BR>}<BR>/*设置宿主变量的信息*/<BR>set_bind_variables()<BR>{<BR>int i,
n;<BR>char bind_var[64];
<P>/* 通过DESCRIBE语句，将处理语句的参数名、数据类型等信息存放在bind_dp中*/<BR>EXEC SQL
WHENEVER SQLERROR DO sql_error();<BR>bind_dp->N = MAX_ITEMS; /*
Init. count of array elements. */<BR>EXEC SQL DESCRIBE BIND
VARIABLES FOR S INTO bind_dp;<BR>/*
F小于0，表示SQLSQLDAAlloc()分配的空间不够，即实际参数的个数超出预算的最大值. */<BR>if
(bind_dp->F < 0)<BR>{<BR>printf("\nToo many bind variables
(%d), maximum is %d.\n",<BR>-bind_dp->F,
MAX_ITEMS);<BR>return;<BR>}<BR>/*
将N（最大值）设置为实际的参数个数*/<BR>bind_dp->N = bind_dp->F;<BR>/*
提示用户输入参数值，并设置SQLDA的其他相关值，如：长度等。*/<BR>for (i = 0; i <
bind_dp->F; i++)<BR>{<BR>printf ("\nEnter value for bind variable
%.*s: ",<BR>(int)bind_dp->C[i],
bind_dp->S[i]);<BR>fgets(bind_var, sizeof bind_var, stdin);
<P>/* 获得长度，去掉NULL结束符 */<BR>n = strlen(bind_var) - 1;<BR>/*设置参数长度
*/<BR>bind_dp->L[i] = n;<BR>/* 分配存放参数数据的内存空间
*/<BR>bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
(bind_dp->L[i] + 1)); <BR>/* 将数据放在这个内存空间中
*/<BR>strncpy(bind_dp->V[i], bind_var, n);<BR>/*
设置指示符变量的值*/<BR>if ((strncmp(bind_dp->V[i], "NULL", 4) == 0)
||<BR>(strncmp(bind_dp->V[i], "null", 4) ==
0))<BR>*bind_dp->I[i] = -1;<BR>else<BR>*bind_dp->I[i] =
0;<BR>/* 设置数据类型为CHAR，ORACLE会根据列的数据类型自动转换 */<BR>bind_dp->T[i] =
1;<BR>}<BR>}<BR>/*处理语句*/<BR>process_select_list()<BR>{<BR>int i,
null_ok, precision, scale;<BR>/*如果不是查询语句，则设置F（即返回的列数）为0*/<BR>if
((strncmp(dyn_statement, "SELECT", 6) != 0)
&&<BR>(strncmp(dyn_statement, "select", 6) !=
0))<BR>{<BR>select_dp->F = 0;<BR>return;<BR>}
<P>/*
如果是SELECT语句，则通过DESCRIBE函数返回列名、数据类型、长度和是否为NULL标志*/<BR>select_dp->N
= MAX_ITEMS;<BR>EXEC SQL DESCRIBE SELECT LIST FOR S INTO
select_dp;<BR>/* 如果F小于0。则表示比预定的列数要多。*/<BR>if (select_dp->F <
0)<BR>{<BR>printf<BR>("\nToo many select-list items (%d), maximum is
%d\n",<BR>-(select_dp->F), MAX_ITEMS);<BR>return;<BR>}<BR>/*
设置最大列数为实际列数*/<BR>select_dp->N = select_dp->F;<BR>/*
为每列分配空间。<BR>SQLNumberPrecV6()
函数的作用是从select_dp->L[i]获得精度和长度。<BR>SQLColumnNullCheck()
函数的作用是检查该列是否为NULL。*/<BR><BR>printf ("\n");<BR>for (i = 0; i <
select_dp->F; i++)<BR>{<BR>/* 关闭最高位*/<BR>SQLColumnNullCheck
(&(select_dp->T[i]),<BR>&(select_dp->T[i]),
&null_ok);
<P>switch (select_dp->T[i])<BR>{<BR>case 1 : /* CHAR
*/<BR>break;<BR>case 2 : /* NUMBER ，获得精度和范围*/<BR>SQLNumberPrecV6
(SQL_SINGLE_RCTX, &(select_dp->L[i]), &precision,
<BR>&scale);<BR>/* 如果精度为0，则设置为最大值40 */<BR>if (precision == 0)
precision = 40;<BR>if (scale > 0)<BR>select_dp->L[i] =
sizeof(float);<BR>else<BR>select_dp->L[i] =
sizeof(int);<BR>break;
<P>case 8 : /* LONG*/<BR>select_dp->L[i] = 240;<BR>break;
<P>case 11 : /* ROWID datatype */<BR>select_dp->L[i] =
18;<BR>break;
<P>case 12 : /* DATE datatype */<BR>select_dp->L[i] =
9;<BR>break;<BR><BR>case 23 : /* RAW datatype */<BR>break;
<P>case 24 : /* LONG RAW datatype */<BR>select_dp->L[i] =
240;<BR>break;<BR>}<BR>/* 申请空间给SQLDA来存放数据*/<BR>if
(select_dp->T[i] != 2)<BR>select_dp->V[i] = (char *)
realloc(select_dp->V[i],<BR>select_dp->L[i] + 1);
<BR>else<BR>select_dp->V[i] = (char *)
realloc(select_dp->V[i],<BR>select_dp->L[i]);
<P>/* 输出列名*/<BR>if (select_dp->T[i] == 2)<BR>if (scale >
0)<BR>printf ("%.*s ",select_dp->L[i]+3,
select_dp->S[i]);<BR>else<BR>printf ("%.*s ", select_dp->L[i],
select_dp->S[i]);<BR>else<BR>printf ("%-.*s ",
select_dp->L[i], select_dp->S[i]);
<P>/* 除了LONG RAW和NUMBER，其他数据类型转换为字符型数据类型*/<BR>if (select_dp->T[i]
!= 24 && select_dp->T[i] != 2)<BR>select_dp->T[i] = 1;
<P>/* 将 NUMBER数据类型转换为浮点型数据类型或int数据类型*/<BR>if (select_dp->T[i] ==
2)<BR>if (scale > 0)<BR>select_dp->T[i] = 4; /* float
*/<BR>else<BR>select_dp->T[i] = 3; /* int */<BR>}<BR>printf
("\n\n");
<P>/* 取出每一行数据*/<BR>EXEC SQL WHENEVER NOT FOUND GOTO
end_select_loop;<BR>for (;;)<BR>{<BR>EXEC SQL FETCH C USING
DESCRIPTOR select_dp;
<P>/*输出列数据。除了float和int数据类型，其他数据类型都被转为字符型*/<BR>for (i = 0; i <
select_dp->F; i++)<BR>{<BR>if (*select_dp->I[i] < 0)<BR>if
(select_dp->T[i] == 4) <BR>printf ("%-*c
",(int)select_dp->L[i]+3, ' ');<BR>else<BR>printf ("%-*c
",(int)select_dp->L[i], ' ');<BR>else<BR>if (select_dp->T[i]
== 3) /* int datatype */<BR>printf ("%*d ", (int)select_dp->L[i],
<BR>*(int *)select_dp->V[i]);<BR>else if (select_dp->T[i] ==
4)/* float datatype*/<BR>printf ("%*.2f ", (int)select_dp->L[i],
<BR>*(float *)select_dp->V[i]);<BR>else /* character string
*/<BR>printf ("%-*s ",<BR>(int)select_dp->L[i],
select_dp->V[i]);<BR>}<BR>printf
("\n");<BR>}<BR>end_select_loop:<BR>return;<BR>}
<P>
<P>help()<BR>{<BR>puts("\n\nEnter a SQL statement or a PL/SQL
block");<BR>puts("at the SQL> prompt.");<BR>puts("Statements can
be continued over several");<BR>puts("lines, except within string
literals.");<BR>puts("Terminate a SQL statement with a
semicolon.");<BR>puts("Terminate a PL/SQL block");<BR>puts("(which
can contain embedded semicolons)");<BR>puts("with a slash
(/).");<BR>puts("Typing \"exit\" (no semicolon
needed)");<BR>puts("exits the program.");<BR>puts("You typed \"?\"
or \"help\"");<BR>puts(" to get this
message.\n\n");<BR>}<BR>sql_error()<BR>{<BR>int i;
<P>/* ORACLE error handler */<BR>printf
("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);<BR>if
(parse_flag)<BR>printf("Parse error at character offset
%d.\n",<BR>sqlca.sqlerrd[4]);
<P>EXEC SQL WHENEVER SQLERROR CONTINUE;<BR>EXEC SQL ROLLBACK
WORK;<BR>longjmp(jmp_continue, 1);<BR>}
<P>
<P>SQLDA中的sqldata存放着指向数据的地址。你可以认为，如果查询出的数据是整数（如：258），那么这个地址是指向整数的地址（也就是说，INTEL是高位在后，低位在前。所以第一个字节为00000001，第二个字节为00000010），如果查询出的数据是字符（‘2'），那么这个地址是指向字符的地址(也就是说，第一个字节为00110010—2的ASCII码)。又因为，sqldata声明的是指向字符的指针，所以，你必须按照不同的数据类型做转换，即：对于整数，应该是（*（int
*）sqldata)，告诉系统，sqldata目前指向的数据应该按照整数来解释。如果按照字符来解释，那么第一个字符是ASCII值为1的字符，显然不正确。从数据库向sqldata赋值时，是直接赋值。如：*p=*q，*（P+1）=*（Q+1）。如果从数据库查询出数据为258，则存放在sqlda中也是258，存放格式为：第一个字节为00000001，第二个字节为00000010。你可以执行以下语句，来体会上述论述。<BR>#include
<stdio.h><BR>main()<BR>{ <BR>int li_i;<BR>int *
lp_int;<BR>char * lp_char;<BR>char
lc_char;<BR>li_i=258;<BR><BR>lp_char=malloc(10);<BR>lp_int=lp_char;<BR>*lp_int=258;<BR>/*(lp_char+2)='\0';*/<BR>printf("*lp_char=%d\n",*((int
*)lp_char));<BR>printf("*lp_char=%s\n",lp_char);<BR>return;<BR>}
<P>第五节INFORMIX的嵌入SQL/C语言<BR>5.1
一个简单的入门例子<BR>例1、查询customer表中所有lname的第一个字符小于C的顾客信息。<BR>#include
<stdio.h><BR>/*定义两个常量*/<BR>EXEC SQL define FNAME_LEN
15;<BR>EXEC SQL define LNAME_LEN
15;<BR>main()<BR>{<BR>/*声明宿主变量*/<BR>EXEC SQL BEGIN DECLARE
SECTION;<BR>char fname[ FNAME_LEN + 1 ];<BR>char lname[ LNAM⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -