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.

Thanks for your quick response.  The only reason for raising the issue was that our Server Team were getting concerned about how much memory was being used on the server.

I will feed back your response.

Looking at the console log I can see the following which I presume implies it is using large pages?

05/13/17-02:41:19:083 (3416) 0 Allocated 17186MB shared memory (large pages): 15360MB global buffers, 1023MB routine buffers

Thanks again,

Graham