Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^2: Accessing MS-Access memofield via DBI

by reneeb (Chaplain)
on Sep 13, 2005 at 08:10 UTC ( #491512=note: print w/ replies, xml ) Need Help??


in reply to Re: Accessing MS-Access memofield via DBI
in thread Accessing MS-Access memofield via DBI

Can I do the binding dynamically depending on the datatype of the field??

I tried

#! /usr/bin/perl use strict; use warnings; use DBI ':sql_types'; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*.md +b);dbq=db1.mdb',user,pass); my $col = 'Testcol'; my $statement = "INSERT INTO table (`$col`) VALUES(?)"; my $coltype = get_type($col,$dbh); $coltype = 'SQL_'.$coltype; my $sth = $dbh->prepare($statement); for(2..1000){ my $var = <STDIN>; chomp $var; $sth->bind_param(1,$var,$coltype); $sth->execute($var) or die $dbh->errstr(); } sub get_type{ my ($name,$dbh) = @_; my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name); my $hashref = $sthcolinfo->fetchrow_hashref(); return $hashref->{TYPE_NAME}; }


But I got the following errormessage:
DBI::st=HASH(0x3e9c2dc)->bind_param(...): attribute parameter 'SQL_INTEGER' is not a hash ref at C:/access.pl line 19


Comment on Re^2: Accessing MS-Access memofield via DBI
Select or Download Code
Re^3: Accessing MS-Access memofield via DBI
by reneeb (Chaplain) on Sep 14, 2005 at 07:27 UTC
    This piece of code works:
    #! /usr/bin/perl use strict; use warnings; use DBI ':sql_types'; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*.md +b);dbq=db1.mdb',user,pass); my $col = 'Testcol'; my $statement = "INSERT INTO table (`$col`) VALUES(?)"; my $coltype = get_type($col,$dbh); $coltype = 'SQL_'.$coltype; my $sth = $dbh->prepare($statement); for(2..1000){ my $var = <STDIN>; chomp $var; no strict 'refs'; $sth->bind_param(1,$var,&{"DBI::$coltype"}); use strict 'refs'; $sth->execute($var) or die $dbh->errstr(); } sub get_type{ my ($name,$dbh) = @_; my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name); my $hashref = $sthcolinfo->fetchrow_hashref(); return $hashref->{TYPE_NAME}; }


    But when I split the code in "script" and "module" it does not work.
    I've tried this:
    #! /usr/bin/perl use strict; use warnings; use lib qw(.); use DbiTest; use DBI ':sql_types'; my $col = 'Testcol'; DbiTest->new($col);


    module:
    package DbiTest; use strict; use warnings; use DBI ':sql_types'; sub new{ my ($class,$col) = @_; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*. +mdb);dbq=db1.mdb',user,pass); my $statement = "INSERT INTO table (`$col`) VALUES(?)"; my $coltype = get_type($col,$dbh); $coltype = 'SQL_'.$coltype; my $sth = $dbh->prepare($statement); for(2..1000){ my $var = <STDIN>; chomp $var; no strict 'refs'; $sth->bind_param(1,$var,&{"DBI::$coltype"}); use strict 'refs'; $sth->execute($var) or die $dbh->errstr(); } } sub get_type{ my ($name,$dbh) = @_; my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name); my $hashref = $sthcolinfo->fetchrow_hashref(); return $hashref->{TYPE_NAME}; } 1;


    With that code the script dies with the following errormessage:
    Usage: SQL_LONGVARCHAR() at DbiTest line 21
      The solution is quite simple:
      $sth->bind_param(1,$var,&{"DBI::$coltype"}());

      But the question is why my version (&{"DBI::$coltype"}) runs in a single script and why it doesn't work after splitting into script and module...
        I had the same problem as you and I duplicated your solution, with the same complication. When the insert code is in a different package than the calling routine it didn't work. Must be a namespace issue. If you resolve it please let me know. In the meantime thank you, thank you to everyone in this thread. I was going nuts with this problem.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://491512]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (9)
As of 2014-07-29 06:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (211 votes), past polls