Sybase ASE15.0.2中使用嵌入式SQL/C程式設計

使用C介面訪問Sybase ASE，有多種方式。見到itpub論壇上有人問起，索性做一個簡單的demo。發現，使用SQL/C比Open Client介面更直觀，比較適合喜歡偷懶的coder.下邊介紹詳細過程.

 

1. 資料庫環境

資料庫iihero, 有使用者spring/spring1, 使用sa使用者登入,執行如下指令碼(test_proc.sql),目的是建立一個示例表及資料,併為它建立一個儲存過程,給後邊的程式用.

 

use iihero go setuser 'spring' go if exists ( select 1 from sysobjects where name = 'test_proc' ) drop proc test_proc go if exists ( select 1 from sysobjects where name = 't123') drop table t123 go create table t123(id int primary key, col2 varchar(32) not null) insert into t123 values(1, 'iihero') insert into t123 values(2, 'Sybase') insert into t123 values(3, 'ASE') go create proc test_proc (@id_min int, @num_t123 int output) with recompile as select @num_t123 = count( a.id ) from iihero.spring.t123 a where a.id > = @id_min go setuser go declare @num_t123 int exec spring.test_proc 1, @num_t123 output -- select @num_123 = count(a.id) from iihero.spring.t123 a where a.id>=1 select @num_t123 go

 

最後它有一段驗證過程.結果顯然是3了.

 

2. 看我們的程式.

就是通過嵌入式SQL訪問這個儲存過程test_proc,並返回結果(example.cp), 內容如下:

 

#define ERREXIT -1 #define STDEXIT 0 #include <stdio.h> /* Declare the SQLCA. */ EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; #define TYPESIZE 13 #define TIDSIZE 6 EXEC SQL END DECLARE SECTION; #define EOLN '/0' void error_handler(void) { fprintf(stderr, "/n** SQLCODE=(%ld)", sqlca.sqlcode); if (sqlca.sqlerrm.sqlerrml) { fprintf(stderr, "/n** ASE Error "); fprintf(stderr, "/n** %s", sqlca.sqlerrm.sqlerrmc); } fprintf(stderr, "/n/n"); exit(ERREXIT); } void warning_handler(void) { if (sqlca.sqlwarn[1] == 'W') { fprintf(stderr, "/n** Data truncated./n"); } if (sqlca.sqlwarn[3] == 'W') { fprintf(stderr, "/n** Insufficient host variables to store results./n"); } return; } int main() { EXEC SQL BEGIN DECLARE SECTION; /* storage for login name and password. */ char username[30]; char password[30]; EXEC SQL END DECLARE SECTION; EXEC SQL BEGIN DECLARE SECTION; CS_INT id_min; CS_INT num_t123; CS_SMALLINT retcode; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR CALL error_handler(); EXEC SQL WHENEVER SQLWARNING CALL warning_handler(); EXEC SQL WHENEVER NOT FOUND CONTINUE; strcpy(username, "spring"); strcpy(password, "spring1"); EXEC SQL CONNECT :username IDENTIFIED BY :password; EXEC SQL USE iihero; printf("Begin test!/n"); id_min = 0; exec sql exec :retcode = test_proc :id_min, :num_t123 output; printf("num_t123=%ld/n", num_t123); EXEC SQL DISCONNECT DEFAULT; printf("End test!/n"); return STDEXIT; }

 

3. 編譯和連結

Sybase ASE中的SQL/C程式設計,需要進行預編譯,編譯,連結三個步驟.

首先要從命令列裡進入vc6的dos環境, 就是進入控制檯視窗,執行VC98/bin下邊的VCVARS32.bat檔案.

確保你的LIB變數有%SYBASE%/%SYBASE_OCS%/lib這個路徑.

a. 預編譯: (預編譯命令是cpre)

E:/MyDocument/MYBOOKS/ASE/code/esqlc>cpre -CMSVC -m -O example.c example.cp
Precompilation Successful. No Errors or Warnings found.
Statistical Report:
        Program name: cpre
        Options specified: /m
        Input file name: example.cp
        Listing file name:
        Target file name: example.c
        ISQL file name:
        Tag ID specified:
        Compiler used: MSVC
        Open Client version: CS_VERSION_150
        Number of information messages: 11
        Number of warning messages: 0
        Number of error messages: 0
        Number of SQL statements parsed: 11
        Number of host variables declared: 6
        Number of SQL cursors declared: 0
        Number of dynamic SQL statements: 0
        Number of stored Procedures generated: 1
        Connection(s) information:
                User id:
                Server:
                Database:

b. 編譯: (它還需要%SYBASE%/%SYBASE_OCS%/include/sybesql.c編譯成obj檔案)

因此使用如下命令,編譯兩個.c檔案:

E:/MyDocument/MYBOOKS/ASE/code/esqlc>cl /DDEBUG=1 /D_DEBUG=1 /DNET_DEBUG=1 /Od /
Z7 /MD /nologo /DWIN32 -Id:/sybase/OCS-15_0/include d:/sybase/OCS-15_0/include/s
ybesql.c example.c /c
sybesql.c
example.c
Generating Code...

 

c. 連結:

E:/MyDocument/MYBOOKS/ASE/code/esqlc>link example.obj sybesql.obj /out:example.e
xe libsybct.lib libsybcs.lib MSVCRT.lib
Microsoft (R) Incremental Linker Version 6.00.8447
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.

 

3. 最後結果:

E:/MyDocument/MYBOOKS/ASE/code/esqlc>example.exe
Begin test!
num_t123=3
End test!

 

上述過程只是簡單的演示一下一個簡單的儲存過程在SYBASE ESQL/C中的使用. 有興趣可以一試.

至於編譯器,VC6, VC7/8/9,都應該支援.

之所以使用命令列,是因為不想依賴於整合開發環境.