MSSqlServer plugin

General Programming
1

I want to make a Xojo app written to talk to Postgres, to use Sql Server instead.

I recall that a recent Xojo update no longer has the MSSqlServer plugin installed and you have to do something to load that.

To date I haven’t used plugins. To my astonishment, searching for “install plugin” in the docs produces no useful result (mostly extra incantations to install plugins on Linux). Searching for MSSQLServer just provides some help topics that assume that plugin is installed apparently.

I’m sure this is very simple … .where do I find the MSSqlServer plugin and how do I install it? (Xojo 2022 r4.1). Is it a simple matter of copying MSSQLServerPlugin.xojo_plugin from Extras to Plugins and restarting?

2

Well looks like there’s no more to it than dropping the plugin into the plugins folder and restarting. Seems to be working anyway.

3

Yeah doc searches are not very effective in the new doc system :frowning:
Multi word queries dont seem effective in any way

The old pages said
https://docs.xojo.com/MSSQLServerDatabase

Notes

In order to use this class, you must have the MSSQServerDatabase plug-in in your plugins folder. The plug-in is included in the installation.

The new pages are at
https://documentation.xojo.com/getting_started/using_the_ide/included_plugins.html

The MS SQL Server plugin MAY give you grief - I gave up using it and opted for MBS

I do NOT think Xojo has moved to using any newer library from MS that might make it possible to make an x-platform MS SQL Server Plugin

You may be able to just download the ODBC driver for MS SQL Server and use it through the Xojo ODBC plugin on any platform
Haven’t tried that myself

4

If you need something better than what’s built-in, you can always try MBS Xojo SQL Plugin.

e.g.

Sub Connect()
	Dim m As New SQLDatabaseMBS
	
	m.Option("UseAPI") = "ODBC"
	m.Client = SQLConnectionMBS.kSQLServerClient
	m.UserName = "xxx"
	m.Password = "yyy"
	m.DatabaseName = "crm@CRM;MARS_Connection=yes"
	
	If m.Connect Then
		Return m
	Else
		MsgBox "Can't connect to the SQL database."+EndOfLine+EndOfLine+m.ErrorMessage
		Quit
	end if
End Sub

That is what I recently used on a Windows app to convert it from MySQL to Microsoft SQL.

5

Yes. I remember some discussion about them moving this plugin out of the Plugins folder with the reason being that it only worked with Windows applications, so like the MS Office plugin, the user would have to copy it over themselves.

But I can’t find any reference to this in the docs nor release notes. There is only this clue that you have to “install” it. In fact, the documentation is incorrect in another area (gasp!) where it states that the plugin is there by default (used to be, of course).

The forum doesn’t seem to support “highlight links”. The two links above were supposed to take you to the relevant section and highlight it. So just search for “plugins folder” on both pages.

6

What sort of grief, in your experience? This is a CRUD app so it’s not doing anything very fancy, and I manage all my inserts / updates via SQL, I’m not trying to update via rowsets. I understand of course that the SQL dialect is different, mostly in parameter passing and the tendency for how fields are named (postgres_field vs SqlServerField due to differences in DB object case sensitivity), plus the added presence of schema names in Postgres.

7

Well for my particular client we wanted / needed access from Windows, Mac & Linux
So that ruled out the native client API the MS SQL plugin used

Secondly it had issues with varchar max columns - thats common to most native clients
We’re using ODBC with the MBS plugin and that handles this better

Those were the most crucial issues
MBS also give you more info about the DB type you’ve retrieved
The Xojo MS sql server plugin only give you the type they have converted the value TO, not the original type as defined in the DB. This is particularly useful to know since we do some custom conversions to types in our code that are NOT intrinsics in Xojo (like BCD to handle Numeric columns instead of turning them in currency or doubles)

8

Ah OK. Well for my weird situation I just need SQL Server in Windows and it happens none of my tables the app is concerned with have any floating point or decimal numerics, just strings, ints and some dates. And I’m not doing any fancy conversions. I should get by.

I wrote the app originally for MacOS and Postgres, now I need to leverage that code to do the same thing for a client in Windows but against Sql Server with virtually identical table structures.

9

I’ve been using this for years, Windows desktop client with MS SQL Server in a Windows Domain. The ODBC plugin seems to work better than the MS SQL plugin.

dbDocs = New ODBCDatabase   ' dbDocs is a global property in a module, type ODBCDatabase
dbDocs.DataSource = "Driver={SQL Server};Server=ServerName\InstanceName;Database=DatabaseName;TrustedConnection=Yes;"
If Not dbDocs.Connect Then
  ' issue error message
  Return
End
1 Like
10

Thanks, that gives me options. I would imagine a native driver SHOULD be more efficient but it scarcely matters in a simple CRUD app like mine. I also prefer the ability to specify a connection string, in fact it will probably be mandatory at some point to use the “trusted” connection method and I’m not sure how that’s even done with the native driver, which seems to contemplate only sql server authentication.

I haven’t used ODBC in a bazillion years … you still just pass the same TSQL through it though, right? There’s no difference in the syntax at all?

11

Whose ODBC driver are you using? The one that ships with Xojo doesn’t have a DataSource property.

12

Using the ODBC driver there’s no change to the SQL (TSQL).

ODBCDatabase does have a DataSource property, MSSQLServerDatabase does not. Make sure to declare as ODBCDatabase.

I’m not using any additional plugins, just the ones that come with Xojo.

13

My fault, I was newing an ODBCDatabase and putting it into a property of type MSSqlServerDatabase. I should not code before my 2nd cup of coffee :wink:

That was my recollection re: syntax but I think with ODBC you can’t use named parameters whereas at least with a proper native driver you should be able to (@ParamName rather than ?). Or maybe that is actually an ADO.NET thing, I don’t recall – I have just been doing ADO.NET from .NET apps for so long it all blurs together. I’ll get it sorted, and I think you’re right, the ODBC driver may work better for me. Appreciate the tip.

14

So I have Xojo up and running on Windows Server and as per some of the discussion above, the native SqlServer plugin just seems more trouble than it’s worth. Began by whining about the native client not being installed, when in fact Sql Server developer edition in its entirety is directly on that box. I perused TOF and found people talking about installing old versions of the native client and I said, feh, let’s just try ODBC instead.

After some futzing with the the system DSN (Xojo was opening the master DB despite me setting the desired Db property in the OdbcDatabase class; I had to select that at the system DSN level) I now have a query that should return several thousand rows, only returning the first row. There is no TOP n clause, there is no WHERE clause, it’s equivalent to SELECT * FROM TableName, and from SSMS I can see nearly 6K records in that table.

Per TOF, this “only returns one row” issue was happening to some poor soul back in 2019. In his case he was using “prepared statements”, which I’m not. Getting away from prepared statements solved his issue.

The same query works on MacOS when I run the app there, but that’s talking to Postgres. I had to tweak all the queries for sql server, mostly because of differences in how the new PK is returned from an INSERT, differences in how fields are quoted, etc. But Sql Server is something I have WAY more experience with than Postgres so I did not anticipate this sort of weirdness.

100% of the SQL is written by yours truly, so I don’t have any issues with some black box routine generating weird SQL behind my back.

ETA: switching to a different tab control pulling from a different table, all the rows are returned, but in about 17 seconds rather than the 1-2 seconds in the Mac version [sigh]. Anyway this isn’t a general problem where every query returns 1 row. There are other queries populating drop-downs and so forth all working correctly. There’s a more complex query that also is wrongly returning one row (that I’ve noticed).

I’m not seeing any similarities between the offending queries, which are:

SELECT UpperCaseName AS [Upper Case],MixedCaseName As [Mixed Case], LastUpdate AS Updated,Remarks FROM SpecialCapitalization WITH (NOLOCK)

and …

SELECT * FROM Country WITH (NOLOCK) ORDER BY CASE WHEN CountryName = 'United States' THEN '1'               WHEN CountryName = 'Canada' THEN '2'               WHEN CountryName = 'Mexico' THEN '3'               ELSE CountryName          END

Both queries work perfectly from SSMS.

Any ideas?

15

I cant imagine that column names with spaces in them is the issue (since both queries do not use them from your examples)

And you dont get any errors from the database about queries etc ?

I’m honestly not sure since I’ve used the MBS plugin instead of Xojo’s

16

Yeah I threw in the towel and am testing the MBS plugin. I need this up & running for a subcontractor to work with it and then I have to get on with other things.

I am having garden-level connection issues with that, I will track down @MonkeybreadSoftware on that one and I suspect it will Just Work and he’ll get the $149 door prize. I already know this is a pattern and MBS is providing replacements for the abandonware within Xojo.

@npalardy if you happen to know the answer, I posted on TOF here.

2 Likes
17

my connection for ODBC looks like

// connect to Microsoft SQL Database
Dim local_db As SQLDatabaseMBS =New SQLDatabaseMBS

Dim cs As String = "DRIVER={ODBC Driver 17 for SQL Server} ;Server="+m_server+","+Str(m_port)+";UId="+fixedUserID+";PWD="+fixedUserPW+";Database="+m_dbName

local_db.Option("UseAPI") = "ODBC"
local_db.DatabaseName = "ODBC:"+cs

// DB Library settings
local_db.Option("DBPROP_INIT_TIMEOUT") = "30"
local_db.Option("DBPROP_COMMANDTIMEOUT") = "30"

// ODBC settings
local_db.Option("SQL_ATTR_QUERY_TIMEOUT") = "30"
local_db.Option("SQL_ATTR_CONNECTION_TIMEOUT") = "30"
local_db.AutoCommit = SQLDatabaseMBS.kAutoCommitOff
local_db.Option("ODBCAddLongTextBufferSpace") = "false" 
local_db.Option("ODBCUseBigint") = "true"

If local_db.ConnectMT Then
 ..... // good connection !
else
end if

with suitable testing for errors on setting the db properties

1 Like
18

I broke down and tried this because although I’d prefer not to use ODBC, it’d get me out the door. Still getting “DBMS API client not set”. IDK if this is really the issue or if this is the MBS default “I’ve fallen and can’t get up” message.

I take it that the typical ODBC system DSN isn’t in the picture here?

–Bob

19

No I’m composing the DSN on the fly
Thats the line

Dim cs As String = "DRIVER={ODBC Driver 17 for SQL Server} ;Server="+m_server+","+Str(m_port)+";UId="+fixedUserID+";PWD="+fixedUserPW+";Database="+m_dbName
20

Yeah that is what I thought. I just haven’t futzed with ODBC in like 20 years.

I substituted a known working system DSN that I had spun up yesterday for the Xojo ODBC driver that was working, and that got me connected.

Now I’m at least getting a Nil rowset back from a query that should return several rows … I guess in the Xojo world that is progress … although it’s oddly similar to the problem I was having yesterday.