Excuse me, I meant to say I queried the publisher database's system
catalogues and created the subscriber database based on that, not the
"create and install the publisher database" like I said. Late at night....
:-)
Steve
-----Original Message-----
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 10:50 PM
To: Michael A. Chase
Cc: DBI USERS
Subject: RE: Values getting out of order between binding and execute
In this case, the publishing DB is MS SQL 7.0, so I have created the
$columnlist using a:
SELECT name FROM syscolumns WHERE id = object_id('$tablename')
ORDER BY colorder
I use those results to build $columnlist, then use it both for the select,
and for the insert queries to keep that order the same. I actually derive
@columns from this using:
my @columns = split /, /, $columnlist;
Some parts of the script lend themselves to use of the array, but the
derived queries I preferred the $columnlist. I then use the information in
the tracking database to build $where.
I used something similar to create both the scripts that create and layout
the tracking database (based on keys if they exist, or if necessary, all
columns), create and install the publisher database, and create and install
the triggers that record the replicatable transaction into the tracking
database.
The thing I notice that really catches my attention is the difference you
had in the bind_columns method. You used:
\( @array[0 ... $#cols]
That looks like just a different way of expressing what I was by using the
scalar function, but I want to make sure: I am not so familiar with $#cols
notation.
<yours>
Your best bet is to execute the script under the Perl debugger. 'perldoc
perldebug' will help get you started.
</yours>
That's good advice. I'll try that when I get back into work tomorrow. Who
knows, after I iron the head dents out of my desk I might even see something
obvious and stupid. I'm just hung, and since my Perl is definitely my
weakest point in this, I thought maybe I was doing something out of whack in
the way I was getting data from the bind_columns to the execute().
Thanks,
Steve Howard
-----Original Message-----
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 9:16 PM
To: Steve Howard; DBI USERS
Subject: Re: Values getting out of order between binding and execute
----- 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.
