Pro*c/c++ Help !!!

Pro*c/c++ Help !!! - SQL/NoSQL - Programmation

Marsh Posté le 15-12-2004 à 16:49:36    

Bonjour à tous,
 
Je commence en même temps le c et le precompilateur suscité d'oracle...
 
J'ai tatonné pas mal mais là j'aurais besoin d'un coup de main de la part d'une bonne âme  :D .
Je n'arrive pas à trouver d'explication aux erreurs qui me restent...
 
Voici le code de mon petit machin:
 

Code :
  1. #include <stdio.h>
  2. #include <string.h>
  3. #include <sqlca.h>
  4. #include <stdlib.h>
  5. #include <sqlda.h>
  6. #include <sqlcpr.h>
  9. /* Declare variables.  No declare section is
  10.    needed if MODE=ORACLE. */
  12. EXEC SQL BEGIN DECLARE SECTION;
  14. char    db_username[40];
  15. char    db_passwd[40];
  16. char    uid[81];
  18. char      CreateRole[255];
  19. char      GrantCreateSession[255];
  20. char      GrantCreateTable[255];
  21. char      GrantCreateView[255];
  22. char      GrantCreateTrigger[255];
  23. char      GrantUpdate[255];
  24. char      GrantInsert[255];
  25. char      GrantSelect[255];
  26. char     GrantAlter[255];
  28. sql_cursor  table_cursor;
  29. char     owner_name[40];
  30. char    role_name[80];
  31. char    table_fqn[255];
  32. char    used_table[50];
  33. int     rec_nr = 0;
  35. EXEC SQL END DECLARE SECTION;
  38. void sql_error(msg)
  39.     char *msg;
  40. {
  41.     char err_msg[512];
  42.     size_t buf_len, msg_len;
  44.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  46.     printf("\n%s\n", msg);
  48. /* Call sqlglm() to get the complete text of the
  49. * error message.
  50. */
  51.     buf_len = sizeof (err_msg);
  52.     sqlglm(err_msg, &buf_len, &msg_len);
  53.     printf("%.*s\n", msg_len, err_msg);
  55.     EXEC SQL ROLLBACK RELEASE;
  56.     exit(EXIT_FAILURE);
  57. }
  59. void main()
  60. {
  63. /*Get db username*/
  64.     db_username = 0;
  65.         printf("\nEnter db username (0 to quit): " );
  66.         gets(db_username);
  67.         if (db_username == 0)
  68.             break;
  70. /*Get db passwd*/
  71.     db_passwd = 0;
  72.         printf("\nEnter db password (0 to quit): " );
  73.         gets(db_passwd);
  74.         if (db_passwd == 0)
  75.             break;
  76.            
  78. /*Get owner name*/
  79.         printf("\nEnter schema owner name (0 to quit): " );
  80.         gets(owner_name);
  81.         if (owner_name == 0)
  82.             break;
  85. /*Get role name*/           
  86.     role_name = 0;
  87.         printf("\nEnter role name (0 to quit): " );
  88.         gets(role_name);
  89.         if (role_name == 0)
  90.             break;     
  91.    
  96. /* Connect to ORACLE. */
  98.  spintf(uid,"%s/%s",db_username,db_passwd);
  100.     EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--" );
  103.     EXEC SQL CONNECT :uid;
  104.     printf("\nConnected to ORACLE as user: %s\n", db_username);
  107.   rec_nr = EXEC SQL SELECT COUNT(*)
  108. FROM SYS.ALL_TABLES
  109.  WHERE owner = :owner_name; 
  111. /*retrieve table list in a cursor predefined structure*/
  112. EXEC SQL ALLOCATE :table_cursor;
  114. EXEC SQL EXECUTE
  115.     BEGIN
  116.         OPEN :table_cursor FOR SELECT TABLE_NAME FROM SYS.ALL_TABLES
  117.              WHERE owner = :owner_name;
  118.     END;
  119.   END-EXEC;
  122. /*Create role*/
  123. //DDL statements can't be specified with host variables in it.
  125. sprintf(CreateRole, "EXEC SQL CREATE ROLE %s", role_name);
  126. EXEC SQL PREPARE q FROM :CreateRole;
  127. EXEC SQL EXECUTE q;
  130. /*Grant privileges to role*/
  131. sprintf(GrantCreateSession, "EXEC SQL GRANT CREATE SESSION TO %s", role_name);
  132. EXEC SQL PREPARE q FROM :GrantCreateSession;
  133. EXEC SQL EXECUTE q;
  135. sprintf(GrantCreateTable, "EXEC SQL GRANT CREATE TABLE TO %s", role_name);
  136. EXEC SQL PREPARE q FROM :GrantCreateTable;
  137. EXEC SQL EXECUTE q;
  139. sprintf(GrantCreateView, "EXEC SQL GRANT CREATE VIEW TO %s", role_name);
  140. EXEC SQL PREPARE q FROM :GrantCreateView;
  141. EXEC SQL EXECUTE q;
  143. sprintf(GrantCreateTrigger, "EXEC SQL GRANT CREATE TRIGGER TO %s", role_name);
  144. EXEC SQL PREPARE q FROM :GrantCreateTrigger;
  145. EXEC SQL EXECUTE q;
  149. for(int i = 0;i < rec_nr;i++)
  150. {
  151.   used_table = table_cursor[i];
  153.   sprintf(table_fqn, "%s.%s", owner_name, used_table);
  156.   sprintf(GrantUpdate, "EXEC SQL GRANT UPDATE ON %s",table_fqn ," TO %s", role_name);
  157.   EXEC SQL PREPARE q FROM :GrantUpdate;
  158.   EXEC SQL EXECUTE q;
  160.   sprintf(GrantInsert, "EXEC SQL GRANT INSERT ON %s",table_fqn ," TO %s", role_name);
  161.   EXEC SQL PREPARE q FROM :GrantInsert;
  162.   EXEC SQL EXECUTE q;
  164.   sprintf(GrantSelect, "EXEC SQL GRANT SELECT ON %s",table_fqn ," TO %s", role_name);
  165.   EXEC SQL PREPARE q FROM :GrantSelect;
  166.   EXEC SQL EXECUTE q;
  168.   sprintf(GrantAlter, "EXEC SQL GRANT Alter ON %s",table_fqn ," TO %s", role_name);
  169.   EXEC SQL PREPARE q FROM :GrantAlter;
  170.   EXEC SQL EXECUTE q;
  172. }
  174. EXEC SQL CLOSE :table_cursor;
  176. /*Role granting*/
  177.     sprintf(GrantRoleToUser, "EXEC SQL GRANT %s",role_name ," TO %s", owner_name);
  178.     EXEC SQL PREPARE q FROM :GrantRoleToUser;
  179.     EXEC SQL EXECUTE q;
  181.     sprintf(SetDefaultRole, "EXEC SQL ALTER USER %s",owner_name," DEFAULT ROLE %s", role_name);
  182.     EXEC SQL PREPARE q FROM :SetDefaultRole;
  183.     EXEC SQL EXECUTE q;
  186.     printf("\nArrivederci.\n\n" );
  188.     EXEC SQL COMMIT WORK RELEASE;
  189.     exit(EXIT_SUCCESS);
  190. }


 
et les erreurs générées par la version win32 du precompilateur :
 

Citation :


 
Erreur à la ligne 117, colonne 12 dans le fichier grant_role.pc
  rec_nr = EXEC SQL SELECT COUNT(*)  
...........1
PLS-S-00201, l'identificateur 'SYS.ALL_TABLES' doit être déclaré
Erreur à la ligne 117, colonne 12 dans le fichier grant_role.pc
  rec_nr = EXEC SQL SELECT COUNT(*)  
...........1
PLS-S-00000, SQL Statement ignored
erreur sémantique en ligne 117, colonne 12, fichier grant_role.pc:
  rec_nr = EXEC SQL SELECT COUNT(*)  
...........1
PCC-S-02346, PL/SQL a trouvé des erreurs sémantiques
        OPEN :table_cursor FOR SELECT TABLE_NAME FROM SYS.ALL_TABLES
..........................................................1
PLS-S-00201, l'identificateur 'SYS.ALL_TABLES' doit être déclaré
        OPEN :table_cursor FOR SELECT TABLE_NAME FROM SYS.ALL_TABLES
...............................1
PLS-S-00000, SQL Statement ignored
erreur sémantique en ligne 125, colonne 5, fichier grant_role.pc:
    BEGIN
....1
PCC-S-02346, PL/SQL a trouvé des erreurs sémantiques
erreur sémantique en ligne 188, colonne 30, fichier grant_role.pc:
    EXEC SQL PREPARE q FROM :GrantRoleToUser;
.............................1
PCC-S-02322, identificateur inconnu rencontré
erreur sémantique en ligne 188, colonne 24, fichier grant_role.pc:
    EXEC SQL PREPARE q FROM :GrantRoleToUser;
.......................1
PCC-S-02319, type de l'expression différent de son utilisation
erreur sémantique en ligne 192, colonne 30, fichier grant_role.pc:
    EXEC SQL PREPARE q FROM :SetDefaultRole;
.............................1
PCC-S-02322, identificateur inconnu rencontré
erreur sémantique en ligne 192, colonne 24, fichier grant_role.pc:
    EXEC SQL PREPARE q FROM :SetDefaultRole;
.......................1
PCC-S-02319, type de l'expression différent de son utilisation


 
globalement deux erreurs quoi...
 
par ailleurs, la version linux du même precompilateur me retourne carrément :
 

Citation :

Error at line 202, column 2 in file /home/oracle/grant_role.pc
}
.1
PCC-S-02010, found end-of-file while scanning string literal
Error at line 0, column 0 in file grant_role.pc
PCC-F-02102, Fatal error while doing C preprocessing


 
 :??:  
 
Je vois pas ou, et en plus, comment ce fait-il que la version windows ne m'insulte pas de la même maniére ?
 
A vot bon coeur messieurs, dames...
 
 :jap:  

Reply

Marsh Posté le 15-12-2004 à 16:49:36   

Reply

Marsh Posté le 15-12-2004 à 20:19:57    

Fais plutôt :
 

Code :
  1. EXEC SQL SELECT COUNT(*)
  2. INTO :rec_nr
  3. FROM SYS.ALL_TABLES
  4. WHERE owner = :owner_name;


 
Pour les curseurs, il faut faire :
 

Code :
  1. EXEC SQL DECLARE CURSOR my_cursor FOR
  2. SELECT TABLE_NAME FROM SYS.ALL_TABLES
  3. WHERE owner = :owner_name;
  5. EXEC SQL OPEN my_cursor;

Reply

Marsh Posté le 16-12-2004 à 10:58:07    

Merci beaucoup pour ton aide Beegee  ;) .
 
Pour ceux qui auraient le même genre de pb, voici la version qui marche bien de mon petit machin :
 

Code :
  1. #include <stdio.h>
  2. #include <string.h>
  3. #include <sqlca.h>
  4. #include <stdlib.h>
  5. #include <sqlda.h>
  6. #include <sqlcpr.h>
  9. /* Declare variables.  No declare section is
  10.    needed if MODE=ORACLE. */
  12. EXEC SQL BEGIN DECLARE SECTION;
  14. char    db_username[40];
  15. char    db_passwd[40];
  16. char    uid[81];
  18. char      CreateRole[255];
  19. char      GrantCreateSession[255];
  20. char      GrantCreateTable[255];
  21. char      GrantCreateView[255];
  22. char      GrantCreateTrigger[255];
  23. char      GrantUpdate[255];
  24. char      GrantInsert[255];
  25. char      GrantSelect[255];
  26. char     GrantAlter[255];
  27. char    GrantRoleToUser[255];
  28. char    SetDefaultRole[255];
  31. sql_cursor  table_cursor;
  32. char     owner_name[40];
  33. char    role_name[80];
  34. char    table_fqn[255];
  35. char    used_table[50];
  36. int     rec_nr = 0;
  38. EXEC SQL END DECLARE SECTION;
  41. void sql_error(msg)
  42.     char *msg;
  43. {
  44.     char err_msg[512];
  45.     size_t buf_len, msg_len;
  47.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  49.     printf("\n%s\n", msg);
  51. /* Call sqlglm() to get the complete text of the
  52. * error message.
  53. */
  54.     buf_len = sizeof (err_msg);
  55.     sqlglm(err_msg, &buf_len, &msg_len);
  56.     printf("%.*s\n", msg_len, err_msg);
  58.     EXEC SQL ROLLBACK RELEASE;
  59.     exit(EXIT_FAILURE);
  60. }
  62. void main()
  63. {
  65. /*Get db passwd*/
  66.     db_passwd = 0;
  67.         printf("\nEnter db password (0 to quit): " );
  68.         gets(db_passwd);
  69.         if (db_passwd == 0)
  70.             break;
  71.            
  73. /*Get owner name*/
  74.         printf("\nEnter schema owner name (0 to quit): " );
  75.         gets(owner_name);
  76.         if (owner_name == 0)
  77.             break;
  80. /*Get role name*/           
  81.     role_name = 0;
  82.         printf("\nEnter role name (0 to quit): " );
  83.         gets(role_name);
  84.         if (role_name == 0)
  85.             break;     
  86.    
  91. /* Connect to ORACLE. */
  93.  db_username = 'sys';
  95.  spintf(uid,"%s/%s",db_username,db_passwd);
  97.     EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--" );
  100.     EXEC SQL CONNECT :uid IN SYSDBA MODE;
  101.     printf("\nConnected to ORACLE as user: %s\n", db_username);
  103.  EXEC SQL DECLARE ALL_TABLES TABLE (TABLE_NAME VARCHAR2(30) , OWNER VARCHAR2(30));
  105.     EXEC SQL SELECT COUNT(*)
  106.      INTO :rec_nr
  107.     FROM ALL_TABLES
  108.       WHERE owner = :owner_name;
  110. /*retrieve table list in a cursor predefined structure*/
  111.    EXEC SQL DECLARE table_cursor CURSOR FOR
  112.     SELECT TABLE_NAME FROM ALL_TABLES 
  113.      WHERE owner = :owner_name;
  115.    EXEC SQL OPEN table_cursor;
  117. /*Create role*/
  118. //DDL statements can't be specified with host variables in it.
  120. sprintf(CreateRole, "EXEC SQL CREATE ROLE %s", role_name);
  121. EXEC SQL PREPARE q FROM :CreateRole;
  122. EXEC SQL EXECUTE q;
  125. /*Grant privileges to role*/
  126. sprintf(GrantCreateSession, "EXEC SQL GRANT CREATE SESSION TO %s", role_name);
  127. EXEC SQL PREPARE q FROM :GrantCreateSession;
  128. EXEC SQL EXECUTE q;
  130. sprintf(GrantCreateTable, "EXEC SQL GRANT CREATE TABLE TO %s", role_name);
  131. EXEC SQL PREPARE q FROM :GrantCreateTable;
  132. EXEC SQL EXECUTE q;
  134. sprintf(GrantCreateView, "EXEC SQL GRANT CREATE VIEW TO %s", role_name);
  135. EXEC SQL PREPARE q FROM :GrantCreateView;
  136. EXEC SQL EXECUTE q;
  138. sprintf(GrantCreateTrigger, "EXEC SQL GRANT CREATE TRIGGER TO %s", role_name);
  139. EXEC SQL PREPARE q FROM :GrantCreateTrigger;
  140. EXEC SQL EXECUTE q;
  144. for(int i = 0;i < rec_nr;i++)
  145. {
  146.   used_table = table_cursor[i];
  148.   sprintf(table_fqn, "%s.%s", owner_name, used_table);
  151.   sprintf(GrantUpdate, "EXEC SQL GRANT UPDATE ON %s",table_fqn ," TO %s", role_name);
  152.   EXEC SQL PREPARE q FROM :GrantUpdate;
  153.   EXEC SQL EXECUTE q;
  155.   sprintf(GrantInsert, "EXEC SQL GRANT INSERT ON %s",table_fqn ," TO %s", role_name);
  156.   EXEC SQL PREPARE q FROM :GrantInsert;
  157.   EXEC SQL EXECUTE q;
  159.   sprintf(GrantSelect, "EXEC SQL GRANT SELECT ON %s",table_fqn ," TO %s", role_name);
  160.   EXEC SQL PREPARE q FROM :GrantSelect;
  161.   EXEC SQL EXECUTE q;
  163.   sprintf(GrantAlter, "EXEC SQL GRANT Alter ON %s",table_fqn ," TO %s", role_name);
  164.   EXEC SQL PREPARE q FROM :GrantAlter;
  165.   EXEC SQL EXECUTE q;
  167. }
  169. EXEC SQL CLOSE :table_cursor;
  171. /*Role granting*/
  172.     sprintf(GrantRoleToUser, "EXEC SQL GRANT %s",role_name ," TO %s", owner_name);
  173.     EXEC SQL PREPARE q FROM :GrantRoleToUser;
  174.     EXEC SQL EXECUTE q;
  176.     sprintf(SetDefaultRole, "EXEC SQL ALTER USER %s",owner_name," DEFAULT ROLE %s", role_name);
  177.     EXEC SQL PREPARE q FROM :SetDefaultRole;
  178.     EXEC SQL EXECUTE q;
  181.     printf("\nArrivederci.\n\n" );
  183.     EXEC SQL COMMIT WORK RELEASE;
  184.     exit(EXIT_SUCCESS);
  185. }

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed