B4J Question Updating MS SQL Server database with JDBC

[Peekay]

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]

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

 

[Peekay]

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]

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]

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]

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]

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]

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:

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

PK

 

[EnriqueGonzalez]

Hi peekay.

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]

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]

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

You mean the links to the databases? right.

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

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.

'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

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

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

i have a sweet tooth actually!

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]

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]

hi Peekay!

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

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]

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:

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

Here is the (almost) full 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]

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]

That was the error.
Thanks Enrique.

 

[Peekay]

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]

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

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

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

 

[Peekay]

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:

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:

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 &apos;,&apos;.</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near &apos;,&apos;.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

 

[EnriqueGonzalez]

to correct this error:

Error message:

you need to adjust this query:

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

to SQL SERVER:

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.