SQL Server & Xojo - server local app (cross posted TOF)

I am communicating with a sql server based ERP system to manage work orders. I can write my own queries, functions,stored procedures etc and build views but that leaves me periodically polling the db for the information I need. I want it to feed my application when an insert or update is made without having to poll it. I think Im talking fewer than 50 insert/updates/day but sporadically throughout with no pattern.

My first thought was I could set a couple of triggers on the table and write out the affected rows and monitor the folder it writes to but that seems so bogus. I just hate “hot folders”.

Im looking for is someone who can help me use CDC, SSIS or other sql server (not limited to) tools to push data to a windows xojo app. Im also trying to understand the relative burden of various appraoches to 2 way communication with linked servers and openquery vs running them all in the hosting db.

There must be an efficient, light burden way to do this. Is there a way to connect CDC to a webhook from sql server or other such. I feel like there must be something Im not finding…

I am open to consulting on this too.

I’ve pinged @Paddy who I think might be able to help

2 Likes

Sounds like a job for Query Notifications and Service Broker?

I’ve not tried this in Xojo to be fair, I don’t know whether the Xojo SQL plugin would allow you to do this - I went to the MBS SQL plugin pretty early and stayed with that…

Another article

These are what I was hoping to find. Thank you Paddy! I use MBS too. Is there a particularly simple route and method(s) you are using in MBS? Do you happen to know if MBS impliments ‘SqlDependency’ ? That looks like the simplest ticket for me. I will start reading these articles more and looking in the MBS docs…

After a quick read I am assuming you must be using the mbs method, SQLExexcute? Do you/anyone happen to have an example of how you’re calling the service/queue? I would be extrememly greatful if anyone does or has any tips! I’m going to start down the road of execute/SqlDependency and see if I can figure it out.

Just be aware that MBS kind of has 3 APIs for accessing the DB in one plugin :slight_smile:

I use it for client projects and that took me a while to figure out

1 Like

Please do elaborate a tich Norm, thanks! I see MSSQL and MicrosoftSQL and the former seems to only be a few basic calls. Whats the 3rd? What do I need to know or look out for or how to read up on it?

Oh I mean in terms of the classes that are possible to use to interact with the database

hmmm … maybe only 2 really

You’ll find classes & methods that are subclasses of Xojo’s “Database” and related API’s
https://www.monkeybreadsoftware.net/class-database.shtml

As well you will find ones that are similar to ADO
https://www.monkeybreadsoftware.net/class-sqlcommandmbs.shtml
https://www.monkeybreadsoftware.net/class-sqlconnectionmbs.shtml

You can mostly interoperate between these but I found its better to try & stick to one style or the other

But you CAN use them to connect to just about anything you can find a driver for since they support ODBC :slight_smile:
So I can easily connect to MS SQL server from macOS :slight_smile:

1 Like

Hi - to be clear I’ve not done this method in Xojo. If I were I would go straight to the MBS plugins to attempt rather than use the Xojo plugin - I’ve always had far better results with MBS.

Its a far more complete SQL plugin IMHO.

2 Likes

Are there significant issues with Xojo’s MS SQL Server plugin?

At work it looks like we are going to go with an ERP that uses MS SQL server… I don’t know if I am going to be allowed to access it from an Xojo app, or even if I’m going to need to… but I might, and I really would not want to spend more money on a plugin for that!

-Karen

You mean besides the fact that it never worked from macOS ?
That was kind of a roadblock being on a Mac :slight_smile:

It does not work at all on a Mac!!! That is outrageous!!!

I obviously never used it… At work they are switching everyone to PCs… I have been given special dispensation to stay on my Mac until it can’t be supported by Office (they don’t want me to retire right now! :wink: )… Which means about 2 more years… the one I have at work (intel) can’t go beyond Monterey.

-Karen

Wait a minute … you can’t use Sql Server with a MacOS client? WTAF?

What HAVE I gotten myself into …

Oh well I guess if I ever need that I’ll just lay out a few more large for MBS. I get the sense that without some of these 3rd party vendors, the whole ecosystem would be in complete collapse.