All I do is:
Select * FROM DBserversAnd I get a sql error message:
This is what I could find:
https://connect.microsoft.com/SQLServer/feedback/details/467300/problem-with-varchar-max-via-sql-native-client
Also if i try to:
Select Id From DBServersI get all the records back. But if I do:
Select Name From DbserversI only get the first record back
Have a look at Williams comments at <https://xojo.com/issue/30780>.
I use the ODBC plugin to get around this problem.
[quote=135127:@Wayne Golding]Have a look at Williams comments at <https://xojo.com/issue/30780>.
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’m willing to switch to the MBS plugin if @Christian Schmitz can confirm this issue doesn’t exist in his plugin.
Please make simple test project. I’m never heart about such a bug.
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.
Set it up to create a DSN in code ?
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
Ok
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
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?
Change the column definition? Instead of “Max” use a fixed (large) number?
[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.
dim p as Picture
Dim mb as MemoryBlock
mb = DecodeHex(mydataString)
p = Picture.FromData(mb)
This did it for me.
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.
As originally reported way back when this bug is an MS bug in their client code NOT just a plugin bug
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