B4J Question Updating MS SQL Server database with JDBC

Peekay

Peekay

Active Member
Licensed User
Longtime User
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
 
Sort by date Sort by votes
Peekay

Peekay

Active Member
Licensed User
Longtime User
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 &apos;animals&apos; 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 &apos;animals&apos; 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>
Click to expand...

PK
 
Upvote 0
Peekay

Peekay

Active Member
Licensed User
Longtime User
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 &apos;animals&apos; 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 &apos;animals&apos; 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!
Click to expand...



PK
 
Last edited:
Upvote 0
EnriqueGonzalez

EnriqueGonzalez

Expert
Licensed User
Longtime User
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?
Click to expand...
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?
Click to expand...
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!
Click to expand...
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 (?,?)
 
Upvote 0
Peekay

Peekay

Active Member
Licensed User
Longtime User
Enrique Gonzalez R said:
sql.insert_animal=INSERT INTO animals VALUES (?,?)
Click to expand...

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>
Click to expand...
 
Upvote 0
EnriqueGonzalez

EnriqueGonzalez

Expert
Licensed User
Longtime User
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" ))
 
Upvote 0
You must log in or register to reply here.

Similar Threads

P
  • Question
B4J Question How do I use the JRDC when the database and JRDC are on different servers？
Replies
6
Views
2K
pengbm
P
Peekay
  • Question
B4J Question JDRC serving different databases
Replies
2
Views
608
Erel
Erel
bjfhs
  • Question
B4J Question B4J and Database ACCESS save the data(solved)
Replies
4
Views
1K
bjfhs
bjfhs
J
  • Question
B4J Question How to handle multiple SQLite databases?
Replies
4
Views
1K
jtare
J
Y
  • Question
B4J Question Server SQL database
Replies
0
Views
1K
yiankos1
Y
Top