Teradata Python Module - passing one parameter with IN produced errors

Hadoop
AWT
Enthusiast

Teradata Python Module - passing one parameter with IN produced errors

Hi there,

 

I have a need to call a Teradata Stored Procedure from Python and so naturally I would think Teradata Python Module is the right module to use.

I would think calling Teradata Stored Procedure should be quite straightforward and it was with one example. However, I have issue calling a Teradata Stored Procedure with a single IN parameter. I demonstrated the issue below. Any help from expert is greatly appreciated.

 

=============== My environment is this:

/********************************************************************************
 * Application Name: HelloWorld
 *          Version: 1.0
 *       Run Number: 20180417171309-640
 *             Host: quickstart.cloudera
 *         Platform: Linux-2.6.32-573.el6.x86_64-x86_64-with-centos-6.7-Final
 *          OS User: root
 *   Python Version: 2.7.13
 *  Python Compiler: GCC 4.4.7 20120313 (Red Hat 4.4.7-18)
 *     Python Build: ('default', 'Apr 12 2017 06:53:51')
 *  UdaExec Version: 15.10.0.21

 


=============== My simple test code is below:

# Create or replace a stored procedure -- myclass_build1
for result in session.execute(
    """replace PROCEDURE tddbtest.myclass_build1
        (IN p1 INTEGER,OUT p2 INTEGER,in p3 varchar(20),out p4 varchar(20))
        BEGIN
            SET p2=p1;
            set p4=p3;
        END;"""):

# Create or replace a stored procedure -- myclass_build2
for result in session.execute(
    """replace PROCEDURE tddbtest.myclass_build2
        (IN p1 varchar(20))
        BEGIN
            declare myp1 varchar(20);
            SET myp1=p1;
        END;"""):

result = session.callproc("tddbtest.myclass_build1",(12345, teradata.OutParam("p2", dataType="INTEGER"),'HelloWord',teradata.OutParam("p4",dataType="VARCHAR")))
print "--- This is OK! ----------\n"
print "This is P2 -> %s"%result.p2
print "This is P4 -> %s"%result.p4

result = session.callproc("tddbtest.myclass_build2",('2018-04-06'))
print "--- This is NOT OK! ----------\n"

 

Thanks

AWT







=============== The out of the code can be seen as follows:

--- This is OK! ----------

This is P2 -> 12345
This is P4 -> HelloWord
[root@quickstart isbcurated]# exit

[root@quickstart isbcurated]# python testme16.py tdexpress141001 lasradm Orion123
Connection Parameters=ChainMap({'method': 'odbc', 'sessionmode': 'Yes'}, {'username': 'lasradm', 'queryBands': {'Name': 'TEST'}, 'password': 'Orion123', 'system': 'tdexpress141001'})

Row 1: [T]

--- This is OK! ----------

This is P2 -> 12345
This is P4 -> HelloWord
Traceback (most recent call last):
  File "testme16.py", line 54, in <module>
    result = session.callproc("tddbtest.myclass_build2",('2018-04-06'))
  File "/opt/rh/python27/root/usr/lib/python2.7/site-packages/teradata/udaexec.py", line 672, in callproc
    return self.internalCursor.callproc(procname, params, **kwargs)
  File "/opt/rh/python27/root/usr/lib/python2.7/site-packages/teradata/udaexec.py", line 710, in callproc
    outparams = self.cursor.callproc(procname, params, **kwargs)
  File "/opt/rh/python27/root/usr/lib/python2.7/site-packages/teradata/tdodbc.py", line 579, in callproc
    self.execute(query, params, queryTimeout=queryTimeout)
  File "/opt/rh/python27/root/usr/lib/python2.7/site-packages/teradata/tdodbc.py", line 604, in execute
    self.executemany(query, [params, ], queryTimeout)
  File "/opt/rh/python27/root/usr/lib/python2.7/site-packages/teradata/tdodbc.py", line 624, in executemany
    checkStatus(rc, hStmt=self.hStmt, method="SQLPrepare")
  File "/opt/rh/python27/root/usr/lib/python2.7/site-packages/teradata/tdodbc.py", line 231, in checkStatus
    raise DatabaseError(i[2], u"[{}] {}".format(i[0], msg), i[0])
teradata.api.DatabaseError: (3813, u'[21S01] [Teradata][ODBC Teradata Driver][Teradata Database](-3813)The positional assignment list has too many values.')

 

 


Accepted Solutions
Highlighted
AWT
Enthusiast

Re: Teradata Python Module - passing one parameter with IN produced errors

Padhia,

 

Awesome answer. I tested and it worked. I struggled for a few days just to get this one right.

Thanks again for your answer.

1 ACCEPTED SOLUTION
3 REPLIES
AWT
Enthusiast

Re: Teradata Python Module - passing one parameter with IN produced errors

Hi there,

 

Anyone is kind enough to shed some lights? Currently, I am still stuck.

If this is not the right forum, can you tell me the right forum to post this question to.

 

Thanks

Allan

Teradata Employee

Re: Teradata Python Module - passing one parameter with IN produced errors

Try replacing:

result = session.callproc("tddbtest.myclass_build2",('2018-04-06'))

 

with:

result = session.callproc("tddbtest.myclass_build2",('2018-04-06',))

Note the extra comma. If you have one-item tuple, python requires a trailing comma to distinguish it from a parenthesized expression.

Highlighted
AWT
Enthusiast

Re: Teradata Python Module - passing one parameter with IN produced errors

Padhia,

 

Awesome answer. I tested and it worked. I struggled for a few days just to get this one right.

Thanks again for your answer.