ODBC Support

ptotushek

Great job on Objo Studio! I've used some of your code on Github in the past and have appreciated your willingness to share and the quality of the code. I've been testing out Objo Studio, and it's just about to the point where I could start converting some of my programs. A few of my programs access a MS SQL Server. Any thoughts adding ODBC support in the future?

Thanks,
-Pete

bgrommes

ODBC support should definitely be on the roadmap.

One of the things that lit a fire under me to get away from Xojo was that their native ODBC support had a rep for being indifferently maintained and supported, and I had to pay an external vendor for a better implementation and even that (and with excellent support) was hiccuping on Windows Server and Sql Server where I had to deploy. So getting this right could be a real differentiator for Objo.

I moved away from Xojo before we transitioned to Azure DB from straight Sql Server and that might have been even more problems for all I know. Azure does fully support ODBC (and OLEDB) and has deprecated the native driver protocol. So I guess a good ODBC implementation will cover the bases for Azure / Sql Server, although it may need additional support for Microsoft Entra ID on Azure.

FWIW Garry, I'd be able to test against both Azure and Native Sql Server on Windows Server, although you can probably get a copy of Sql Server Developer Edition for free for either Windows or Linux (equivalent features set to Sql Server Enterprise -- you don't have to pay unless you deploy for production apps, it's free for development). But I can test against Windows Server on a box that has both a native Sql Server locally and talks to Azure DB as well.

I will have to resurrect that old Xojo project and see if I can remember what I was having to work around there with ODBC ... might be good to know.

Garry

Thanks both - this is really useful feedback.

I agree that SQL Server support belongs on the roadmap, especially for people looking to bring existing Xojo-style business/database apps across to Objo. Pete, your use case is exactly the kind of thing I want Objo to support well, that is, an app that needs to talk to an existing Microsoft SQL Server database without a lot of friction.

After looking into this, I think the best first step is to add first-class SQL Server support rather than starting with a generic ODBC layer:

The distinction is that ODBC is a general driver interface, whereas SQL Server is the actual database engine being targeted here. A generic ODBCDatabase class would potentially let Objo talk to many different installed ODBC drivers, but it also brings a larger support surface: DSNs, native driver installation, Windows/macOS/Linux driver manager differences, Apple Silicon vs Intel driver architecture issues, and provider-specific metadata behaviour. That's a large surface area for bugs to creep in.

For SQL Server and Azure SQL specifically, Objo can use Microsoft's current .NET SQL Server provider, Microsoft.Data.SqlClient really easily. I've checked the licensing and it is MIT licensed, so it is suitable for Objo. This should let me add a SQLServerDatabase class that fits into the existing Objo database API alongside SQLiteDatabase, MySQLDatabase, and PostgreSQLDatabase.

The goal would be that SQLServerDatabase supports the same common Objo database surface: Connect(), Close(), SelectSQL() etc where SQL Server exposes it cleanly.

So the current plan is:

Add SQLServerDatabase for SQL Server and Azure SQL first.
Keep ODBCDatabase as a separate possible compatibility feature later.
Make sure the implementation works with Objo's existing Database interface so provider-neutral code can still be written where possible.

@bgrommes, your offer to test against both local SQL Server and Azure SQL is very welcome. Once the first implementation is in place, real-world testing against Windows Server and Azure SQL would be much more valuable than only testing against a toy local database.

I've logged this as an accepted feature request here: https://feedback.objo.dev/feature/468

I hope this is OK?

ptotushek

Gary, that would work great for me!

Garry

I'll try to get it into the next release. Am currently wrestling with migrating Objo from Avalonia 11 to Avalonia 12. Trying to ditch technical debt but it's a tricky process!

bgrommes

The SQL Server Native Client (SNAC or SQLNCLI) is deprecated. Current guidance is to use OleDB or ODBC. This keeps popping up on various online mentions. I find it surprising and ironic because ever since the 1990s when I first had contact with Sql Server 7.0, we were consistently steered away from ODBC and OLEDB, so this is a recent sea change. I recall reading a post somewhere last year that claimed there was too much technical debt in the native client and MSFT wanted to embrace something more standards-based. Hearsay and gossip, but informed hearsay and gossip supposedly.

All that said, I use Microsoft.Data.SqlClient and it works perfectly. Maybe it uses OleDB internally, IDK. That namespace replaced System.Data.SqlClient awhile back, so they definitely swapped something out. You still need System.Data.SqlTypes, weirdly, to build named parameters with a specific SqlDbType specified.

One thing Microsoft.Data.SqlClient does NOT support is ? parameter substitution, everything must be named parameters. You'll need to expose an equivalent to the CommandType enum (Text or StoredProcedure) and methods to define named parameters. I am accustomed to using my own helper methods I wrote a generation ago, and don't remember for sure but I think the API takes Object for all parameter values and then probably is fairly forgiving around e.g. effectively passing an Int64 in to an Int32 parameter definition. Otherwise you may have some explicit casting to do to get all the SqlDbTypes working with Objo's more limited type system.

This would be sufficient IMO. I would NOT try to support DataSets or DataTables. I never use the former and use the latter (stand-alone) only when I need random access to the query result (as opposed to forward-only); I would just have to roll my own in some form if I need that in Objo.

Basically DataSets / DataTables / DataViews / relations were intended to be like an in-memory DB with basic primary key and search/sort support and record versioning. The idea was to support CRUD apps with parent/child relationships without a persistent DB connection but IMO it's over-engineered and a paradigm that would be alien to most people outside the .NET world. Even within .NET it's probably most popular with old-school WinForms developers since WinForms was originally designed to bind to those structures. But anytime since about 2010 it seems like most .NET DB devs write their own UPDATE / DELETE SQL calls, etc. on a record by record basis rather than trying to bulk update all changes in a DataTable or DataSet. That approach has its own problems. It's a heavier abstraction, harder to debug, etc, even if in some ways convenient.

SqlDataReader exposes a boolean property, HasRows, that is not part of the IDataReader interface. This allows you to detect if no rows came back from your query without the overhead of setting up a Read() loop that won't end up doing anything, and is occasionally useful as a return value of a function when you're just checking if some record exists in the DB. You might consider exposing that on the Objo side in some fashion. Supporting little touches like that will definitely make a SQL Server dev feel right at home ;-)

MikeB

I would like to throw out a concern about SQL Server requiring named parameters instead of the ? substitution. I do a lot of programming in Delphi with FireDAC and the huge advantage in the way it is implemented is the consistency that things like query/command parameters are implemented the same no matter what DB driver is being used under the covers. It does make migrating code between databases very easy as long as you stay with SQL standard conventions.

IMHO - Keeping database access consistent regardless of the underlying datastore would be a great advantage for Objo. I would go as far as saying that even if current Objo code needed to be broken for the sake of long term standardization for database access it would be better than having multiple ways of having to write database code.

bgrommes

MikeB Named parameters are not without their advantages, not least better protection against injection attacks. But ... there are a lot of differences in SQL dialects besides that. I used to maintain an app that supported either Postgres or Sql Server and yes we had separate SQL for each but it wasn't entirely because of the differences in parameters because that app used ODBC to talk to Sql Server so it was ? parameters all the way.
For example, Postgres uses LIMIT rather than TOP, NOW() instead of GETDATE(), || rather than + for string concatenation, and so forth.
SqlLite has very few column types compared to either Postgres or SQL Server.
It's been probably nearly 30 years since I worked with Oracle but I remember that had its own weirdnesses.
So I don’t think you’re going to get to a universal SQL dialect for all, at least not totally.
Also there's just the expectations of Sql Server devs and what they're accustomed to. Not everyone's objectives are the same.
I would say though that your concerns would be addressed by ODBC support, which is coming at some point.

madamov

Regarding testing and since Garry is, as I understand, mainly working on ARM Mac, the easiest way of doing that for me was installing docker image with SQL Server on Linux, it run in intel emulation on Apple Silicon. Latest version of SQL Server runs properly in intel emulation layer on Windows 11 ARM, so now I run it on Mac in VMware Fusion virtual machine.

Garry

SQL Server support has been added for the next release: https://feedback.objo.dev/feature/468