-1

I have the following update statement which works fine in SQL Developer:

UPDATE SDUSERS SET PWDATTEMPTS=PWDATTEMPTS+1 WHERE lower(UserName)='test';

Now, in my VB.NET it does not update the database for some reason and it produces error ORA-01013: user requested cancel of current operation, Error code: -2147467259

Dim sqlUpdateFailed As New OracleCommand("UPDATE " & oConnection.SCHEMA_NAME.ToUpper 
 & "SDUSERS SET PWDATTEMPTS=PWDATTEMPTS+1 WHERE lower(USERNAME)='" & sUsername & "'", conn)
                sqlUpdateFailed.CommandTimeout = 20
                sqlUpdateFailed.ExecuteNonQuery()

Any ideas what I might be doing wrong?

alwaysVBNET
  • 3,150
  • 8
  • 32
  • 65

1 Answers1

0

Most likely, reason of ORA-1013 in your case is a query execution timeout reached. Check it, try some big timeout value.

Such update should be executed fast, very fast (I guess table is small, isn't it?) So if it's hang it means the record is locked. Maybe you updated this record into SQL Developer and forgot to commit, maybe something else. You can check it if execute something like SELECT * FROM SDUSERS WHERE lower(UserName) = :username FOR UPDATE NOWAIT before your update statement.

BTW, your source gives an excellent ability for SQL Injection.

Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28
  • After I executed your suggested query I got a ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Any suggestions on how to proceed with that? My program runs locally, so I don't believe I will be having sql injection attacks, but in any case what would you suggest to change it to? – alwaysVBNET Apr 03 '15 at 11:31
  • What I did was to update the table with a value, manually commit the changes from the SQL Developer and then rerun the program. The connection from the application needs to close to view the updates in the table. If you could elaborate on changing the query it would be great. – alwaysVBNET Apr 03 '15 at 11:59
  • So you should locate who holds the lock. I'd suggest you to start with `select * from dba_dml_locks where name = 'SDUSERS'`. But generally you should either check all your SDUSERS operations or show some expertise with Oracle itself. – Sanders the Softwarer Apr 03 '15 at 12:08