This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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
 
Similar Threads
Migrating from Ms Sql Server(T-SQL) to Oracle database 10g(PL-SQL)
Suggest me a best way to do this
changing from MSSQL to MySQL
Binary datatype in MS SQL via JDBC
How would you create a Content Server/Provider using J2EE