File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql script works in mssql but not in mysql

 
john mattucci
Ranch Hand
Posts: 331
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33671
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
john mattucci
Ranch Hand
Posts: 331
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I realized my mistake in mssql the table names are not case sensitive but in mysql they are
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33671
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John,
Good to know. I'm surprised because db2 and Oracle aren't case sensitive either.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic