Unable to Connect to MsSql External Database

[darcudarcu]

Unable to connect ThingWorx 8.3.0-b349 to MS SQL external Database. I use the extension pointed by the various tutorials about, then I create a thing using the ThingTemplate MsSQL server. I've tried to change different parameters for the configuration tab. In the application log i receive the messages :

[message: Entity Not Found : [JdbcCreator]]
[message: Entity Not Found : [MySqlServer]] (note that I use the Template and parameters for Ms SQl not MySql)
[message: Unable To Convert From java.lang.String to NUMBER]

I verified that in the Tomcat folder lib there is the MsSql.jar file.
I used a similar procedure for Postgres Database and works, but I keep failing with MsSql.

[vangne]

Hi @darcudarcu 

You may refer this community post External-database-connection-guide-for-ThingWorx-v8 for more information.

Also, please check these two articles.

1. CS243088
2. CS310222

Thanks,

Vibhuti

[darcudarcu]

Thanks, 

I can connect now.

However, in my DataBase I have two tables, I can query one table successfully but querying the other one gives me the error : Connection Closed.

Commands not related to a specific table (such as getdate()) work. The query is corrected because is tested  and copied and pasted from the database. 

The two tables have the same connection properties.

[vangne]

@darcudarcu 

Thanks for letting know that the connection issue is resolved.

Could you please provide the exact error/screenshot about the querying error?

[darcudarcu]

@vangne 

This is the error, note that querying another table of the same database it works fine.

[vangne · Accepted solution]

Hi @darcudarcu 

I found these 2 articles which might be helpful, please check.

1.CS297147

2. CS295944

Thanks,

Vibhuti

[darcudarcu]

Hi @vangne ,

the connection to the database works, the property "isConnected" is TRUE and the default line "GETDATE()" works. Also the query for "Table1" works, but I receive the error only with "Table2" belonging to the same database. 

[darcudarcu]

Hi @vangne ,

the problem was linked to a column data type not supported by thingworx ("sql_variant" for MS SQL).

To solve this problem I had to cast the value into a FLOAT.

Thanks for the support.