[Gambas-user] Doing query of Stored procedure with no related data crashes gambas

[BB]

Major snip. Here's some stuff from my notepad.

Stored procedures in postgresql

This is a very interesting topic. It came to my attention from a post in 
the gambas mailing list from Safiur Rahman entitled "[Gambas-user] Doing 
query of Stored procedure with no related data crashes gambas". On 
looking into it I found that since version 11, postgresql has supported 
the concept of stored procedures, which are functions that do not 
necessarilyreturn an outcome (as a result object). They can be invoked 
using the SQL "CALL" command.

Now, why this is of interest to me is that

 1. "stored procedures" is not a concept overtly supported by the gambas
    db interface. (more below)
 2. I had no idea that postgresql supported the concept.

Safiur's issue revloved around a call to the gb.db.mysql EXEC command, viz

res = $qrScanCon.Exec("CALL spGetTransByTraceId('" & sIndex & "')")

where res is defined as a Result object and when the exec call did not 
receive the expected res object because the all the stored procedure did 
when there was no tuples that matched the filter conditions it simply 
raises several "Notice" outputs and does not return the expected 
function output parameter.


This is a gambas "bug" in one sense and not so in others.EXEC expects a 
Result object to be returned, no matter whether the provided parameter 
is a valid SQL sentence or not! In his case I believe it may have not 
returned such. Further, his report of the Gambas error "Query failed: 
Commands out of sync; you can't run this command now" indicates that 
there could be a further and actual bug in the Gambas db component.

As Christof points out in the thread, "If it is an illegal command the 
DB should throw an error and Gambas should also throw an error but must 
not crash.". Even further, I am not sure that Gambas did actually 
"crash" or whether the db component invalidated itself internally 
somehow and refused to process any later commands. This is evidenced by 
Saifur's comment "Gambas never returns the result even if the stored 
procedure gets data later on". I have seen this condition myself when I 
have used some poor SQL command in postgresql and have not found a 
resolution. The Gambas program simply continues and continuously outputs 
"You can't do this now" error messages. So I'd add to Christof's comment 
"and should not invalidate any further calls."



Finally (for the moment!) here is what I think the real issue is. The 
Gambas db component needs a new method to allow calls to the underlying 
db engine that will only expect an optional Result object and also take 
into account "Notices" raised by the call. Since, as adequately 
expounded in the thread, these are quite valid things that one can ask 
the interface to perform then Gambas should do so, quietly and nicely.



I'm currently wading through the C in the component. I cannot see an 
easy solution using the EXEC function. More later.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20230219/3286d606/attachment.htm>