Connect 6.0 Memory Allocation Issue

[Daniel Lee]

I’m running Quovadx 5.8 on AIX using the Connect 6.0 drivers.

I’m calling a stored procedure that returns input parameters but when I have a SQL_VARCHAR that is over 31 characters I get junk characters past the 31st character.  Sometimes it’s actually variables values that exist other places in my program which leads me to believe the bind isn’t allocating the proper memory space for the input parameters returned by the stored procedure.  The odd thing is that if in my bind I use SQL_CHAR then the correct value displays except for 1 extra junk character at the very end.  However, when I do a SQLDescribeCol on the stored procedure the DataTypePtr says it’s SQL_VARCHAR.  Has anyone had problems with the SQLBindCol not allocating the appropriate memory space?

Below is the pertinent lines of my code and attached is a screenshot from testing the proc in the test tool.

In the screenshot the address is supposed to be:

219 7th Avenue, SE_ WWWWWWWWWWWWWWWWWWWW

Code:

set returnCode [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
echo “Create env handle: $returnCode”

set returnCode [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
echo “Set environment: $returnCode”

set returnCode [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]
echo “Create db handle: $returnCode”

set returnCode [odbc SQLConnect $hdbc DATAS_NAME SQL_NTS USERN SQL_NTS passw SQL_NTS]
echo “Connect to DB: $returnCode”

#set returnCode [odbc SQLConnect $hdbc DATAS_NAME SQL_NTS]
#echo “Connect to DB: $returnCode”

set returnCode [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]
echo “Create stored procedure handle: $returnCode”

set rCode [odbc SQLBindCol $hstmt 18 SQL_INTEGER pat_age_in_years 2 pcbValue1]
echo “bind=$rCode”

set rcode [odbc SQLBindCol $hstmt 19 SQL_VARCHAR pat_addr_line 40 pcbValue19]

set rcode [odbc SQLBindCol $hstmt 20 SQL_VARCHAR pat_addr_line2 40 pcbValue20]

set storedproc “exec crdsp_get_patient_demog_r @pat_nbr=”12345678″, @pat_ref_code=”MC””
       echo $storedproc

set returnCode [odbc SQLPrepare $hstmt $storedproc SQL_NTS]
echo “Preparing stored procedure: $returnCode”

set returnCode [odbc SQLExecute $hstmt]
echo “Executing stored procedure: $returnCode”

set x 0
###Fetch the columns that you bound to variables using SQLBindCol
###  From only the first row of the result set
set returnCode [odbc SQLFetch $hstmt]

set rcode [odbc SQLDescribeCol $hstmt 19 address 40 NameLengthPtr DataTypePtr ColumnSizePtr DecimalDigitsPtr NullablePtr]
echo $NameLengthPtr
echo $DataTypePtr
echo $ColumnSizePtr
       ###Loop through the rest of the rows of the result set
          while {$returnCode == “SQL_SUCCESS” || $returnCode == “SQL_SUCCESS_WITH_INFO”} {

               echo “pat_age_in_years: $pat_age_in_years”
               echo “pat_addr_line1: $pat_addr_line”
               echo “pat_addr_line2: $pat_addr_line2”
               echo “============ End Record ==============”
               set returnCode [odbc SQLFetch $hstmt]
               incr x
       }
echo “Returned $x rows”

###Clean up statement handle
odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt
###Disconnect from the database
odbc SQLDisconnect $hdbc
###Clean up the database, and environment handles
odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
odbc SQLFreeHandle SQL_HANDLE_ENV $henv

Topic

[Daniel Lee]

Also, does anyone know what the very last parameter of the SQLBind statement is/does?  In all examples I see is as “pcbValueNUM” but can’t find any documentation of what it is for and doesn’t seem to affect anything when I change it.

[Jim Kosloskey]

Dan,

When we invoke a Stored Procedure and pass parameters we use the sqlbindparameter statement like this:

set bind [odbc SQLBindParameter $hstmt $bind_num SQL_PARAM_INPUT SQL_C_VARCHAR SQL_VARCHAR [subst $curr_parm_len_name] 0 parm_$bind_num parm_len_$bind_num NULL]

We also have one for OUTPUT parameters as we send a aparameter and expect one back.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Daniel Lee]

If I’m reading the ODBC documentation correctly, it looks like SQLBindParameter is for output parameters.  Since our stored procedure is returning a result set don’t we have to use the SQLBindCol?

[Peter Heggie]

SQLBindParameter is used when your query or stored procedure invocation has question marks as placeholders, and the SQLBindParameter indicates what variables should be used to provide values in those places. SQLBindColumns is for your result set columns. So it is possible you will use both in your code.

I use the microsoft reference a lot: <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms714562(v=vs.85).aspx” class=”bbcode_url”>http://msdn.microsoft.com/en-us/library/windows/desktop/ms714562(v=vs.85).aspx.

I believe the last argument on the BindCol call is a variable name that will hold the length of the data returned, or will indicate if null data is returned, but I’m not sure because I’ve never used it. That seems to be what the documentation says.

I’ve found that sometimes the last character is truncated and I allow for it by adding one to the parameter argument – for instance if my stored procedure parameter is varchar(36) I’ll put a value of 37 in the invocation:

Code:

odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 37 0 Cluid 37 NULL

, otherwise I’ll lose that character.

I don’t know why you are getting values from other variables. Also, I never got output parameters to work (I only tried for a few hours and gave up) but I got input/output parameters to work without problem.

The stored procedure definition is like this:

Code:

ALTER PROCEDURE [dbo].[prBatchJobStatusFinish]
@Cluid varchar(36),
   @App varchar(20),
   @Job varchar(20),
   @Step varchar(20),
   @Rc int,
   @Lines int,
   @Object varchar(500),
   @Detail varchar(500),
   @RCode int OUTPUT,
   @RDesc varchar(200) OUTPUT

The tcl ODBC code looks like this:

Code:

# prepare SQL statement for execution
 set SQLCmd “exec $database.$sproc ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ”
 set err [odbc SQLPrepare $hstmt $SQLCmd SQL_NTS]
 if {$debug > 1} {puts “[gts] DEBUG: SQLPrepare err: $err”}

 # bind Parameters to variables
 odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 37 0 Cluid 37 NULL
 odbc SQLBindParameter $hstmt 2 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 21 0 App 21 NULL
 odbc SQLBindParameter $hstmt 3 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 21 0 Job 21 NULL
 odbc SQLBindParameter $hstmt 4 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 21 0 Step 21 NULL
 odbc SQLBindParameter $hstmt 5 SQL_PARAM_INPUT SQL_C_SSHORT SQL_INTEGER 12 0 Rc 12 NULL
 odbc SQLBindParameter $hstmt 6 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 11 0 Lines 11 NULL
 odbc SQLBindParameter $hstmt 7 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 501 0 Object 501 NULL
 odbc SQLBindParameter $hstmt 8 SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR 501 0 Detail 501 NULL
 odbc SQLBindParameter $hstmt 9 SQL_PARAM_INPUT_OUTPUT SQL_C_SSHORT SQL_INTEGER 5 0 RCode 5 NULL
 odbc SQLBindParameter $hstmt 10 SQL_PARAM_INPUT_OUTPUT SQL_C_CHAR SQL_VARCHAR 201 0 RDesc 201 NULL

 set Cluid [keylget klParms GUID]
 set App   [keylget klParms APP]
 set Job   [keylget klParms JOB]
 set Step  [keylget klParms STEP]  
 set Rc    [expr [keylget klParms RC] * 1]
 set Lines  [keylget klParms LINES]
 set Object [keylget klParms OBJECT]
 set Detail [keylget klParms DETAIL]
 set RCode “0”
 set RDesc “0”

 # Execute prepared statement
 set result [odbc SQLExecute $hstmt]
 if {$debug > 1} {echo “[gts] $module SP executed – result: $result”}

Peter

Peter Heggie

[Daniel Lee]

I’m getting closer but still am a little lost.  The reason why I’m having a hard time figuring out how I would set up Paramaters for an SQLBindParamater is because my stored procedure looks like this:

Code:

“exec crdsp_get_demogr @pat_nbr=”12345678″, @pat_ref_code=”TT””

There are no reall paramater markers that I can see to set up for it.  The stored procedure just returns a row of data that I have the specs for and used the specs to set up my SQLBindCol.  Can I use SQLBindParamater in that case?  What would my Paramater binds be?

Also, I see that the last argument sayas it holds the length of the data returned but I try to echo it out after the fetch and I get an error like it’s never instanciated.

[Peter Heggie]

ok – maybe that style of invocation is permissable but I have not gotten it to work. When I have called a stored procedure with values in that call, I used this style:

Code:

set drug [lindex $argv 0 ]

…..

set call “execute cloversql_test.dbo.prGetNDDFIngredients N’$drug’;”
set err [odbc SQLPrepare $hstmt $call SQL_NTS]
set err [odbc SQLExecute $hstmt]

so using your example, I would code:

Code:

set call “exec crdsp_get_demogr N’12345678′, N’TT’;”
set err [odbc SQLPrepare $hstmt $call SQL_NTS]
set err [odbc SQLExecute $hstmt]

This assumes that the two arguments position and type match the stored procedure definition in the database.

Peter Heggie

[Jim Kosloskey]

Peter,

With SQL-Server, upon return from the Stored Procedure you will get a recordset even if the Stored Procedure does not create one.

Your Output Paramedters are ‘hidden’ behind that record set. So you need to ‘get next’ in that record set until ‘no more data’ (should only happen once if the Stored Procedure does not create a record set). Then your variables associated with the Output Parameter will be populated. This not well documented by MS and took me a long time to find it. If you would like an example of the code I use to ’empty’ the record set let me know.

The above is NOT true for Oracle or DB2 (at least not DB2 in our environment).

Daniel BindParameter is used for both input and output parameter. One of the arguments specifies the argument type.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Daniel Lee]

Jim,  So are you saying that my Parameters may be hidden behind the seans and I may be able to bind to them even though they are not in my execution statement for the stored procedure?  I have tried to use the SQLBindParameter using the variable names that the db admin gave me for the stored procedure and that didn’t work.  However, I wonder if you’re sayiing that I can put the extra “?” in the execute statment even though I didn’t get the stored procedure in specs from the DBA.

In other words, the specs from the DBA say I need to use the following format:

Code:

“exec crdsp_get_demogr @pat_nbr=”12345678″, @pat_ref_code=”TT””

Are you saying that I can still put the “?” at the end for the output paramaters even though his specs don’t include them?

Code:

“exec crdsp_get_demogr @pat_nbr=”12345678″, @pat_ref_code=”TT”, ?, ?, ?, ?”

The specs he gave me only include places to put the input data for the function calls.  In other words, I give it the patient MRN, Facility, etc. and then it returns the result set rows.  I didn’t see anywhere in his specs where I can include paramaters for the output data.

The db for the stored procedure is sybase.

[Daniel Lee]

I was able to get the SQLBindParameter to return SQL_SUCCESS but haven’t been able to get them to bind any data from the result set to the parameters.  Does anyone know why they use SQLBindParameter instead of SQLBindCol?  I’m wondering if you’ve seen some type of issue with it in the past.

[Peter Heggie]

Here is an MSDN article about named parameters. I think it is saying that you use additional ODBC calls to set the name of the parameter and set a flag that indicates named parameters are used.

<a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms715435(v=vs.85).aspx” class=”bbcode_url”>http://msdn.microsoft.com/en-us/library/windows/desktop/ms715435(v=vs.85).aspx

<a href="http://msdn.microsoft.com/en-us/library/ms131697(v=sql.105).aspx” class=”bbcode_url”>http://msdn.microsoft.com/en-us/library/ms131697(v=sql.105).aspx

You may need to call SQLGetStmtAttr (once) and SQLSetDescField (for each parameter) even though everything is hardcoded. I’m really not sure if you must use these additional calls if you are using the @name=value syntax.

Here is one about SQLBindCol. It mentions how memory for the results is allocated. Possibly it describes how variable memory might be corrupted.

<a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms710118(v=vs.85).aspx” class=”bbcode_url”>http://msdn.microsoft.com/en-us/library/windows/desktop/ms710118(v=vs.85).aspx

Lastly, I’m still fuzzy about the difference between ODBC parameter/column data types and ODBC C parameter/column data types.

For instance on a SQLBindParameter we use:

SQL_PARAM_INPUT SQL_C_CHAR SQL_VARCHAR

after the variable type (input or output) comes the C data type, then the ? data type.. not sure what the difference is but there is a difference.

On the SQLBindCol call it only uses one datatype argument. The msdn article mentions using the C data type. So maybe you should try using SQL_C_CHAR instead of SQL_VARCHAR.

Peter

Peter Heggie

[Daniel Lee]

Thanks Peter, a lot of good information here that I’ll look into.  I’ve tried using SQL_C_CHAR and SQL_CHAR instead of SQL_VARCHAR and it does seem to make a difference.  We’ve run this past an ODBC expert and he suggested that it could be a problem in the ODBC driver failing to take the endianess of the system it’s reading data from into consideration.  Especially since my bound variable seems to chop the data off after the 31st character.  I’m not sure how I’d validate that though.

[Author]

Replies