Problem in query with SqlServer

Databases
1

Hi, I’m testing and interfacing my application desktop with SqlServer
(OS Windows 10, Xojo 2019 r3.1)
Already during the insertion I have some problems: if I try to insert an empty string, in reality some “strange characters” are inserted in the table. I solved this by turning the empty string into a space, right before doing ps.Bind
(very tedious and expensive solution).
But the thing that’s most troubling me is an error I get during a SELECT query. The error is as follows:

9005: Error retrieving large column values due to a bug in the SQL Native Client

Any indication? thank you

2

Hi Ant,

I have no experience with this myself, but I did find an entry in this forum that might help.

Take a look at this link. Good luck!

3

what field type u used in ms sql database table for this string?

4

@Ant_Col Do use MonkeyBread’s SQL plugin with an ODBC connection. This just works. To be fair, we did all the research on the different connection issues quite a few years ago so some might be fixed, but we’ve been using Monkeybread/ODBC combination every since.

Another recommendation is to do all the SQL in stored procedures and just execute the stored procedure from Xojo. Created classes to connect to the database and to call the stored procedures with the parameters you specify. This allows you to globally set how the connection is made, set error traps and transactions around the call to the stored procedure, and to check for dodgy characters that need escaping in the parameters without having to do it every time in your code.
E.g.

' Open a connection to the SQL Database
Dim db As SQLDatabase = App.Outline.GetDatabase
Dim sp As New SQLStoredProcedure (db, "CCGetAuditDescription")
Dim rs As RecordSet

' Setup the parameters
sp.AddParameter("@AuditId", AuditId) 
sp.AddParameter("@EventType", EventType)


' Run the Stored procedure. This version returns a recordset
rs = sp.ExecuteRecordSet

' This just executes the stored procedure
' sp.ExecuteNonRecordset

' Get the Description
Description = GetField(rs, "Description").StringValue

' Close Database Objects
rs.Close
db.Close

This SQLStoredProcedure class generates the following SQL for Xojo to execute:

BEGIN TRY
	BEGIN TRAN
	
		EXEC CCGetAuditDescription @AuditId = 1, @EventType='Start'
	
	COMMIT
END TRY
BEGIN CATCH
	DECLARE @Errmsg nvarchar (4000)
	SELECT @Errmsg=CONVERT(nvarchar(5),ERROR_NUMBER()) + ':' + ERROR_MESSAGE ()
	ROLLBACK
	RAISERROR (@Errmsg, 16, 1)
END CATCH

And your stored procedure in SQL might look like this:

CREATE PROCEDURE CCGetAuditDescription	
(
	@AuditId 	INT,
	@EventType 	NVARCHAR(20)

)
AS
BEGIN

	SET NOCOUNT ON

	SELECT	Description

	FROM	CCAudit

	WHERE	AuditId   = @AuditId
	AND		EventType = @EventType
	
END
5

Has your table a field VARCHAR(MAX)? If so, change to VARCHAR(5000) (or any other length instead of MAX)

6

Hi everyone, sorry for the delay, you were fantastic to give me all these answers, but I can only now read and resend because I had big health problems. Normally I use varchar (x). Now I don’t even remember which fields I got the problem on. Now, after 1 month and a half at home, I’m back and I’m working on something else. I will definitely come back to the problem later. Thanks again everyone.