[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Blob/Memo fields with DBI

Posted on 1999-11-08
3
Medium Priority
?
363 Views
Last Modified: 2008-03-06
A little background:

Developing a simple DBI script that connects to my Access 97 database using ODBC to pull some data out. The data rows consist of a couple of text fields and one large memo field.

The problem? It works on rows with small fields, but if the memo field is large, it fails. I can't seem to get it to read anything out. The code is as follows:

$sth = $dbh->prepare( "
      SELECT * FROM Main1 WHERE id = 2
      " );
                         
if ( !defined $sth ) {
      die "Cannot prepare statement: $DBI::errstr\n";
}
                                     
# Execute the statement at the database level
$sth->execute;

# Fetch the rows back from the SELECT statement
@row = $sth->fetchrow;

for $tt (@row)
{
      print $tt."\n";
}
0
Comment
Question by:Ishani
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
windfall earned 400 total points
ID: 2192079
I have had the same problem... Here is how I fixed it....

#This is the Generic Link to the database
#Now connect to database
$dbhndl = (DBI->connect ('DBI:ODBC:something database',"something","something"));
#log and die if we do not connect
&log_and_die($DBI::errstr) unless $dbhndl;
#Send the SQL call and check that it is good

#Place the following to handle long variables
$dbhndl->{'LongReadLen'} = 6000 ; # to better handle long variables



#create the following sql query
$sql = "SELECT * FROM whatever  WHERE (something) ORDER BY (whatever)DESC";

$sth = $dbhndl->prepare($sql);
&log_and_die($sth->errstr) unless $sth;
#Now execute this query
$sth->execute || &log_and_die($sth->err);
#Make a while loop through all the returned rows
while (@row =$sth->fetchrow)
{
      #now format column <--> row using my
      my $columnname= $row[0];
      my $columnname= $row[1];
      my $columnname= $row[2];
      my $columnname= $row[3];
      etc
}
$sth->finish;
#We are done with this db-handle now.
$dbhndl->disconnect();
0
 

Author Comment

by:Ishani
ID: 2192098
Top. It works. Thanks!

Btw, do you know of any performance hits that this would cause?
0
 
LVL 2

Expert Comment

by:windfall
ID: 2194100
No, I really don't have a clue....
I use this Perl DBI with WIN NT =>ODBC=>MS SQL7 on an Emergency Room Patient/Staffing  Intranet....so my user base is relatively low and, my database would be small and my server and bandwidth resource is high compared to some WWW database applications....Maybe one of the REAL gurus (OZO? or PRAKASHK?) would know or you could check out the mailing list for the DBI at:
http://www.bitmechanic.com/mail-archives/dbi-users/
Have a good one!


0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question