Dienstag, 3. September 2013

Native character strings with Oracle Pro*C and C

There are times where you can't get get around the Oracle Pro*C precompiler. It allows you to access a Oracle database from within your C programs. However, having seen things like JDBC in Java, Pro*C is technology from a past century.
Yes, it's not directly a friendship between me and the precompiler. If you're allowed to introduce a third-party open source library, I highly recommend OCILIB for working with your Oracle database. It allows a more intuitive and C native way (without precompiling) to access the data.

Coming back to Pro*C. All code I came across was handling character strings with the Oracle supplied VARCHAR struct. This struct contains the actual data (data field) and it's length (len field). Before using the string one has to NULL-terminated manually like in this example:
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR string1[20];
VARCHAR string2[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE c cursor FOR
SELECT 'a test',
       ' an other test ' 
FROM dual;

EXEC SQL OPEN c;
EXEC SQL WHENEVER NOT FOUND DO break;

for (;;) {
    EXEC SQL FETCH c INTO :string1, :string2;

    // manually adding NULL to terminate string
    string1.arr[string1.len] = '\0';
    string2.arr[string2.len] = '\0';

    printf("..%s..\n", string1.arr);
    printf("..%s..\n", string2.arr);
}
EXEC SQL CLOSE c; 

After spending some hours with the Pro*C developers guide I discovered a more C like way to accomplish the same thing. Basically, it uses a new character map "STRING" which is either set as precompiler option or set inline like in the following example:
// either as precompiler option or inline in the code
EXEC ORACLE OPTION (CHAR_MAP=STRING);

EXEC SQL BEGIN DECLARE SECTION;
char *string1[20];
char *string2[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE c cursor FOR
SELECT 'a test',
       ' an other test ' 
FROM dual;

EXEC SQL OPEN c;
EXEC SQL WHENEVER NOT FOUND DO break;

for (;;) {
    EXEC SQL FETCH c INTO :string1, :string2;
    printf("..%s..\n", string1);
    printf("..%s..\n", string2);
}
EXEC SQL CLOSE c;
Both examples do the same thing, the last one reliefs you from dealing with NULL termination of strings yourself.


Keine Kommentare:

Kommentar veröffentlichen