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.

[mariosv]

In Firebird 3. Chapter 5. Data Definition (DDL) Statements there are a couple of procedure samples on how to INSERT a record or returning a value to a SQL statement.

[CRDF]

@mariosv

returning a value to a SQL statement.

I think my error was not having
 
RETURNING … INTO < return variable >;
END
 
I tried
RETURN < variable >;
END
⇒ERROR.
Will try it monday. Thanks!

[CRDF]

Elaborating over @Villeroy 's demo DB ask128634.odb, I’m able to execute SP to insert into MINERALS via both SQL Window and code.
But still unable to retrieve the return value that should be the new ID auto-increment

CREATE PROCEDURE ADD_MATERIAL (NAME VARCHAR(50), COEFFICIENT INT, UNIT VARCHAR(10)
RETURNS (MATERIAL_ID INT)  AS
    BEGIN
        INSERT INTO MINERALS (N, C, UNIT)
        VALUES (:NAME, :COEFFICIENT, :UNIT)
        RETURNING ID INTO MATERIAL_ID; -- return parameter
    END

[RobertG]

Returning without content? Might be this one: 105220 – Firebird: Insert with direct SQL - RETURNING without values

[CRDF]

@RobertG

Returning without content?

Well, I don’t know if there is no content or I don’t know how to retrieve it (?)
In code if I put
(Long) retorno = […]
it returns -1.