Home » Developer & Programmer » Precompilers, OCI & OCCI » Error when updating (-2118) (Oracle Client 9.2, Pro*C)
Error when updating (-2118) [message #418675] Tue, 18 August 2009 01:54 Go to next message
s.g.
Messages: 6
Registered: August 2009
Junior Member
No Message Body

[Updated on: Tue, 18 August 2009 02:04]

Report message to a moderator

Re: Error when updating (-2118) [message #418679 is a reply to message #418675] Tue, 18 August 2009 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No Answer.

Regards
Michel
Re: Error when updating (-2118) [message #418681 is a reply to message #418679] Tue, 18 August 2009 02:14 Go to previous messageGo to next message
s.g.
Messages: 6
Registered: August 2009
Junior Member
No Message Body
Re: Error when updating (-2118) [message #418682 is a reply to message #418679] Tue, 18 August 2009 02:14 Go to previous messageGo to next message
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]

Report message to a moderator

Re: Error when updating (-2118) [message #418685 is a reply to message #418682] Tue, 18 August 2009 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
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

Report message to a moderator

Re: Error when updating (-2118) [message #418823 is a reply to message #418675] Tue, 18 August 2009 11:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Error when updating (-2118) [message #418966 is a reply to message #418823] Wed, 19 August 2009 07:02 Go to previous messageGo to next message
s.g.
Messages: 6
Registered: August 2009
Junior Member
Thank you, Cookiemonster,

Now I get an error (SQLCODE -54) when opening the cursor...
Re: Error when updating (-2118) [message #418985 is a reply to message #418966] Wed, 19 August 2009 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is clear than someelse is holding the row you try to update.

Regards
Michel
Re: Error when updating (-2118) [message #418988 is a reply to message #418985] Wed, 19 August 2009 08:45 Go to previous message
s.g.
Messages: 6
Registered: August 2009
Junior Member
Thank you, Michel and Cookiemonster.
You are right, I'll have to ask the DB responsible.
Previous Topic: Issue with Host Variable Usage in Pro*C
Next Topic: Need Help - Proc Error - 1540-0040 (S) The text "&" is unexpected.
Goto Forum:
  


Current Time: Thu Mar 28 13:58:07 CDT 2024