B4J Question Calling a MySQL stored procedure.

Discussion in 'B4J Questions' started by JakeBullet70, Mar 1, 2015.

  1. JakeBullet70

    JakeBullet70 Well-Known Member Licensed User

    Hi All.

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

    Thanks.
     
  2. Erel

    Erel Administrator Staff Member Licensed User

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

    Code:
    Dim cs As Object = sql1.CreateCallStatement("{call someMethod(?, ?)}"Array(value1, value2))
    Dim rs As ResultSet = sql1.ExecCall(cs)
    ...
    Consider it a beta version.
     
  3. JakeBullet70

    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!!!!
     
    MrKim and B4JExplorer like this.
  4. B4JExplorer

    B4JExplorer Active Member Licensed User

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

    Erel Administrator Staff Member Licensed User

    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.
     
    B4JExplorer and JakeBullet70 like this.
  6. JakeBullet70

    JakeBullet70 Well-Known Member Licensed User

    Very nice Erel. I was going to ask about that.
     
    B4JExplorer likes this.
  7. B4JExplorer

    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:


    Code:
    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]
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    The first column is 0 not 1.
     
  9. B4JExplorer

    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.
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  11. B4JExplorer

    B4JExplorer Active Member Licensed User

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

    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?



    Code:
    cs_Pidm = sql_Db.CreateCallStatement( "{call GB_COMMON.f_get_pidm( ? ) }"Array( s_StudentId ) )
    rs_Pidm = sql_Db.ExecCall( cs_Pidm )
     
  13. B4JExplorer

    B4JExplorer Active Member Licensed User

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

    Code:
    "SELECT GB_COMMON.f_get_pidm( " & "'" & sStudentId & "'" & " )" & " FROM DUAL "
     
  14. Erel

    Erel Administrator Staff Member Licensed User

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

    B4JExplorer Active Member Licensed User

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

    So, not sure why the error is occurring.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice