|
|
|
| Re: Error when updating (-2118) [message #418682 is a reply to message #418679] |
Tue, 18 August 2009 02:14   |
s.g.
Messages: 6
Registered: August 2009 |
Junior Member |
|
|
This is a simplified version of the code that gives me the error (it means "no answer", I guess... thanks, Michel).
I was trying to post it, but somehow the body text got lost in the process.
Anyway, I think the connection is Ok, because I get the values from the FETCH all right. Can it be a privileges problem or something like that?
Thank you in advance.
EXEC SQL DECLARE dbz DATABASE;
EXEC SQL AT dbz DECLARE sql_query STATEMENT;
EXEC SQL BEGIN DECLARE SECTION;
char szConnection[ 512 ];
char varContract[10];
int varNu;
char varProd[10];
char szSqlCursor[ 1200 ] = {0};
varchar szError[ 1000 ] = {0};
EXEC SQL END DECLARE SECTION;
int main( int argc, char** argv )
{
strcpy(szConnection,"CONNECTIONKEY");
EXEC SQL AT dbz CONNECT :szConnection;
strcpy( szError.arr, "Hello");
szError.len = strlen(szError.arr);
szError.arr[szError.len] = '\0';
strcpy( szSqlCursor, " SELECT CONTRACT,NU,PROD ");
strcat( szSqlCursor, " FROM TABLECONTRACT");
EXEC SQL AT dbz
DECLARE CUR_CONTR CURSOR FOR sql_query;
EXEC SQL AT dbz
PREPARE sql_query FROM :szSqlCursor;
EXEC SQL AT dbz
OPEN CUR_CONTR;
EXEC sql AT dbz
FETCH CUR_CONTR
into :varContract, :varNu, :varProd;
EXEC SQL AT dbz
UPDATE TABLECONTRACT
SET TX_ERROR = :szError
WHERE CURRENT OF CUR_CONTR;
if ( SQLCODE != BD_OK && SQLCODE != BD_LEER_NULL )
{
printf( "Error <%ld> when updating from cursor %s/%d-%s.\n"
, SQLCODE
, varContract
, varNu
, varProd
);
}
}
[Updated on: Tue, 18 August 2009 02:31]
|
|
|
| Re: Error when updating (-2118) [message #418685 is a reply to message #418682] |
Tue, 18 August 2009 02:33 |
Michel Cadot
Messages: 29436
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | WHERE CURRENT OF CUR_CONTR
|
Database PL/SQL User's Guide and Reference
Chapter 13 PL/SQL Language Elements
Section UPDATE Statement
Paragraph WHERE CURRENT OF cursor_name
| Quote: | Refers to the latest row processed by the FETCH statement associated with the specified cursor. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
|
|
|
| Re: Error when updating (-2118) [message #418693 is a reply to message #418685] |
Tue, 18 August 2009 03:26 |
s.g.
Messages: 6
Registered: August 2009 |
Junior Member |
|
|
Thank you very much, Michel.
You were right about the FOR UPDATE, but I assure you I did read the manual:
| Quote: |
The FOR UPDATE OF clause is optional when you DECLARE a cursor that is referenced in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause signals the precompiler to add a FOR UPDATE clause if necessary
|
and neither the compiler nor the precompiler were issuing an error. I think that is because of the sql_query.
Anyway, I've corrected that, changing the cursor declaration to this one:
EXEC SQL AT dbz
DECLARE CUR_CONTR CURSOR FOR
SELECT CONTRACT, NU, PROD
FROM TABLECONTRACT
FOR UPDATE OF TX_ERROR;
And now the program just stops when it hits the update. As if it were stuck. I've tried the update from Toad and I've verified it takes less than a second.
Any hints?
P.D. I should have read the posting guidelines, you are right.
[Updated on: Tue, 18 August 2009 03:42] by Moderator
|
|
|
| Re: Error when updating (-2118) [message #418823 is a reply to message #418675] |
Tue, 18 August 2009 11:46 |
cookiemonster
Messages: 1438
Registered: September 2008
Location: Rainy Manchester |
Senior Member |
|
|
Well that cursor is going to try and lock every row in the table and if any of the rows are locked by someone else it'll hang.
Try changing it to:
FOR UPDATE OF TX_ERROR NOWAIT;
and see what happens.
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
