2011/7/7 Brian Raven <bra...@nyx.com>:
>> -----Original Message-----
>> From: perl-win32-users-boun...@listserv.activestate.com [mailto:perl-
>> win32-users-boun...@listserv.activestate.com] On Behalf Of Claus Kick
>> Sent: 07 July 2011 11:49
>> To: Perl-Win32-Users@listserv.ActiveState.com
>> Subject: MS Access Update Memo Field with 750 characters
>>
>> Hello everyone,
>>
>> I am having troubles with updating a memo field in an access database.
>>
>> Currently I am using the following
>>
>> my $dbh = DBI->connect("dbi:ADO:$dsn", $user, $password, $att ) or die
>> $DBI::errstr;
>>
>> sub update_country_releases
>> {
>> my $db = shift;
>>
>> foreach (keys %country_releases)
>> {
>> my $primkey = $_.":ZZZ";
>> my $sth = $db->prepare('UPDATE PROD_INT set COUNTRY =
>> \''.$country_releases{$_}.'\' where ROW_ID = \''.$primkey.'\'');
>> $sth->execute;
>> if ($sth->errstr)
>> {
>> print $sth->errstr;
>> }
>> }
>> }
>>
>> No error during insert. However, I have written a test sub routine
>> which compares what should be there with what actually is in the
>> database.
>> This sub routine returns an error. Looking into it, I realized that
>> the only difference is the length of the value.
>
> Is it possible that the SQL parser is changing the strings? One way to avoid
> that is to use parameter binding. Also, I suggest using Perl's quoting
> operators which can make your SQL statements easier to read. So, assuming
> RaiseError => 1 when you connect, your sub could look something like this.
>
> sub update_country_releases {
> my $db = shift;
>
> my $sql = qq{UPDATE PROD_INT set COUNTRY = ? where ROW_ID = ?};
> my $sth = $db->prepare($sql);
>
> foreach (keys %country_releases) {
> $sth->execute($country_releases{$_}, $_ . ":ZZZ");
> }
> }
>
Hello Brian,
thanks for your response.
If I do without parameter bindings, I get:
OLE exception from "Microsoft OLE DB Provider for ODBC Drivers":
[Microsoft][ODBC Microsoft Access Driver]Invalid precision value
If I use:
$sth->bind_param(1, $country_releases{$_}, DBI::SQL_LONGVARCHAR);
$sth->bind_param(2, $_ . ":ZZZ", DBI::SQL_LONGVARCHAR);
I get:
OLE exception from "ADODB.Command":
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
Win32::OLE(0.1709) error 0x800a0e7c
If I use
$sth->bind_param(1, $country_releases{$_}, { ado_type => 203 }); #adLongVarWChar
$sth->bind_param(2, $_ . ":ZZZ", { ado_type => 202 }); #adVarWChar
I get the same:
OLE exception from "ADODB.Command":
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
Win32::OLE(0.1709) error 0x800a0e7c
_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
