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

[catehortua]

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.

 

[Erel]

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]

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

 

[catehortua]

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]

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]

Tks Alex. I don't have web server.

Any suggestion or example using jRDC?

 

[aeric]

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]

@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]

@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]

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]

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]

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]

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:

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

 

[catehortua]

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.

'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]

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

Edit: The correct code should be

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]

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

 

[aeric]

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

 

[catehortua]

Learning