Skip to Main Content
Spotfire Ideas Portal
Status Future Consideration
Categories JDBC / ODBC / ADO
Created by Guest
Created on Mar 6, 2018

Add support for placeholders for stored procedures, to ODBC/JDBC drivers

The existing [as of 7.0.5.00.01] drivers for TDV do not support SQL placeholders (bind parameters), when used with stored procedures.

 

Using PERL or Python, the following kind of code fails:

PERL:

----

use DBI;
use DBD::ODBC;

my $dsnName = 'DSN_NAME';
my $dsn="dbi:ODBC:".$dsnName;
my $dbc=DBI->connect($dsn,'','') || die($DBI::errstr);
$dbc->{odbc_default_bind_type} = 12; # didn't help http://grokbase.com/t/perl/dbi-users/042b3qr4g0/problem-binding-parameters-in-dbd-odbc

my $intermediateFolder = '/path/to/folder';
my $query = 'exec util.createAllFolders ?';

my $query_handle = $dbc->prepare($query);
$query_handle->bind_param(1,$intermediateFolder);

---


Fails with an error similar to:

DBD::ODBC::st execute failed: Unable to parse query text: unexpected token: ?. On line 1, column 28. [parser-2904201]
Cause: unexpected token: ? [Log ID: 747eaf57-2276-4fab-b025-942420bc4fe7] (SQL-) at ./alexTest line 31.
Fail to execute: at ./alexTest line 31.


Python:

---

import pyodbc
import pandas as pd
import numpy as np
import pandas.io.sql as pds

conn = pyodbc.connect("dsn={dsn_name}")
def conv2009(value):return value
conn.add_output_converter(2009,conv2009) # workaround ODBC data type 2009 is not supported. Cannot read column faultResponse.
intermediatePath = '/path/to/folder'

sql = "exec util.createAllFolders ?"
df = pds.read_sql(sql,conn,params=[intermediatePath])

for index,row in df.iterrows():
print str(row['success']) + " - " + row['faultResponse']

---

Fails with an error similar to:

pandas.io.sql.DatabaseError: Execution failed on sql 'exec util.createAllFolders ?': ('HY000', '[] Unable to parse query text: unexpected token: ?. On line 1, column 28. [parser-2904201]\nCause: unexpected token: ? [Log ID: 9af654d9-d08e-4c87-b252-b49c7ded9d70] (1000) (SQLNumParams)')

 

Replacing the exec query with a normal SELECT query works fine, so this appears to be specific to execing a stored procedure.

 

Please add support for this. 

 

 

 

  • Attach files