Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved SQL transaction not working

  1. Issue
0
Votes
Undo
  1. Rômulo Sorato
    Rômulo Sorato
  2. PowerBuilder
  3. Tuesday, 10 September 2019 12:33 PM UTC

Hello.

I´m trying to execute some exclusions in database

this is the code

DELETE FROM CONTRATO WHERE PESSOA_ID = :ID_PESSOA
DELETE FROM PESSOAS WHERE ID = :ID_PESSOA;
commit;

if sqlca.sqlcode < 0 then
	MessageBox ("Erro na exclusão de dados", &
	sqlca.sqlerrtext, Exclamation!)
	HALT
end If

However nothing happens in database.

I need to delete rows in contrato table before pessoas table.

I already set the transactional object and set the update properties.

 

What i´m doing wrong?

  1. Embedded SQL
Datawindow border on top and double border
PB2017 Appeon MS Web Browser Control Issue
Accepted Answer
Rômulo Sorato
Rômulo Sorato Accepted Answer Pending Moderation
  1. Tuesday, 10 September 2019 13:41 PM UTC
  2. PowerBuilder
  3. # Permalink

Hey it´s solved.

 

The problem is an error with the first sql statement

DELETE FROM CONTRATO WHERE PESSOA_ID = :ID_PESSOA

CONTRATO is typed wrong, the correct is "CONTRATOS"

 

A colleague tell me to check from now on, every statement

 

So i changed the code to this:

 

string lsMensagemErroBanco
boolean lbSucesso

lbSucesso = true

DELETE FROM CONTRATOS WHERE PESSOA_ID = :ID_PESSOA;
if sqlca.sqlcode < 0 then
lsMensagemErroBanco = sqlca.sqlerrtext
lbSucesso = false
else
DELETE FROM PESSOAS WHERE ID = :ID_PESSOA;
if sqlca.sqlcode < 0 then
lsMensagemErroBanco = sqlca.sqlerrtext
lbSucesso = false
end if
end if

if lbSucesso then
commit;
else
rollback;
MessageBox ("Erro na exclusão de dados", lsMensagemErroBanco, Exclamation!)
end if

 

Comment
There are no comments made yet.
Responses (5)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 11 September 2019 14:59 PM UTC
  2. PowerBuilder
  3. # 1

Simplest code in DBError event:

MessageBox("DB Error "+ string(code), sqlErrorText + "~r~nSQL SYNTAX~r~n" + sqlSyntax)

This code has several issues like exposing tech info to adversaries/hackers, locking app before releasing locks, and it will pop up every time you call DBHandle( ) to ensure a DB connection is no longer connected to DB.

Better example:

if this.ib_IgnoreDBError then return // DONE

// Log error
of_LogDBError(code, sqlErrorText, sqlSyntax)

// Display error
string title, text
title = "Database Error " + string(code)
text = "See error log for details."
post MessageBox(title, text, Exclamation!)

Technical Notes

  • Logging depends on your needs.
    You may write to text file. Easy for developer; insufficient for production.
  • In real app I wouldn't display error code. Code may expose security details.
  • POST MessageBox allows app to release locks (ROLLBACK) before app blocks waiting for user.
  • App turns on ib_IgnoreDBError before DBHandle( ) because return value zero fires DBError.

HTH /Michael

Comment
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 10 September 2019 19:32 PM UTC
  2. PowerBuilder
  3. # 2

Hi,

you can add code in SQLCA's class for DBError event. Delivers 3 arguments:

  • Code -- analogue to SQLCA.SQLDBCode
  • SQLErrorText -- analogue to SQLCA.SQLErrText
  • SQLSyntax -- SQL code causing the error

Write once; run everywhere.

HTH /Michael

Comment
Rômulo Sorato
  1. Rômulo Sorato
  2. Wednesday, 11 September 2019 14:08 PM UTC
Could you give an example?
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Wednesday, 11 September 2019 15:00 PM UTC
Se reply # 5 below (link https://community.appeon.com/index.php/qna/q-a/sql-transaction-not-working#reply-15568 )
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 10 September 2019 14:12 PM UTC
  2. PowerBuilder
  3. # 3

Don't forget that unless you have AUTOCOMMIT turned on, you need to manully COMMIT your changes before they actually take effect.

Olan

 

Comment
There are no comments made yet.
Saurabh Sharma
Saurabh Sharma Accepted Answer Pending Moderation
  1. Tuesday, 10 September 2019 12:37 PM UTC
  2. PowerBuilder
  3. # 4

Did you debug and check the error code coming in sqlca.sqlcode ?

Comment
Rômulo Sorato
  1. Rômulo Sorato
  2. Tuesday, 10 September 2019 13:09 PM UTC
Yes it´s 0, no error.But nothing happens on database or datawindow
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Tuesday, 10 September 2019 20:01 PM UTC
If you are deleting rows in script, you have to re-retrieve any DataWindow that might include those rows.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 10 September 2019 20:15 PM UTC
Hi Roland;

Or .... just copy them back from the DWO's "Original" buffer. ;-)

Regards ... Chris
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.