Re: SQL Translator, this is something for the experts

Tommy Akre (tommy_akre_at_hotmail.com) wrote:
: Hi!
 

: I wish to implement support for Oracle database in my program. the program
: is ment to be database-independent and currently supports Microsoft Access,
: Microsoft SQL Server 6.5 and 7.0. The application is mighty big and consists
: of ALOT of complex and heavy SQL queries. The technique I use in order to
: query a database is to use ADODB through ODBC.
 

: My problem is this:
 

: My program is based on parameters (i.e, the individual users choose what to
: view from the database) and therefore use alot of dynamically built SQL's
: that the program pushes throuch ADODB/ODBC. These SQL's has been written to
: support SQL's that Access and MS Sql Server understands. ORACLE DOES
: NOT!!!!. I believe the problem to be located in the FROM part of the SQL's
: where its used alot of INNER JOIN/OUTER JOIN statements. As far as I can
: see/understand of Oracle syntax, the joins are written in the WHERE part of
: the SQL instead of FROM.

All the databases allow standard SQL format (some organization such as ISO defines the standard). If you use that standard then you won't have a problem.

Having said that, the non-standard features are often very useful, (personally I rather like using inner joins combined with UPDATE).

A standard SQL join uses the FROM and the WHERE.

ms access custom join (please forgive syntax errors, it's been a while since I used access)

	select * 
	from table1 inner join table2 on table1.id = table2.id
	where table1.name = 'FRED'

standard SQL, recognized by oracle and access, and other databases

	select * 
	from table1 , table2
	where table1.id = table2.id
	AND   table1.name = 'FRED'

--
Want to access the command line of your CGI account?  Need to debug your
installed CGI scripts?  Transfer and edit files right from your browser? 

What you need is "ispy.cgi" - visit http://nisoftware.com/ispy.cgi