According to DBI documentation:
The data type is 'sticky' in that bind values passed to execute() are bound
with the data type specified by earlier bind_param() calls, if any.
Portable applications should not rely on being able to change the data type
after the first C<bind_param> call.
It seems not work as described above with DBD::SQLite.
This also completely breaks
bind_param_array($n, [@vals], $sql_type);
(as it uses bind_param($n, undef, $sql_type) once to assign type);
Attached patch fixes this problem; includes regression test and update to broken
t/rt_71311_bind_col_and_unicode.t
From 6770b3aff4b33174943007cef235f43dd416f9f0 Mon Sep 17 00:00:00 2001
From: "Yuriy M. Kaminskiy" <yum...@gmail.com>
Date: Fri, 14 Oct 2011 20:50:02 +0400
Subject: [PATCH] Make bind_param type assignment sticky as per DBI
documentation.
Also fixes bind_param_array($col, $aref, SQL_xxx).
Breaks t/rt_71311_bind_col_and_unicode.t (that depends on unsticky
bind_param), fix included.
WARNING: potentially affects code depending on current undocumented and
broken behavior.
---
dbdimp.c | 13 ++++------
t/48_bind_param_is_sticky.t | 48 +++++++++++++++++++++++++++++++++++++
t/rt_71311_bind_col_and_unicode.t | 1 +
3 files changed, 54 insertions(+), 8 deletions(-)
create mode 100644 t/48_bind_param_is_sticky.t
diff --git a/dbdimp.c b/dbdimp.c
index 87df8d4..f77d93c 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -661,11 +661,11 @@ sqlite_st_execute(SV *sth, imp_sth_t *imp_sth)
}
for (i = 0; i < num_params; i++) {
- SV *value = av_shift(imp_sth->params);
- SV *sql_type_sv = av_shift(imp_sth->params);
- int sql_type = sqlite_type_from_odbc_type(SvIV(sql_type_sv));
+ SV **pvalue = av_fetch(imp_sth->params, 2*i, 0);
+ SV **sql_type_sv = av_fetch(imp_sth->params, 2*i+1, 0);
+ SV *value = pvalue ? *pvalue : &PL_sv_undef;
+ int sql_type = sqlite_type_from_odbc_type(sql_type_sv ? SvIV(*sql_type_sv) : 0);
- sqlite_trace(sth, imp_sth, 4, form("params left in 0x%p: %ld", imp_sth->params, 1+av_len(imp_sth->params)));
sqlite_trace(sth, imp_sth, 4, form("bind %d type %d as %s", i, sql_type, SvPV_nolen_undef_ok(value)));
if (!SvOK(value)) {
@@ -721,10 +721,6 @@ sqlite_st_execute(SV *sth, imp_sth_t *imp_sth)
}
}
- if (value) {
- SvREFCNT_dec(value);
- }
- SvREFCNT_dec(sql_type_sv);
if (rc != SQLITE_OK) {
sqlite_error(sth, rc, sqlite3_errmsg(imp_dbh->db));
return -4; /* -> undef in SQLite.xsi */
@@ -1191,6 +1187,7 @@ sqlite_bind_ph(SV *sth, imp_sth_t *imp_sth,
pos = 2 * (SvIV(param) - 1);
sqlite_trace(sth, imp_sth, 3, form("bind into 0x%p: %"IVdf" => %s (%"IVdf") pos %d", imp_sth->params, SvIV(param), SvPV_nolen_undef_ok(value), sql_type, pos));
av_store(imp_sth->params, pos, SvREFCNT_inc(value));
+ if (sql_type)
av_store(imp_sth->params, pos+1, newSViv(sql_type));
return TRUE;
diff --git a/t/48_bind_param_is_sticky.t b/t/48_bind_param_is_sticky.t
new file mode 100644
index 0000000..df4523f
--- /dev/null
+++ b/t/48_bind_param_is_sticky.t
@@ -0,0 +1,48 @@
+#!/usr/bin/perl
+
+# Check data type assignment in bind_param is sticky
+
+use strict;
+BEGIN {
+ $| = 1;
+ $^W = 1;
+}
+
+use t::lib::Test qw/connect_ok/;
+use DBI qw(:sql_types);
+use Test::More;
+use Test::NoWarnings;
+
+plan tests => 10 + 1;
+
+my $dbh = connect_ok(
+ RaiseError => 1,
+ PrintError => 0,
+ AutoCommit => 0,
+);
+$dbh->do("CREATE TABLE Blah ( id INTEGER, val BLOB )");
+$dbh->commit;
+my $sth;
+ok($sth = $dbh->prepare("INSERT INTO Blah VALUES (?, ?)"), "prepare");
+$sth->bind_param(1, 1);
+$sth->bind_param(2, 'foo', SQL_BLOB);
+$sth->execute;
+$sth->execute(2, 'bar');
+sub verify_types() {
+ my $rows = $dbh->selectall_arrayref("SELECT typeof(val) FROM Blah ORDER BY id");
+ ok($rows, "selectall_arrayref returned data");
+ ok(@{$rows} == 2, "... with expectd number of rows");
+ ok($rows->[0]->[0] eq 'blob', "$rows->[0]->[0] eq blob");
+ ok($rows->[1]->[0] eq 'blob', "$rows->[1]->[0] eq blob");
+}
+verify_types();
+$dbh->commit;
+$dbh->do("DELETE FROM Blah");
+$sth->bind_param_array(1, [1, 2]);
+$sth->bind_param_array(2, [qw/FOO BAR/], SQL_BLOB);
+$sth->execute_array({});
+verify_types();
+$dbh->commit;
+
+$dbh->disconnect;
+undef($dbh);
diff --git a/t/rt_71311_bind_col_and_unicode.t b/t/rt_71311_bind_col_and_unicode.t
index d85ac1e..3c60f78 100644
--- a/t/rt_71311_bind_col_and_unicode.t
+++ b/t/rt_71311_bind_col_and_unicode.t
@@ -37,6 +37,7 @@ my $str = "\x{20ac}";
$sth->bind_param(2, $blob, {TYPE => SQL_BLOB});
$sth->execute;
+ $sth->bind_param(2, undef, SQL_VARCHAR);
$sth->execute(4, $str);
$sth->bind_param(1, 5);;
--
1.7.6.4
_______________________________________________
DBD-SQLite mailing list
DBD-SQLite@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
