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>
  7. /* Declare variables.  No declare section is
  8.    needed if MODE=ORACLE. */
  9. EXEC SQL BEGIN DECLARE SECTION;
  10. char    db_username[40];
  11. char    db_passwd[40];
  12. char    uid[81];
  13. char      CreateRole[255];
  14. char      GrantCreateSession[255];
  15. char      GrantCreateTable[255];
  16. char      GrantCreateView[255];
  17. char      GrantCreateTrigger[255];
  18. char      GrantUpdate[255];
  19. char      GrantInsert[255];
  20. char      GrantSelect[255];
  21. char     GrantAlter[255];
  22. sql_cursor  table_cursor;
  23. char     owner_name[40];
  24. char    role_name[80];
  25. char    table_fqn[255];
  26. char    used_table[50];
  27. int     rec_nr = 0;
  28. EXEC SQL END DECLARE SECTION;
  29. void sql_error(msg)
  30.     char *msg;
  31. {
  32.     char err_msg[512];
  33.     size_t buf_len, msg_len;
  34.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  35.     printf("\n%s\n", msg);
  36. /* Call sqlglm() to get the complete text of the
  37. * error message.
  38. */
  39.     buf_len = sizeof (err_msg);
  40.     sqlglm(err_msg, &buf_len, &msg_len);
  41.     printf("%.*s\n", msg_len, err_msg);
  42.     EXEC SQL ROLLBACK RELEASE;
  43.     exit(EXIT_FAILURE);
  44. }
  45. void main()
  46. {
  47. /*Get db username*/
  48.     db_username = 0;
  49.         printf("\nEnter db username (0 to quit): " );
  50.         gets(db_username);
  51.         if (db_username == 0)
  52.             break;
  53. /*Get db passwd*/
  54.     db_passwd = 0;
  55.         printf("\nEnter db password (0 to quit): " );
  56.         gets(db_passwd);
  57.         if (db_passwd == 0)
  58.             break;
  59.            
  60. /*Get owner name*/
  61.         printf("\nEnter schema owner name (0 to quit): " );
  62.         gets(owner_name);
  63.         if (owner_name == 0)
  64.             break;
  65. /*Get role name*/           
  66.     role_name = 0;
  67.         printf("\nEnter role name (0 to quit): " );
  68.         gets(role_name);
  69.         if (role_name == 0)
  70.             break;     
  71.    
  72. /* Connect to ORACLE. */
  73.  spintf(uid,"%s/%s",db_username,db_passwd);
  74.     EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--" );
  75.     EXEC SQL CONNECT :uid;
  76.     printf("\nConnected to ORACLE as user: %s\n", db_username);
  77.   rec_nr = EXEC SQL SELECT COUNT(*)
  78. FROM SYS.ALL_TABLES
  79.  WHERE owner = :owner_name; 
  80. /*retrieve table list in a cursor predefined structure*/
  81. EXEC SQL ALLOCATE :table_cursor;
  82. EXEC SQL EXECUTE
  83.     BEGIN
  84.         OPEN :table_cursor FOR SELECT TABLE_NAME FROM SYS.ALL_TABLES
  85.              WHERE owner = :owner_name;
  86.     END;
  87.   END-EXEC;
  88. /*Create role*/
  89. //DDL statements can't be specified with host variables in it.
  90. sprintf(CreateRole, "EXEC SQL CREATE ROLE %s", role_name);
  91. EXEC SQL PREPARE q FROM :CreateRole;
  92. EXEC SQL EXECUTE q;
  93. /*Grant privileges to role*/
  94. sprintf(GrantCreateSession, "EXEC SQL GRANT CREATE SESSION TO %s", role_name);
  95. EXEC SQL PREPARE q FROM :GrantCreateSession;
  96. EXEC SQL EXECUTE q;
  97. sprintf(GrantCreateTable, "EXEC SQL GRANT CREATE TABLE TO %s", role_name);
  98. EXEC SQL PREPARE q FROM :GrantCreateTable;
  99. EXEC SQL EXECUTE q;
  100. sprintf(GrantCreateView, "EXEC SQL GRANT CREATE VIEW TO %s", role_name);
  101. EXEC SQL PREPARE q FROM :GrantCreateView;
  102. EXEC SQL EXECUTE q;
  103. sprintf(GrantCreateTrigger, "EXEC SQL GRANT CREATE TRIGGER TO %s", role_name);
  104. EXEC SQL PREPARE q FROM :GrantCreateTrigger;
  105. EXEC SQL EXECUTE q;
  106. for(int i = 0;i < rec_nr;i++)
  107. {
  108.   used_table = table_cursor[i];
  109.   sprintf(table_fqn, "%s.%s", owner_name, used_table);
  110.   sprintf(GrantUpdate, "EXEC SQL GRANT UPDATE ON %s",table_fqn ," TO %s", role_name);
  111.   EXEC SQL PREPARE q FROM :GrantUpdate;
  112.   EXEC SQL EXECUTE q;
  113.   sprintf(GrantInsert, "EXEC SQL GRANT INSERT ON %s",table_fqn ," TO %s", role_name);
  114.   EXEC SQL PREPARE q FROM :GrantInsert;
  115.   EXEC SQL EXECUTE q;
  116.   sprintf(GrantSelect, "EXEC SQL GRANT SELECT ON %s",table_fqn ," TO %s", role_name);
  117.   EXEC SQL PREPARE q FROM :GrantSelect;
  118.   EXEC SQL EXECUTE q;
  119.   sprintf(GrantAlter, "EXEC SQL GRANT Alter ON %s",table_fqn ," TO %s", role_name);
  120.   EXEC SQL PREPARE q FROM :GrantAlter;
  121.   EXEC SQL EXECUTE q;
  122. }
  123. EXEC SQL CLOSE :table_cursor;
  124. /*Role granting*/
  125.     sprintf(GrantRoleToUser, "EXEC SQL GRANT %s",role_name ," TO %s", owner_name);
  126.     EXEC SQL PREPARE q FROM :GrantRoleToUser;
  127.     EXEC SQL EXECUTE q;
  128.     sprintf(SetDefaultRole, "EXEC SQL ALTER USER %s",owner_name," DEFAULT ROLE %s", role_name);
  129.     EXEC SQL PREPARE q FROM :SetDefaultRole;
  130.     EXEC SQL EXECUTE q;
  131.     printf("\nArrivederci.\n\n" );
  132.     EXEC SQL COMMIT WORK RELEASE;
  133.     exit(EXIT_SUCCESS);
  134. }


 
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;
  4. 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>
  7. /* Declare variables.  No declare section is
  8.    needed if MODE=ORACLE. */
  9. EXEC SQL BEGIN DECLARE SECTION;
  10. char    db_username[40];
  11. char    db_passwd[40];
  12. char    uid[81];
  13. char      CreateRole[255];
  14. char      GrantCreateSession[255];
  15. char      GrantCreateTable[255];
  16. char      GrantCreateView[255];
  17. char      GrantCreateTrigger[255];
  18. char      GrantUpdate[255];
  19. char      GrantInsert[255];
  20. char      GrantSelect[255];
  21. char     GrantAlter[255];
  22. char    GrantRoleToUser[255];
  23. char    SetDefaultRole[255];
  24. sql_cursor  table_cursor;
  25. char     owner_name[40];
  26. char    role_name[80];
  27. char    table_fqn[255];
  28. char    used_table[50];
  29. int     rec_nr = 0;
  30. EXEC SQL END DECLARE SECTION;
  31. void sql_error(msg)
  32.     char *msg;
  33. {
  34.     char err_msg[512];
  35.     size_t buf_len, msg_len;
  36.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  37.     printf("\n%s\n", msg);
  38. /* Call sqlglm() to get the complete text of the
  39. * error message.
  40. */
  41.     buf_len = sizeof (err_msg);
  42.     sqlglm(err_msg, &buf_len, &msg_len);
  43.     printf("%.*s\n", msg_len, err_msg);
  44.     EXEC SQL ROLLBACK RELEASE;
  45.     exit(EXIT_FAILURE);
  46. }
  47. void main()
  48. {
  49. /*Get db passwd*/
  50.     db_passwd = 0;
  51.         printf("\nEnter db password (0 to quit): " );
  52.         gets(db_passwd);
  53.         if (db_passwd == 0)
  54.             break;
  55.            
  56. /*Get owner name*/
  57.         printf("\nEnter schema owner name (0 to quit): " );
  58.         gets(owner_name);
  59.         if (owner_name == 0)
  60.             break;
  61. /*Get role name*/           
  62.     role_name = 0;
  63.         printf("\nEnter role name (0 to quit): " );
  64.         gets(role_name);
  65.         if (role_name == 0)
  66.             break;     
  67.    
  68. /* Connect to ORACLE. */
  69.  db_username = 'sys';
  70.  spintf(uid,"%s/%s",db_username,db_passwd);
  71.     EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--" );
  72.     EXEC SQL CONNECT :uid IN SYSDBA MODE;
  73.     printf("\nConnected to ORACLE as user: %s\n", db_username);
  74.  EXEC SQL DECLARE ALL_TABLES TABLE (TABLE_NAME VARCHAR2(30) , OWNER VARCHAR2(30));
  75.     EXEC SQL SELECT COUNT(*)
  76.      INTO :rec_nr
  77.     FROM ALL_TABLES
  78.       WHERE owner = :owner_name;
  79. /*retrieve table list in a cursor predefined structure*/
  80.    EXEC SQL DECLARE table_cursor CURSOR FOR
  81.     SELECT TABLE_NAME FROM ALL_TABLES 
  82.      WHERE owner = :owner_name;
  83.    EXEC SQL OPEN table_cursor;
  84. /*Create role*/
  85. //DDL statements can't be specified with host variables in it.
  86. sprintf(CreateRole, "EXEC SQL CREATE ROLE %s", role_name);
  87. EXEC SQL PREPARE q FROM :CreateRole;
  88. EXEC SQL EXECUTE q;
  89. /*Grant privileges to role*/
  90. sprintf(GrantCreateSession, "EXEC SQL GRANT CREATE SESSION TO %s", role_name);
  91. EXEC SQL PREPARE q FROM :GrantCreateSession;
  92. EXEC SQL EXECUTE q;
  93. sprintf(GrantCreateTable, "EXEC SQL GRANT CREATE TABLE TO %s", role_name);
  94. EXEC SQL PREPARE q FROM :GrantCreateTable;
  95. EXEC SQL EXECUTE q;
  96. sprintf(GrantCreateView, "EXEC SQL GRANT CREATE VIEW TO %s", role_name);
  97. EXEC SQL PREPARE q FROM :GrantCreateView;
  98. EXEC SQL EXECUTE q;
  99. sprintf(GrantCreateTrigger, "EXEC SQL GRANT CREATE TRIGGER TO %s", role_name);
  100. EXEC SQL PREPARE q FROM :GrantCreateTrigger;
  101. EXEC SQL EXECUTE q;
  102. for(int i = 0;i < rec_nr;i++)
  103. {
  104.   used_table = table_cursor[i];
  105.   sprintf(table_fqn, "%s.%s", owner_name, used_table);
  106.   sprintf(GrantUpdate, "EXEC SQL GRANT UPDATE ON %s",table_fqn ," TO %s", role_name);
  107.   EXEC SQL PREPARE q FROM :GrantUpdate;
  108.   EXEC SQL EXECUTE q;
  109.   sprintf(GrantInsert, "EXEC SQL GRANT INSERT ON %s",table_fqn ," TO %s", role_name);
  110.   EXEC SQL PREPARE q FROM :GrantInsert;
  111.   EXEC SQL EXECUTE q;
  112.   sprintf(GrantSelect, "EXEC SQL GRANT SELECT ON %s",table_fqn ," TO %s", role_name);
  113.   EXEC SQL PREPARE q FROM :GrantSelect;
  114.   EXEC SQL EXECUTE q;
  115.   sprintf(GrantAlter, "EXEC SQL GRANT Alter ON %s",table_fqn ," TO %s", role_name);
  116.   EXEC SQL PREPARE q FROM :GrantAlter;
  117.   EXEC SQL EXECUTE q;
  118. }
  119. EXEC SQL CLOSE :table_cursor;
  120. /*Role granting*/
  121.     sprintf(GrantRoleToUser, "EXEC SQL GRANT %s",role_name ," TO %s", owner_name);
  122.     EXEC SQL PREPARE q FROM :GrantRoleToUser;
  123.     EXEC SQL EXECUTE q;
  124.     sprintf(SetDefaultRole, "EXEC SQL ALTER USER %s",owner_name," DEFAULT ROLE %s", role_name);
  125.     EXEC SQL PREPARE q FROM :SetDefaultRole;
  126.     EXEC SQL EXECUTE q;
  127.     printf("\nArrivederci.\n\n" );
  128.     EXEC SQL COMMIT WORK RELEASE;
  129.     exit(EXIT_SUCCESS);
  130. }

Reply

Sujets relatifs:

Leave a Replay

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