B4J Question Calling a MySQL stored procedure.

[JakeBullet70]

Hi All.

Can I call a MySQL sproc from b4j? Is this possible? My server is local

Thanks.

 

[Erel]

Please download the attached library (v1.30).
It includes two new methods:
- CreateCallStatement
- ExecCall

Dim cs As Object = sql1.CreateCallStatement("{call someMethod(?, ?)}", Array(value1, value2))
Dim rs As ResultSet = sql1.ExecCall(cs)
...

Consider it a beta version.

 

[JakeBullet70]

Thanks Erel. It is working fine. Would love to have an async version but for the moment it is working great.

Thanks again!!!!

 

[B4JExplorer]

Cool. I'll beta-test this too, thanks Erel.

 

[Erel]

Note that the reason for breaking the API into two methods is to allow you to configure the callable query before it is executed (with JavaObject). This is required if you want to support OUT parameters.

 

[JakeBullet70]

Very nice Erel. I was going to ask about that.

 

[B4JExplorer]

Erel,

Should we open a separate thread for questions about testing this jsql, or use this thread?

I'm getting an Invalid column index error, at the CreateCallStatement line:

Dim n_Pidm As Int
Dim cs_Pidm As Object
Dim rs_Pidm As ResultSet


cs_Pidm = sql_Db.CreateCallStatement( "GB_COMMON.f_get_pidm", Array( s_StudentId ) )
rs_Pidm = sql_Db.ExecCall( cs_Pidm )
n_Pidm = rs_Pidm.GetInt( 1 )

returns

java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5321)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8364)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9060)
at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4983)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:232)
at anywheresoftware.b4j.objects.SQL.CreateCallStatement(SQL.java:302)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:563)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:221)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:156)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:82)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.example.main.main(main.java:29)[/code]

 

[Erel]

The first column is 0 not 1.

 

[B4JExplorer]

Yes, you're right, thanks.

But that's not where the error occurs. It doesn't get to that point, because it crashes on the first statement.

 

[Erel]

You need to use syntax similar to the syntax I posted in post #2. There must be a question mark for each of the variables.

 

[B4JExplorer]

Including the braces and the call? Ok, will try, thanks.

 

[B4JExplorer]

Well, got a little further, thanks. But now there's an error on the second statement, the ExecCall. It says that f_get_pidm is not a procedure or is undefined.

Does the beta JSQL CreateCallStatement support procedures in other schemas?

cs_Pidm = sql_Db.CreateCallStatement( "{call GB_COMMON.f_get_pidm( ? ) }", Array( s_StudentId ) )
rs_Pidm = sql_Db.ExecCall( cs_Pidm )

 

[B4JExplorer]

This is the SELECT/DUAL statement, normally used, which works:

"SELECT GB_COMMON.f_get_pidm( " & "'" & sStudentId & "'" & " )" & " FROM DUAL "

 

[Erel]

Does the beta JSQL CreateCallStatement support procedures in other schemas?

I'm not an expert in this field so I'm not sure that I understand. Where was the stored procedure created?

 

[B4JExplorer]

I checked, and it looks like it's the same user.

So, not sure why the error is occurring.