Dans cet article on va voir comment on peut accéder a Sybase en utilisant du SQL embarqué dans du C (Embedded SQL)
La dernière fois on a installé toutes les librairies coté client (32 bits), et plus particulièrement le précompilateur C qui va nous permettre d’embarquer des requêtes SQL (ou Transact-SQL : extension SQL pour Sybase) dans du code C classique, le précompilateur se trouve ici : /opt/sybase/OCS-15_0/bin/cpre :
toc@tocNewServer:/opt/sybase$ which cpre /opt/sybase/OCS-15_0/bin/cpre toc@tocNewServer:/opt/sybase$ cpre -v Sybase ESQL/C Precompiler/15.5/P-EBF18132 ESD #6/DRV.15.5.1/Linux Intel/Linux 2.6.9-55.ELsmp x86_64/BUILD1550-008/OPT/Mon Jul 26 20:21:43 2010 Confidential property of Sybase, Inc. Copyright 1987, 2010 Sybase, Inc. All rights reserved. Unpublished rights reserved under U.S. copyright laws. This software contains confidential and trade secret information of Sybase, Inc. Use, duplication or disclosure of the software and documentation by the U.S. Government is subject to restrictions set forth in a license agreement between the Government and Sybase, Inc. or other written agreement specifying the Government's rights to use the software and any applicable FAR provisions, for example, FAR 52.227-19. Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
Pareil que pour le PRO*C pour Oracle, ici on doit faire une compilation en deux phases : le précompilateur Sybase pour C (cpre) puis une compilation gcc. Les fichiers qui contiennent du SQL embarqués pour Sybase on l’extension .cp.
On va travailler sur la base PUBS2 (qu’on a déjà installé), plus particulièrement la table authors, dont voici la description :
sybase@tcoServer:~$ isql -Utarek -P123456 -STCOSERVER 1> use pubs2 2> go 1> sp_help authors 2> go Name Owner Object_type Object_status Create_date ------- ----- ----------- ------------- ------------------- authors dbo user table -- none -- May 16 2013 10:53AM (1 row affected) Column_name Type Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity ----------- ------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ---------- au_id id 11 NULL NULL 0 0 NULL NULL NULL NULL 0 au_lname varchar 40 NULL NULL 0 0 NULL NULL NULL NULL 0 au_fname varchar 20 NULL NULL 0 0 NULL NULL NULL NULL 0 phone char 12 NULL NULL 0 0 phonedflt NULL NULL NULL 0 address varchar 40 NULL NULL 1 0 NULL NULL NULL NULL 0 city varchar 20 NULL NULL 1 0 NULL NULL NULL NULL 0 state char 2 NULL NULL 1 0 NULL NULL NULL NULL 0 country varchar 12 NULL NULL 1 0 NULL NULL NULL NULL 0 postalcode char 10 NULL NULL 1 0 NULL NULL NULL NULL 0
On crée un simple fichier : sybase-hello-embedded-sql.cp :
/*
* sybase-hello-embedded-sql.cp : est simple programme
* qui montre l'utilisation du "Embedded SQL" pour Sybase
*/
#include <stdio.h>
#include <stdlib.h>
/* Include des headers pour "Embedded SQL" */
#include <sybhesql.h>
#include <sybtesql.h>
EXEC SQL INCLUDE SQLCA;
/* Les identifiants pour se connecter a la base */
#define SYBASE_USER "tarek"
#define SYBASE_PASSWORD "123456"
#define SYBASE_SERVER "TCOSERVER" /* Defini dans le fichier "/opt/sybase/interfaces" */
#define OK 0
#define KO 1
/* Define des longueurs des clonnes */
#define AU_ID_LEN 11
#define AU_LNAME_LEN 40
#define AU_FNAME_LEN 20
#define PHONE_LEN 12
#define ADDRESS_LEN 40
#define CITY_LEN 20
#define STATE_LEN 2
#define COUNTRY_LEN 12
#define POSTALCODE_LEN 10
void error_handler();
void warning_handler();
int main(int argc, char **argv)
{
EXEC SQL BEGIN DECLARE SECTION;
/* Pour le login, pass et le serveur */
char username[30];
char password[30];
char server[30];
/* Pour les colonnes */
char au_id_var[AU_ID_LEN + 1];
char au_lname_var[AU_LNAME_LEN + 1];
char au_fname_var[AU_FNAME_LEN + 1];
char phone_var[PHONE_LEN + 1];
char address_var[ADDRESS_LEN + 1];
char city_var[CITY_LEN + 1];
char state_var[STATE_LEN + 1];
char country_var[COUNTRY_LEN + 1];
char postalcode_var[POSTALCODE_LEN + 1];
EXEC SQL END DECLARE SECTION;
/* Mise en place de la gestion des erreurs */
EXEC SQL WHENEVER SQLERROR CALL error_handler();
EXEC SQL WHENEVER SQLWARNING CALL warning_handler();
EXEC SQL WHENEVER NOT FOUND CONTINUE;
/* Connexion au serveur */
strcpy(username, SYBASE_USER);
strcpy(password, SYBASE_PASSWORD);
strcpy(server, "TCOSERVER");
EXEC SQL CONNECT :username IDENTIFIED BY :password using :server;
/* On veut utiliser la base "PUBS2" */
EXEC SQL USE pubs2;
/* Declarer un cursor pour notre requete sur la table 'authors' */
EXEC SQL DECLARE authors_list CURSOR FOR
SELECT * FROM authors; /* Attention les noms sont 'case sensitive' */
/* Ouverture du curseur */
EXEC SQL OPEN authors_list;
/* Parcourir et afficher le resultat de la requete */
for (;;) {
EXEC SQL FETCH authors_list INTO
:au_id_var, :au_lname_var, :au_fname_var, :phone_var,
:address_var, :city_var, :state_var, :country_var, :postalcode_var;
/* Si l'erreur sqlca.sqlcode est 100 : qui veut dire qu'il n' y a
* plus de row dans le curseur, on arrete
*/
if (sqlca.sqlcode == 100)
break;
/* Affichage ligne par ligne */
fprintf(stdout, "%s|%s|%s|%s|%s|%s|%s|%s|%s\n", au_id_var, au_lname_var, au_fname_var, \
phone_var, address_var, city_var, state_var, country_var, postalcode_var);
}
/* On ferme le curseur */
EXEC SQL CLOSE authors_list;
/* On se deconnecte "CURRENT" ici veut dire la connexion courante */
EXEC SQL DISCONNECT CURRENT;
return OK;
}
/* Pour les erreurs */
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(EXIT_FAILURE);
}
/* Pour les warnings */
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;
}
Qu’on compile comme suit (en deux phases : precompilation et compilation) :
toc@tocNewServer:~/toc_src/DATABASES/Sybase$ cpre sybase-hello-embedded-sql.cp Precompilation Successful. No Errors or Warnings found. Statistical Report: Program name: cpre Options specified: Input file name: sybase-hello-embedded-sql.cp Listing file name: Target file name: sybase-hello-embedded-sql.c ISQL file name: Tag ID specified: Compiler used: ANSI_C Open Client version: CS_CURRENT_VERSION Number of information messages: 11 Number of warning messages: 0 Number of error messages: 0 Number of SQL statements parsed: 12 Number of host variables declared: 13 Number of SQL cursors declared: 1 Number of dynamic SQL statements: 0 Number of stored Procedures generated: 0 Connection(s) information: User id: Server: Database: toc@tocNewServer:~/toc_src/DATABASES/Sybase$ gcc -Wall sybase-hello-embedded-sql.c -o sybase-hello-embedded-sql -I/opt/sybase/OCS-15_0/include -L/opt/sybase/OCS-15_0/lib -lsybct -lsybdb
Et qu’on exécute :
toc@tocNewServer:~/toc_src/DATABASES/Sybase$ ./sybase-hello-embedded-sql 172-32-1176|White |Johnson |408 496-7223|10932 Bigge Rd. |Menlo Park |CA|USA |94025 213-46-8915|Green |Marjorie |415 986-7020|309 63rd St. #411 |Oakland |CA|USA |94618 238-95-7766|Carson |Cheryl |415 548-7723|589 Darwin Ln. |Berkeley |CA|USA |94705 267-41-2394|O'Leary |Michael |408 286-2428|22 Cleveland Av. #14 |San Jose |CA|USA |95128 274-80-9391|Straight |Dick |415 834-2919|5420 College Av. |Oakland |CA|USA |94609 341-22-1782|Smith |Meander |913 843-0462|10 Mississippi Dr. |Lawrence |KS|USA |66044 409-56-7008|Bennet |Abraham |415 658-9932|6223 Bateman St. |Berkeley |CA|USA |94705 427-17-2319|Dull |Ann |415 836-7128|3410 Blonde St. |Palo Alto |CA|USA |94301 472-27-2349|Gringlesby |Burt |707 938-6445|PO Box 792 |Covelo |CA|USA |95428 486-29-1786|Locksley |Chastity |415 585-4620|18 Broadway Av. |San Francisco |CA|USA |94130 527-72-3246|Greene |Morningstar |615 297-2723|22 Graybar House Rd. |Nashville |TN|USA |37215 648-92-1872|Blotchet-Halls |Reginald |503 745-6402|55 Hillsdale Bl. |Corvallis |OR|USA |97330 672-71-3249|Yokomoto |Akiko |415 935-4228|3 Silver Ct. |Walnut Creek |CA|USA |94595 712-45-1867|del Castillo |Innes |615 996-8275|2286 Cram Pl. #86 |Ann Arbor |MI|USA |48105 722-51-5454|DeFrance |Michel |219 547-9982|3 Balding Pl. |Gary |IN|USA |46403 724-08-9931|Stringer |Dirk |415 843-2991|5420 Telegraph Av. |Oakland |CA|USA |94609 724-80-9391|MacFeather |Stearns |415 354-7128|44 Upland Hts. |Oakland |CA|USA |94612 756-30-7391|Karsen |Livia |415 534-9219|5720 McAuley St. |Oakland |CA|USA |94609 807-91-6654|Panteley |Sylvia |301 946-8853|1956 Arlington Pl. |Rockville |MD|USA |20853 846-92-7186|Hunter |Sheryl |415 836-7128|3410 Blonde St. |Palo Alto |CA|USA |94301 893-72-1158|McBadden |Heather |707 448-4982|301 Putnam |Vacaville |CA|USA |95688 899-46-2035|Ringer |Anne |801 826-0752|67 Seventh Av. |Salt Lake City |UT|USA |84152 998-72-3567|Ringer |Albert |801 826-0752|67 Seventh Av. |Salt Lake City |UT|USA |84152
Dans un prochain article on verra comment utiliser Sybase Client Library pour accéder a Sybase.
Lost In Mac 
Hello,that is a good post.Thanks for sharing!
You’re welcome 🙂