Thanks for this - I located and fixed the typo.

Regards,

Graham

Thanks Eduard, that's really helped.  I have now got it to work.

Graham

Thanks Eduard,

Your help is much appreciated.

The code generated by the wizard for this parameter is below:

 s err=$zf(-5,%SQLGateway,62,QHandle,5,1,$s(%SQLGateway("-uv")&&$zbitget($zversion(0),1):-8,1:1),12,0,0,-2)
 s err=$zf(-5,%SQLGateway,4,QHandle)
 i err=99 s lerr=$zf(-5,%SQLGateway,55,QHandle) s err=$li(lerr)
 i err=99 s err=$$setStream^%apiGTW(QHandle,RawData,0,1)

I then mapped  the following:

s err=$zf(-5,%SQLGateway,62,QHandle,5,1,$s(%SQLGateway("-uv")&&$zbitget($zversion(0),1):-8,1:1),12,0,0,-2)

To in my revised code.  I am not sure what the -2 or the "$s(%SQLGateway("-uv")&&$zbitget($zversion(0),1):-8,1:1)" means

Set sc=gc.BindParameter(hstmt,2,1,1,12,0,0,-2)

I then noticed that this generated the following error:

(HY104) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]Invalid precision value

I therefore revised it to the following as I am not sure what you need to pass for these parameters for a varchar(max) datatype:

Set sc=gc.BindParameter(hstmt,2,1,1,12,10,10,-2)

I then tried this code:

set sc = gc.PutData(hstmt, "TEXT")

However this resulted in the following error:

(HY010) NativeError: [0] Message: [Microsoft][ODBC Driver Manager] Function sequence error

I then tried this code:

set sc= gc.SetParameter(hstmt,$LB("TEXT"),2)

This worked and the value TEXT was stored in the database.

However when I pass another line of text as below the value in the database gets overwritten instead of appended to:

set sc = gc.SetParameter(hstmt,$LB("HELLO"),2)

Do you know why this could be?  Is it to do with how I have used BindParameter and that I am using SetParameter instead of PutData?

Thanks, however I still can't get it to work.  All I keep getting is the following error:

SQLState: (07002) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

I think I must be doing something fundamentally wrong as I am fairly new to object script.  Below is my revised code.  To simplify things I have created a new stored procedure in a Microsoft SQL Server database with one input parameter of type varchar(max).

Any help would be much appreciated.

set gc=##class(%SQLGatewayConnection).%New()
Set pDSN="Test dsn"
Set usr="testuser"
Set pwd="testpwd"
Set sc=gc.Connect(pDSN,usr,pwd,0)
Set sc=gc.AllocateStatement(.hstmt)
Set pQuery= "{? =call dbo.""spTIE_TestTable""(?)}"
Set sc=gc.Prepare(hstmt,pQuery)
Set sc=gc.BindParameter(hstmt,1,4,1,4,10,0,10)
Set sc=gc.BindParameter(hstmt,2,1,1,12,0,0,-2)
Set RAWDATA = ##class(%Stream.GlobalCharacter).%New()
Do RAWDATA.Write("RawData")
set temp = RAWDATA.Read(16000)
set sc=gc.SetParameter(hstmt,$LB(gc.PutData(hstmt,.temp)),2)
set sc=gc.Execute(hstmt)
set sc=gc.Disconnect()

Thanks Eduard,  I am trying to implement your suggestion but not sure how to get the the PutData method to work.  Do you have any examples?  I have used the BindParameter and SetParameter methods on the first 3 parameters I am passing to the SQL stored procedure but want to use this method to pass the steam (as this appears to be what you need to do for a varchar(max) parameter)

Thanks Robert,  When I do this I get the following ODBC error:

ErrorMsg:   SQLState: (HY104) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]Invalid precision value

 I will give Eduard's approach a try and ditch the code generated by the wizard.

Thanks Scott,  It's SQL Server 2016. Yes 8000 works fine  but when  I change it to  be 8001 the class generated by the link procedure wizard changes the parameter to be %Stream.GlobalCharacter instead of %String.  It then no longer works.