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
Replies are listed 'Best First'.
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 pondering the Monastery: (5)
As of 2015-07-08 01:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (93 votes), past polls