DBD::ADO howto avoid "SET FMTONLY ON..." when using placeholders.

Greetings all,
I have scripts that are running on Win32, with an underlying MDAC/ADO
2.8 RTM version, with MSSQL Server 2000 or 2005 as the database. It is
not possible for us to either update/upgrade MDAC itself, and this
version has a bug in the way it handles the SET FMTONLY option (see
http://support.microsoft.com/kb/836830/en-us for further information),
where the actual query is executed when ADO uses an unbound parameter.

I am also using DBD::ADO 2.94

I am preparing a statement like this:
my $sql=< UPDATE text_table
SET uni_text = ?
WHERE uni_id = $ {uni_id}
EOF

my $sth = $dbh->prepare($sql);

This runs the following in SQL Server:
SET FMTONLY ON SELECT uni_text FROM text_table SET FMTONLY OFF

This possibly triggers the aforementioned bug, and as the table contains
several million rows, I suspect it may be contributing to slowing down
the system (the statement can be run several times a minute).

I've tried the following to avoid this, but it doesn't give the expected
results, the SET FMTONLY... sequence is still run:

-- The uni_text column is a nvarchar field (Unicode)
my $sth = $dbh->prepare($sql, {ado_type => 203});

I suspect I am doing something wrong, or that DBD::ADO doesn't support
what I am trying to do. Has anyone encountered this and found a
solution ?

Thanks,
--
Olivier

Re: DBD::ADO howto avoid "SET FMTONLY ON..." when usingplaceholders.

Olivier Poulet wrote:

> Greetings all,
> I have scripts that are running on Win32, with an underlying MDAC/ADO
> 2.8 RTM version, with MSSQL Server 2000 or 2005 as the database. It is
> not possible for us to either update/upgrade MDAC itself, and this
> version has a bug in the way it handles the SET FMTONLY option (see
> http://support.microsoft.com/kb/836830/en-us for further information),
> where the actual query is executed when ADO uses an unbound parameter.
>
> I am also using DBD::ADO 2.94
>
> I am preparing a statement like this:
> my $sql=< > UPDATE text_table
> SET uni_text = ?
> WHERE uni_id = $ {uni_id}
> EOF
>
> my $sth = $dbh->prepare($sql);
>
> This runs the following in SQL Server:
> SET FMTONLY ON SELECT uni_text FROM text_table SET FMTONLY OFF

Out of curiosity, how can I inspect this?

> This possibly triggers the aforementioned bug, and as the table contains
> several million rows, I suspect it may be contributing to slowing down
> the system (the statement can be run several times a minute).
>
> I've tried the following to avoid this, but it doesn't give the expected
> results, the SET FMTONLY... sequence is still run:
>
> -- The uni_text column is a nvarchar field (Unicode)
> my $sth = $dbh->prepare($sql, {ado_type => 203});
>
> I suspect I am doing something wrong, or that DBD::ADO doesn't support
> what I am trying to do. Has anyone encountered this and found a
> solution ?

Dunno if it helps: Normally, the prepare() method calls ADO's
$command->Parameters->Refresh() method. Because this method isn't
supported by all ADO providers, DBD::ADO has its own (poor mans)
_refresh() method. The latter is called if the former fails. You
can avoid trying to call Refresh() with the 'ado_refresh' attribute:

$sth = $dbh->prepare( $sql, { ado_refresh => 2 } );


Steffen