Working with Oracle, SQL Server & mySQL

FarshadV · Post by **FarshadV** » Sun 08 Mar 2009 20:36

I am very new to this and I would hope that there is a simple answer. I have an application which must work with Oracle, MS SQL and my SQL. It appears that my SQL statements are not formatted to meet the provider's needs. For example, the following call:

Code: Select all

SELECT 
  unique_id FROM ta_util_conv_history 
WHERE 
  unique_id = 'test';

Will work for SQL Srver and mySQL but will not for Oracle. It has to be changed to:

Code: Select all

SELECT 
  "unique_id" FROM "ta_util_conv_history"
WHERE 
  "unique_id" = 'test'

Note all the double quotes and the removal of ";" atthe end of the SQL command. I would have thought that usinh uniDac coponents would do all of this for me in thebackground.

Please let me know what I am doing wrong.

Beast Regards,
Farshad R. Vossoughi

Plash · Post by **Plash** » Tue 10 Mar 2009 08:36

1. You should not add semicolon to the end of SQL statement. This works with all DBMS.

2. You have created the database table in Oracle incorrecly. By default Oracle uses identifiers in upper case. In CREATE TABLE statement you should not quote identifiers or use quoted identifiers in upper case. For example:

Code: Select all

CREATE TABLE ta_util_conv_history 
(
  unique_id INTEGER
)

or

Code: Select all

CREATE TABLE "TA_UTIL_CONV_HISTORY" 
(
  "UNIQUE_ID" INTEGER
)

FarshadV · Post by **FarshadV** » Tue 10 Mar 2009 12:52

Thank you very much.

It now works as expected.
Does the rule for not wrapping table and column names in double quotes apply to Oracle, MS SQL and mySQL?

Also, do you have any plans for supporting SQLITE?
The reason for the question is that in some cases, I have to deploy my application in an environment where the user does not have access to a DB Server and is not allowed to install such services on their system. I have looked at InterBase but it looks like it has to install several DLLs and specific folder structure.

Plash · Post by **Plash** » Wed 11 Mar 2009 08:34

Yes, this rule is used for all database servers.

Currently we have no plans for supporting SQLite.