ODBC example
frioux opened this issue · 10 comments
Before I mention the problems, I just want to say this is an awesome idea and I really hope it gets traction. DSNs are the worst! :)
Ok, now with the harder issues. For MSSQL, my current DSNs look like this:
dbi:ODBC:server=10.6.6.17;port=1433;database=Lynx;driver=SQL Server Native Client 11.0;LongReadLen=10000;uid=sa;pwd=...
Note that there is a driver and that's the ODBC driver to use. Before MS ported their ODBC driver to linux my DSN looked like this:
dbi:ODBC:server=10.6.6.17;port=1433;database=Lynx;driver=FreeTDS;tds_version=8.0;LongReadLen=10000;uid=sa;pwd=...
So in this case would the URI end up being the following?
db:sqlserver://sa:...@10.6.6.17:1443/Lynx?driver=FreeTDS&tds_version=8.0&LongReadLen=10000
Also, you can technically use DBD::ADO to connect to SQL Server, so it doesn't just have the one driver option... How would that be expressed? Another param?
Hey @frioux. Yes, those URIs look right. However, Have a look at URI::db::sqlserver. Its dbi_dsn method currently assumes an ODBC driver. Maybe it should use ADO, instead, to avoid the driver problem?
My thought with the dbi_dsn method is to provide a best practice DSN to use in most cases; one can still construct a custom one if one needs something else.
Ok. FWIW ODBC is certainly the preferred driver, but someone may want to use ADO. I just wasn't sure how one might specify if they preferred ADO for some reason
Do you think I should remove the hard-coding of the Driver in the URI::db::sqlserver dbi_dsn, and leave it to the user to provide a driver in the query?
I think ODBC is a good default, but there needs to be a way for the user to specify if they want to use ADO. Maybe use ODBC unless they pass a driver param?
If they want to use ADO, they would need to construct the DSN themselves, I think. Or s/ODBC/ADO/. The Driver is not "ODBC" or "ADO", after all.
ok, so basically you just aren't supporting ADO? I'm ok with that, since we found it insufficient anyway. Just I guess I would say that this is problematic if this were to become some kind of standard, since now there are DSN's that the spec cannot represent.
I am neither supporting ADO nor not supporting it. The point of this module is to support a database URI standard. The dbi_dsn method is a convenience method useful for common DBI cases, but not meant to be comprehensive, since the concept of a driver is not encapsulated in the URI format. The URI says where to connect, not how.
Does that make sense?
Yeah it does make sense. I guess in my mind due to the way things have always been done the driver is inexplicably linked to how and where you connect. And I guess if this standard were to somehow be adopted by all drivers you could do something like DBI->connect('ADO' => $uri) or something to pick your driver. I'll mark the issue as closed. It might be worth discussing this in the standard though
I don't think the driver should be part of the standard, which is meant to be language-agnostic. DBD::Pg won't mean anything to a Ruby dev, you know?
yeah, that's a good point, esp when one considers what a URI is. I just think that you may want to point out in the standard, or somewhere, that the user will need to pick their driver somehow, and with many languages that's done as part of the DSN, for better or worse.