Android Question Calling Procedures from B4A

demonio_jack · Mar 20, 2020

I have reviewed several posts to understand how to call a stored procedure in MySQL and I can't get it. I am grateful to those who have answered me with "links", however, and due to the little experience I have with B4A, I ask again for help from an expert to guide me through the steps to achieve this. I understand that it can be basic for most, but I have seen multiple solutions and almost all take many things for granted, such as the libraries and references to use.
I have read and tested this link several times

"https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/"

in which the procedure that does it appears, however the library or version used is not clear to me.
Could some of you share a brief example of calling a procedure with 2 input and 1 output parameters?
This is part of the code I am using:

Sub connect1 As ResumableSub
mysql.InitializeAsync("mysql", driver, jdbcUrl, Username, Password)
Wait For mysql_ready (Success As Boolean)
If Success = False Then
Log("Check unfiltered logs for JDBC errors.")
End If
Return Success
End Sub

Sub ValidarCuenta1 As ResumableSub
Wait For (connect1) Complete (Success As Boolean)
If Success Then
Try
Dim cur As Object = mysql.ExecQueryAsync("mysql","Select * from creadores where idcreador='" & Main.gUsuario & "' And password='" & Main.gPassword & "'",Null) 'general.Encriptar(txtPassword.Text.trim) & "'")
Wait For (cur) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
If Success Then
Return Crsr
End If
Catch
Return Crsr
Log(LastException)
End Try
CloseConnection
End If
Return Null
End Sub

Sub ExecQuerySQL As ResumableSub
Wait For (connect1) Complete (Success As Boolean)
If Success Then
Try
Dim cur As Object = mysql.ExecQueryAsync("mysql",Main.gsql,Null)
Wait For (cur) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
If Success Then
Return Crsr
End If
Catch
Return Crsr
Log(LastException)
End Try
CloseConnection
End If
Return Null
End Sub

The calls to SELECT, INSERT, UPDATE and DELETE work perfectly ... but the calls to procedures are breaking my head. ;-(

References used:

Thanks again.

MarcoRome · Mar 20, 2020

Look this:
https://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content ( Where you have library and Movie with example ) and this

SQL Tutorial

B4J jSQL library is similar to Basic4android SQL library. This tutorial will cover the differences between the two libraries. You can read more about the library methods here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content jSQL library is not tied to any specific SQL...

www.b4x.com

demonio_jack · Mar 20, 2020

It finally works !!!
Thank you very much for your patience. !!!!

OliverA · Mar 21, 2020

So what was the solution? Many people look for examples on calling procedures with in and out parameters. The more successful posts are available, the easier it would be to find them.

Nokia · Mar 21, 2020

this may be a bit lazy but I do mine like this:

B4X:

Dim s as string

        s = "Declare @i int, @s varchar(max) " &  _
                "EXECUTE [dbo].[ng_update_person] " & _
                  "@i output" & _
                  ",@s output" & _
                  ",'" & sIDToUse & "'" & _
                  ",0" & _
                  ",NULL" & _
                  ",'" & sLastN & "'" & _
                  ",'" & sMidN & "'" & _
                  ",'" & sFirstN & "'" & _
                  ",'~'" & _
                  ",'" & sAdd1 & "'" & _
                  ",'" & sAdd1_2 & "'" & _
                  ",'" & sCity1 & "'" & _
                  ",'" & sState1 & "'" & _
                  ",'" & sZip1 & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sPhone1 & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sPhone1 & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sDOB & "'" & _
                  ",'" & sSex & "'" & _
                  ",'" & sSSN & "'" & _
                  ",'" & sMarried & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sRaceID & "'" & _
                  ",'" & sPrefLanguageID & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sRace & "'" & _
                  ",'" & sPrefLanguage & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sEmailAdd & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sEntID & "'" & _
                  ",NULL" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'" & sEthinicity & "'" & _
                  ",'" & sEthinicityID & "'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",'~'" & _
                  ",NULL" & _
                  ",NULL" & _
                  " " & _
                  "Select @i as [cd], @s as [des]"

MarcoRome · Mar 21, 2020

OliverA said:
So what was the solution? Many people look for examples on calling procedures with in and out parameters. The more successful posts are available, the easier it would be to find them.

Hi Oliver you can see also this:

Stored Procedure with Parameter IN / OUT

Hi all. I have seen in several threads that they wondered how to perform and receive the results from stored procedures with IN / OUT parameters There may be a procedure to call where we need to pass values and return a set (IN) or a procedure that returns a result (OUT) Example Procedure IN...

www.b4x.com

Android Question Calling Procedures from B4A

demonio_jack

Member

MarcoRome

Expert

SQL Tutorial

demonio_jack

Member

OliverA

Expert

Nokia

Active Member

MarcoRome

Expert

Stored Procedure with Parameter IN / OUT

Similar Threads

Android Question Calling Procedures from B4A

Member

Expert

Member

Expert

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency