PERL Miscellaneous - can't rebind placehodler - Blob, ODBC, SQL Server, DBI, Win32

Author

can't rebind placehodler - Blob, ODBC, SQL Server, DBI, Win32

Eric Anderson

2005-08-25, 6:57 pm

I have been banging my head against the wall for a while now trying to
figure out why I can't upload a some blob data to a SQL Server database
via DBI. I think I had it working a while back but I cannot be sure and
obviously I am missing something here. The basic code is:

$sql = 'UPDATE "File" SET Data = ? WHERE FileID = ?';
$stmt = $self->storage()->prepare( $sql );
$stmt->trace('5', 'C:\trace.log');
$stmt->bind_param(1, $self->data(), {TYPE=>SQL_LONGVARBINARY});
$stmt->bind_param(2, $self->id());
$stmt->execute();
$stmt->trace('0');

I've tried many variations of the above but I always get the same basic
results. The above variations is what I understand as the "correct"
method based on the documentation. This code is inside an object so
$self->data() returns the blob data as a scalar and $self->id() returns
the id of the record we are updating. The whole thing is in the context
of a CGI application (Mason actually) that is doing a file upload. My
test file is a PDF file (but I have tried many different files of
various sizes and types). $self->storage() returns a database handler to
the SQL server and I generally haven't had any problems storing other
types of data. It will return a brand new handler (new connection) so
there is not an issue of a previous execution getting in the way.

My error message I get is:

Can't rebind placeholder 1 at /Program Files/Apache
Group/Apache2/sites/cordata/singlesignon/sso/app/models//Data/File.pm
line 36.

Line 36 is the execute() statement. To try to debug this I enabled
tracing (as you can see from the code). I have pasted the trace at the
end of this message. I would be very thankful for any insight that could
be provided. The two things that jump out to me in the trace are:

SQLDescribeParam failed reverting to default type for this parameter:
HY010 [Microsoft][ODBC Driver Manager] Function sequence error

and

_rebind_ph/SQLBindParameter error -1 recorded: [Microsoft][ODBC SQL
Server Driver]Invalid precision value (SQL-HY104)(DBD:
_rebind_ph/SQLBindParameter err=-1)

but I don't know enough about what is going on to determine what I need
to do to fix the problem. Again thank you for any help you can provide.

Eric

############# TRACE FILE ############

DBI::st=HASH(0x234d2ac) trace level set to 0x5/0 (DBI @ Ox0/0) in
DBI 1.42-ithread (pid 3704)
-> bind_param for DBD::ODBC::st (DBI::st=HASH(0x234e404)~0x234d2ac
1 '%PDF-1.2
%âãÏÓ

773 0 obj
<<
/Linearized 1
/O 775
/H [ 1488 1484 ]
/L 470565
/E 25965
/N 112
/T 454986[color=darkred]
endobj
xref
773 54
0000000016 00000 n
0000001431 00000 n
0000002972 00000 n
0000003206 00000 n
0000003336 00000 n
0000003359 00000 n
0000005511 00000 n
0000005786 00000 n
0000005943 00000 n
0000006149 00000 n
000000638...' HASH(0x23507f0)) thr#2243ec
bind 1 <== '%PDF-1.2
%âãÏÓ

773 0 obj
<<
/Linearized 1
/O 775
/H [ 1488 1484 ]
/L 470565
/E 25965
/N 112
/T 454986[color=darkred]
endobj
xref
773 54
0000000016 000' (attribs: HASH(0x23507f0)), type 0
SQLDescribeParam idx = 1.
SQLDescribeParam failed reverting to default type for this parameter:
HY010 [Microsoft][ODBC Driver Manager] Function sequence error
<- bind_param= 1 at /Program Files/Apache
Group/Apache2/sites/cordata/singlesignon/sso/app/models//Data/File.pm
line 34 via \Program Files\Apache
Group\Apache2\sites\cordata\singlesignon
\sso\tmp\obj\app\controllers\file\edit.html
line 115
-> bind_param for DBD::ODBC::st (DBI::st=HASH(0x234e404)~0x234d2ac
2 '41') thr#2243ec
bind 2 <== '41' (attribs: ), type 0
SQLDescribeParam idx = 2.
SQLDescribeParam failed reverting to default type for this parameter:
HY010 [Microsoft][ODBC Driver Manager] Function sequence error
<- bind_param= 1 at /Program Files/Apache
Group/Apache2/sites/cordata/singlesignon/sso/app/models//Data/File.pm
line 35 via \Program Files\Apache
Group\Apache2\sites\cordata\singlesignon
\sso\tmp\obj\app\controllers\file\edit.html
line 115
-> execute for DBD::ODBC::st (DBI::st=HASH(0x234e404)~0x234d2ac)
thr#2243ec
dbd_st_execute (outparams = 0)...
bind 1 <== '%PDF-1.2
%âãÏÓ

773 0 obj
<<
/Linearized 1
/O 775
/H [ 1488 1484 ]
/L 470565
/E 25965
/N 112
/T 454986[color=darkred]
endobj
xref
773 54
0000000016 00000 n
0000001431 00000 n
0000002972 00000 n
0000003206 00000 n
0000003336 00000 n
0000003359 00000 n
0000005511 00000 n
0000005786 00000 n
0000005943 00000 n
0000006149 00000 n
000000638...' (size 470565/470566/0, ptype 4, otype 1, sqltype 12)
bind 1 <== '%PDF-1.2
%âãÏÓ

773 0 obj
<<
/Linearized 1
/O 775
/H [ 1488 1484 ]
/L 470565
/E 25965
/N 112
' (len 470565/470565, null 0)
bind 1: CTy=1, STy=VARCHAR, CD=470565, Sc=0, VM=470565.
SQLBindParameter: idx = 1: fParamType=1, name=1, fCtype=1, SQL_Type
= 12, cbColDef=470565, scale=32767, rgbValue = 234b374,
cbValueMax=470565, cbValue = -470665
Param value =
dbd_error: err_rc=-1 rc=0 s/d/e: 38869944/38867784/38867616
dbd_error: SQL-HY104 (native 0): [Microsoft][ODBC SQL Server
Driver]Invalid precision value (SQL-HY104)
dbd_error: err_rc=-1 rc=0 s/d/e: 0/38867784/38867616
dbd_error: err_rc=-1 rc=0 s/d/e: 0/0/38867616
_rebind_ph/SQLBindParameter error -1 recorded: [Microsoft][ODBC SQL
Server Driver]Invalid precision value (SQL-HY104)(DBD:
_rebind_ph/SQLBindParameter err=-1)
! -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x234d2ac)~INNER) thr#2243ec
ERROR: -1 '[Microsoft][ODBC SQL Server Driver]Invalid precision
value (SQL-HY104)(DBD: _rebind_ph/SQLBindParameter err=-1)' (err#0)
! <- DESTROY= undef during global destruction

Mark Clements

2005-08-25, 6:57 pm

Eric Anderson wrote:
> I have been banging my head against the wall for a while now trying to
> figure out why I can't upload a some blob data to a SQL Server database
> via DBI. I think I had it working a while back but I cannot be sure and
> obviously I am missing something here. The basic code is:
>
> $sql = 'UPDATE "File" SET Data = ? WHERE FileID = ?';
> $stmt = $self->storage()->prepare( $sql );
> $stmt->trace('5', 'C:\trace.log');
> $stmt->bind_param(1, $self->data(), {TYPE=>SQL_LONGVARBINARY});
> $stmt->bind_param(2, $self->id());
> $stmt->execute();
> $stmt->trace('0');
>
> I've tried many variations of the above but I always get the same basic
> results. The above variations is what I understand as the "correct"
> method based on the documentation. This code is inside an object so
> $self->data() returns the blob data as a scalar and $self->id() returns
> the id of the record we are updating. The whole thing is in the context
> of a CGI application (Mason actually) that is doing a file upload. My
> test file is a PDF file (but I have tried many different files of
> various sizes and types). $self->storage() returns a database handler to
> the SQL server and I generally haven't had any problems storing other
> types of data. It will return a brand new handler (new connection) so
> there is not an issue of a previous execution getting in the way.
>
> My error message I get is:
>
> Can't rebind placeholder 1 at /Program Files/Apache
> Group/Apache2/sites/cordata/singlesignon/sso/app/models//Data/File.pm
> line 36.
>
> Line 36 is the execute() statement. To try to debug this I enabled
> tracing (as you can see from the code). I have pasted the trace at the
> end of this message. I would be very thankful for any insight that could
> be provided. The two things that jump out to me in the trace are:
>
> SQLDescribeParam failed reverting to default type for this parameter:
> HY010 [Microsoft][ODBC Driver Manager] Function sequence error
>
> and
>
> _rebind_ph/SQLBindParameter error -1 recorded: [Microsoft][ODBC SQL
> Server Driver]Invalid precision value (SQL-HY104)(DBD:
> _rebind_ph/SQLBindParameter err=-1)
>
> but I don't know enough about what is going on to determine what I need
> to do to fix the problem. Again thank you for any help you can provide.
<snip trace>

Have you tried binding as different types eg

SQL_BINARY
SQL_VARBINARY

what is the actual datatype of the field in question ie "Data" (which
isn't a very descriptive name :) )?

Mark

Eric Anderson

2005-08-29, 9:56 pm

Mark Clements wrote:
> Have you tried binding as different types eg
>
> SQL_BINARY
> SQL_VARBINARY
>
> what is the actual datatype of the field in question ie "Data" (which
> isn't a very descriptive name :) )?

Yes, I have tried all sorts of types. The actual type is an image field
type which is basically SQL Server talk for "large blob". I finally used
the shortcut syntax ($stmt->bind_param(1, $self->data(),
SQL_LONGVARBINARY);) and it worked. Who knows the issue. there are so
many layers in this setup (DBI unixODBC, FreeTDS, SQL server) who knows
who's fault it is. I'm just glad I got it working.

Thanks for trying,

Eric

Sponsored Links