B4J Question Updating MS SQL Server database with JDBC

Peekay · Jun 15, 2020

This Link does shed some light on handling multiple databases.
However, I need to understand it more fully.

I only compile one jRDC2 object, in which the config.properties file is embedded, so naturally I need to specify both the database links in that file or I need to make two config.properties file?
I also understand that I need two database names in one or more of these files with two different ports, and sometimes two different usernames and passwords.
How would I do it?

Thanks
PK

jahswant · Jun 15, 2020

To avoid headache ? do run multiple instances of jRDC2 on different ports : 17178 , 17179 , 17180 etc. Create different queries for different databases.

Peekay · Jun 16, 2020

jahswani,

I had thought that a good idea, but how do I run two connectors with the same name on the same server?
The jRDC2 example project makes a connector always with the same name jRDC.jar, or can I change that - and how does the Driver and Url change in the config,.properties file?

PK

jahswant · Jun 16, 2020

Just change the connection strings and also rename the project accordingly to your needs then you can java -jar jRDCData1.jar ,java -jar jRDCData2.jar , java -jar jRDCData3.jar

Peekay · Jun 16, 2020

jahswani,
Sorry for being uninitiated and a newbie.
Do I still make the jRDC.jar with only one file in the files folder named config.properties and then rename it with java -jar jRDCData1.jar. Does it know it must rename the jRDC.jar when I run that command.
After that I make a new config.properties file, recompile and then do the java -jar jRDCData2.jar command?

Thanks
PK

jahswant · Jun 16, 2020

Peekay said:
Sorry for being uninitiated and a newbie.
Do I still make the jRDC.jar with only one file in the files folder named config.properties and then rename it with java -jar jRDCData1.jar. Does it know it must rename the jRDC.jar when I run that command.

No you will need to create multiple projects for your multiple databases and rename the projects accordingly.

EnriqueGonzalez · Jun 16, 2020

Hi peekay.
Don't over complicate yourself with more than one project.

A few days ago Erel publicated a jrdc multi database

JRDC2 multiple database

Does anybody have some running JRDC2 code that does this? I've been trying to adjust the code without luck. The client application connects to a central database (database 1). The result from the query is the name of the database and the location of the database on the server (full path + name...

www.b4x.com

In my opinion. This is not the exact solution you are looking for but the idea that you can modify jrdc to suit your needs stands.

Another concept that may help you is that... If you are using sql server and all the databases are in the same instance you can call them in different queries with the same connector.

Select * from database1.dbo.tsble1
Select * from database2.dbo.table2

This is without modifying jrdc.

Peekay · Jun 20, 2020

Enrique,

I am working on only one instance of SQL Server, so the name and password can stay the same.

Select * from database1.dbo.tsble1
Select * from database2.dbo.table2

My understanding of the above that I have to put these into the config.properties file, just with the appropriate naming of the queries in that file, like in:

Query:

sql.select_employee=database1.SELECT name, id FROM employees WHERE id=1

PK

EnriqueGonzalez · Jun 20, 2020

Hi peekay.

Peekay said:
My understanding of the above that I have to put these into the config.properties file,

Your understanding is correct. You have access to the whole database.

Once you get acostumed to jrdc, if more than one database shares the database sql you can modify jrdc to receive not only the query but to database too.

Ps. thanks for the token! Really appreciated

Peekay · Jun 20, 2020

Thanks Enrique,
Can you perhaps show me how I do the links in the config.properties file for these. Say I have two databases in the same instance named database1 and database2.
I presume the driver link stays the same.
And how do I address these in the B4A globals and command calls.
p.s. You did not tell me whether you took sugar - and I love mariachi music.

Thanks
PK

EnriqueGonzalez · Jun 20, 2020

Peekay said:
Can you perhaps show me how I do the links in the config.properties file

You mean the links to the databases? right.

B4X:

sql.query_database1=SELECT name, id FROM database1.dbo.employees WHERE id=?
sql.query_database2= SELECT name, id FROM database2.dbo.employees WHERE id=?

Peekay said:
I presume the driver link stays the same.

Yes, i like to make the Master database as the default database, but it could be any, if your user has permissions to the database you will be able to use it.

When on Android you will be using DBCommand.

B4X:

'database1
Dim cmd As DBCommand = CreateCommand("query_database1", Array(5))

'database2
Dim cmd As DBCommand = CreateCommand("query_database2", Array(5))

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
   Dim cmd As DBCommand
   cmd.Initialize
   cmd.Name = Name
   If Parameters <> Null Then cmd.Parameters = Parameters
   Return cmd
End Sub

Peekay said:
And how do I address these in the B4A globals and command calls.

you will have to manage a bit your B4A project around the notion of JRDC but just follow along the instructions here:

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

RDC is a middleware server that makes it simple to safely connect clients and remote SQL database servers. jRDC2 is the latest version. All new projects should use this version. jRDC2 is made of two components: - B4J server. The server receives the requests from the clients, issues the SQL...

www.b4x.com

Peekay said:
p.s. You did not tell me whether you took sugar

i have a sweet tooth actually!

Peekay said:
and I love mariachi music

i enjoy it, every now and then.

Prepare a project of B4A and if you get stuck, ask again, no problem.

Peekay · Jun 22, 2020

I still cannot see anything in the config.properties file which code line line tells the query to which database it must go.
I have about ten databases under one Sql Server instance.
In the config.properties file I might have six URL's, each pointing to a different database, but how do I apply these URL's in my program to direct the query to the correct database, and how do I set the ports in the config.properties file differently for each database?

PK

EnriqueGonzalez · Jun 22, 2020

hi Peekay!

Peekay said:
I still cannot see anything in the config.properties file which code line line tells the query to which database it must go.

Peekay said:
I am working on only one instance of SQL Server

Because you are working on one instance of SQl Server then the config.properties connectionString doesnt need to be changed, you can leave it as is.

Where you must write the database that you want to use is in the Query itself.

sql.query_database1=SELECT name, id FROM database1.dbo.employees WHERE id=?

This wil work if
1. Indeed your databases are in one instance of Sql Server
2. You dont have 100 databases on that instance and all of them are the same. because you will need to multiply all your queries by 100.

if that is so, then is better to use another approach like JRDC Multi that Erel released a couple of days ago. Even when using that other approach you could simplify a bit if your databases are in one instance.

Peekay · Jun 26, 2020

Enrique Gonzalez R said:
Prepare a project of B4A and if you get stuck, ask again, no problem.

I still cannot get the test code right. It has a problem with nulls in one or both lines below:

testlines:

Dim cmd As DBCommand = CreateCommand("sql.create_table",Null)
    Dim J As HttpJob = CreateRequest.ExecuteBatch(Array(cmd),Null)

Here is the (almost) full code:

Test code:

Sub CreateRequest As DBRequestManager
    Dim req As DBRequestManager
    req.Initialize(Me, rdcLink)
    Return req
End Sub

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = Name
    If Parameters <> Null Then cmd.Parameters = Parameters
    Return cmd
End Sub

Sub GetRecord (id As Int)
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("select_animal", Array(id))
    Wait For (req.ExecuteQuery(cmd, 0, id)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        'work with result
        req.PrintTable(res)
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
End Sub

Sub InsertRecord (Name As String)
    DateTime.DateFormat="dd-MMM-yy HH:mm"
    Dim cmd As DBCommand = CreateCommand("sql.sendmessagefromsite",Array(DateTime.Now, "Peka", edtMessage.text))
    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 btnQuery8_Click
    Dim cmd As DBCommand = CreateCommand("sql.create_table",Null)
    Dim J As HttpJob = CreateRequest.ExecuteBatch(Array(cmd),Null)
    Wait for(J) jobdone(j As HttpJob)
    If J.Success Then
        Log("Table created successfully!")
    End If
    J.release
End Sub

Thanks
PK

EnriqueGonzalez · Jun 26, 2020

Peekay said:
Dim cmd As DBCommand = CreateCommand("sql.create_table",Null)

i think you are almost there, on this line you dont have to write the "sql." part. (on any command, that part is added automatically by the server)

If that is not the error, please post the error you are getting on any of both sides, server or client.

Peekay · Jun 26, 2020

That was the error.
Thanks Enrique.

Peekay · Jun 26, 2020

Can you please check this error for me for inserting:

*** Command not found: sql.insert_animal
(SQLServerException) com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
(IllegalStateException) java.lang.IllegalStateException: WRITER
Command: , took: 2ms, client=192.168.1.63

EnriqueGonzalez · Jun 26, 2020

Peekay said:
Can you please check this error for me for inserting:

No problem, this error usually happens when you have a different number of parameters than question marks on the query.

if you have

B4X:

SELECT * FROM table WHERE column = 1 ' no question marks

and you are actually passing a non empty list that issue will appear.

Peekay · Jun 26, 2020

I am sorry, I have changed so many things and lost track. Can I start over again one by one.

Creating the table:
config.properties file:

Config file:

sql.create_table=CREATE TABLE animals (id INTEGER PRIMARY KEY AUTO_INCREMENT,name CHAR(30) NOT NULL,image BLOB) ;
sql.insert_animal=INSERT INTO animals VALUES (null, ?,?) ;
sql.select_animal=SELECT name, image, id FROM animals WHERE id = ?;

Query code:

Create table code:

Sub btnQuery8_Click
    Dim cmd As DBCommand = CreateCommand("create_table",Null)
    Dim J As HttpJob = CreateRequest.ExecuteBatch(Array(cmd),Null)
    Wait for(J) jobdone(j As HttpJob)
    If J.Success Then
        Log("Table created successfully!")
    End If
    J.release
End Sub

Error message:

ResponseError. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','., Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

EnriqueGonzalez · Jun 26, 2020

to correct this error:

Peekay said:
Error message:

you need to adjust this query:

B4X:

sql.create_table=CREATE TABLE animals (id INTEGER PRIMARY KEY AUTO_INCREMENT,name CHAR(30) NOT NULL,image BLOB) ;

to SQL SERVER:

B4X:

sql.create_table=CREATE TABLE animals (id int identity(1,1),name nvarchar(30) NOT NULL, VARBINARY(MAX) )

Check if i am right with the syntax, then keep going.

B4J Question Updating MS SQL Server database with JDBC

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency