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.
 

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!!!!
 

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]
 

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.
 

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 )
 

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 "
 
Top