B4J Question Calling a MySQL stored procedure.

JakeBullet70

Well-Known Member
Licensed User
Hi All.

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

Thanks.
 

Erel

B4X founder
Staff member
Licensed User
Please download the attached library (v1.30).
It includes two new methods:
- CreateCallStatement
- ExecCall

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

Consider it a beta version.
 
Upvote 0

JakeBullet70

Well-Known Member
Licensed User
Thanks Erel. It is working fine. Would love to have an async version but for the moment it is working great.

Thanks again!!!!
 
Upvote 0

B4JExplorer

Active Member
Licensed User
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:


B4X:
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]
 
Upvote 0

B4JExplorer

Active Member
Licensed User
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.
 
Upvote 0

B4JExplorer

Active Member
Licensed User
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?



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

B4JExplorer

Active Member
Licensed User
This is the SELECT/DUAL statement, normally used, which works:

B4X:
"SELECT GB_COMMON.f_get_pidm( " & "'" & sStudentId & "'" & " )" & " FROM DUAL "
 
Upvote 0
Top