# include <stdio.h>
/* Connection a Oracle, avec message d'erreur si un probleme apparait */
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle");
/* Copie du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n",\
username.arr);
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("\nProblem de connection pour l'usager
: %s \n",\
username.arr);
return(0);
}
______________________________________________________
Exemple II :
# include <stdio.h>
/* Connection a Oracle, demande a l'utilisateur
le numero d'un employe,
interroge la BD sur le nom, le departement, le
salaire et la commission de l'employe,
et enfin affiche le resultat avec message d'erreur
si un probleme apparait */
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
int emp_number;
VARCHAR emp_name[15];
float salary;
float commission;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
/* CONNECTION A ORACLE */
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n",\username.arr);
/* DEMANDE DU NUMERO DE L'EMPLOYE */
printf("\n\nDonner le numero de l'employe :");
scanf("%d", &emp_number);
/* INTERROGATION D'ORACLE */
EXEC SQL SELECT ENAME, SAL, COMM
INTO :emp_name, :salary, :commission
FROM EMP
WHERE EMPNO = :emp_number;
/* Il faut toujours terminer les chaines de caracteres
par \0 */
emp_name.arr[emp_name.len] = '\0';
/* IMPRESSION DU RESULTAT */
printf("\n\nEmploye\tSalaire\tCommission\n");
printf("-------\t-------\t----------\n");
printf("%-s\t%6.2f\t%6.2f\n",emp_name.arr,salary,commission);
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
return(0);
}
_____________________________________________________
Exemple III :
# include <stdio.h>
/* Connection a Oracle, interroge la BD sur la
liste des employes dont le
job est SALESMAN affiche le resultat avec message
d'erreur si un probleme apparait */
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
int emp_number;
VARCHAR emp_name[15];
float salary;
float commission;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
/* CONNECTION A ORACLE */
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n",\username.arr);
/* INTERROGATION D'ORACLE */
EXEC SQL DECLARE salesmen CURSOR FOR
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB='SALESMAN';
/* TRAITEMENT DE salesmen */
EXEC SQL OPEN salesmen;
/* PAS DE RESULTAT */
EXEC SQL WHENEVER NOT FOUND GOTO fin_de_reponse;
/* IMPRESSION DU RESULTAT */
printf("\n\n LISTE DES SALESMEN ");
printf("\nEmploye \tSalaire \tCommission\n");
printf("------- \t---------- \t----------\n");
for ( ; ; )
{
EXEC SQL FETCH salesmen INTO :emp_name, :salary,
:commission;
/* il faut toujours terminer les chaines par \0
*/
emp_name.arr[emp_name.len]='\0';
printf("%-s\t\t%8.2f\t%8.2f\n",emp_name.arr,salary,commission);
}
fin_de_reponse :
EXEC SQL CLOSE salesmen;
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
return(0);
}
______________________________________________________
Exemple IV :
# include <stdio.h>
/* Connection a Oracle, interroge la BD sur la
liste des employes
en demandant leur numero, leur nom et leur salaire
Ces triplets sont recuperes et affiches par paquets
de 5,
message d'erreur en cas de probleme */
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
int emp_number[5];
VARCHAR emp_name[5][15];
float salary[5];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
void afficher_nuplets(); /* defini apres le programme principal */
main()
{
int nb_nuplets; /* nb de nuplets manipules */
/* CONNECTION A ORACLE */
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n",\username.arr);
/* INTERROGATION D'ORACLE */
EXEC SQL DECLARE triplet CURSOR FOR
SELECT EMPNO, ENAME, SAL
FROM EMP;
/* TRAITEMENT DE triplet */
EXEC SQL OPEN triplet;
/* PAS DE RESULTAT */
EXEC SQL WHENEVER NOT FOUND GOTO fin_de_reponse;
/* TRAITEMENT DES RESULTATS */
nb_nuplets = 0;
while(1)
{
/* les triplets sont recuperes et affiches
par paquets de 5 */
EXEC SQL FETCH triplet INTO :emp_number, :emp_name,
:salary;
/* sqlca.errd[2] donne le nombre de triplets manipules
,
sqlca.errd[2] est incremente apres chaque FETCH
*/
afficher_nuplets(sqlca.sqlerrd[2] - nb_nuplets);
nb_nuplets = sqlca.sqlerrd[2];
}
fin_de_reponse :
if ((sqlca.sqlerrd[2] - nb_nuplets) > 0)
afficher_nuplets(sqlca.sqlerrd[2] - nb_nuplets);
EXEC SQL CLOSE triplet;
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
return(0);
}
void afficher_nuplets(n)
{
int i;
/* IMPRESSION DU RESULTAT */
printf("\nNumero \tNom \tSalaire\n");
printf("------ \t--- \t-------\n");
for (i=0 ;i<n ;i++ )
{
/* il faut toujours terminer les chaines par \0
*/
emp_name[i].arr[emp_name[i].len]='\0';
printf("%-15d\t%-13s\t%6.2f\n",emp_number[i],emp_name[i].arr,salary[i])
;
}
}
______________________________________________________
Exemple V :
# include <stdio.h>
/* Connection a Oracle,
avec message d'erreur si un probleme apparait
1) Destruction de la table EMP - si elle existait
auparavant
2) Creation de la table EMP a partir de la table
EMP accessible par synonyme
3) Mise a jour de la table EMP : on met a 100
les commissions nulles
les points 1, 2 et 3 ne fonctionnent pas au sein
du meme programme
les points 1 et 2 doivent être mis dans
un fichier
le point 3 doit être mis dans un autre fichier
*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
char requete[132];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n", \
username.arr);
strcpy(requete,"DROP TABLE EMP");
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL EXECUTE IMMEDIATE :requete;
printf("La table EMP est detruite. \n");
EXEC SQL WHENEVER SQLERROR GOTO errexit;
strcpy(requete,"CREATE TABLE EMP AS SELECT EMPNO,
ENAME, JOB, SAL, COMM FROM EMP");
EXEC SQL EXECUTE IMMEDIATE :requete;
printf("La table EMP est creee. \n");
EXEC SQL COMMIT WORK;
printf("La transaction est validee. \n");
strcpy(requete,"UPDATE EMP SET COMM=100 WHERE
COMM IS NULL");
EXEC SQL EXECUTE IMMEDIATE :requete;
printf("La mise a jour est faite, %d nuplets modifies.
\n", sqlca.sqlerrd[2]);
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("\nProblem de connection pour l'usager
: %s \n", username.arr);
return(0);
}
______________________________________________________
Exemple VI :
# include <stdio.h>
/* Connection a Oracle,
avec message d'erreur si un probleme apparait
On suppose pour executer ce programme que la relation
EMP existe, et que l'on peut la modifier. On met a 100 la commission de
certains employes. La condition (clause where) est fournie par l'utilisateur
*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
char requete[132];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
char clausewhere[80];
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n", \
username.arr);
strcpy(requete,"UPDATE EMP SET COMM = 100 WHERE
");
printf("Completez la requete %s ", requete);
asks("", clausewhere);
strcat(requete, clausewhere);
EXEC SQL EXECUTE IMMEDIATE :requete);
printf("%s executee. \n %d nuplets modifies. \n",requete,sqlca.sqlerrd[2]);
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("\nProblem de connection pour l'usager
: %s \n", username.arr);
return(0);
}
/* askn(text,variable)
print the 'text' on STDOUT and read an integer
variable from
SDTIN. text points to the null terminated string
to be printed
variable points to an integer variable
askn returns a 1 if the variable was read successfully
or a
-1 if -eof- was encountered
*/
int askn(text,variable)
char text[];
int *variable;
{
char s[20];
printf(text);
if ( gets(s) == (char *)0 )
return(EOF);
*variable = atoi(s);
return(1);
}
/* asks(text,variable)
print the 'text' on STDOUT and read up to 'len'
characters into
the buffer pointed to by variable from STDIN.
text points to the null terminated string to be
printed
variable points to a buffer of at least 'len'+1
characters
asks returns the number of character read into
the string, or a
-1 if -eof- was encountered
*/
int asks(text,variable)
char text[],variable[];
{
printf(text);
return( gets(variable) == (char *)0 ? EOF : strlen(variable)
);
}
______________________________________________________
Exemple VII :
# include <stdio.h>
/* Connection a Oracle,
avec message d'erreur si un probleme apparait
On suppose pour executer ce programme que la relation
EMP existe, et que l'on peut la modifier. On met a jour la commission de
certains employes. La condition (clause where) est fournie par l'utilisateur
ainsi que la nouvelle valeur de la commission */
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
char requete[132];
int commission;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
char clausewhere[80];
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n", \
username.arr);
strcpy(requete,"UPDATE EMP SET COMM = :commission
WHERE ");
printf("Completez la requete %s ", requete);
asks("", clausewhere);
strcat(requete, clausewhere);
EXEC SQL PREPARE S1 FROM :requete;
askn("Donnez la nouvelle valeur de la commission
:",&commission);
EXEC SQL EXECUTE S1 USING :commission;
printf("%s executee. \nNouvelle commission :%d\n
%d nuplets modifies. \n",requete,commission,sqlca.sqlerrd[2]);
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("\nProblem de connection pour l'usager
: %s \n", username.arr);
return(0);
}
______________________________________________________
Exemple VIII :
# include <stdio.h>
/* Connection a Oracle,
avec message d'erreur si un probleme apparait
On suppose pour executer ce programme que la relation
EMP existe, et que l'on peut la lire. On liste le contenu de la relation
EMP
La condition (clause where) est fournie par l'utilisateur
*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
char requete[132];
int deptno;
char nom[10];
float salaire;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
main()
{
char clausewhere[80];
int i;
strcpy(username.arr,"login_oracle"); /* Copie
du username*/
username.len = strlen(username.arr);
strcpy(password.arr,"motdepasse_oracle"); /* Copie
du mot de passe */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnection sur ORACLE de l'usager :
%s \n", username.arr);
strcpy(requete,"SELECT ENAME, SAL FROM EMP ");
printf("Completez la requete %s ", requete);
asks("", clausewhere);
strcat(requete, clausewhere);
EXEC SQL PREPARE S1 FROM :requete;
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
printf("Employe \tSalaire \n");
printf("----------\t----------\n");
EXEC SQL WHENEVER NOT FOUND GOTO finboucle;
for (i=0;;i++)
{ EXEC SQL FETCH C1 INTO :nom, :salaire);
printf("%-10s\t%6.2f\n",nom,salaire);
}
finboucle:
printf("\n\n%d nuplets selectionnes.\n",i);
EXEC SQL COMMIT WORK RELEASE;
printf("\nFin de connection sur Oracle. \n");
return(1);
errexit:
printf("\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("\nProblem de connection pour l'usager
: %s \n", username.arr);
return(0);
}