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,都应该支持.

之所以使用命令行,是因为不想依赖于集成开发环境.