World: r4wp

created this for general database discussion.

Thanks :)

(Continue from !Cheyenne)

Graham: it would be nice to write OpenDBX binding for Red/System 
too.

Not sure what to do about the lack of metadata support ...which is 
only really used when updating tables

why doesn't it support metadata ?   that's a strange limitation

http://comments.gmane.org/gmane.comp.db.opendbx.devel/417

dunno what the answer that Norbert gives means

on many database servers there is INFORMATION_SCHEMA adn it can be 
queried like a normal table.

so they think its better to keep it. 

so if you use MSSQL use sp_databases, or sp_tables; for mysql use 
SHOW TABLES etc..

or select * from INFORMATION_SCHEMA.tables for many of them (works 
on MSSQL 2005+, mysql, postgre, not sure about sqlite)

oh well, all obstacles removed then :)

I guess the odbc driver does this automatically

almost :) Oracle doesn't support it, I think. http://www.petefreitag.com/item/666.cfm

yes, ODBC does it. But I think it doesn't use INFORMATION_SCHEMA, 
the ODBC driver itself gets those information from db server.

Need to put this into the port abstraction then :(

Or better put it only for unsupported db server like Oracle.

Endo, ADO is a layer over ODBC. ODBC is closer to the db. People 
used ADO because it had an object-oriented API, useful for ActiveX 
languages. ODBC has a lower-level API that was more awkward for OO 
programs to use directly, so they used it through ADO.

OLEDB was sometimes as close to the db as ODBC, or was a layer over 
ODBC. Now, it's deprecated by MS, which is recommending that you 
use the faster, more standard ODBC instead.

OLEDB was never as fast as ODBC, but for maybe one version of SQL 
Server (2000, I think) it was promoted instead of ODBC. For every 
version before and since it has been better to use ODBC drivers. 
Starting with SQL 2008 they don't even make OLEDB drivers for SQL 
Server anymore.

So, it's back to ODBC these days?

None of this matters if you're writing COM or .Net apps, since you 
generally use wrappers like ADO (for COM), ADO.NET (for .NET) or 
Entity Framework (for modern Windows).

I think this is a Rebol forum :)

For us, it's ODBC all the way. And we need to improve the ODBC drivers 
for REBOL.

R2 chokes on every ODBC datatype added to SQL Server since 2000, 
possibly including 2000.

Carl did offer us the sources but no one took him up on that

ChristianE's open-source (yay!) R3 driver chokes on a different and 
larger set of datatypes than R2's, including working with datatypes 
introduced in 2000 that R2 chokes on, like float.

and did you try opendbx?

Fortunately we can improve ChristianE's R3 driver. Since I use it 
a lot at work, I need to. I'm just waiting until I have the time 
to spare, which hasn't happened in several months.

Haven't tried OpenDBX yet. Don't know how R3-like its REBOL API is.

WIP

ChristianE implements an R2-like API in an R3 port model, and it 
just feels alien, though it does allow me to have more portable code 
between R2 and R3. This helps when i have to have a script extract 
data in R2 because it needs to get text or numeric type data, but 
then close and call itself in R3 to parse that data. My %rebol.r 
is R2 and R3 compatible.

I figured out a way to make an R3-like ODBC API, and have an R2-like 
API emulated when you try to use it in an R2-like way. But then I 
ran out of time, was assigned to other projects for a while.

Nowadays I mostly write SQL, sometimes calling that SQL using R3, 
or others call it with ASP.NET, PHP or Java (merger code collection).

OpenDBX: Where are the REBOL drivers? The link on their site doesn't 
work.

http://www.compkarori.co.nz:8000/OpenDBX

Darn, OpenDBX's support for MSSQL is worse than REBOL's :(

OLEDB vs. ODBC; from Microsoft: "You are strongly encouraged to use 
one of the native OLE DB Providers

instead of the Microsoft Open Database Connectivity (ODBC) Provider. 
Native

OLE DB Providers provide better application stability and performance."

at least for it was for SQL Server. But as you said OLEDB is now 
obsolete..

Yup, that must be an old quote, from back when they were promoting 
OLE DB so your code would be non-portable. They don't do that anymore. 
Now their new ODBC stacks in Vista and 7 are better than OLE DB.

http://linuxnetworks.de/doc/index.php/OpenDBX/DBMS_Datatypes

what is exactly defiicient?  I never used sql server

BrianH: yes, this is an old quote. 
OpenDBX's support for MSSQL is worse than REBOL's

 - it doesn't look that bad, just new datatypes are not supported 
 like TIME, DATE.

Not new, core types. There are types OpenDBX can't that I am required 
to support.

can't -> can't handle

Like the nchar and nvarchar types. OpenDBX converts those types to 
ASCII, then R3 would need to convert them back to Unicode.

A lot of the OpenDBX references to SQL Server don't seem to take 
SQL Server 2008 into account, so it's at least 3 versions behind 
now. Comparable to R2 I guess.

It makes a lot more sense to have OpenDBX bindings for R2 though, 
since the lack of Unicode support won't matter there.

OpenDBX is not a full SQL interface to databases, but a database 
abstraction of its own. They're probably not interested in supporting 
other features

There's another interesting product here - http://sqlrelay.sourceforge.net/

If you knew C, then you could add the nchar, and nvchar support

Knowing C, it would be better to add it to the ODBC API, at least 
for someone who already knows SQL. That way you can get wider database 
support, since most SQL database vendors support ODBC. Heck, even 
Microsoft is making a free unixodbc driver for MSSQL.

It isn't even clear how sqlrelay works

it sets up a tcp listener but I can't find the documentation of their 
line protocol