Hi Jon,
Tried your suggestions, and it did not work. So heres all the code that
does all the
work:
Table creation code:
------------------------------------------------
use refclips;
drop table if exists cliptypes;
create table cliptypes
(
cliptype_id int unsigned not null auto_increment primary key,
cliptype varchar (100) not null,
cliptype_description varchar (255),
index i_cliptype (cliptype)
)
------------------------------------------------
perl code putting data into the table:
------------------------------------------------
#!/usr/bin/perl -wT
use strict;
use DBI;
my $file = "../data/background.txt";
my %document;
my $key;
my $read;
my @clip;
open (DATA, $file);
#data entry done by this function.
sub enterclip
{
my @clip = @_;
my $dbh = DBI->connect
("dbi:mysql:database=refclips:host=localhost","jatin","qwerty");
foreach (@clip)
{
next if (!$_);
print "typofclip: $_\n";
my $sql1 = " select cliptype_id from cliptypes where cliptype =
\"$_\"";
print "$sql1\n";
my $sth = $dbh -> prepare ($sql1);
$sth -> execute;
my @row;
my $rowcount = $sth->rows();
if ($rowcount == 0)
{
my $sql3 = "insert into cliptypes (cliptype,
cliptype_description) values (\"$_\", \" \")";
print "Insert = $sql3\n";
$dbh->do ($sql3);
}
}
$dbh->disconnect;
}
#code to read in the data from flat txt file and call data entry sub
foreach (<DATA>)
{
next if /^#/;
if (/^\r\n$/)
{
%document = ();
# $read = <stdin>;
enterclip (@clip);
@clip = ();
}
if ( /^TYPE OF CLIPPING\s*:\s*(.*)$/)
{
push @clip, $1;
}
#code to display data as it is read off the file.
# if (/^\r\n$/)
# {
# foreach $key (keys (%document))
# {
# if ($key eq "keywords")
# {
# print "if = $key\n";
# my $ctr;
# foreach $ctr (0..$#{ $document{$key} })
# {
# print "\t$document{$key}[$ctr]\n";
# }
# }
# else
# {
# print "if = $key -> $document{$key}\n";
# }
# }
# }
#$read = <stdin>;
}
----------------------------------------------------
nothing really great out there.
I am using mysql, so if you want I can tar / zip the database and send
to you,
so you can plug it into your mysql and see the result yourself, but the
data is quite
a lot.
Hope you could get an idea out of that.
Another thing is that if no one else has faced such an issue, I am
thinking of
upgrading to the latest mysql server. If any one has coupled mysql
3.23.39 and DBI
without problems pls reply.
Jatin
Jon Barker wrote:
>MySQL doesn't space pad varchar fields.
>
>The mysql client displays the data with a column width sufficient for
>whichever is greater of the column heading, or the longest item of data
>in the result set.
>
>If you ned to trim spaces you can do it in the select with TRIM() :-
> SELECT TRIM(beelzebub) FROM ...
>If "TRIM(beelzebub)" is too long for your aesthetic beliefs, you can :-
> SELECT TRIM(beelzebub) AS dev FROM ...
>Which would change the column heading to "dev" from "TRIM(beelzebub)".
>
>If that's not your problem, you're going to have to post code.
>
>Jon
>
>>Hello,
>>
>>I am facing a peculiar problem with dbi (1.14) / mysql (3.23.22-beta).
>>whenever i enter data
>>to a varchar column in a mysql table using dbi, there is a lot of spaces
>>suffixed to the data.
>>I think it fills up the column to its total width with spaces. As a
>>result the display of the data
>>using the mysql client is quite terrible, with data getting blanked out.
>>I have no idea what is
>>happening, and this is my first attempt with dbi.
>>
>>Please tell me where could I be going wrong here.
>>
>>Thanks
>>
>>Jatin
>>
>>
>
>
>
