Skip to main content
B
beck.smith1-Visitor
1-Visitor
October 21, 2015
Question

Reading and Writing to external MySQL database

  • October 21, 2015
  • 3 replies
  • 2726 views

Anybody else had these problems accessing MySQL database?

I've done a lot of research and can supply all the background detail but the problem is basically when building a Database Thing with a Query service.

Important Note: the MySQL database is up, healthy and reachable.  In fact with case #1 (below) when using the standard "Database" template I can actually see the Tables/Columns when editing the service and can issue DDL against the database change service type to SQL(command) such as 'create table x as y" .

Just can't issue any DML (select *  from table a) because of the bad syntax (OPTION) mentioned below.

Setting the "Max Rows" to 0 in the service description has no effect.

Any ideas or someone successfully selected from a MySQL database please advise.

Thanks,

Problem descriptions follow:

===================================================================================================

Note there are two different errors depending on the Database template used:

1. When I create a database Thing using the standard "Database" template, I get the following error executing my query service:

Error executing service

Unable to Invoke Service BSQueryTestTable on BSMySQLDatabaseTest : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=500' at line 1

2. When I create a database Thing using the mysql-connector-java-5137-binJDBCTemplate, I get the following error executing my query service:

Error executing service

Unable to Invoke Service BSQueryTestTable on BSMySQLDBTest : Cannot get a connection, pool error Unable to validate object

Thanks,

    3 replies

    B
    beck.smith1-VisitorAuthor
    1-Visitor
    October 21, 2015

    Note: In this case the Tomcat server had to be restarted by cloud services (See PTC case 12796347), so the problem was actually one of stale handle to and "old" JDBC configuration.  You must use the default "Database" template that comes with initial install (example 1 in the original post).

    to build a Database Thing and have installed the mysql-connector-java-5137-binJDBCTemplate before restart.

    (At least that's the order I performed to get it to work...)

    See PTC Case CS219958 for details of installing the JDBC Connector extension and then JDBC drivers.

    As of now, I have connectivity to an external MySQL database and can issue both DDL and DML.

    Thanks,

    T
    thingworxuser11-Garnet
    11-Garnet
    May 18, 2016

    I too had the same problem. I think it is problem with the MySQL server version. When I started including "limit 0,500" at the end of my Query it started working.

    T
    thingworxuser11-Garnet
    11-Garnet
    May 18, 2016

    If you replace MySQL server version it works.

    Terms & ConditionsAccessibility statement