help with SQL embedded in C

R

Rob

I'm not sure if this is the right newsgroup for this question, but I
wasn't sure where else to post it. I'm having some trouble with SQL in
C.

I'm trying to catch sql state codes in my program. For example the
code "02000" means "no tuples found". I made an array SQLSTATE[6], but
I can't get these codes to go into the array. I'm not sure if I'm
missing something, or there's something I'm forgetting to #include,
because whenever I check SQLSTATE, it's empty. I have something like
this:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;
char movie_title[64];
short movie_year;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

int main() {
/* connect to database */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT title, year
FROM Movies
WHERE Movies.actor = 'Tom Hanks';

EXEC SQL OPEN c1;

while(1) {
EXEC SQL FETCH c1 INTO :movie_title, :movie_year

if (strcmp(SQLSTATE, "02000") == 0) {
printf("No movies found for that actor.");
} else {
printf("title: %s, year: %d", movie_title, movie_year);
}
}
}



I thought SQLSTATE would automatically get the state codes, but that's
not happening. Not sure what I'm missing. Thanks for any help!
Rob
 
G

Gordon Burditt

#include said:
This is a syntax error in standard C. Unfortunately I don't know how SQL
works so I can't offer much further help.
One possibility is that EXEC SQL INCLUDE is defined somewhere in a header
(sql.h or something like that), and it compiles to something meaningful in
C. Another possibility is that you are supposed to pass the C source through
some sort of SQL front end.

I recognize this dialect as Oracle Pro*C, which uses a preprocessor.
The question of how to get status codes is very Pro*C dependent.
Unfortunately I haven't used it enough to know the answer.
EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION; These statements are valid C.
char movie_title[64];
short movie_year;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
As it stands this stuff won't compile with a C compiler. You need to find
out what to do to translate your SQL statements into C.

It *is* possible to do SQL directly in C extended by a vendor library
without a SQL preprocesor. You don't get some of the nice features
like binding C variables into SQL statements, but it works. Queries
are strings (and quoting data in them, if needed, is your responsibility).
Results are strings, sorta like argv[], except that a SQL null value
may map into a NULL pointer.

#include <time.h>
#include <stdio.h>
#include "mysql.h"

char sql_cmd_buffer[10240];
struct tm *tp;
char *hostname;
int count;
char *ipaddr;
MYSQL *myp;
int ret;

/*
initialize myp by calling mysql_real_connect to set up
a connection to the database (similar to calling fopen()
to open a file, but the arguments are different, and
often include a hostname, database name, user name,
and password.

initialize values for tp, hostname, count, and ipaddr.
*/

/*
* Note that if these values might have quotes or backslashes in
* them, it's up to you to quote them, possibly using
* mysql_real_escape_string()
*/
sprintf(sql_cmd_buffer,
"INSERT INTO spammers VALUES ('%s', %d, '%d-%d-%d', '%s')",
hostname,
count,
tp->tm_year, tp->tm_mon+1, tp->tm_mday,
ipaddr);
ret = mysql_query(myp, sql_cmd_buffer);
if (ret != 0) { ... some error happened ... }

Gordon L. Burditt
 
L

Larry__Weiss

Rob said:
I'm not sure if this is the right newsgroup for this question, but I
wasn't sure where else to post it. I'm having some trouble with SQL in
C...

Try either or both of:

news:comp.databases.oracle.tools
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top