Linked Stored Procedure Query in Ensemble 2017.2.1

Question

Question

Graham Hartley · May 10, 2018

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)
  QUIT $g(RETURNVALUE)
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 NAMESPACE = "WYTTEST"
  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")
  Set sc = ##class(dbo.spTIETest)."spTIETest"(OBJECT,NAMESPACE,CACHEVERSION,.RAWDATA)

Thanks,

Graham

Discussion (12)1

Log in or sign up to continue

Eduard Lebedyuk · May 14, 2018

1. If you replace:

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

with:

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?

0 0

Eduard Lebedyuk · May 10, 2018

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

0 0

score 0 · Answer 1 · 2018-05-10T11:14:58-04:00

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.

score 0 · Answer 2 · 2018-05-10T12:20:26-04:00

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.

score 0 · Answer 3 · 2018-05-11T11:12:35-04:00

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)

score 0 · Answer 4 · 2018-05-10T13:14:45-04:00

Robert Cemper · May 10, 2018

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

0 0

score 0 · Answer 5 · 2018-05-10T13:33:16-04:00

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.

score 0 · Answer 6 · 2018-05-15T13:05:00-04:00

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?

score 0 · Answer 7 · 2018-05-16T02:45:09-04:00

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.

score 0 · Answer 8 · 2018-06-01T08:57:38-04:00

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

score 0 · Answer 9 · 2018-05-14T13:33:54-04:00

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

score 0 · Answer 10 · 2018-05-11T12:08:00-04:00

The call to

$$setStream^%apiGTW(QHandle,RawData,0,1)

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)
  quit:string=""
  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) }
      Quit:$$$ISERR(tSC)
      Set temp=pStream.Read(16000)
    }
  }
  Quit tSC
}