Graham Hartley · May 10, 2018

Linked Stored Procedure Query in Ensemble 2017.2.1

I  have generated a class using the linked procedure wizard however  I can't get it to work if the  datatype  of one of the parameters is VARCHAR(MAX).  It works fine if I change it to say VARCHAR(500) and  rerun the  stored procedure wizard. 

I get the following  error returned.:

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

Can anyone see what I am doing wrong?  I think it is to do with the way I am using the  %Stream.GlobalCharacter?   Any help is much appreciated.

Class generated by linked procedure wizard:

Include %occInclude
Class dbo.spTIETest [ Not ProcedureBlock ]

ClassMethod spTIETest(ObjectName As %String(MAXLEN=200), NamespaceName As %String(MAXLEN=50), CacheVersion As %String(MAXLEN=500), RawData As %Stream.GlobalCharacter) As %Integer [ ProcedureBlock = 1, ReturnResultsets, SqlName = spTIETest, SqlProc ]
  if '$isobject($g(%sqlcontext)) s %sqlcontext=##class(%Library.ProcedureContext).%New() s RETURNVALUE=$g(RETURNVALUE)
  s ObjectName=$g(ObjectName)
  s NamespaceName=$g(NamespaceName)
  s CacheVersion=$g(CacheVersion)
  s RawData=$g(RawData)
  s hdbc=$$GetConnection^%apiGTW("TestTIEUserProd")
  if hdbc="" { set %msg=$$$ERROR($$$GTWCConnectionError),%msg=$$GetErrorText^%apiOBJ(%msg) goto setError }
  s QHandle=$zf(-5,%SQLGateway,5,hdbc)
  if QHandle="" { set %msg=$$$ERROR($$$GTWCAllocationError),%msg=$$GetErrorText^%apiOBJ(%msg) goto setError }
  s err=$zf(-5,%SQLGateway,$s(%SQLGateway("-uv"):37,1:3),QHandle,"{?=call dbo.""spTIE_InsertTIE_OBJECT_EXPORT""(?,?,?,?)}")
  i err g getError
  s err=$zf(-5,%SQLGateway,62,QHandle,1,4,1,4,10,0,10)
  s err=$zf(-5,%SQLGateway,62,QHandle,2,1,1,12,200,0,200)
  s err=$zf(-5,%SQLGateway,9,QHandle,$lb(ObjectName),2)
  i err g getError
  s err=$zf(-5,%SQLGateway,62,QHandle,3,1,1,12,50,0,50)
  s err=$zf(-5,%SQLGateway,9,QHandle,$lb(NamespaceName),3)
  i err g getError
  s err=$zf(-5,%SQLGateway,62,QHandle,4,1,1,12,500,0,500)
  s err=$zf(-5,%SQLGateway,9,QHandle,$lb(CacheVersion),4)
  i err g getError
  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 err g getError
  s %ROWCOUNT=$zf(-5,%SQLGateway,31,QHandle)
  set tConnection = ##class(%Library.SQLGatewayConnection).%New() set tConnection.DLLHandle = %SQLGateway, tConnection.ConnectionHandle = hdbc
  set rrs = ##class(%SQL.Gateway.ODBCResultSet).%New(tConnection,QHandle)
  while $Isobject(rrs) {
    if rrs.%GetMetadata().columnCount do %sqlcontext.AddResultSet(##class(%ResultSet.Static).%New(rrs))
    set rrs = tConnection.getNextResultSet(QHandle)
  s RETURNVALUE=$li($zf(-5,%SQLGateway,22,QHandle,1))
setError ;
  If ($g(%sqlcontext)'=$$$NULLOREF) {
    Set %sqlcontext.%SQLCODE = $g(err)
    Set %sqlcontext.%Message = $g(%msg)
    Set %sqlcontext.%ROWCOUNT = $g(%ROWCOUNT)
    w "ErrorMsg: "_%sqlcontext.%Message,!
  s err=$zf(-5,%SQLGateway,16,QHandle)
getError ;
  set %msg=$li($zf(-5,%SQLGateway,12,hdbc,QHandle)) goto setError


Code I am using to call the  method from  terminal:

  Set OBJECT = "UHSM.LPI.LinkedTable.LPIGPPractice"
  Set CACHEVERSION = "Cache for Windows (x86-64) 2017.2.1 (Build 801U) Wed Dec 6 2017 09:07:51 EST"
  Set RAWDATA = ##class(%Stream.GlobalCharacter).%New()
  Do RAWDATA.Write("RawData")



0 724
Discussion (12)3
Log in or sign up to continue

What type of database is the stored procedure on? If it is Microsoft have you tried VARCHAR(8000)? MAX does not exist in some SQL languages so sometime you have to limit it down to the number of bytes I believe. Though I might be wrong.

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 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)

Have you tried to set it back to %String manually?
There should be no issue from Ensemble side

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.

1. If you replace:

set sc=gc.SetParameter(hstmt,$LB(gc.PutData(hstmt,.temp)),2)


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

or with:

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

What do you get?

2. Also in your original 4-parameter generated code the stream is bound via:

 s err=$zf(-5,%SQLGateway,62,QHandle,4,1,1,12,500,0,500)

which is equal to

set sc=gc.BindParameter(hstmt, 4,1,1,12,500,0,500)

In your new code you bind the stream with:

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

Is it from some other generated code?

Thanks Eduard,

Your help is much appreciated.

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

 err=99 lerr=$zf(-5,%SQLGateway,55,QHandleerr=$li(lerr)
 err=99 err=$$setStream^%apiGTW(QHandle,RawData,0,1)

I then mapped  the following:


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?

value in the database gets overwritten

You need to set the whole value at once with SetParameter.

If you mean different procedure calls  than you need to debug the procedure itself.

Not sure if this will help but I was having problems to connect to a database in ensemble. I needed to call a store procedure and one of the problems was because of the input variables was a VARCHAR(MAX).

Have a look in case it can help... Post

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()

The call to


Is roughly equal to:

#dim gc As %SQLGatewayConnection
#dim RawData As %Stream.Object
//set sc = gc.ParamData(hstmt, .index) //not sure if it's required
while 'RawData.AtEnd {
  set string = RawData.Read(16000)
  set sc=gc.PutData(hstmt, string) // PutDataW

Examples are available in EnsLib.SQL.Common class:

Method putLOBStream(pHS As %String, pStream As %Stream.Object, tBin As %Boolean) As %Status
  Set tSC=..%Connection.ParamData(pHS,.tInd) Set:$$$SQLCODENeedData=..%Connection.sqlcode tSC=$$$OK  Quit:$$$ISERR(tSC) tSC
  Set temp=pStream.Read(16000)
  If (temp = "") {
    Set err=..%Connection.PutData(pHS,"") 
  } Else {
    While ""'=temp {
      If ..IsUnicodeDLL&&'tBin { Set tSC=..%Connection.PutDataW(pHS,temp) }
      Else { Set tSC=..%Connection.PutData(pHS,temp) }
      Set temp=pStream.Read(16000)
  Quit tSC

Maybe remove $zf calls?

Here's sample code:

ClassMethod Test()
//Create new Gateway connection object
set gc=##class(%SQLGatewayConnection).%New()
if gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
//Make connection to target DSN
set pDSN="Cache Samples"
set usr="_system"
set pwd="SYS"
set sc=gc.Connect(pDSN,usr,pwd,0) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
if gc.ConnectionHandle="" W !, $$$ERROR($$$GeneralError,"Connection failed") QUIT
set sc=gc.AllocateStatement(.hstmt) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Prepare statement for execution
set pQuery= "select Name, DOB from Sample.Person WHERE Name %STARTSWITH ?"
set sc=gc.Prepare(hstmt,pQuery) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Bind Parameters
set sc=gc.BindParameter(hstmt,1,1,1,12,30,0,30)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
set var = "A"
set sc=gc.SetParameter(hstmt,$LB(var),1)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Execute statement
set sc=gc.Execute(hstmt)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Get list of columns returned by query
set sc=gc.DescribeColumns(hstmt, .columnlist) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//display column headers delimited by ":"
set numcols=$listlength(columnlist)-1 //get number of columns
for colnum=2:1:numcols+1 {
    Write $listget($listget(columnlist,colnum),1),":"
write !
//Return first 20 rows 
set sc=gc.Fetch(hstmt)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
s rownum=1
while((gc.sqlcode'=100) && (rownum<=20)) {
       for ii=1:1:numcols {
       s sc=gc.GetData(hstmt, ii, 1, .val)
       w " "_val
       if $$$ISERR(sc) break
       s rownum=rownum+1
  write !
  set sc=gc.Fetch(hstmt)
if $$$ISERR(sc) break
  //Close cursor and then disconnect
set sc=gc.CloseCursor(hstmt)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
set sc=gc.Disconnect()
quit sc

Check BindParameter method and EnsSQLTypes for SQL types macro definitions. Varchar maybe.

You can also try to call PutData method and similar methods (by passing parts of stream there).