ORACLE "Returning ... Into" with SQLCommandMBS

Databases
1

I need the ID of the record I just entered

version: ORACLE 11g

I created the T1 table with 2 columns:
C1 number (value automatically generated by insert command with trigger and sequence)
C2 char(1)
//

my code:

var con as SQLConnectionMBS = …
var sql as string = “INSERT INTO T1 VALUES (‘A’) RETURNING C1 INTO :Id”
var cmd As New SQLCommandMBS (con,sql)

var RetId As Integer = cmd.Param(“Id”).asInteger
cmd.Execute

the result is:
RetId = 0
Id = Nil

nothing changes before or after cmd.Commit

I also tried:
cmd.Execute
var RetId As Integer = cmd.Param(“Id”).asInteger
but again nothing changes

Can @Christian_Schmitz help with this?

thank you.

2

Please do

  1. Execute
  2. FetchNext
  3. Read parameter

that should work in that order.

3

with or without “Returning … Into,” into the sql command, the instruction
Var b As Boolean = cmd.FetchNext
generates this SqlErrorExceptionMBS:
ORA-24374: define not done before fetch or execute and fetch

4

Okay, leave away the FetchNext.

Does the param “RETURN_VALUE” exist with the value?

You could inspect parameters dictionary if you like.

5

Var con As SQLConnectionMBS = Session.db
Var sql As String = " INSERT INTO T1 (c2) VALUES (‘c’) RETURNING Id INTO :RetId "
Var cmd As New SQLCommandMBS (con,sql)
cmd.Execute

the parameters dictionary before and after Execute:
image

6

sorry, I am still writing on this topic:
@Christian_Schmitz or others who use the MBS plugin with Oracle have any suggestions?
thank you.

7

I don’t know anything about Oracle, but this “:RetId” looks fishy to me. Have you tried omitting the colon?
And: Check your Oracle log files, maybe they contain more information about the exact nature of the problem.

8

Looking at the documentation here, it should just work:

Not sure why it fails. And I have no oracle sever here to try.

I wonder for SQLParamMBS whether the parameter is maybe not marked as output parameter?
Can you query the SQLParamMBS object using Param(“RetId”) in SQLCommandMBS class and then check if IsOutput or IsInput is set?
Maybe change the type before the Execute to fix?

p.type = SQLParamMBS.kParamDirTypeOutput
9

thank you for your answers.

it is possible something in the SqlAPI++ documentation could help me, but I have not found it.

I tried it this way:

Var con As SQLConnectionMBS = Session.db
Var sql As String = " INSERT INTO T1 (c2) VALUES (‘c’) RETURNING Id INTO :return_value "
Var cmd As New SQLCommandMBS (con,sql)
cmd.Param(“return_value”).type = SQLParamMBS.kParamDirTypeOutput
Var b1 As Boolean = cmd.Param(“return_value”).IsInput
Var b2 As Boolean = cmd.Param(“return_value”).IsOutput
Var i1 As Integer = cmd.Param(“return_value”).DirType
Var i2 As Integer = cmd.Param(“return_value”).Type
cmd.Execute

b2 remains False (why?)

image
image794×282 17.7 KB

return_value remains Nil

image
image1244×112 5.34 KB

With this additional information, can you give me more suggestions?

10

After consulting even more carefully the documentation of the SQLParamMBS class and after many attempts, it finally works.

This is the modified code:

cmd.Param(“return_value”).setAsLong(-1)
cmd.Param(“return_value”).Type = SQLParamMBS.kDataTypeLong
cmd.Param(“return_value”).DirType = SQLParamMBS.kParamDirTypeOutput

only 3 lines, but a lot of effort.

thanks for the help.

1 Like
11

Great. So you need to declare it as output parameter.