Android Question How to deal with Jdbc Resultset with more that one recordset

[Fayez Boulos]

Hello,
I am trying to call a stored procedure in SQL server database, and the stored procedure returns more than one table

Like
select * from table1
select * from table2
select * from table...n

When the resultset returns It looks like having only the first table in the Crsr object.
What should I do if I have more than one table (E.g. Passenger, and His list of payments).

I had to split what I want into 2 different calls each returns one table / query... but I feel it is not efficient. Here is my example Code (It is just trials and not having complete meaning).
Also I have another question that could be related: Is there a way to do the calls to the database as synchronous code (with all possible blocking risks)? and how...If you want me to post that separately, please let me know.
Your help is greatly appreciated. Thanks

Sub Connect As ResumableSub
    RemoteSQL.InitializeAsync("RemoteSQL", driver, jdbcUrl, Username, Password)
    Wait For RemoteSQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub
Sub CloseConnection
    RemoteSQL.Close
End Sub
Sub GetPassenger As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
    Try
        Dim sf As Object = RemoteSQL.ExecQueryAsync("RemoteSQL", "exec up_read_passenger ?", Array(19567))    
        Wait For (sf) RemoteSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
        If Success Then
            Do While Crsr.NextRow
                lblWelcome.text=Crsr.GetString("last_name") & ", " & Crsr.GetString("first_name")                   
            Loop
            Crsr.Close
        sf=RemoteSQL.ExecQueryAsync("RemoteSQL", "exec up_read_package ?", Array(134))
        Wait For (sf) RemoteSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            Do While Crsr.NextRow
                txtPackage.text=Crsr.GetString("package_name")
            Loop
            Crsr.Close               
        End If
    Catch
        Success = False
        Log(LastException)
    End Try
    CloseConnection
    End If
    Return Success
End Sub

 

[OliverA]

If you want a passenger and his list of payments, why are you not creating a SQL statement that just produces that? BTW, according to this link (https://stackoverflow.com/questions...ored-procedure-which-has-multiple-result-sets) ADO.NET is the only environment that gives you what you describe, even T-SQL just creates a union of all tables involved.

 

[Fayez Boulos]

Oliver,
What is wrong in creating multi-tables resultset in return of a call to a stored procedure? it is not special to .Net, and what do you mean by "even T-SQL just creates a union of all tables involved" This is not a UNION It is one passenger record, and N payments OR an invoice and many line items. Union is between selects having same number/type/order of columns of many selects....

However, if this is what can be done using the Jdbc, I am OK with that, just asking. Thanks Oliver
Regards,

 

[OliverA]

Check this out (https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/). I have no experience with this library so you will have to run some of your own tests to see if it can provide the results you are looking for.

Update: Reading through the first post of that link, the library does support multiple result sets.

 

[Fayez Boulos]

Thanks OliverA,
I will try understanding the examples in the provided link...Give me a day or so to do that... My main problem is that I do not know where to get the libraries from... I will try. Thanks again

 

[OliverA]

The library are located in the "SQLCallPRoc Version 1-10.zip" file located in the first post of the link. Download it and unpack it. It will contain two files (SQLCallProc.jar and SQLCallProc.xml). Copy these two files to your Additional Libraries folder. That folder is set up in the IDE under Tools->Configure Paths. Ok, this is the Android forum, so I'm guessing your using B4A and you are using the JdbcSQL library (https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/)? If not, this will not work. On top of needing to use the JdbcSQL library (again - I'm talking about B4A), you would need to use Microsoft's JDBC driver(s) to connect to your MSSQL server. If that is what you are doing, great!!! In the past, the MS JDBC drivers did not work with Android, so if you did get it running it would be good if you would do a post about it on the forums. Several people have asked about this and I'm not aware of any posted successes yet. Most people use the jTDS drivers (http://jtds.sourceforge.net/) and with that driver, the SQLCallProc library will not work. Once more, any success you have with your endeavors, be it successfully connection to MSSQL from Android via the JdbcSQL library and any of Microsoft's JDBC drivers are worth a post on this forum. On top of that, if you are successful in using SQLCallProc with this, that is worth another post (under Tutorials and Examples - https://www.b4x.com/android/forum/forums/tutorials-examples.27/). Good luck and keep us posted!

 

[Fayez Boulos]

Hi Oliver,
I had some troubles of missing libraries, and issues that I could not resolve, however, I managed using the code that I picked up with your help. And I believe as you indicated it could be useful for some of the forum friends.
Attached is a fully working App (Yes for B4A), and it is with your help my first trial to create some code for mobile.

Database

• The application is using a database called mobileSurehajj (You can change it as the value of Starter.database).
• User ID and Password are Fake (Change them as values of Starter.UserName, Starter.Password).
• The application is using a real table (User_login), and 2 tables created for demo purpose (Invoice, line_item).
• 2 Stored procedures are created up_create_invoice and up_create_lineitem
• The user should be granted permissions to Read/Write, and execute the 2 stored procedures
• A full script to create the tables and stored procedures is Here

/****** Object:  Table [dbo].[Login_user]    Script Date: 21/02/2019 11:58:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Login_user](
    [login_user_id] [int] IDENTITY(1,1) NOT NULL,
    [Login_ID] [int] NOT NULL,
    [Login_Name] [varchar](100) NOT NULL,
    [Login_FullName] [varchar](200) NOT NULL,
    [company_name] [varchar](100) NOT NULL,
    [location_alt] [bigint] NULL,
    [location_lon] [bigint] NULL,
    [location_latestTime] [datetime] NULL,
    [login_lastDatetime] [datetime] NULL,
 CONSTRAINT [PK_Login_user] PRIMARY KEY CLUSTERED 
(
    [login_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Invoice
    (
    invoice_id int NOT NULL IDENTITY (1, 1),
    invoice_company varchar(100) NOT NULL,
    invoice_customer varchar(100) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Invoice ADD CONSTRAINT
    PK_Invoice PRIMARY KEY CLUSTERED 
    (
    invoice_id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Invoice SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.line_item
    (
    line_item_id int NOT NULL IDENTITY (1, 1),
    invoice_id int NOT NULL,
    line_item_name varchar(100) NOT NULL,
    line_item_qty int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.line_item ADD CONSTRAINT
    PK_line_item PRIMARY KEY CLUSTERED 
    (
    line_item_id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.line_item SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE up_createInvoice 
    -- Add the parameters for the stored procedure here
    @InvoiceCompany varchar(100)
    ,@invoiceCustomer varchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    insert into invoice(invoice_company, invoice_customer) values (@invoicecompany, @invoicecustomer)
    select max(invoice_id) as theID from invoice;
END
GO
CREATE PROCEDURE up_create_lineitem 
    -- Add the parameters for the stored procedure here
    @Invoiceid int
    ,@lineItemName varchar(100)
    ,@lineItemQty    int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    insert into line_item(invoice_id, line_item_name, line_item_qty) values (@Invoiceid, @lineItemName, @lineItemQty)
END
GO

The used Libraries

• #AdditionalJar: jtds-1.3.1
  
• jdbcSQL

The code

The code is fully tested, and Has enough comments everywhere. Please notice the following:

• There is a button for login which execute "select statement against user_login table"
• There is another button to create an invoice with its related many line items. This uses both SQL transaction and stored procedures.
• To avoid the limitation on the number of parameters, I am suggesting to use customer structures (Or whatever that is called in B4A) like what I indicated for the invoice, and the line items... If you have a better way, please let me know.
• The full project export is attached. It is only missing the jdts 1.3.1.jar (which is attached separately).

Enjoy