About Stored Procedures

[CRDF]

Hi.
Trying to learn how to execute SPs…
To begin with, at first returning just 1 value, wrote this one:

CREATE PROCEDURE TESTE(N INT) RETURNS (M INT) AS
BEGIN
M = N*2;
END

It shows no error on executing in SQL Window.
But I’m lost on how to retrieve the returned value.
Tried several alternatives, always some ERROR like:

• Object variable not set at line 12

or

• r = -1

Option Explicit
Sub spTeste(Evt As Object)
    Dim F As Object, Con As Object, Stmt As Object, Rst As Object
    Dim sComando As String
    Dim r As Integer
    On Error GoTo Erro
    F = Evt.Source.Model.Parent
    Con = F.activeConnection()
    sComando = "EXECUTE PROCEDURE ""TESTE""(2)"
    print sComando
    Stmt = Con.prepareCall(sComando)
    r = Stmt.execute()
    REM TRIED SEVERAL ALTERNATIVES INCLUDING Rst = [...]
    print r
    Exit Sub
    Erro: MsgBox Error & Chr(13) & "na linha " & Erl, 16, "ERRO"
End Sub

Thanks in advance!

[Wanderer]

@CRDF

Trying to learn how to execute SPs…

HSQLDB or Firebird ? (Or something else … ?)

Also: Why do you have the name of the stored procedure in " enclosed ?

I’ve googled: HSQLDB seems to use CALL to invoke…

[RobertG]

Missing the database you want to use with this procedure.

Here is a hint for MariaDB: save the content in a temporary table.

CALL Teste();

will show the temporary table created by Teste(). Now you could write a query to select the content.

[CRDF]

@Wanderer

HSQLDB or Firebird ? (Or something else … ?)

Firebird.

@Wanderer

Why do you have the name of the stored procedure in " enclosed ?

Because SQLs in macros encloses objects in " "…
But will try without…
Just trying to figure out how to do it in Base.

@RobertG

save the content in a temporary table.

CALL Teste();

will show the temporary table created by Teste(). Now you could write a query to select the content.

No temporary table. SP just returns scalar value = 2*parameter.
As long as I understood command is
EXECUTE PROCEDURE TESTE( < integer parameter >);
GUI does not accept this.
SQL Window execute it but in the return window message is
“0 rows updated”.
I understand a variable is needed to store the returned value.
So code, but how

 

[CRDF]

Wow: it’s very tricky!
Needs a
SUSPEND;
after setting value of the return variable, to open a RecordSet!
And it’s called by a
SELECT * FROM
 
Works also in the GUI > direct.
In code: same as running SELECTs.
 
Next learning steps:

• SP returning table;
• SPs for UPDATE, INSERT.