1. Rômulo Sorato
  2. PowerBuilder
  3. Tuesday, 10 September 2019

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?

Accepted Answer
Rômulo Sorato Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # Permalink
Michael Kramer Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Wednesday, 11 September 2019
  2. PowerBuilder
  3. # 1
Michael Kramer Accepted Answer Pending Moderation
1
Votes
Undo

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
Could you give an example?
  1. Rômulo Sorato
  2. Wednesday, 11 September 2019
There are no comments made yet.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # 2
Olan Knight Accepted Answer Pending Moderation
1
Votes
Undo

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.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # 3
Saurabh Sharma Accepted Answer Pending Moderation
0
Votes
Undo

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

Comment
Yes it´s 0, no error.But nothing happens on database or datawindow
  1. Rômulo Sorato
  2. Tuesday, 10 September 2019
If you are deleting rows in script, you have to re-retrieve any DataWindow that might include those rows.
  1. Roland Smith
  2. Tuesday, 10 September 2019
Hi Roland;

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

Regards ... Chris
  1. Chris Pollach
  2. Tuesday, 10 September 2019
There are no comments made yet.
  1. Tuesday, 10 September 2019
  2. PowerBuilder
  3. # 4
  • Page :
  • 1


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