in reply to DBD::Pg INSERT enlightenment

It would have been helpful if you posted the schema for the table, but from what you wrote, I'll assume you have a table that's defined something like: 

create table tbl_name (
    product_id      int,
    model_number    int,
    description     char[]
);

[download]

If your description is really a "character array", may I suggest that you change it to type "text" instead.

Your new table would look like: 

create table new_tbl_name (
    product_id      int,
    model_number    int,
    description     text
);

[download]

Your array problems should go away, since you're not even dealing with an array anymore.

Just for your information, if you want to put data into a column that is an array of another type, you have to use the { } notation. It's kind of like the [ ] array constructor in perl.

Here's an example: (Assume we're using the tbl_name table); 

my $text = join('', <STDIN>);
my $pg_array = 
    join  ('',
    map   { "'$_', " } 
    split ('', $text));

$pg_array =~ s/, $//;
$pg_array = "{ $pg_array }";

$dbh->do("
    insert into tbl_name (product_id, model_number, description)
    values ($prod_id, $mod_num, $pg_array)
");

[download]

Really, though -- don't use character arrays for text. Just use a text field.

Replies are listed 'Best First'.
Re: DBD::Pg INSERT enlightenment
by penguinfuz (Pilgrim) on Feb 06, 2001 at 07:37 UTC

    Thanks for the input, especially the second piece of code you offered.

    I've debated using a TEXT field; However, my original idea was to have an array of values, I could later QUERY on. For example...

    • CPU
    • PIII
    • 600Mhz

    I figured I could acomplish the same thing with a TEXT field, but I would then put the work on the perl script, parsing the DESCRIPTION field for values.

    Anyway, it seems I'm getting more into postgres, and less into perl with this, thanks again for the information.

[reply]
      I've debated using a TEXT field; However, my original idea was to have an array of values, I could later QUERY on. For example...
      • CPU
      • PIII
      • 600Mhz

      May I suggest that these kinds of things get their own column in the table? Trust me on this one. 

      create table whatever (
    product_id      int,
    model_number    varchar(16),
    cpu             varchar(16),
    mhz             int
);

      [download]

      It'll make your queries more readable.

[reply]
[d/l]

        I've slightly modified the code you posted for the array, and it's working pretty well. Here's what I've done...

        print "\nEnter a brief description:  ";
  my $desc = join('', <STDIN>);
  my $pg_array = join ('', map {split ' '} $desc);
  
  $pg_array =~ s/, $//;
  $pg_array = "{$pg_array}";
  
  print "$pg_array\n\n";
  
  $sth = $dbh->prepare("
    INSERT INTO $tbl_name (product_id, model_number, description)
    VALUES (?, ?, ?)
  ");
  
  $sth->execute($prod_id, $mod_num, $pg_array);
  $sth->finish();

        [download]

        I really cannot afford seperate columns, as the description will contain more than just CPU and MHZ. My plan is to use only ONE table for CPU, RAM, HD, Monitor, case, NIC, MB, and videocard.

        Thanks.

[reply]
[d/l]
      Another alternative is using a relational database system. 

      create table products (
 prod_id int primary key,
 model_num int );

create table prod_desc (
 prod_id int foreign key,
 desc text );

SELECT desc FROM products p, prod_desc d WHERE p.prod_id = d.prod_id A
+ND p.prod_id = ?;

      [download]
      However that may be overkill for your purposes, and being no database and sql expert my syntax may not work (I only know mySQL and Access, and neither perfectly).
[reply]
[d/l]

In Section Seekers of Perl Wisdom