cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - You can Bookmark boards, posts or articles that you'd like to access again easily! X

Reading and Writing to external MySQL database

beck.smith
1-Newbie

Reading and Writing to external MySQL database

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 3

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,

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.

Regards & Thanks,
Shiva

If you replace MySQL server version it works.

Regards & Thanks,
Shiva
Top Tags