/*==============================================================================
This is a sample program which include SQL sentence about
CREATE ,INSERT a table.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
CHAR sno[10],sname[10],city[10];
INT status;
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
EXEC SQL CONNECT "cobase:cobase" ;
EXEC SQL CREATE TABLE S_TEST /* Create a table named S_TEST */
(SNO CHAR (9),
SNAME CHAR (20),
STATUS INT ,CITY CHAR(10));
/*=============================================================================
This is a sample program which include SQL sentence about
UPDATE,DELETE a table.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
CHAR sno[10],sname[10],city[10];
INT status;
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
/*=============================================================================
This is a sample program which uses SELECT statement of Esql.
It is an example of how to do queries that return one row.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[10],city[10],isno[10];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
printf("input the sno for update:");
scanf("%s",sno);
EXEC SQL SELECT sno,sname,status,city
INTO :sno , :sname ,:status,:city
from S_TEST WHERE sno=:sno;
/*=============================================================================
This is a sample program which uses SELECT statement of Esql.
It is an example of how to do queries that return more than
one row.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
INT status[10];
CHAR sno[10][10],sname[10][15],city[10][20],isno[10];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{ int i;
EXEC SQL CONNECT "cobase:cobase" ; /* log into COBASE */
/*=============================================================================
This is a sample program which uses Cursor.Include DECLARE,
OPEN,FETCH and CLOSE cursor name.
It is an example of how to do queries that return more than
one row.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[15],city[20];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
main()
{
int i;
printf("Now exec connect ...");
EXEC SQL CONNECT "cobase:cobase" ; /* log into COBASE */
printf("Now exec declare_open_fetch_close cursor ...");
/* Declare statement name ---"s1" for INSERT statement */
EXEC SQL DECLARE cursor1 CURSOR FOR SELECT * FROM S_TEST;
EXEC SQL OPEN cursor1 ;
printf("sno sname status city\n ");
do
{
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city ;
If (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
COBASE支持SQL格式" CURRENT OF CURSOR".这条语句将指向一个游标中最新取出的行,以用于修改和删除操作.该语句必须在取操作之后使用 ,它等同于存储一个ROWID,并使用它.其格式如下:
(1) EXEC SQL
UPDATE <表名>;
SET <列名>; = <值表达式>; | NULL
[,<列名>; = <值表达式>; | NULL ....]
WHERE CURRENT OF <游标名>; ;
(2) EXEC SQL
UPDATE <表名>;
SET ( <列名表>; ) = ( <子查询>; )
WHERE CURRENT OF <游标名>; ;
(3) EXEC SQL DELETE FROM <表名>;
WHERE CURRENT OF <游标名>; ;
/*==============================================================================
This is a sample program which uses DELETE STATEMENT at
CURRENT Cursor.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[15],city[20];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
printf("Now exec declare cursor ...");
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT sno,sname,status,city FROM S_TEST FOR UPDATE;
printf("Now exec open cursor ...");
EXEC SQL OPEN cursor1;
printf("sno sname status city\n");
for(;;)
{
printf("Now exec fetch cursor ...\n");
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city;
if (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
//Delete the first record that be fetched
printf(" delete current ?(0/1)");
scanf("%d",&i);
if (i==1)
EXEC SQL DELETE FROM S_TEST
WHERE CURRENT of cursor1;
}
/*=============================================================================
This is a sample program which uses UPDATE STATEMENT at
CURRENT Cursor.
==============================================================================*/
EXEC SQL BEGIN DECLARE SECTION ;
INT status;
CHAR sno[10],sname[15],city[20];
EXEC SQL END DECLARE SECTION ;
EXEC SQL INCLUDE USERCA;
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT SNO, SNAME, STATUS,CITY FROM S_TEST FOR UPDATE;
EXEC SQL OPEN cursor1;
printf("sno sname status city\n");
for(;;)
{
EXEC SQL FETCH cursor1 INTO :sno,:sname,:status,:city;
if (userca.sqlcode==2000) break;
printf("%8s %8s %8d %8s \n",sno,sname,status,city);
/* Delete the first record that be fetched */
printf(" update current ?(0/1)");
scanf("%d",&i);
if (i==1)
{printf("input sno=");
scanf("%s",sno);
printf("\ninput sname=");
scanf("%s",sname);
printf("\ninput status=");
scanf("%d",&status);
printf("\ninput city=");
scanf("%s",city);
EXEC SQL UPDATE S_TEST SET sno=:sno,sname=:sname,status=:status,city=:city
WHERE CURRENT of cursor1;
EXEC SQL COMMIT;
作为标志,以区别于字段名.
描述部分)
;