Is There SQL Parameter Binding for Arrays

Is there SQL parameter binding for arrays?

You specify "this is the SQL for a query with one parameter" -- that won't work when you want many parameters. It's a pain to deal with, of course. Two other variations to what was suggested already:

1) Use DBI->quote instead of place holders.

my $sql = "select foo from bar where baz in ("
           . join(",", map { $dbh->quote($_) } @bazs)
           . ")";
my $data = $dbh->selectall_arrayref($sql);

2) Use an ORM to do this sort of low level stuff for you. DBIx::Class or Rose::DB::Object, for example.

Doctrine - How to bind array to the SQL?

You can't use prepared statements with arrays simply because sql itself does not support arrays. Which is a real shame. Somewhere along the line you actually need to determine if your data contains say three items and emit a IN (?,?,?). The Doctrine ORM entity manager does this for you automatically.

Fortunately, the DBAL has you covered. You just don't use bind or prepare. The manual has an example: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

In your case it would look something like:

$sql = "select * from user where id in (?) and status = ?";
$values = [$accounts,'declined'];
$types = [Connection::PARAM_INT_ARRAY, \PDO::PARAM_STR];
$stmt = $conn->executeQuery($sql,$values,$types);
$result = $stmt->fetchAll();

The above code is untested but you should get the idea. (Make sure you use Doctrine\DBAL\Connection; for Connection::PARAM_INT_ARRAY)

Note for people using named parameters:

If you are using named parameters (:param instead of ?), you should respect the parameter names when providing types. For example:

$sql = "select * from user where id in (:accounts) and status = :status";
$values = ['accounts' => $accounts, 'status' => 'declined'];
$types = ['accounts' => Connection::PARAM_INT_ARRAY, 'status' => \PDO::PARAM_STR];

How to bind an Array Parameter (Stored Procedure) via Native ODBC

Digging deeper, I found out that the first ODBC API call failing is actually the binding call for the TVP column / parameter:

SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, tvp_name, 0, &cb);

This call returns SQL_ERROR and checking the diagnostic record issued by this gave me the following error:

HY004 [Microsoft][ODBC SQL Server Driver]Invalid SQL data type

This specific issue was asked here already but sadly remained unsolved. Ultimately using an outdated ODBC driver was the cause of this whole problem. See my answer on another question for more details on how to fix that: https://stackoverflow.com/a/47113255/2334932

Then two points brought up by @A.K. in his comment finally solved the issue:

1. Parameter Length

The last value passed to SQLBindParameter, the parameter length or cb here, needed to have actual amount of available rows rather than SQL_NTS, as it is used as input parameter here.

2. Passing the parameter name is not necessary as they are bound by position

I think it will work with or without, but specifying the name of the TVP here is actually not necessary and can be left out.

So changing the third SQLBindParameter call to this fixed the rest of the issue(s):

cb = arr_size;
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, NULL, 0, &cb);

Full working code

void UpdateCharacterNationTestTVP()
{
    unsigned int old_nation_id = 2;
    unsigned int new_nation_id = 4;

    unsigned int excluded_character_ids[] = {24, 36};
    SQLLEN arr_size = ARRAYSIZE(excluded_character_ids);
    SQLINTEGER cb = arr_size; // Needs to have the actual amount of available rows

    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &old_nation_id, 0, NULL);
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &new_nation_id, 0, NULL);
    SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, NULL, 0, &cb); // Does not need the name of the TVP

    // Super scary binding stuff tvp requires you to do
    SQLINTEGER cb_rows[] = {SQL_NTS, SQL_NTS};
    SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)3, SQL_IS_INTEGER);  // focusing the third parmeter (the TVP one) for the call(s) below
    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, arr_size /*or 0*/, 0, excluded_character_ids, sizeof(UINT), cb_rows); // binding of the actual array in a column styled fashion here
    SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER);  // resetting the focus

    SQLRETURN res = SQLExecDirect(hstmt, (SQLCHAR*)"{call dbo.Update_Character_Nation(?,?,?)}", SQL_NTS);
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO)
    {
        printf("Error during query execution: %hd\n", res);
        ProcessLogs(SQL_HANDLE_STMT, hstmt);
    }
}

php pdo bind array parameters in bindParam without foreach loop

Many PDO users think they have to use bindParam(). You don't.

You can pass an array directly to execute() with all your parameter values. It's this easy:

$stmt->execute($data);

If you used named parameters in your SQL, use a hash array. If you used positional parameters, use a plain array.

For more complete code examples, read them here: http://php.net/manual/en/pdo.prepare.php

---

Related Topics