1. 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?

Accepted Answer
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.
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 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
  1. Rômulo Sorato
  2. Wednesday, 11 September 2019 14:08 PM UTC
Could you give an example?
  1. Helpful
  1. Michael Kramer
  2. Wednesday, 11 September 2019 15:00 PM UTC
  1. Helpful
There are no comments made yet.
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 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
  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
  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
  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.