On 30-Jan-2002 Jeff Urlwin wrote:
> Tim,
>
> Ok -- that was a big help in describing the problem and letting me
> understand it. Right now, here's the situation:
> DBD::ODBC *assumes* that all binds are of type VARCHAR and only when
> overidden on the bind_col will it actually change that. Your column, in
> this case, is a SQL_LONGVARCHAR. The current method of handling that is to
> over-ride the type in the bind call.
Yes, that is how I saw it and why I suggested to Tim, to override the parameter
type but knowing what he is doing now that is not possible as he does not know
the parameter type.
> HOWEVER:
>
> There is code in there to do the work, we just have to allow it to occur.
> Basically if the SQL_TYPE comes in as 0, then it's currently being set to
> SQL_VARCHAR. There is code, in dbd_rebind_ph, that checks to see if the
> sql_type is 0 and performs the check of the column type with
> SQLDescribeParam. Altogether a good thing, but it makes me nervous as if
> SQLDescribeParam fails, the the code issues an error which would stop
> current programs from working. That's the problem from my perspective. It
> will break, most likely, someone's code.
>
> Here's what I propose:
> - I patch for attempting to use SQLDescribeParam
> if SQLDescribeParam fails, we revert back to the current assumption of
> SQL_VARCHAR.
Sounds good to me (although see below) so long as we remember that some drivers
do not have SQLDescribeParam (e.g. MSAccess/Jet). I notice SQLDescribeParam is
called whatever and not after ascertaining if the driver has SQLDescribeParam
(there is already one call to SQLGetFunctions to see if the driver has
SQLMoreResults - shouldn't this be done for SQLDescribeParam too and perhaps
only the first time required and not every time).
> There may be issues, though, with data conversions this way. I'm not sure,
> but there is a comment in the code indicating there may be an issue with the
> code. I'll try to test it with the data that I have (Access and Oracle for
> sure, I'm working on a SQL Server 2000 machine now, too.
Is the comment you are referring to:
/* XXX
This will fail (IM001) on drivers which don't support it.
We need to check for this and bind the param as varchars.
This will work on many drivers and databases.
If the database won't convert a varchar to an int (for example)
the user will get an error at execute time
but can add an explicit conversion to the SQL:
"... where num_field > int(?) ..."
According to the table in the ODBC 3.0 reference, conversions from SQL_C_CHAR
to any other data type should be supported.
and in a prevoius email Jeff said:
> Also, I'm missing something (history, etc) here with the TimeStamp parameter
> issue. The trace should help me greatly.
When looking at the OOB log of Tim's script I saw the following call:
SQLBindParameter(0x81fe1e8,9,1(Input::),1,12,80,11,0x82048f8,11,0x81ff5e4)
Parameter 9 was bound for input as an SQL_C_CHAR(1) to be interpreted as an
SQL_VARCHAR(12) with a ColumnSize of 80, DecimalDigits of 11 and BufferLength
of 11. What concerned me was the DecimalDigits of 11. According to ODBC spec if
the parameter type is SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, SQL_INTERVAL_XXX (where
XXX includes SECOND) the DecimalDigits value sets the Precision field in the
IPD. Now, in this case ParameterType is SQL_VARCHAR so I don't think it matters
but if you make the proposed change above then SQLDescribeParam will come back
in Tim's case with SQL_TYPE_TIMESTAMP in which case DecimalDigits is describing
the precision of the fractional seconds part of the timestamp content which is
definitely not 11. You get away with alot more when using SQL_VARCHAR
ParameterType.
So, for timestamps you would need to know how many chrs are after the decimal
point and set the DecimalDigits accordingly. There are other driver specific
issues but that would be up to the script writer. Here's a relevant
driver-specific entry from my OOB FAQ:
==========
4.50 Why cannot I insert timestamps with sub-millisecond fractions in to
MS SQL Server datetime field?
Firstly, consult
http://support.microsoft.com/support/kb/articles/Q263/8/72.asp
MS SQL Server only does millisecond accuracy (precision of 3) on
datetime columns so anything sub-millisecond tends to generate an
error such as 22008 [Microsoft][ODBC SQL Server Driver]Datetime field
overflow. As the fraction part of a timestamp can range from 0 to
999,999,999 you need to specify the fraction in thousands of second.
If you are using column-wise bound parameters to insert a timestamp in
to a datetime field make sure the ColumnSize is set to 23.
==========
As always, I can easily check any pre-release versions of DBD::ODBC with a
large number of ODBC drivers and am happy to do so. Just send them my way with
a comment on the changes made and I'll do whatever testing I can.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
