Thread: Embedded SQL ... should be quick ...

Here's the code in question:

Code:

     FILE *inputFile;
     char *buff = malloc(sizeof(char[255]));
     inputFile = fopen(filePath, "r");

     while (fgets(buff, 256, inputFile) != NULL) {
          EXEC SQL EXECUTE IMMEDIATE :buff; 
          EXEC SQL COMMIT;
     }
	
     fclose(inputFile);

I have a text file with one SQL query per line. This section of code should get each line from the text file, execute the query (non-SELECT query ... they're all UPDATE, INSERT, and DELETE), and commit the changes that query made. The loop does read in the lines from the input file. However, it doesn't do anything with the queries. What is going wrong?

What version of Pro*C client are you using on this? If it is something which has come with Oracle 7.3 or below, this is not the way to do it. You need to firstly PREPARE the STATEMENT and then EXECUTE.

I'm using the version that comes with Oracle 8i, which is Pro*C Release 8.1.6.0.0.

The method I'm trying to use is listed here as Method 1. Since I'm only trying to do UPDATE, DELETE, and INSERT, I can use Method 1, according to the brief description they give on that page:

This method lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables. For example, the following host strings qualify:

Code:

'DELETE FROM EMP WHERE DEPTNO = 20' 
'GRANT SELECT ON EMP TO scott'

With Method 1, the SQL statement is parsed every time it is executed.

It's pretty much guaranteed that the input file will not have SELECT statements in it.

You know what? I feel so stupid. Here's my original input file:

Code:

UPDATE Customer SET CCITY='Boston' WHERE SSN=200005;
INSERT INTO Vehicle VALUES (90711, 'Dodge', 'Intrepid', 2002);
INSERT INTO Vehicle VALUES (90712, 'Dodge', 'Neon', 2002);
DELETE FROM BuyVehicle WHERE SSN=200008;
INSERT INTO BuyVehicle (VIN, VMAKE, VMODEL, VYEAR) VALUES (200009, 90711, 18000, 2002);
INSERT INTO BuyVehicle (VIN, VMAKE, VMODEL, VYEAR) VALUES (200009, 90712, 12000, 2002);

My code didn't work because of the FRIGGIN' SEMICOLONS AT THE END OF EACH LINE. I feel really really dumb right now.