aspose file tools*
The moose likes JDBC and the fly likes sql script works in mssql but not in mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql script works in mssql but not in mysql" Watch "sql script works in mssql but not in mysql" New topic
Author

sql script works in mssql but not in mysql

john mattucci
Ranch Hand

Joined: Nov 03, 2000
Posts: 331
The following

SELECT count(*) FROM itemlist WHERE ID like '%a%' or itemDescription like '%a%'

works in mssql but i get the error "Table 'xxx.itemlist' doesn't exist"
in mysql. How can I make this work
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30894
    
158

John,
Most databases (including mysql, Oracle, db2, ...) use an owner name for the schema. When you do a query, the database tries to run the query on the schema for the id running. There are two ways to execute the query:

1) Use a fully qualified name (with schema owner) in your query directly.
2) Create a synonym to map the fully qualified name to the one word table name.

I favor #1 because it allows you to run the query as different users without having to change the synonym/alias.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
john mattucci
Ranch Hand

Joined: Nov 03, 2000
Posts: 331
I realized my mistake in mssql the table names are not case sensitive but in mysql they are
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30894
    
158

John,
Good to know. I'm surprised because db2 and Oracle aren't case sensitive either.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql script works in mssql but not in mysql