Database independence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database independence

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-02 : 02:25:57

I have a friend developing a product which is meant to be database independent.
They are using ado and odbc for the interface and seems to work quite well for access, sql server and oracle.
One problem they have is that the idetifier delimitter for oracle is "" whereas sql server and access it is [] so they are adding database dependency to get round it. It's going to be necessary at some point anyway.
For this they need to know which database they are pointing at.

They can get this from the connection custom properties.
For access and sql server it is in "DBMS Name" and "DBMS Version"
Anyone know what it is and the value for Oracle (mysql would be good too).
(They are developing against access and sql server and go to a client site for Oracle access so it's periodic)
Hoping it's "DBMS Name" value "Oracle"
Here's some code in VB to get the values I am looking for

Dim DBCon As adodb.Connection

Set DBCon = New adodb.Connection
DBCon.ConnectionString = "Provider=msdasql;data source=oracleodbc"
DBCon.CursorLocation = adUseClient
DBCon.Open
Dim i As Integer
For i = 0 To DBCon.Properties.Count - 1
debug.print i & " - " & DBCon.Properties(i).Name & " - " & DBCon.Properties(i).Value
Next

They are only allowing queries on a single table with a filter (and clauses only)
Basically sending select * from tbl where ...
They get the tables from openschema

Set objRs = DBCon.OpenSchema("adSchemaTables")

Anyone know of a better way to achieve independence?

I am doing a stored proc interface for them - getting the paramters and resultset format.
Looks quite easy - the schema gives the parameters and have to call the SP to get the resultset format.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-03 : 11:01:38

You're kidding....right?

Don't forget DB2 OS/390....double quotes as well....

I mean Oracle 8i should be generally problematic with a generic approach...outter joins and all...

What about versions?

Even ealrier versions on DB2 client server where feature poor...

What about syntaxes like GetDate(), Date() or CURRENT TIMESTAMP?

It's not April 1st....is it?

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 11:09:57

As I said they are just allowing
select * from tbl where ...
No joins and this is very limitted too as it is controlled by a graphical interface.
They can limit it to the syntax that is common between databases that they support i.e. those tat the big clients require (access, sql server and Oracle at the moment).
Any differences can be handled by overloading methods when needed.

The approach will work because they can just say that anything which doesn't work isn't allowed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-03 : 11:31:28

I don't know about mySQL...

But DB2


SELECT "LAST_NAME" FROM "PERS_TELE_ALL" 
WHERE "LAST_NAME" = 'KAISER'

And SQL Server


SELECT "OrderId" FROM "Order Details"
WHERE "OrderId" = '10285'

Both work...

Access is a problem though....

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 11:59:39

That depends on the settings for sql server.
It should be

SELECT [OrderId] FROM [Order Details]
WHERE [OrderId] = '10285'

But identifiers aren't a problem - they are defining the delimitters per database - they just need to be able to identify the database.
If you could see how the database is identified in the ado connection properties collection for any databases other than sql server and access that would be good - or better post all the properties collection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources