$sth = $dbh->prepare(
    "insert into $tblname (product_id, model_number, description)
     values (?, ?, ?)");
    $sth->execute($prod_id, $mod_num, $data);
[download]

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.

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.

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.

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).

I agree that repson has it right, if using a relational database, take advantage of it's strengths and create a second table for the many in your one->many relationship. Then you can take advantage of foreign key restraints which can help keep some rogue code from messing with the relationships, adding descriptions to prod_id's that don't already exist in the parent table.

Another simpler option is to remove the unique restraints on Prod_id and just add multiple rows to the table. you'll get the same data back from a select on prod_id as you would using a join. the downside is space... if that is an issue, then use the two table approach.

so Postgress has an "array" type?

> take advantage of it's strengths and create a second table...

Actually, this IS the second table. 8)
The first table consist of an inventory_id, product_id, and quantity.


> Another simpler option is to remove the unique restraints on Prod_id and just add multiple rows to the table...

Unfortunately I cannot add many rows to the second table as I have MANY entries to INSERT.


> so Postgress has an "array" type?

Yes, just create a column as *usual* but use quare-brackets to specify the dimensions of the array.

Since the only mention of perl on this last posting came from THIS sentence, I think I'll stop.
Thanks everyone for the ideas. 8)