def testProcedureWithBinaryAndFloatParameters(self):
if self.dsn == "ODBC":
with udaExec.connect(self.dsn, username=self.username,
password=self.password) as conn:
self.assertIsNotNone(conn)
for r in conn.execute(
"""REPLACE PROCEDURE testProcedure1
(INOUT p1 VARBYTE(128), OUT p2 VARBYTE(128),
INOUT p3 FLOAT, OUT p4 FLOAT, OUT p5 TIMESTAMP)
BEGIN
SET p2 = p1;
SET p4 = p3;
SET p5 = CURRENT_TIMESTAMP;
END;"""):
logger.info(r)
result = conn.callproc(
"testProcedure1",
(teradata.InOutParam(bytearray([0xFF]), "p1"),
teradata.OutParam("p2"),
teradata.InOutParam(float("inf"), "p3"),
teradata.OutParam("p4", dataType="FLOAT"),
teradata.OutParam("p5", dataType="TIMESTAMP")))
self.assertEqual(result.p1, bytearray([0xFF]))
self.assertEqual(result.p2, result.p1)
self.assertEqual(result.p3, float('inf'))
self.assertEqual(result.p4, result.p3)
def testProcedure(self):
# REST-307 - Unable to create Stored Procedure using REST, always use
# ODBC.
with udaExec.connect("ODBC", username=self.username,
password=self.password) as conn:
self.assertIsNotNone(conn)
for r in conn.execute(
"""REPLACE PROCEDURE testProcedure1
(IN p1 INTEGER, OUT p2 INTEGER)
BEGIN
SET p2 = p1;
END;"""):
logger.info(r)
for r in conn.execute(
"""REPLACE PROCEDURE testProcedure2 (INOUT p2 INTEGER)
BEGIN
SET p2 = p2 * p2;
END;"""):
logger.info(r)
with udaExec.connect(self.dsn, username=self.username,
password=self.password) as conn:
for i in range(0, 10):
result = conn.callproc(
"testProcedure1",
(i, teradata.OutParam("p2", dataType="INTEGER")))
self.assertEqual(result["p2"], i)
# Does not work with REST due to REST-308
if self.dsn == "ODBC":
for i in range(0, 10):
result = conn.callproc(
"testProcedure2",
(teradata.InOutParam(i, "p1", dataType="INTEGER"), ))
self.assertEqual(result["p1"], i * i)
def testProcedureInOutParamNull(self):
if self.dsn == "ODBC":
with udaExec.connect("ODBC",
username=self.username,
password=self.password) as conn:
self.assertIsNotNone(conn)
for r in conn.execute("""REPLACE PROCEDURE testProcedure1
(IN p1 INTEGER, INOUT p2 INTEGER,
INOUT p3 VARCHAR(200), INOUT p4 FLOAT,
INOUT p5 VARBYTE(128))
BEGIN
IF p2 IS NULL THEN
SET p2 = p1;
END IF;
IF p3 IS NULL THEN
SET p3 = 'PASS';
END IF;
IF p4 IS NULL THEN
SET p4 = p1;
END IF;
IF p5 IS NULL THEN
SET p5 = 'AABBCCDDEEFF'XBV;
END IF;
END;"""):
logger.info(r)
with udaExec.connect(self.dsn,
username=self.username,
password=self.password) as conn:
for i in range(0, 10):
result = conn.callproc(
"testProcedure1",
(i, teradata.InOutParam(None, "p2",
dataType='INTEGER'),
teradata.InOutParam(None, "p3", size=200),
teradata.InOutParam(
None, "p4"), teradata.InOutParam(None, "p5")))
self.assertEqual(result["p2"], i)
self.assertEqual(result["p3"], "PASS")
self.assertEqual(result["p4"], i)