Android Question Connection to two MS SQL Database depending on the registered user

catehortua · Aug 30, 2023

Hello.

I need help with the following situation:

Context:
I have an initial screen that asks for a username and password. Depending on the user that enters I need to make the connection to database #1 or #2 or #3.
I connect to MS SQL Server using jRDC2 and a file containing the config properties whith this sentence:

Return File.ReadMap(File.DirAssets, "config.properties").

In this file is the ip, the name of the database and the port:
JdbcUrl=jdbc:jtds:sqlserver://xx.xx.xxx.xxx/BDxxxx
User=xxx-xx
Password=xx.yyyy.xx
ServerPort=#####

Ask:
Depending on the registered user, how can the database be dynamically assigned?
The IP and the ServerPort are the same. Just change the DB

Thank you very much and greetings.

aeric · Sep 6, 2023

Tell me if you don't understand what I wrote in post #13

Edit: The correct code should be

B4X:

Sub InsertRecord (Nombre As String, Salario As Int)
    Dim req As DBRequestManager = CreateRequest(UseDb)
    Dim cmd As DBCommand = CreateCommand("insert_employees", Array(Nombre, Salario))
    'Dim j As HttpJob = req.ExecuteBatch(Array(cmd), Null)
    Dim j As HttpJob = req.ExecuteCommand(cmd, Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    End If
    j.Release
End Sub

Erel · Aug 30, 2023

1. jRDC2 is a simple B4J server solution. You have full control over its behavior and it is quite simple to make it select the database using some parameter.
2. Example of modified jRDC2 that works with multiple dbs: https://www.b4x.com/android/forum/threads/jrdc2-multiple-database.112013/post-698561

catehortua · Aug 30, 2023

Thank you very much Erel for your quick and timely response. I proceed to review what you sent me.
Greetings

catehortua · Sep 3, 2023

Help me with something.
In the config.properties file I see that the DB can be sent as a parameter:

JdbcUrl=jdbc:mysql://localhost/$DB$?characterEncoding=utf8

When is the name of the database sent as a parameter? Is it when the SQL statement is sent?
I use this statement when I connect to a single DB:

Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("select_nit", Array(id))

Dim cadena As String
Dim separador As String
Dim longitud As Int

Wait For (req.ExecuteQuery(cmd, 0, id)) JobDone(j As HttpJob)
If j.Success Then ...

Tks

Alex_197 · Sep 3, 2023

Another solution - if you have a web server you can create eother php or asp.net page and pass some parameters and then based on these paremeters connect to the database accordingly

catehortua · Sep 5, 2023

Tks Alex. I don't have web server.

Any suggestion or example using jRDC?

aeric · Sep 5, 2023

Take a look here:

[Solved What do I need to modify on the client side for jRDC2 Multi to work?

Situation: I am currently developing an APP where I use jRDC2 (with their respective jRDC2.bas and DBRequestManager.bas in B4A) and it works PERFECT. But I need to connect with the same jRDC2 Server to multiple Databases. @Erel I published last year this version of jRDC2 Multi that supports...

www.b4x.com

Access database name from client parameter with JRDC ?

I have problems getting data from several databases in one host, for example : dbsample_01 dbsample_02 etc... how do i pass parameters to jrdc's config.properties? or there are special changes to be able to access between databases? how to get the data with the following query? select * from...

www.b4x.com

catehortua · Sep 5, 2023

@aeric, thank you so much. I was able to select the BDs with the links you sent me. I really appreciate your help.

Now I need a little help with the Insert and Update of the selected DB.
I used the following statements when the DB was predefined. Thanks in advance.

Sub InsertRecord (Nombre As String, Salario As Int)
Dim cmd As DBCommand = CreateCommand("insert_employees", Array(Nombre, Salario))
Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
Wait For(j) JobDone(j As HttpJob)
If j.Success Then
Log("Inserted successfully!")
End If
j.Release
End Sub

Sub DeleteRecord (Nombre As String)
Dim cmd As DBCommand = CreateCommand("delete_employees", Array(Nombre))
Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
Wait For(j) JobDone(j As HttpJob)
If j.Success Then
Log("Deleted successfully!")
End If
j.Release
End Sub

aeric · Sep 5, 2023

catehortua said:
@aeric, thank you so much. I was able to select the BDs with the links you sent me. I really appreciate your help.

Now I need a little help with the Insert and Update of the selected DB.
I used the following statements when the DB was predefined. Thanks in advance.

Sub InsertRecord (Nombre As String, Salario As Int)
Dim cmd As DBCommand = CreateCommand("insert_employees", Array(Nombre, Salario))
Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
Wait For(j) JobDone(j As HttpJob)
If j.Success Then
Log("Inserted successfully!")
End If
j.Release
End Sub

Sub DeleteRecord (Nombre As String)
Dim cmd As DBCommand = CreateCommand("delete_employees", Array(Nombre))
Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
Wait For(j) JobDone(j As HttpJob)
If j.Success Then
Log("Deleted successfully!")
End If
j.Release
End Sub

Please use code tags when posting your code.

Now, what is the problem with your code?

catehortua · Sep 5, 2023

Thanks for the suggestion. I'll keep it in mind.

It's just that I don't know where to include the name of the DB to which Insert and Update are going to be done.

If you can add the code to do the Insert and Update I appreciate it.

aeric · Sep 5, 2023

Have you studied the original jrdc2 project?
The queries are stored in the config.properties file
The command "insert_employee" is the key which map to the actual query.

catehortua · Sep 5, 2023

Yes. My config.properties have this *SQL* sentence.

But I have a problem with this instruction.

It is underlined with red indicating that a parenthesis is missing "(". I check the parentheses and I have two "(" and two ")".

aeric · Sep 5, 2023

catehortua said:
Yes. My config.properties have this *SQL* sentence.

But I have a problem with this instruction.

View attachment 145577

It is underlined with red indicating that a parenthesis is missing "(". I check the parentheses and I have two "(" and two ")".

Your code is wrong.
~~CreateRequest should be a function which follow by () not an object follow by.~~

Edit: The modified CreateRequest expects a parameter UseDb therefore it cannot be use without passing the value.
If you don't declare a req variable, you may write the code as:

B4X:

Dim j As HttpJob = CreateRequest(UseDb).ExecuteBatch(Array(cmd), Null)

catehortua · Sep 6, 2023

Hello @aeric.
Nothing. I'm stuck with the insert. I've browsed the forum and tried several alternatives and it still gives me an error in the CreateRequest.
Any clue what is wrong in the syntax?

I found these instructions but I keep getting the syntax error.

B4X:

'Test 1
    Dim req As DBRequestManager = CreateRequest(UseDb)
    Dim cmd As DBCommand = CreateCommand ("insert_employees", Array As Object (Nombre, Salario))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null) 'Sintax Error'
    Wait For (j) JobDone(j As HttpJob)

'Test 2
    Dim req As DBRequestManager = CreateRequest(UseDb)
    Dim cmd As DBCommand = CreateCommand ("insert_employees", Array(Nombre, Salario))
    Wait For (CreateRequest.ExecuteBatch(Array(cmd), Null)) JobDone(j As HttpJob) 'Sintax Error'

aeric · Sep 6, 2023

Tell me if you don't understand what I wrote in post #13

Edit: The correct code should be

B4X:

Sub InsertRecord (Nombre As String, Salario As Int)
    Dim req As DBRequestManager = CreateRequest(UseDb)
    Dim cmd As DBCommand = CreateCommand("insert_employees", Array(Nombre, Salario))
    'Dim j As HttpJob = req.ExecuteBatch(Array(cmd), Null)
    Dim j As HttpJob = req.ExecuteCommand(cmd, Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    End If
    j.Release
End Sub

catehortua · Sep 6, 2023

Thanks a lot. It worked perfectly!!
I really appreciate your help

aeric · Sep 6, 2023

You should have mark my post as answer, not your post.

catehortua · Sep 6, 2023

Learning

Android Question Connection to two MS SQL Database depending on the registered user

Member

B4X founder

Member

Member

Well-Known Member

Member

Expert

Member

Expert

Member

Expert

Member

Expert

Member

Expert

Member

Expert

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency