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

Re^3: Accessing MS-Access memofield via DBI

by reneeb (Chaplain)
on Sep 14, 2005 at 07:27 UTC ( #491777=note: print w/ replies, xml ) Need Help??


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

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


Comment on Re^3: Accessing MS-Access memofield via DBI
Select or Download Code
Re^4: Accessing MS-Access memofield via DBI
by reneeb (Chaplain) on Sep 14, 2005 at 10:32 UTC
    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://491777]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (13)
As of 2014-09-16 18:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (42 votes), past polls