Android Question Updating SQL server database with JDBC.

Peekay · Jun 23, 2020

I have done the jRDC2 B4J project and it works on the browser.
I now need the routines in B4A to create, select, update and delete records.
I have downloaded DbUtils, but it seems to be a B4J module and it needs jSQL and iSQL which are apparently not available for B4A.
Is there a link where I can find these routines for B4A?

Thanks
PK

DonManfred · Jun 23, 2020

A JRDC2 is a B4J Project.
The jRDC2 can not run in android.

You can FOR SURE use a b4a client to connect to a jRDC2 server to fetch or send Data.

See the jRDC2 tutorials.

Peekay · Jun 23, 2020

DonManfred said:
You can FOR SURE use a b4a client to connect to a jRDC2 server to fetch or request Data.
See the jRDC2 tutorials.

DonManfred, that is what I asked for. I have gone through many of them but could not find a suitable one.

DonManfred · Jun 23, 2020

Peekay said:
DonManfred, that is what I asked for. I have gone through many of them but could not find a suitable one.

i do not believe that!

Go over

[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

again and follow the Client configuration carefully.

Peekay · Jun 23, 2020

I have this query in my config.properties file:

Insert record:

sql.sendmessagefromsite=INSERT INTO messagesfromsite (datetime, fromuserno, message) VALUES (?, ?, ?);

I have this routine to insert a record:

Insert routine:

Sub InsertRecord (Name As String)
    DateTime.DateFormat="dd-MMM-yy"
    Dim cmd As DBCommand = CreateCommand("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 btnQuery3_Click
    If edtMessage.Text="" Then
        ToastMessageShow("First enter the message",True)
        Return
    End If
    InsertRecord("sendmessagefromsite")
End Sub

No insert happens in the database. What does the 'Name' parameter in InsertRecord(Name ... refer to?

Response error:

Invalid object name 'messagesfromsite (The table name). HTTP error 500. SQLServerException: The index 1 is out of range (The id is an autoincrement field)

PK

DonManfred · Jun 23, 2020

Peekay said:
What does the 'Name' parameter in InsertRecord(Name ... refer to?

you are not using it in the sub so i would expect nowhere

Peekay said:
Response error:

As the error points out the 1st field in the db is an autoincrement value which you can not set.

Peekay said:
sql.sendmessagefromsite=INSERT INTO messagesfromsite (datetime, fromuserno, message) VALUES (?, ?, ?);

change it to, for ex.

B4X:

sql.sendmessagefromsite=INSERT INTO messagesfromsite SET field_date=?, fieldFromUserno=?, fieldmessage=?;

this way you explicitely specify the fields you want to use in an exact order (your parameters need them)

Based on the errormessage i would guess the field datetime is a field with autoincrement = true?

OliverA · Jun 23, 2020

In the jRDC2 server's config, are you connecting to the database that has the messagesfromsite table? Or are you connecting to the SQL server in such a way that you need to pre-pend the database name to the table name. So in the config.properties file, does

SQL:

sql.sendmessagefromsite=INSERT INTO messagesfromsite (datetime, fromuserno, message) VALUES (?, ?, ?);

need to be something like

SQL:

sql.sendmessagefromsite=INSERT INTO NAMEOFDATABASE.messagesfromsite (datetime, fromuserno, message) VALUES (?, ?, ?);

and with SQL server it may even be something like

SQL:

sql.sendmessagefromsite=INSERT INTO dbo.DATABASENAME.messagesfromsite (datetime, fromuserno, message) VALUES (?, ?, ?);

Peekay · Jun 23, 2020

DonManfred said:
change it to, for ex.

I do not understand this.

OliverA said:
In the jRDC2 server's config, are you connecting to the database that has the messagesfromsite table? Or are you connecting to the SQL server in such a way that you need to pre-pend the database name to the table name. So in the config.properties file, does

This is my URL in config.properties:

URL:

JDBCUrl=jdbc:sqlserver://192.168.1.10:1433;databaseName=IBMSphcc

I can append the database name if needs be.

I think DonManfred and OliverA are right or have valid points. The auto increment field is id, but I cannot put a ? and parameter for that. How do I handle it?
With this URL, do I need the prefix for the tables?

Thanks to both you guys. I appreciate it.

PK

DonManfred · Jun 23, 2020

Peekay said:
but I cannot put a ? and parameter for that.

correct. You can´t. Just do not use the autoincrement field in your fieldlist. The DB Engine will set it automatically for you.

Peekay · Jun 24, 2020

This is the query like it looks like now:

Query:

sql.sendmessagefromsite=INSERT INTO IBMSphcc.dbo.messagesfromsite (datetime, fromuserno, message) VALUES (?, ?, ?);

Here is the code error (It happens in HttpJob=Createrequest... in the routine above):

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

OliverA · Jun 24, 2020

The invalid object name error seems to indicate that the table messagesfromsite does not exist. So a) it does not exist, b) the spelling of the table is slightly off (technically same as a), or c) the user you are using to log in with has no access rights to the table and therefore can’t see the table (this is a guess, since MS SQL might throw a different error message for this case)

Peekay · Jun 24, 2020

Oliver, Thanks .. just a stupid mistake as always. I used the wrong field type.

?

Android Question Updating SQL server database with JDBC.

Peekay

Active Member

DonManfred

Expert

Peekay

Active Member

DonManfred

Expert

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

Peekay

Active Member

DonManfred

Expert

OliverA

Expert

Peekay

Active Member

DonManfred

Expert

Peekay

Active Member

OliverA

Expert

Peekay

Active Member

Similar Threads

Android Question Updating SQL server database with JDBC.

Active Member

Expert

Active Member

Expert

Active Member

Expert

Expert

Active Member

Expert

Active Member

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency