I use the ODBC plugin to get around this problem.[/quote]
I tried the ODBC but when I run it I get this ODBC Microsoft Access Setup window that pops up. This isn’t really acceptable with the scaling/distribution of the webApp using this. Anyway to get around that? I essentially dynamically connect to any number of databases, and new ones can get added at any time.
I know of another case of someone experiencing the same thing with Xojo recently. Ended up using the ODBC plugin and having to cast some of the more MSSQL’ish column types to text to get anything back.
Like you mean pass the datasource string?
I did and it still prompts that popup box.
dim db as new ODBCDatabase
db.DataSource = "Driver={Microsoft Access Driver (*.mdb)}"+_
";Server="+datasource+_
";Database="+initialcatalog+_
";UID="+dbuser+_
";Pwd="+dbpass
return db
So apparently my ODBC on Windows and Mac need different connection strings.
For mac I used:
dim dbt as new CBODBCDatabase
dbt.DataSource = "Driver=/Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/atsqlsrv.so"+_
";Server="+dataSource+_
";Database="+initialCatalog+_
";UID="+dbUser+_
";Pwd="+dbPass
But for windows I had to use:
dim dbt as new CBODBCDatabase
dim d as new Dictionary
d.Value("Driver") = "{SQL Server}"
d.Value("Server") = dataSource
d.Value("Database") = initialCatalog
d.Value("Uid") = dbUser
d.Value("Pwd") = dbPass
dim p() as string
for i as integer = 0 to d.Count - 1
p.Append(d.Key(i).StringValue+"="+d.Value(d.Key(i)).StringValue)
next
dbt.DataSource = join(p, ";") + ";"
Setting the driver to anything else like:
{SQL Server Native Client 11.0}
{SQL Server Native Client 10.0}
{SQL Native Client}
Will fail
[quote=135317:@Brock Nash]The only thing that isn’t working currently is the " varbinary(max)" column where I store some small UI images in one table.
Any suggestions?[/quote]
ODBC does not like MSSQL specific types. You can cast the column to text or a very large varchar.
For the varbinary column I only get the very beginning of the file:
[quote] PNG
[/quote]
^There’s one of the <?> characters and some returns.
I tried to use:
Convert(nvarchar(4000), ColumnName) as ‘ColumnName’ and it just returns a couple asian characters.
Convert(varchar(8000), ColumnName) as ‘ColumnName’ and my program hard crashes
The weird thing is that ODBC works fine from my Mac but just about everything on windows is failing hard.
Using MySQLDatabase instead of ODBC I can get it to return as a hex string if I do this:
convert(varbinary(8000), data) as ‘Data’
Any idea how to turn a hex string into a picture?
I might just have to dynamically choose based on what data I want which database connection type I need to use, which is rather annoying.
Hi. I’ve just hit this bug and the field that’s triggering it is the one I need to read, so no way to work around it.
Is the only workaround for this issue for MS SQL connections to use ODBC? I’d hate to have to require to have ODBC set-up but it’s shaping to be that way from all I’ve seen.
The bug seems to be there since at least 2009 (on the native client) and there seems to be no workaround for it without switching clients.
Yes. I know. I didn’t think I was implying otherwise
I was asking because I realize it’s possible to use the native client and work around this issue (which seems to be an effect of buffering) but I don’t know if the workaround can be done in Xojo: https://msdn.microsoft.com/en-us/library/ms130896.aspx