----- Original Message -----
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "DBI USERS" <[EMAIL PROTECTED]>
Sent: Thursday, June 28, 2001 18:55
Subject: Values getting out of order between binding and execute
> <aside> I am writing this from home, I work on this project at work. the
> code I am giving here is hend typed into the e-mail, so I'm not going back
> to use strict or -w, and I might miss a semicolon, but the part I am
having
> trouble with is between binding and execute, and the concept I am using to
> get it there.</aside>
That's fine for quick and dirty to get something started, but for permanent
work, I strongly recumbent '-w' and 'use strict;'
> #after tracking database has been queried for transactions,
> # and tables with replicatable transactions are identified, and
> # table structure has been determined by queries to system catalogues.
>
> $select = qq{SELECT
> $columnlist
> FROM $table
> WHERE $where};
>
> my $values = ",?"x scalar(@columns);
>
> $values =~ s/,//;
>
> my $insert = qq{INSERT INTO $repldb.$dbo.$table
> ($columnlist)
> VALUES
> ($values)};
>
> $subscrh = $subscriber->prepare($insert) || die qq(Can't
> prepare\n$insert\n$subscriber::errstr\n};
You should use $DBI::errstr instead of $subscriber::errstr. The later
refers to $errstr in package 'subscriber' which I don't think is what you
meant. The same applies below to $publisher::errstr and $subscrh::errstr.
> $selecth = $publisher->prepare($select) || die qq{Can't
> prepare\n$select\n$publisher::errstr\n};
>
> $selecth->execute() || die qq{Can't
execute\n$select\n$publisher::errstr\n};
>
> my ($row, @valarray);
>
> $selecth->bind_columns(undef,(\@valarray[0 .. scalar(@columns)-1]));
The reference operator (\) is distributive, so I normally write that as:
$sth -> bind_columns(
\( @array[0 ... $#cols], $scalar, $scalar, @array2[1..3] ) );
That that allows me to pass lots of different references without worrying
about missing a '\'.
> while ($row = $selecth->fetchrow_arrayref) {
>
> $subscrh->execute(@valarray[0 .. scalar(@columns)-1])) || die qq{Can't
> execute\n$insert\n$subscrh::errstr};
>
> }
>
>
>
> ########################
>
> I thought I was binding an ordered array, but it errors out usually giving
a
> type mismatch or truncation error. When I print this out, I find that
> print @valarray[0..scalar(@columns)-1];
>
> doesn't usually print the values in the order I expected them to be in the
> array, so I can only assume that varchar values are trying to be inserted
> into datetime columns etc.
I didn't see how you created $columnlist. I'd check where you do that very
carefully to see if it's giving the columns in the order you are expecting.
Your best bet is to execute the script under the Perl debugger. 'perldoc
perldebug' will help get you started.
> What am I misunderstanding? How would I keep them in the same order
between
> binding and inserting?
I'd check the two table's definitions again to make sure the columns with
the same names have the same characteristics.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
