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



Discussion (12)1
Log in or sign up to continue

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)

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?

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