SSIS中Informix ODBC数据源SQL函数调用如何使用参数或变量？

Absolutely! There are several straightforward, secure ways to replace those hardcoded values with parameters or variables when calling your sp_ccdr stored procedure via Informix ODBC. The approach depends a bit on the tool or programming language you’re using, so let’s cover the most common scenarios:

1. Parameterized Queries with ODBC API (C/C++) #

If you’re working directly with the ODBC API (e.g., in a C/C++ application), you can use parameter binding to pass variables safely. This gives you full control over type handling and avoids manual string formatting:

#include <sql.h>
#include <sqlext.h>

SQLHSTMT hstmt;
SQLRETURN ret;

// Assume you've already established an ODBC connection (hdbc)
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

// Define your variables
SQLCHAR start_dt[] = "2018-04-23 04:00:00";
SQLCHAR end_dt[] = "2018-04-24 03:59:59";
SQLCHAR param3[] = "0";

// Prepare the parameterized procedure call
SQLPrepare(hstmt, (SQLCHAR*)"{call sp_ccdr(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}", SQL_NTS);

// Bind each parameter (adjust types/lengths to match your procedure's inputs)
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 19, 0, start_dt, 0, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 19, 0, end_dt, 0, NULL);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 1, 0, param3, 0, NULL);
// For null parameters, pass SQL_NULL_DATA as the value pointer
SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, NULL, 0, NULL);
// ... bind remaining parameters following the same pattern

// Execute the procedure
ret = SQLExecute(hstmt);

2. Using Scripting Languages (e.g., Python with pyodbc) #

If you’re using a scripting language like Python with the pyodbc library, parameterized calls are even simpler. Use ? as placeholders for your variables:

import pyodbc

# Establish ODBC connection (adjust your connection string to match your setup)
conn_str = "DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_pass;"
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Define your variables
start_date = "2018-04-23 04:00:00"
end_date = "2018-04-24 03:59:59"
param3 = "0"
# Use None for null values in Python

# Execute the parameterized stored procedure
cursor.execute("{call sp_ccdr(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}",
               start_date, end_date, param3, None, None, None, None, None, None, None, None, None, "NULL", "NULL", "f")

# Fetch and process results if needed
results = cursor.fetchall()
for row in results:
    print(row)

conn.close()

3. Informix Native Tools (e.g., DB-Access or Shell Scripts) #

If you’re using Informix’s built-in tools, you can leverage host variables or shell variables to inject values:

Using Shell Variables in a Bash Script #

#!/bin/bash

START_DT="2018-04-23 04:00:00"
END_DT="2018-04-24 03:59:59"
PARAM3="0"

# Call the procedure via dbaccess
dbaccess your_database << EOF
execute procedure sp_ccdr('$START_DT', '$END_DT', '$PARAM3', null, null, null, null, null, null, null, null, null, 'NULL', 'NULL', 'f');
EOF

Using Host Variables in DB-Access #

DEFINE start_dt CHAR(19);
DEFINE end_dt CHAR(19);

LET start_dt = '2018-04-23 04:00:00';
LET end_dt = '2018-04-24 03:59:59';

EXECUTE PROCEDURE sp_ccdr(start_dt, end_dt, '0', null, null, null, null, null, null, null, null, null, 'NULL', 'NULL', 'f');

Key Best Practices #

• Always use parameterized queries (when available) instead of string concatenation to avoid SQL injection risks and ensure proper type conversion.
• For date/time parameters, confirm your variables match the format Informix expects (most ODBC drivers handle automatic conversion if you pass native date types).
• Use the appropriate syntax for null values in your tool (e.g., None in Python, SQL_NULL_DATA in ODBC API, null in SQL).

内容的提问来源于stack exchange，提问作者Abdulquadir Shaikh