B4J Question Calling a MySQL stored procedure.

JakeBullet70

Well-Known Member
Licensed User
Longtime 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
Longtime 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

B4JExplorer

Active Member
Licensed User
Longtime 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
Longtime 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
Longtime 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
Longtime User
This is the SELECT/DUAL statement, normally used, which works:

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