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

Peekay · Jun 27, 2020

Three questions:
1. I see you do not put semi colons after the query code line. The example by Erel puts it behind some of the lines, but not all. What is correct?
2. I see you use nvarchar. Why not varchar?
3. The table was created successfully, thanks!

I get an error when I wish to insert a record with this code line:

Insert:

    Dim cmd As DBCommand = CreateCommand("insert_animal",Array("Dog",Null))

ResponseError. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'animals' can only be specified when a column list is used and IDENTITY_INSERT is ON., Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'animals' can only be specified when a column list is used and IDENTITY_INSERT is ON.</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'animals' can only be specified when a column list is used and IDENTITY_INSERT is ON.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

PK

Peekay · Jun 27, 2020

It says that I cannot insert unless the IDENTITY_INSERT is ON.
How can I do three queries (in one) like in putting identity insert on , insert record and then putting identity insert off?

I have tried this code:

Insert record:

Sub btnQuery9_Click
    Dim cmd As DBCommand = CreateCommand("setidentityon",Null)
    Dim J As HttpJob = CreateRequest.ExecuteBatch(Array(cmd),Null)
    Wait for(J) jobdone(j As HttpJob)
    If J.Success Then
        Log("Identity on!")
    End If
    J.release
    
    DateTime.DateFormat="dd-MMM-yy HH:mm"
    Dim cmd As DBCommand = CreateCommand("insert_animal",Array("Dog",Null))
    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
    
    Dim cmdoff As DBCommand = CreateCommand("setidentityoff",Null)
    Dim J As HttpJob = CreateRequest.ExecuteBatch(Array(cmdoff),Null)
    Wait for(J) jobdone(j As HttpJob)
    If J.Success Then   
        Log("Identity off!")
    End If
    J.release
End Sub

and it gives me this error:

Identity on!
ResponseError. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'animals' can only be specified when a column list is used and IDENTITY_INSERT is ON., Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'animals' can only be specified when a column list is used and IDENTITY_INSERT is ON.</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'animals' can only be specified when a column list is used and IDENTITY_INSERT is ON.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>
Identity off!

PK

EnriqueGonzalez · Jun 27, 2020

Hi peekay.

Peekay said:
I see you do not put semi colons after the query code line. The example by Erel puts it behind some of the lines, but not all. What is correct?

Mysql or mariadb requieres a semicolon by the end of each statement. Because Erel created that example for these databases he is using a semicolon.

Peekay said:
I see you use nvarchar. Why not varchar?

Mainly my main preference. When you work on an international environment it's better to go unicode,

Peekay said:
The table was created successfully, thanks!

Awesome.

That error happens when an identity column you try to add a value just change this line

sql.insert_animal=INSERT INTO animals VALUES (null, ?,?) ;

To

sql.insert_animal=INSERT INTO animals VALUES (?,?)

EnriqueGonzalez · Jun 27, 2020

Peekay said:
CreateCommand("setidentityoff",Null)

Don't use this part. First, it's wrong and second it is not needed

Peekay · Jun 27, 2020

Enrique Gonzalez R said:
sql.insert_animal=INSERT INTO animals VALUES (?,?)

It gives me this error (I think it reads "dog" as the ID field, now that the null is gone):

*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
ResponseError. Reason: java.net.SocketTimeoutException: failed to connect to /192.168.1.10 (port 17178) from /192.168.1.63 (port 54814) after 30000ms, Response:
ResponseError. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query., Response: <html>
<head>

<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

EnriqueGonzalez · Jun 27, 2020

No. It is because the varbinary doesn't like passing a null. Just to make this happen.

Try

sql.insert_animal=INSERT INTO animals VALUES (?, null)

And the create command do:
CreateCommand("insert_animal",Array("Dog" ))

Peekay · Jun 27, 2020

It Inserted and Selected successfully.
Thank you so much!!!

PK

EnriqueGonzalez · Jun 27, 2020

Peekay said:
It Inserted and Selected successfully.

That's awesome!

Peekay said:
Thank you so much!!!

My pleasure

And thanks for the double cappuccino.

B4J Question Updating MS SQL Server database with JDBC

Peekay

Active Member

Peekay

Active Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Peekay

Active Member

EnriqueGonzalez

Well-Known Member

Similar Threads

B4J Question Updating MS SQL Server database with JDBC

Active Member

Active Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency