RE: Column Names

[Steve Howard]

my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic

while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)


Right, no worries, but a good point to make is that the hash keys are in an
array - so the order in which they are returned while doing that is always
in the order they returned by the query, so it is perfectly safe to do
something like this when the two tables have the same columns:

my $select = qq{SELECT * FROM Sometable};
my $selecth = $dbh1->prepare($select) || die "Can't
prepare\n$select\n$DBI::errstr\n"
$selecth->execute() || die "Can't execute\n$select\n$DBI::errstr\n";
$selecth->bind_columns(undef, \(@col{ @{$selecth->{NAME}}}));
my $insert = qq{INSERT INTO SomeOtherTable ($columnlist) VALUES (}
. '?' . '?' x $#col{ @{$selecth->{NAME}}} . ')';
my $inserth = $dbh2->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr";

while ($selecth->fetch) {
#do some manipulation if necessary
$inserth->execute(@col{ @{$selecth->{NAME}}}) || die "Can't execute
$insert: $DBI::errstr\n";
}

# or if you are making a pipe delimited file instead of inserting elsewhere:

while ($selecth->fetch) {
#do some manipulation if necessary
print outfile join('|', @{$selecth->{NAME}}}) . "\n";
}

Syntax untested in that example, but I use the principle sometimes. It gives
the advantage of the speed of bind_columns instead of fetchrow_hashref, and
the ability to access the columns by name, and it keeps all the columns in
order for the use in execute or print or whatever else might be useful. I
find it very slick when I need column names.

Steve H.


-Original Message-
From: Scott R. Godin [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 02, 2001 6:28 AM
To: [EMAIL PROTECTED]
Subject: Re: Column Names


In article <[EMAIL PROTECTED]>,
 [EMAIL PROTECTED] (Bart Lateur) wrote:

> On Thu, 01 Nov 2001 18:56:18 -0800, Venkataramana Mokkapati wrote:
>
> >How do I get column names and order of column names
> >for a "select * from ..." query.
>
> If you have
>
>   $sth = $dbh->prepare("select * from ...");
>
> then try
>
>   @column names = @{$sth->{NAME}};
>
> You may have to do an "execute" first, for this to return anything of
> value.
>
> It's in the DBI docs under the heading "Statement Handle Attributes", in
> the DBI POD formatted as text around line 2284.

the absolute neatest trick I've seen with this, that is so totally
perlish it defies description.. you stare at it for a bit and suddenly
all becomes clear.

   $sth->execute
or die("Cannot Execute SQL Statement: ", $sth->errstr(), "\n");

my $rows = $sth->rows;
 # only expecting one row for a unique ID . this should NEVER happen.
   safe_error("invalid number of rows returned from database ($rows) for
ID $id")
if $rows > 1;
# although this might...
safe_error("no match in database for ID $id")
if $rows < 1;

my %db;
$sth->bind_columns( \( @db{ @{ $sth->{NAME} } } ));# magic

while ($sth->fetch)
{
#... and no worries about which order the columns get returned in
#... since you access them via the $db{ColumnName} method :)

--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services  |web : http://www.webdragon.net/

RE: Copying image data from Sybase to Mssql or vice versa

[Steve Howard]

The 'Best way' may not be Perl. Is this a one time shot, or something where
the two servers need to interact constantly?

If this is one time, or something that needs to happen only periodically, I
would recommend Data Transformation Services (DTS). That is part of the MS
SQL installation if it is version 7.0 or higher. It is actually quite good
for transferring data between any two data sources where you can connect to
each either by ODBC or some OLE-DB compliant interface - neither one of them
have to be MS SQL.

It can be very simple, or you can put together pretty complex transformation
packages that can be stored and executed periodically. I'd recommend
starting out in the MS SQL Server Books Online for details on its use.

Hope this helps.

Steve H.

-Original Message-
From: Veera P. Nallamilli [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 02, 2001 9:53 AM
To: [EMAIL PROTECTED]
Subject: Copying image data from Sybase to Mssql or vice versa


I am using DBD:Sybase to interact with Sybase database
and using DBD:ODBC for Mssql database. I tried to copy
image data either way , but unfortunately failed. Could
anybody please suggest me like what is the best way
to transfer the data from sybase to Mssql or vice versa.

Thanks
prasad

RE: ::massive sql query using dbi - please help::

[Steve Howard]

The users_old table has 120,000 rows and the users_new has 910,000 rows.


If you have no indexes, I'm not at all surprised it takes that long or even
longer to get results from a join on MySQL on two tables with this many
rows. The join must be completed before results are returned, and that is a
long, processor intensive process without indexes.

Can e-mail addresses be a primary key on either table? if it can, it should
be. If not, it should at least be indexed.

Only suggestion I have beyond the indexes is don't use Legacy syntax - that
won't speed up the execution, but is just a good habit to have for when you
want to do more complex queries. Use this type of join syntax:

SELECT users_old.UserId, users_old.Email FROM
users_new INNER JOIN users_old ON users_old.Email = users_new.Email

aliasing will then save you a bit of typing, but that's secondary - indexing
your tables is what you need to improve the performance in this case.

Steve H.


-Original Message-
From: Hastie, Christa [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 02, 2001 12:58 PM
To: [EMAIL PROTECTED]
Subject: ::massive sql query using dbi - please help::


Hello to all!
This is my first time posting to this group!  But I'm in desperate need of
any help!
(BTW, thanks for all the emails from contributors to this list..I learn a
lot from you guys every day!)

I have two tables in a mySQL db, named users_old and users_new, both with
UserId and Email columns, no primary keys and no auto-increment columns.
The users_old table has numeric values for the UserId while the users_new
have NULL values.
The users_old table has 120,000 rows and the users_new has 910,000 rows.
I'm trying to find a simple, painless way of querying these two tables so I
can store the UserId and Email from the users_old table if the Email exists
in both tables.

Everything I try just continues to run without ever producing any results -
it just hangs at the command line when running the standalone query.
Perhaps there are just too many rows to compare.
I tried writing a simple script using the Perl DBI to just log the results
of this massive query in a simple tab delimited flat file so I can load the
data into the live database after it finishesbut no luck.

Anybody have any suggestions on a better approach?
My simple code looks like this:

#!/usr/local/bin/perl

use DBI;
use strict;

my($dbh);
my($sth);
my($exclusive_lock);

eval { $dbh = DBI->connect("DBI:mysql:dbname;host=localhost", "dbuser",
"dbpassword", {'RaiseError' => 1}); };
if($@) {
my($error) = "Error opening Database: $@\n";
print "$error\n";
}


my $sth = $dbh->prepare("SELECT users_old.UserId, users_old.Email FROM
users_new, users_old WHERE users_old.Email = users_new.Email");
$sth->execute or die "Unable to execute query: $dbh->errstr\n";
my ($row);

while($row = $sth->fetchrow_arrayref) {
my($data_log) = "/home/chastie/sony_showbiz.txt";
open (DATATEMP, ">>$data_log");
flock (DATATEMP, $exclusive_lock);
print LOG "$row->[0]\t$row->[1]\n";
close (LOG);
}

$sth->finish;
$dbh->disconnect;
exit;

\/
   (o o)
ooO-(_)-Ooo
christa hastie
programmer
sonypicturesdigitalentertainment
www.sonypictures.com
-

RE: Does perl has DBD for MS Access?

[Steve Howard]

You need DBD::ODBC to access MS Access using Perl and DBI. It can be
downloaded from CPAN, or if you are using ActiveState, or PPM you can use
them to search, locate download and install this module.

Steve H.

-Original Message-
From: Linda Xu [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 02, 2001 7:42 PM
To: DBI Users
Subject: Does perl has DBD for MS Access?



Hi,
Does perl has DBD for MS Access? Where I can download it?

Linda

RE: OLE exception

[Steve Howard]

What you are running is not a query. It is an ISQL script. It will work fine
in Query analyzer, but DBI can only prepare and execute one statement at a
time (Read Perldoc DBI). If you want to use a script like that, create a
stored procedure then you can execute it and get the results into your Perl
script.

Another option is do the manipulation in Perl, and execute each statement
separately. Forget the SQL variable declaration - handle all the variables
in Perl if you do it like that, and use placeholders to execute each.

Steve H.


-Original Message-
From: Konstantin Berman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001 3:23 AM
To: '[EMAIL PROTECTED]'
Subject: OLE exception


Hi.

I try to run the following query with DBD:ADO driver:
-
set nocount on

declare @beforedate datetime,
@date datetime,
@casinoid int,
@playertype INT,
@gamingserverID int,
@date2order datetime,
@Month integer,
@Year  integer,
@DAY INT,
@currdate datetime,
@strDate varchar(30)

SET @currdate = GetDate()
SET @Month = DATEPART(MM,@currdate)
SET @Year = Year(@currdate)
SET @DAY= DAY(@currdate)
SET @strDate = convert(varchar(2), @Month)+'/' + convert(varchar(2),
@DAY)+'/' + convert(varchar(4), @YeaR) +'  12:00PM'
SET @date=DATEADD(DD,-1, CONVERT(DATETIME,@strDate))


set @beforedate ='1999-01-01' /* do not change */
set @date2order = dateadd(d,-44,@date)/* -43*/
set @casinoid =207
set @playertype= 0
set @gamingserverID=32
print 'The @date2order is:'
print @date2order
print @date

select  count(t.PRTIME)[Purchases],
userid
into #ponly

from tb_purchaserequest t
where t.gamingserverid=@gamingserverID
and t.PRSTATUS=1
and t.PRTIME>=@beforedate
and t.PRTIME<=@date
and t.casinoid= @casinoid

group by t.userid

select  p.userid,
max(BIDAYMARKER)[LastPlayed],
 p.usdateopened,
sum(biincome) [Income],
sum(bipayouts) [Payouts],
sum(biincome-bipayouts) [Profit],
p.usaccountno

into #betplayer

from betinfo b,player p

where p.userid=b.userid
and p.gamingserverid=@gamingserverID
and b.gamingserverid=p.gamingserverid
and b.CASINOID=p.CASINOID
and b.CASINOID=@casinoid
and b.PLAYERTYPEID=@playertype
and b.PLAYERTYPEID=p.PLAYERTYPEID
and BIDAYMARKER >=@beforedate
and BIDAYMARKER <=@date

group by p.usaccountno,p.userid,p.usdateopened


select usaccountno,
usdateopened,isnull([Purchases],0)+isnull(tt.counts,0)[Counts],Income,Payout
s, Profit, [LastPlayed]--,tt.account ,tt.aleventid,[Purchases],tt.counts,
from #betplayer left join #ponly
on #betplayer.userid=#ponly.userid
left join (select  player.usaccountno[account],tb_adminlog.casinoid,
count( tb_adminlog.ALTIME)[counts],
tb_adminlog.userid
from tb_adminlog inner join player on tb_adminlog.userid=player.userid and
player.gamingserverid=tb_adminlog.gamingserverid  and
player.casinoid=tb_adminlog.casinoid
where (tb_adminlog.ALEVENTID=10036 or tb_adminlog.aleventid = 10002 or
tb_adminlog.aleventid = 5000) and
 tb_adminlog.casinoid=@casinoid

GROUP BY player.usaccountno,
tb_adminlog.casinoid,
tb_adminlog.userid ) as tt on #betplayer.userid = tt.userid
where #betplayer.[LastPlayed]>=@date2order
order by [Counts]desc, Profit desc
drop table #betplayer
drop table #ponly

---
I get the OLE exception while query analyzer says the query is ok. Can
anyone please explain me why?
Thanks in advance.

RE: multiple primary keys

[Steve Howard]

I assume by "Multiple primary keys" you mean a composite primary key. All of
the ones I've had even casual contact with support composite primary keys,
but only one primary key per table (so several columns can make up one
composite primary key).

The ones I've worked with deeply enough to know a little more about (Which
is only two PC based, and one mainframe based) also support unique
constraints which are similar to a primary key, and can be composite as
well, but they are not actually a primary key. An additional difference
between a unique constraint and a primary key (as I've worked with them
anyway) is that you can define columns in a unique constraint to be
nullable, but only one null will be allowed in the key column(s).
In other words, it can have a null, but only one since a second would not be
unique - even though a null doesn't equal a null. Confusing maybe, but
that's how they work.

Steve H.

-Original Message-
From: Vuillemot, Ward W [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 5:51 PM
To: [EMAIL PROTECTED]
Subject: multiple primary keys


I have a question regarding multiple primary keys. Do all DBs handle multple
primary keys?  In addition, can only one of the primary keys be
autoincremented?
I am curious how this pertains to mySQL where we have:
$sth->{'mysql_insertid'};

Is there a way to determine the name of column that was autoincremented?
Easily, that is. . . .ie, a simple command such
$sth->{'hashValueHere'};

Thanks,
Ward

RE: Quick Question

[Steve Howard]

You are putting your $sth in a lexical scope. It might also be better to
define the statement as a variable, then prepare it - just a thought, but
here it is:


my $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user,
$datpassword);
my $select;

if ($contractor_id eq "") {
$select  = qq{select blah1, blah2 from contractors where (username =
'$username') and
(password = '$password')
 };

 } #end if not contractor id

else {

$select = qq{select blah1, blah2 from ap_contractors where (id =
'$contractor_id')
 };

 } #end else
my $sth = $dbh->prepare($select);
$sth->execute();

When you use "my" to declare a variable within a lexical scope (basically,
in a block of code surrounded by curly brackets), that variable goes out of
scope outside the brackets. You used my within the if {} else{} so $sth is
out of scope outside that if statement. Declare the variable outside the
brackets, then modify it inside the brackets.

Steve H.

-Original Message-
From: Greg Thompson [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 22, 2001 8:36 PM
To: [EMAIL PROTECTED]
Subject: Quick Question


Hi, I'm new to the list and had a quick question regarding selection using
if statements. It would be much appreciated if anyone could point me in the
right direction.
I'm new to using Perl with MySQL and was wondering why it will not allow me
to do the following:
my $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user,
$datpassword);

if ($contractor_id eq "") {
my $sth = $dbh->prepare(qq{
select blah1, blah2 from contractors where (username = '$username') and
(password = '$password')
 });

 } #end if not contractor id

else {

my $sth = $dbh->prepare(qq{
select blah1, blah2 from ap_contractors where (id = '$contractor_id')
 });

 } #end else
$sth->execute();

I know this is probably simple, but I do not know why it does not perform
the selection based on my if statements. I've tried selecting stuff using
other if statements, and they did not work either. But once I take away the
if/else statement, it works fine, but then the problem remains that I need
to select different things based on if somethings true (which in the example
I gave above, the condition is if $contractor_id has a value or not).
Any input would be much appreciated.
Thanks,
Greg

RE: Execute with parameters ...

[Steve Howard]

Placeholders can't hold a table name or part of the query - these are
necessary in preparing. Placeholders can hold values. So your example is not
correct, but this would be:

 $sth = $dbh->prepare("SELECT foo FROM table1 WHERE baz=?");
  $sth->execute( 'hey' );

Does that make sense?

Steve H.


-Original Message-
From: Mortimer Hubin [mailto:[EMAIL PROTECTED]]
Sent: Saturday, October 13, 2001 11:25 PM
To: [EMAIL PROTECTED]
Subject: Execute with parameters ...


  In the dbi description & help it is explicitely written this:
  $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
  $sth->execute( $baz );

  but i'm trying this, and it's not working ... Is there any reason ?
  $sth = $dbh->prepare("SELECT foo FROM ? WHERE baz='hey'");
  $sth->execute( 'table1' );
  ...
  $sth->execute( 'table2' );
  ...

  Thanks
  Mortimer.

RE: [OT] Bulk Updates Using Joins or Some Such Nonsense

[Steve Howard]

OK. Here's the source. This is cut and pasted in from MS's Sql books Online
(The books that are installed with their product). I do not have the actual
standard so I have to trust their documentation. This is from the "From"
subsection of the UPDATE section:


The FROM clause supports the SQL-92-SQL syntax for joined tables and derived
tables. SQL-92 syntax provides the INNER, LEFT OUTER, RIGHT OUTER, and FULL
OUTER join operators.



That is what I used for my basis for claiming it to be "Standard syntax".

Steve H.


-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 10, 2001 10:39 PM
To: dbi-users
Subject: Re: [OT] Bulk Updates Using Joins or Some Such Nonsense


Alex Pilosov wrote:
>
> On Wed, 10 Oct 2001, Jeff Zucker wrote:
>
> > Steve Howard wrote:
> > >
> > > The Standard SQL syntax for updating based on a join is this
> > >
> > > UPDATE Table1
> > > Set Column1 = r.Column1, Column2 = r.Column2
> > > FROM Table1 l INNER JOIN Table2 r ON l.Column3 = r.Column3
> >
> > In which standard is that standard syntax? :-)
>
> ANSI SQL3,

Possibly, but not in any sources I've seen.  Could you provide me a
cite? (Not for pedantic purposes or because I'm "challenging" you, but
because I'd like to know the source).

> maybe even SQL2.

Definitely not.  See my reply to Steve.

--
Jeff

RE: [OT] Bulk Updates Using Joins or Some Such Nonsense

[Steve Howard]

That is the ANSI SQL standard.

-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 10, 2001 6:20 PM
To: dbi-users
Subject: Re: [OT] Bulk Updates Using Joins or Some Such Nonsense


Steve Howard wrote:
> 
> The Standard SQL syntax for updating based on a join is this
> 
> UPDATE Table1
> Set Column1 = r.Column1, Column2 = r.Column2
> FROM Table1 l INNER JOIN Table2 r ON l.Column3 = r.Column3

In which standard is that standard syntax? :-)

-- 
Jeff

RE: Bulk Updates Using Joins or Some Such Nonsense

[Steve Howard]

If I understand you, you're wanting to do an update based on a join. I can
give you standard syntax to do this, but when I tried it on the older
version of MySQL that I have here, it was not supported. I don't stay very
current with MySQL because I don't use it, so the version you are using
might support this. Try it and see. If not, this is one situation where I
would seriously look at another DBMS rather than frequently go through a
procedure to update 20,000 rows individually - but that's just an opinion -
don't flame me for it. (I understand it might not even be possible for you
to switch)

The Standard SQL syntax for updating based on a join is this

UPDATE Table1
Set Column1 = r.Column1, Column2 = r.Column2
FROM Table1 l INNER JOIN Table2 r ON l.Column3 = r.Column3


That should be very quick. In that one, Column3 would be the keys that match
between Table1, and Table2. You don't use the alias on the columns being
updated because the table to be updated is specified first, but you must use
the alias of the columns when you are setting that column to a value from
the other table in the join.

Does that make sense? Hope it will work for you - you'll just have to try it
to see.

Steve H.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 10, 2001 4:59 PM
To: [EMAIL PROTECTED]
Subject: Bulk Updates Using Joins or Some Such Nonsense


Dear All,

Using MySQL I'm trying to update field1 in table1 with data from field1 in
table2 where the primary keys in tables 1 and 2 match. In other words I have
2 tables the first with direction information and the second with speed
information both have time as the primary key and what I want is a single
TEMPORARY table with the direction and speed indexed by time. Table 1 is a
copy of the speed information with an extra field to accommodate the
direction information.

So far I've tried

$query = qq{SELECT f_dir.t_table1, f_speed.t_table2 WHERE f_time.t_table1 =
f_time.t_table2};

while my $hasharray = 

{

$query = qq{UPDATE...

}

which is very slow (the tables contain 20 000 records).

Another option would be to select the data into a text file, delete existing
data from the table1 and bulk load the text file into the table, which seems
extremely clumsy.

Is there an option I've missed?

Ta,
Scott

RE: Select X number of rows

[Steve Howard]

One correction, I forgot to alias the table in the third example (That's
what I get for typing straight into the body.
Should be:

SELECT o.Column1, o.Column2, o.Column3 FROM Sometable o
WHERE (SELECT Count(*) FROM Sometable i
WHERE i.Column1 < o.Column1)
BETWEEN 31 AND 40

Still ugly however you look at that one. :-(

Steve H.


-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 7:08 PM
To: Purcell, Scott; [EMAIL PROTECTED]
Subject: RE: Select X number of rows


Three suggestions depending on the DBMS you are using:

1. This method is supported by MS SQL 7.0 or later:

SELECT TOP 20 Column1, Column2, Column3 FROM Sometable
WHERE Column1 NOT IN
(SELECT TOP 40 Column1 FROM Sometable
ORDER BY Column1)
ORDER BY COLUMN1

That will give you rows 41-60.

2.  Using MySQL or PostGreSQL:

SELECT Column1, Column2, Column3 FROM Sometable
ORDER BY Column1 LIMIT (20, 20)

Gets results from rows 21-40.

3. This next will work from most DBMS's, but this is a relative dog
performance wise. If you have a DBMS specific way of paging through the
results, I recommend it over this, but if there is no other way, then do
this:

SELECT o.Column1, o.Column2, o.Column3 FROM Sometable
WHERE (SELECT Count(*) FROM Sometable i
WHERE i.Column1 < o.Column1)
BETWEEN 31 AND 40

That will get you result rows number 31-40 inclusively (10 rows).

In any of those methods, it is imperative that you have a primary key for
them to work.

Other DBMS's may have different methods for doing this. See your docs for
that.

To page through the results, send a hidden field with your page to let you
know where your start number is, then just issue the query the next time
with the numbers so that you can get the next page of results.

Does this help?

Steve H.

-Original Message-
From: Purcell, Scott [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 7:36 AM
To: '[EMAIL PROTECTED]'
Subject: Select X number of rows


Hello,
I am a Perl guy, not a DBA. Anyway, if I have a couple of DBs with X amount
of records, can I select from three databases, acquire the first 20 records
(sort by ASC), then (show them on the web) and when they hit next, show the
next 20, eg. 21-40 and so on and so on. Also, If that is doable, how do I
know how many pages. Is there some command in SQL that would tell me how
many records are in the three DBS without me having to acquire and count all
the records first?

I hope this is not too much of a SQL question, but as I am building this in
Perl.  I hope I do not offend anyone with this morning off-perl question.


Thanks you very much,

Scott Purcell

RE: Select X number of rows

[Steve Howard]

Three suggestions depending on the DBMS you are using:

1. This method is supported by MS SQL 7.0 or later:

SELECT TOP 20 Column1, Column2, Column3 FROM Sometable
WHERE Column1 NOT IN
(SELECT TOP 40 Column1 FROM Sometable
ORDER BY Column1)
ORDER BY COLUMN1

That will give you rows 41-60.

2.  Using MySQL or PostGreSQL:

SELECT Column1, Column2, Column3 FROM Sometable
ORDER BY Column1 LIMIT (20, 20)

Gets results from rows 21-40.

3. This next will work from most DBMS's, but this is a relative dog
performance wise. If you have a DBMS specific way of paging through the
results, I recommend it over this, but if there is no other way, then do
this:

SELECT o.Column1, o.Column2, o.Column3 FROM Sometable
WHERE (SELECT Count(*) FROM Sometable i
WHERE i.Column1 < o.Column1)
BETWEEN 31 AND 40

That will get you result rows number 31-40 inclusively (10 rows).

In any of those methods, it is imperative that you have a primary key for
them to work.

Other DBMS's may have different methods for doing this. See your docs for
that.

To page through the results, send a hidden field with your page to let you
know where your start number is, then just issue the query the next time
with the numbers so that you can get the next page of results.

Does this help?

Steve H.

-Original Message-
From: Purcell, Scott [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 7:36 AM
To: '[EMAIL PROTECTED]'
Subject: Select X number of rows


Hello,
I am a Perl guy, not a DBA. Anyway, if I have a couple of DBs with X amount
of records, can I select from three databases, acquire the first 20 records
(sort by ASC), then (show them on the web) and when they hit next, show the
next 20, eg. 21-40 and so on and so on. Also, If that is doable, how do I
know how many pages. Is there some command in SQL that would tell me how
many records are in the three DBS without me having to acquire and count all
the records first?

I hope this is not too much of a SQL question, but as I am building this in
Perl.  I hope I do not offend anyone with this morning off-perl question.


Thanks you very much,

Scott Purcell

RE: selector screws do in ODBC 0.28

[Steve Howard]

That explanation makes perfect sense. The restriction being in the SQL
Server driver and not on ODBC itself is a good distinction.

Thanks  :-)

Steve H.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
Nick Gorham
Sent: Saturday, September 29, 2001 1:29 PM
To: Steve Howard
Cc: Tim Harsch; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: selector screws do in ODBC 0.28


Steve Howard wrote:

> Nick,
>
> Picky is good. It was my understanding that this was an ODBC restriction.
> I've also seen the same error in the test bed in applications written in C
> and Java and that was the explanation we had come to accept

No its a restriction of the SQL Server ODBC driver, not ODBC itself, there
is a SQLGetInfo
call (SQL_MAX_CONCURRENT_ACTIVITIES) that allows the application to check on
this. I have to
use this in both our SQLEngine and Kylix dbExpress driver to alter the
operation, and create
additional connections as required.

> I'm not arguing with you, but if the restriction is with SQL Server, then
my
> understanding of something else needs to change. I might have a nebulous
> grasp on how this can be, but I would like to hear from someone else to
> (match|firm) up what I'm thinking.

Well the restriction is with TDS the network protocol that SQL Server (And
Sybase) uses. put
simply in non dynamic mode one a query is executed dat just comes back, and
keed comming
untill the end, so there is no way (other that a cancel) to do anything else
on that
connection until all the results have come back. The ODBC driver checks this
for forward
only stmts, but for dynamic it doesn't. With these the data comes back a row
at a time, so
you can in theory muliplex statements. However the problem is that unless
all the data is
returned the protocol stream gets confused, hence the spin wait.

> The following ISQL script does the same sort of thing (it's bad practice
to
> grant permissions to individual users, but it works as an example), but
will
> not return an error, even though the grant is being executed while the usr
> cursor is still active in the established connection. Looking at the
current
> activity while it takes place, it looks as if it handles it as a whole and
> not as individual statements (That's my current theory on how it's
possible
> in an ISQL script), but profiler still shows a statement being prepared,
> executed, and individual fetches being done, and the grant statement being
> executed while usr is still active, and I don't see any additional
> connections being made - only the existing connection is referred to. Do
you
> have a moment to take on explaining this?

Well I would guess its because the script is operating on the server where
the protocol
restrictions dont apply, so there will be no such problem.

Of course I could be wrong, this is all just summise. MS could give you a
exact answer, but
I doub't they would :-)

--
Nick Gorham
Emacs would be a great operating system if it just had a decent text
editor...

RE: selector screws do in ODBC 0.28

[Steve Howard]

Nick,

Picky is good. It was my understanding that this was an ODBC restriction.
I've also seen the same error in the test bed in applications written in C
and Java and that was the explanation we had come to accept.

I'm not arguing with you, but if the restriction is with SQL Server, then my
understanding of something else needs to change. I might have a nebulous
grasp on how this can be, but I would like to hear from someone else to
(match|firm) up what I'm thinking.

The following ISQL script does the same sort of thing (it's bad practice to
grant permissions to individual users, but it works as an example), but will
not return an error, even though the grant is being executed while the usr
cursor is still active in the established connection. Looking at the current
activity while it takes place, it looks as if it handles it as a whole and
not as individual statements (That's my current theory on how it's possible
in an ISQL script), but profiler still shows a statement being prepared,
executed, and individual fetches being done, and the grant statement being
executed while usr is still active, and I don't see any additional
connections being made - only the existing connection is referred to. Do you
have a moment to take on explaining this?




DECLARE usr CURSOR FOR SELECT name FROM sysusers WHERE issqluser = 1
DECLARE @usrname varchar(50), @exec varchar(50)

OPEN usr
FETCH NEXT FROM usr INTO @usrname

WHILE @@fetch_status != -1
BEGIN
IF @@fetch_status != -2
BEGIN
SELECT @exec = 'GRANT SELECT ON Customers TO ' + 
@usrname
exec(@exec)
END
FETCH NEXT FROM usr INTO @usrname
END
CLOSE usr
DEALLOCATE usr




Thanks,

Steve H.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
Nick Gorham
Sent: Saturday, September 29, 2001 5:12 AM
To: Steve Howard
Cc: Tim Harsch; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: selector screws do in ODBC 0.28


Steve Howard wrote:

> If I'm following through this correctly, this is not a bug. ODBC only
allows
> one active statement per connection. You are using the $hDB database
handle
> for this statement:

Well to be picky, its not ODBC but SQL Server that has that restriction, you
"can" get around it by requesting a non forward only cursor on the
statement,
(ask for dynamic, and it will fall back to something lesser). However,
beware,
I can show a a 10 ODBC call program that can put SQL Server into a spin wait
doing that :-(

--
Nick Gorham
Emacs would be a great operating system if it just had a decent text
editor...

RE: db independent way of detecting duplicates?

[Steve Howard]

I had a while tonight and thought I'd take this one on with perhaps more
than you actually asked. This is a hasty re-write of something I wrote and
use at work. Basically, I always try to develop a $where, and a $tablename,
and a $columnlist and an @values when I'm creating a SQL statement - whether
I am getting column lists from the DBI function, or whatever method. If I
follow that basic rule in preparing the statements, it makes a functions
like these easy to use. I made one modification to what I use at work
because I work with an RDBMS that can handle sub-selects, and don't really
care to be db independent - so I use an EXISTS in the select. MySQL will not
handle sub-selects so a count is how I would handle making that compatible -
not as fast as an EXISTS, but more db independent.

Additionally, I would normally use system catalogues or stored procedures to
look up primary keys and unique constraints. This is different among DBMS's.
On the particular DBMS I use most, I would probably use the sp_helpindex
stored procedure to find all Primary key and unique constraint columns. The
lookup will differ from DBMS to DBMS, and also the existence of unique
constraints that are not primary keys. So far as I know, MySQL or Access do
not have such a constraint, MS SQL definitely does, and I'll let someone
else comment on whether other DBMS's have unique constraints that are not
primary keys. Checking for Unique constraints is why there is the null
checking in the foreach loop in the get_dups subroutine - Unique constraints
can have a single null entry whereas a primary key can never have a null
entry.

Anyway, I told you a few problems with it, and yes, I'm counting (rather
than just checking existence), and this is a hasty re-write, but hopefully
it gives what I think is a good method of avoiding a key conflict error.

Steve H.



###
# Preliminary stuff like connect
# and define test data for demo
# this is test data only. Use a more elegant method
# for real application.
###

use DBI;
my $tablename = 'customers';
my $columnlist = 'CustomerID, CompanyName, ContactName, ContactTitle,
Address, City, Region, PostalCode, Country, Phone, Fax';
my $keylist = 'CustomerID';
my @keyvals = ('ALFKZ');
my @values = ('ALFKZ', 'Alfreds Futterkiste',  'Maria Anders','Sales
Representative',
   'Obere Str. 58', 'Berlin', undef, '12209', 'Germany',
'030-0074321', '030-0076545');

my $dbh = DBI->connect($dsn,'sa','') || die $DBI::errstr;
my $dbh1 = DBI->connect($dsn:ODBC:northwind','sa','') || die $DBI::errstr;

###
#Call the sub to check for dups
###

$conflict = get_dups($keylist, \@keyvals, $tablename);

($conflict) ? (conflict_exists()) : (do_insert($columnlist, \@values,
$tablename));

sub get_dups {
my @columns = split /,/, $_[0];
my $where = 'WHERE ';
my @exvals;
foreach (0..$#{$_[1]}) {
if (defined $_[1]->[$_]) {
   $where .= "$columns[$_] = ? AND ";
   push (@exvals, $_[1]->[$_]);
   } else {
   $where .= "$columns[$_] IS NULL AND ";
   }
}
$where =~ s/AND\s*$//;  # get rid of last and;

my $select = qq{SELECT COUNT(*) FROM $_[2] $where};
my $selecth=$dbh1->prepare($select) || die "Can't
prepare\n$select\n$DBI::errstr\n";
$selecth->execute(@exvals);
return ($selecth->fetchrow_array);



}   #end sub get_dups

sub do_insert {
##
#Create the insert statement
##
$values = '?' . ',?'x $#{$_[1]};
my $insert = qq{INSERT INTO $_[2] ($_[0]) VALUES ($values)};

#
#prepare and execute
#
my $inserth = $dbh->prepare($insert) || die "Can't
prepare\n$insert\n$DBI::errstr\n";
$inserth->execute(@{$_[1]}) || die "Can't
execute\n$insert\n$DBI::errstr\n"
}   #end sub do_insert


sub conflict_exists {
# do something to prevent an error
}





-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 28, 2001 4:06 PM
To: [EMAIL PROTECTED]
Subject: db independent way of detecting duplicates?


I'm wondering what other people have developed as database
independent way(s) of determining if the insert or update being
done results in a duplicate key situation.

I can think of 2 methods to handle this:
  1. before doing the insert or update, for *EACH AND EVERY*
 unique key defined for a table, do a select with the where
 clause looking for equality on all the fields in the unique
 key - if any of those selects find rows, then your insert
 or update would cause a duplicate.

  2. allow the database to detect the "duplicate key" error -
 when you have RaiseError turned on and you do the insert
 o

RE: selector screws do in ODBC 0.28

[Steve Howard]

If I'm following through this correctly, this is not a bug. ODBC only allows
one active statement per connection. You are using the $hDB database handle
for this statement:


my $sth = $hDB->prepare( 1, PrintError => 1 } ;
my $dbistr = "DBI:ODBC:HOMER";
$dbistr .= ";TargetUser=$user;TargetAuth=$password" if
defined $user or defined $password;
my $hDB = DBI->connect( $dbistr, undef, undef, $h )
or die $DBI::errstr;

my $hDB2 = DBI->connect( $dbistr, undef, undef, $h )
or die $DBI::errstr;
# ERROR below at DO if this is uncommented
($user) = $hDB2->selectrow_array( "select user_name()"
);
$hDB2->disconnect;

# check database name is a legit database
my $dbname = $opt_D || $ENV{DBPASSWORD};
eval { local $hDB->{PrintError} = 0; $hDB->do( "use
$dbname" ); };
die "Unable to find database '$dbname'\n" if( $@ );

# check grantee is a legit user
die "Error: User '$opt_G' does not exist in database
'$opt_D'" unless
$hDB->selectrow_array( "select 1 from sysusers where
name = '$opt_G'" );

my $sth = $hDB->prepare( fetchrow_arrayref ) {
my @params;

print "The following actions have been performed:\n"
unless $c++;
perms( 'select', $aref->[0], $opt_G )
if defined $opt_s || defined $opt_w || defined
$opt_r;
perms( 'update', $aref->[0], $opt_G )
if defined $opt_u || defined $opt_w;
perms( 'delete', $aref->[0], $opt_G )
if defined $opt_d || defined $opt_w;
perms( 'insert', $aref->[0], $opt_G )
if defined $opt_i || defined $opt_w;
} # end while

sub perms( $ $ $ ) {
my $perm = shift;
my $object = shift;
my $grantee = shift;
# ERROR here if code above is uncommented
$hDB->do( "grant $perm on $object to $grantee" );
print "grant $perm on $object to $grantee\n";
} # end sub

1; # Ancient Druid Custom



__
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone.
http://phone.yahoo.com

RE: I don't seem to be able to 'SET IDENTITY_INSERT {table} ON'using DBI::ODBC.

[Steve Howard]

If you're using MS SQL it won't work. I've been told by someone that using
DBI, you can set IDENTITY_INSERT on and off on Sybase, but I can't confirm
that for you.

using MS SQL, you don't have any of the connection specific items like
IDENTITY_INSERT or ANSI_NULLS or connection specific temporary tables
available. For Temp tables, you can use global temp tables. When I need to
set IDENTITY_INSERT on for a table, I usually use OLE and ADO (That's the
only time I use OLE and ADO instead of DBI).

Reading through Perldoc DBI I think it is due to the way DBI does not
actually recognize a connection - at least it doesn't make a connection the
same way constant the way MS SQL needs it to be for connection specific
settings and temp tables.

Hope someone else can prove me wrong on this one.

Steve H.

-Original Message-
From: Pat Sheehan [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 25, 2001 9:28 AM
To: [EMAIL PROTECTED]
Subject: I don't seem to be able to 'SET IDENTITY_INSERT {table}
ON'using DBI::ODBC.


I don't seem to be able to 'SET IDENTITY_INSERT {table} ON' using DBI::ODBC.
Anybody have suggestions?
Many Thanks!!

RE: Avoid Error if no results

[Steve Howard]

Where is the dberror function? Is that part of your script?

Where ever it is, you are calling it when you get 0 results returned. Is
that what you are wanting to do? It doesn't sound like it from reading your
description. If you don't want that function called when you have 0 results,
then alter the two selectrow_array lines like this:

$active = $dbh->selectrow_array($sqlquery);

and
$expire  = $dbh->selectrow_array($sqlquery);


In that case, $expire will equal 0 when the count is 0 and the dbError
function is not called when 0 rows are returned.

If you want the error returned when there really is an error, but not get an
error when 0 rows are returned, then handle the statements like this:

$sth = $dbh->prepare($sqlquery) || die "Can't prepare $sqlquery\n
$DBI::errstr";
$sth->execute() || die "Can't execute $sqlquery\n $DBI::errstr";
$active = $sth->fetchrow_array();

# do something with $active now.

Does this help?

Steve H.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Saturday, September 22, 2001 6:38 PM
To: [EMAIL PROTECTED]
Subject: Avoid Error if no results


Hi All,

this one seems to puzzle me on how to avoid an unnecessary db error. I need
to
run a tally count on a couple tables, and if there isn't any data in the
table
it displays the dbError, altho there isn't technical any syntax error in the
query other then no return or results.

my $active = 0;
my $expire = 0;

$sqlquery = qq|SELECT COUNT(m.memid) FROM members m,payhistory p
   WHERE p.active = 'Y' AND p.memid = m.memid|;
$active = $dbh->selectrow_array($sqlquery) or dbError();

$sqlquery = qq|SELECT COUNT(*) FROM expired WHERE expdate < CURDATE()|;
$expire  = $dbh->selectrow_array($sqlquery) or dbError();

Is this way to avoid the dbError() if the query returns no results(which
would
indicate a '0' tally.

thx's

Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225)686-2002
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

RE: About DBI and Oracle

[Steve Howard]

This might be a little more fun to respond to than I thought because the
characters look different when I past them into the e-mail. I'll try to type
over them and make them look right."

$state->execute || die "can't execute\n$sth->errstr\n";
print join(', ', @{$state->{NAME}});
$state->bind_columns(undef, \(@val{@{$state->{NAME}}}));

while ($row = $sth->fetchrow_arrayref) {
  print  $val{USER_ID} . "\n";
  }

What I am doing there is dereferencing a reference returned by the NAME call
in the statement handle ($state in this case) Dereferencing it returns a
list of column names as I put in the print join statement. You can carry
this out as I did in the bind_columns function and use this function to bind
the columns so that you can refer to them by the original name. This should
work faster than the way a fetchrow_hashref works.

In your case when you do this, you can get the value of your columns in
$val{USER_ID}, $val{PASSWORD}, $val{KOKYAKU_CD} etc.

Does this give you what you need?

Steve H.

-Original Message-
From: Karina Nahagama [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 11, 2001 11:51 PM
To: [EMAIL PROTECTED]
Subject: About DBI and Oracle


My name is Karina Nagahama.
I want to know about DBI.
I'm working on Windows NT, Apache, Perl, mod_perl Oracle8 and DBI.

I'm using DBI in a .pm file.
In order to select information from the database I wrote wrote the next
sentences:

  my $state = $dbhandler->prepare(q{SELECT USER_ID, PASSWORD, KOKYAKU_CD,
KYOTEN_CD FROM T_M_WEB_USER});
  $state->execute();

Then in order to read the information selected, I wrote the next sentences:

  while (my $record = $state->fetchrow_arrayref){
print 'User ID : ', $record->[0]; #[USER_ID]
print '   PASSWORD : ', $record->[1]; #[PASSWORD]
   }

  In this case I have to pick-up the field information with the field number
$record->[0].
  I want to pick-up the selected information by calling the fields by their
names.
  How can I pick-up the field information with the field name ? (for example
USER_ID)

Sorry for trouble you.
[EMAIL PROTECTED]

FW: Can DTS packages be run from Perl?

[Steve Howard]

Sorry, Forgot to hit Reply all.

-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 03, 2001 8:31 PM
To: Terry Witherspoon
Subject: RE: Can DTS packages be run from Perl?


If you are executing the package on a Win32 machine, you are probably going
to have to use the DTSRun utility that comes with SQL. Look at SQL Server
Books online for the syntax of the DTSRun commands. To find the specific
syntax and switches, go to SQL Server Books Online (Installed with an MS SQL
Standard Installation), go to the Index tab, look up DTS, Package
Execution).

These are usually run from command prompt, so to use them in Perl, you just
need to use one of the methods of executing a shell command - different
according to what you want for output, and how you want to utilize it.
Backticks are probably the easiest, but not necessarily the best, and
certainly not the only way to drop a command through to the shell.

Steve H.

-Original Message-
From: Terry Witherspoon [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 03, 2001 11:24 AM
To: [EMAIL PROTECTED]
Subject: Can DTS packages be run from Perl?



Hi,

Does anyone know of a way to run a DTS package from perl?

TIA, TW

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

RE: Using timestamp as primary key?

[Steve Howard]

Safe? If there is no possibility that any two rows can have the same time.
If so, then its safe.

The better question is: Is the data in that row in that table defined by the
timestamp? or by another column, or combination of columns in the table? Or
possibly they only need a sequence number to identify them uniquely? It is
possible to choose a primary key, and have it be completely meaningless. I
suppose there are times when the time stamp might really define the row, so
the question is just for a guideline for you - not intended to put down.

This might not be an issue we want to discuss too deeply on-list (It's a
database design question rather than a DBI question), but if you're
interested I might discuss it with you in detail off list.

Steve H.

-Original Message-
From: Rajeev Rumale [mailto:[EMAIL PROTECTED]]
Sent: Sunday, September 02, 2001 9:21 PM
To: Steve Howard; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Using timestamp as primary key?


Hi,

Thank you all for anwering me preious question. I am hope ful that I will
get answer for my next one also.

I want to know if it is safe enough to  use a field with "timestamp" type as
primary key for a table.
Will it cause any problem while processing muiltple requests?

with regards

Rajeev


Happiness, Happiness,
The greatest gift that I possess,
I thank the Lord that I've been blessed,
With more than my share of happiness.






- Original Message -
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "Rajeev Rumale" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Saturday, September 01, 2001 10:57 PM
Subject: RE: counting no. of records matching condition.


> Unless I'm missing something, you cannot do this all with one query. You
can
> do it with two queries, but I'm not sure that would be any more efficient
> than handling each with a separate query. The reason you can't get it all
in
> one query is that one of these will require a "Group by" clause, and since
> you are wanting results that have nothing to do with either the aggregate
or
> the group by, trying to combine that will throw an error. This is untested
> since I didn't actually have your table to work with, but I tested the
> concept to be sure I was right on that before I typed this in:
>
> Query 1 (Gives total number of employees, and those present, and those
> absent):
>
> SELECT COUNT(*) as TOTAL,
> (SELECT COUNT(*) FROM Table WHERE status = 'P') as PRESENT,
> (SELECT COUNT(*) FROM Table WHERE STATUS IN ('L', 'A', 'O')) AS ABSENT
> FROM Table
>
> Query 2 (Gives the number of employees of each type):
>
> SELECT type, COUNT(*) as NUMBER
> FROM Table
> GROUP BY type
>
> Now, just embed those into your Perl and you can get the results you are
> looking for into a report.
>
> Hope this helps,
>
>
> Steve H.
>
> -Original Message-
> From: Rajeev Rumale [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 29, 2001 10:51 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: counting no. of records matching condition.
>
>
> Greeting every one,
>
> I need to know the best way to count the number of occurances of multiple
> fields in a single table matching some conditions.
>
> For example I have a table consisting of fields
> id, name, type, status,
>
> Here the type field can have values
> W-Worker,
> S-supervisior
> M-Manager
> A-Adminstrative staff
> T-Tempory / Contract worker
>
> And the status field can have values
> P-present on duty
> L-On Leave
> A-Absent (without applying leave )
> O-Off duty (long vacation given for perticular positions as per
> company terms)
>
>
>
> Here I need to calculate
> 1. Total no of emplaoyees,
> 2. No employees of each type,
> 3. No. of employees Present on duty,
> 4. No. of employees Absent.
>
> Can we do this with a single sql statement.  Currently I am using one for
> each of the condition, which definately not a good way.  The other way I
can
> look for is to fetch all records and then do calcuations in the Perl
Script.
>
> I am use MySql, with Active Perl on a win2k and IIS.
>
> Kindly suggest.
>
> Regards
>
> Rajeev Rumale
>
> --
--
> -
> Your diamonds are not in far distant mountains or in yonder seas; they are
> in your own backyard, if you but dig for them.
>
>
> -
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before
> posting. To request this thread, e-mail
[EMAIL PROTECTED]
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail [EMAIL PROTECTED] instead.
>
>

RE: counting no. of records matching condition.

[Steve Howard]

Unless I'm missing something, you cannot do this all with one query. You can
do it with two queries, but I'm not sure that would be any more efficient
than handling each with a separate query. The reason you can't get it all in
one query is that one of these will require a "Group by" clause, and since
you are wanting results that have nothing to do with either the aggregate or
the group by, trying to combine that will throw an error. This is untested
since I didn't actually have your table to work with, but I tested the
concept to be sure I was right on that before I typed this in:

Query 1 (Gives total number of employees, and those present, and those
absent):

SELECT COUNT(*) as TOTAL,
(SELECT COUNT(*) FROM Table WHERE status = 'P') as PRESENT,
(SELECT COUNT(*) FROM Table WHERE STATUS IN ('L', 'A', 'O')) AS ABSENT
FROM Table

Query 2 (Gives the number of employees of each type):

SELECT type, COUNT(*) as NUMBER
FROM Table
GROUP BY type

Now, just embed those into your Perl and you can get the results you are
looking for into a report.

Hope this helps,


Steve H.

-Original Message-
From: Rajeev Rumale [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 10:51 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: counting no. of records matching condition.


Greeting every one,

I need to know the best way to count the number of occurances of multiple
fields in a single table matching some conditions.

For example I have a table consisting of fields
id, name, type, status,

Here the type field can have values
W-Worker,
S-supervisior
M-Manager
A-Adminstrative staff
T-Tempory / Contract worker

And the status field can have values
P-present on duty
L-On Leave
A-Absent (without applying leave )
O-Off duty (long vacation given for perticular positions as per
company terms)



Here I need to calculate
1. Total no of emplaoyees,
2. No employees of each type,
3. No. of employees Present on duty,
4. No. of employees Absent.

Can we do this with a single sql statement.  Currently I am using one for
each of the condition, which definately not a good way.  The other way I can
look for is to fetch all records and then do calcuations in the Perl Script.

I am use MySql, with Active Perl on a win2k and IIS.

Kindly suggest.

Regards

Rajeev Rumale


-
Your diamonds are not in far distant mountains or in yonder seas; they are
in your own backyard, if you but dig for them.

RE: Perl DBI for SQL Server 7 ?

[Steve Howard]

You got everything right, but I think there is a typo in what you are using.
You need DBI, then DBD::ODBC. Your connection string is correct after DBI
and DBD::ODBC are installed and the DSN is configured.

Steve H.

-Original Message-
From: Argenis Pèrez [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 11:26 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Perl DBI for SQL Server 7 ?



I am using DBIODBC
you need installing DBI
first you need make a DSN for ODBC
after,in the code

use DBI;
$database = (DBI->connect('DBI:ODBC:yourDSN',
 'user',
 'password'));

this, function good whith SQL SERVER.

Bye


-Mensaje original-
De: Steven Vargas [mailto:[EMAIL PROTECTED]]
Enviado el: martes, 28 de agosto de 2001 23:28
Para: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Asunto: RE: Perl DBI for SQL Server 7 ?


Other's can verify this, but I think the DBD-ODBC driver is what you're
looking for.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 28, 2001 2:20 PM
To: [EMAIL PROTECTED]
Subject: Perl DBI for SQL Server 7 ?



I'm looking for a Perl DBI for SQL Server 7.  I'm very happy with ActivePerl
version 5.6.1, but I couldn't find any Perl DBI for SQL Server 7 in Active
State's site.

Same for CPAN and the Perl DBI FAQ, couldn't find anything on a Perl DBI for
SQL Server 7.

Thanks for your help.

Ed

[EMAIL PROTECTED]

RE: another performance question.

[Steve Howard]

Thanks for that answer and that clarification. I think I got it now. Very
nice.

Thanks again,

Steve H.

-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 27, 2001 3:54 AM
To: Tim Bunce
Cc: Steve Howard; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: another performance question.


On Sun, Aug 26, 2001 at 09:38:55PM +0100, Tim Bunce wrote:
> >
> > So what I don't understand is what how bind_columns is dereferencing
this in
> > such a manner as to allow me to refer to the columns as scalars WITHOUT
> > showing seeming to show any overhead.
>
> The magic of aliasing. Watch:
>
>   # create the row buffer (done once at prepare/execute time)
>   $row_field_array = [];
>
>   # bind a column of the row buffer to a scalar variable
>   *bind_column_value = \$row_field_buffer->[4];
>
>   # fetch the fields of the current row
>   foreach (0..9) {
>   $row_field_buffer->[$_] = $_ * 100;
>   }
>
>   print "$bind_column_value\n";
>
> Run it and it prints 400. Magic.

Just to clarify this, I should add that after the aliasing then
$bind_column_value and $row_field_buffer->[4] are *the same variable*.
There's no copying involved.

Tim.

RE: another performance question.

[Steve Howard]

Not the case in this question. What is being returned is the reference -
values other than a single reference are not being copied. This is how
fetchrow_arrayref works as well - only a reference to the array is returned,
but when columns are bound, the reference must be dereferenced somehow so
that I get to the values of the columns as I bound them. I expected there to
be noticeable overhead there, and was thinking by not binding columns, and
dereferencing the returned reference directly I should be able to avoid that
small overhead. My question is aksed because there is NO difference - not
that the direct dereference is slower.

Try what I wrote. You'll get an array reference as the value of $row - not
an array. so

$row = $selecth->fetch; #returns a reference - not an array

is not the same as:

@row = $selecth->fetchrow;  #copies into an array.

So what I don't understand is what how bind_columns is dereferencing this in
such a manner as to allow me to refer to the columns as scalars WITHOUT
showing seeming to show any overhead.

Steve H.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Sunday, August 26, 2001 10:35 AM
To: Steve Howard; [EMAIL PROTECTED]
Subject: Re: another performance question.




-- Steve Howard <[EMAIL PROTECTED]> on 08/26/01 10:08:23 -0500


> dereferencing in the code (is there no overhead to the binding of
> columns?).

Binding uses pre-allocated space to return the result and is
usually faster.  fetch_blah calls have to allocate space for
the result, which is what slows them down.

sl

another performance question.

[Steve Howard]

I have been running a benchmark on binding columns and working with the
results as opposed to directly dereferencing the returned reference. My
hypothesis was that the binding of columns must have some overhead
associated with it that could be avoided by directly dereferencing the
returned reference from $sth->fetch or $sth->fetchrow_arrayref. The results
are inconclusive at best. Sometimes one finishes faster, and sometimes the
other. That's fine, but can someone explain to me HOW bind_columns is able
to return the results as fast as directly dereferencing in the code (is
there no overhead to the binding of columns?).

Using two statement handles: source is $selecth and target is $inserth:



while ($row = $selecth->fetch) {
$inserth->execute(@$row);
}





$selecth->bind_columns(undef, \(@val[0..13]));
while ( $selecth->fetch) {
$inserth->execute(@val);
}



Only those two blocks are benchmarked, and the benchmark times average to
almost exactly the same.

Anyway, not earth shattering, but if someone can explain how bind_columns is
doing this as quickly as the direct dereference, (or maybe this is being
returned because my sample size is too small) I'd appreciate it.

Thanks,

Steve H.

RE: dbi and dbcc on WinNT 4.0

[Steve Howard]

I don't use DBI to do this, but this will work, and is how I normally do the
database maintenance jobs on my databases.

open (logfile, ">e:/tasks/${date}-maint.txt") || die "$!";
# put other DB maint stuff here

# Use back-ticks to bring in the output of the command:

$str = `ISQL -Usa -P -Q"DBCC CHECKDB($dbname)"`;

print logfile $str."\n";
# finish the job

If you'd rather, that can be done with an ISQL script with output redirected
to a log file, and then use Perl to parse that (into another database is my
preference). I suppose that's up to you.


Steve H.


-Original Message-
From: Tobias Hausmann [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 20, 2001 8:46 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: dbi and dbcc on WinNT 4.0


Hi,

Right now, I am working on a dbcc perl script for a Sybase database. I
have noticed that I get a different output with dbi (do(do checkdb ...))
than with isql. What can I do to get the same output with dbi as with
isql. I plan to use the output to check the database for any kind of
inconsistencies.

Any help would be highly appreciated.

Tobias Hausmann


# check database integrity
sub checkdbintegrity
{ 
  my @searchlist = qw (Msg Level State error: corrupt);
  my $sth1 = $dbh->prepare("select name from sysdatabases");
  $sth1->execute();
  while( my $dbname = $sth1->fetchrow_array)
  {
 $sth = $dbh->do("dbcc checkdb ($dbname)")

 #process output 
 $dbh->errstr(); 
 .
  }
}

isql output:

Checking master
Checking sysobjects
The total number of data pages in this table is 4.
Table has 64 data rows.
Checking sysindexes
The total number of data pages in this table is 7.
Table has 76 data rows.
Checking syscolumns
The total number of data pages in this table is 17.
__
dbi do(checkdb checkdb(db)) output:

Server message number=2536 severity=10 state=2 line=1 text=Checking 
master
Server message number=2536 severity=10 state=3 line=1 text=Checking 
sysobjects
Server message number=2579 severity=10 state=1 line=1 text=The total 
number of d
ata pages in this table is 4.
Server message number=7929 severity=10 state=1 line=1 text=Table has 64 
data row
s.

RE: Cannot Insert into SQL Server

[Steve Howard]

Generally, people use q{} and qq{} quotation notation to avoid having to
concatenate, and to avoid the interference of quotes required by the SQL
statement - or other statements. The problem in the original question was
that the person used a single q with the q{} quote notation. A single q
works like a single quote, so the statement is not parsed for variables.
qq{} (double q's) works like double quote marks in that the contents are
parsed for variables, but it is usually better in situations like this than
the " double quotes because it can contain single or double quotes within it
without needing a concatenation. That was the first suggestion. Just change
the q{} notation to a qq{} quote notation, and $addr will be replaced within
it with the value of $addr before the statement is prepared.

Steve H.

-Original Message-
From: Hugh J. Hitchcock [mailto:[EMAIL PROTECTED]]
Sent: Sunday, August 12, 2001 7:35 PM
To: [EMAIL PROTECTED]
Subject: RE: Cannot Insert into SQL Server


probably the placehoder answer would work. But other than that, I think this
would probably work:

   my $sth = $dbh->prepare("insert into emails values ('$addr')")
|| die "Can't prepare statement: $DBI::errstr";

embedding the value inside of double quotes or

  my $sth = $dbh->prepare("insert into emails values ('" . $addr . "')")
|| die "Can't prepare statement: $DBI::errstr";

quoting the string with double qoutes and concatentating the single quotes
with the value contained in $addr, resulting in the correct statement...
more of a perl problem than a DBI problem.

Hope this helps.

H

> -Original Message-
> From: nayeem [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, August 12, 2001 11:26 AM
> To: [EMAIL PROTECTED]
> Subject: Cannot Insert into SQL Server
>
>
> Can Anybody help me to insert the records in Sql Server.
> Actually this code
> is working but inserting $addr as value instead of variable $addr
> 's  value
> that is abc, so is there any solution to add the variable values...
>
> use DBI;
> my $addr ='abc';
> my $dbh = DBI->connect("dbi:ODBC:email", "sa", "zajilpass")
>   || die "Can't connect to $data_source: $DBI::errstr";
>   my $sth = $dbh->prepare( q{
>   insert into emails values ('$addr')
>   }) || die "Can't prepare statement: $DBI::errstr";
>   my $rc = $sth->execute
>   || die "Can't execute statement: $DBI::errstr";
>
>
> check this code and please advice me.
>
> Thanks,
> Nayeem.
>
>
>
>

RE: SQL Service Pack 3 Install Resulted in ODBC Errors

[Steve Howard]

DBI handles sessions differently from running that script in the SQL query
analyzer, or running it as an ISQL or OSQL script. I'm not sure exactly how
you are running this using DBI, but I'll start from the top and comment on
it:

1.  You need to break it apart. A statement handle can only have a single
statement prepared - not an entire script.

2.
set nocount on

That tidies up the script in Query analyzer, but is not necessary from a
script, and in fact is gone when the next statement handle is prepared
anyway.

3.  #temp_table is a session specific temporary table. It is not visible to
any other connections, and is gone immediately when a connection is broken.
A funny thing about DBI using DBD::ODBC (And I imagine most other dblibrary
connection modules) is that it doesn't recognize a session like that. You
can get around this by using a global temporary table from your Perl script.
You would do that by naming this table ##temp_table (double pound signs are
global temporary tables).

4.  \@i int,  again, SQL variables are session specific. Actually, they are
more than that - they are batch specific. You won't be able to use these
while executing something using DBI.


5.  DECLARE search_cursor   Great method (but not necessary for what you're
doing - not even from an OSQL script or in query analyzer) but this is not
how to handle this in DBI. In DBI you could use a SELECT..INTO or an
INSERT...SELECT, or you could prepare one statement handle with the select,
prepare another for the insert (using placeholders) then execute the select
statement handle, use a while loop to navigate through the cursor that is
created by the select statment, and use the values fetched in the
placeholders to execute the insert for each row.



Bottom line, you can use the variables in perl, and write this script out to
a file, then call OSQL or ISQL to execute it, and everything will work
(although think about that cursor, that is totally unnecessary, and very,
very slow compared with an INSERT..SELECT combo, or a SELECT..INTO..FROM
statement).

To bring the results back into your script from an OSQL statement (although
they're much more work to deal with if you do this instead of using DBI):

my $results = `OSQL -Sservername -Uuser -Ppw -Iscript_file`;

Possible...but about the only time I'd do something like that is when
inserting into a table that has an identity column when I am using explicit
values for that column.

Anyway, hope this helps. You're right, the script you wrote would word from
query analyzer, but not with DBI. Hopefully this can help you with that.

Steve Howard.



-Original Message-
From: Jonathan C. Popp [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 3:50 PM
To: [EMAIL PROTECTED]
Subject: SQL Service Pack 3 Install Resulted in ODBC Errors


Hi,

Just installed SQL Service Pack 3 on some of our systems and now the cursor
behavior has changed.  When I run the below SQL statement in SQL Query
Analyzer I get the correct output; however, DBI::ODBC returns the following
error:

Error Detail: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
prepared statement with handle 0. (SQL-37000)(DBD: st_execute/SQLExecute
err=-1): : No such file or directory

Below is a copy of SQL statement.  Any assistance would be greatly
appreciated.

Thanks,

Jon

 SQL ###

set nocount on

DECLARE search_cursor CURSOR LOCAL SCROLL OPTIMISTIC FOR

select
ID as EntryID,
Name as FullName,
EntryCode
FROM Entry
$where
order by EntryCode $asc

create table #temp_table
(
ORD int NOT NULL ,
Results int NULL ,
EntryID int NULL ,
FullName varchar (200) NULL ,
EntryCode varchar (12) NULL
)

OPEN search_cursor

insert into #temp_table (ord,results) values (0,\@\@CURSOR_ROWS)

declare
\@i int,
\@id int,
\@fn varchar (200),
\@ec varchar (12)

select \@i = 0

FETCH ABSOLUTE $st FROM search_cursor INTO \@id,\@fn,\@ec
if ( \@\@FETCH_STATUS = 0 )
begin
select \@i = \@i + 1
insert into #temp_table values (\@i,null,\@id,\@fn,\@ec)
end

while ( (\@i < $ps) and (\@\@FETCH_STATUS = 0) )
begin
FETCH NEXT FROM search_cursor INTO \@id,\@fn,\@ec
if ( \@\@FETCH_STATUS = 0 )
begin
select \@i = \@i + 1
insert into #temp_table values (\@i,null,\@id,\@fn,\@ec)
end
end

select * from #temp_table order by ord

RE: DBI on Win2k

[Steve Howard]

If you are trying to compile (make) you must have Visual C++ installed (The
same compiler as was used to install Activestate. I assume you are using
Activestate if you did not have to use nmake to compile the basic Perl). It
is easier to use PPM like has been suggested to you, but if you do compile,
and you have Visual C++, then substitute "nmake" everywhere you see 'make'
in the instructions.

Steve H.

-Original Message-
From: Neil Lunn [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 31, 2001 2:24 AM
To: [EMAIL PROTECTED]
Subject: RE: DBI on Win2k


Use ppm for ActiveState perl.

>-Original Message-
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, July 31, 2001 3:34 PM
>To: [EMAIL PROTECTED]
>Subject: DBI on Win2k
>
>
>Hi all,
>
>I have installed ActivePerl on windows 2000 and want to
>install and configure DBI module.
>
>After running "perl MakeFile.pl", it requires to run "make"
>
>I think, "make" is for Linux platform and not for Windows.
>What should i need to run ??
>
>If i require that utility to be downloaded from net, please
>also provide me the hyperlink.
>
>Thanx in advance
>
>Denis
>

__
Please Note :
Only  the intended recipient is authorised to access or use this e-mail.  If
you are not the intended recipient,
please delete this e-mail and notify the sender immediately.   The contents
of this e-mail are the writer's
opinion and are not necessarily endorsed by the Gunz Companies unless
expressly stated.

We use virus scanning software but exclude all liability for viruses or
similar in any attachment.

RE: SQL efficiency

[Steve Howard]

Generally speaking, getting a spike above 60% is nothing to worry about. How
long does the processor stay above 60%?  If it is for several seconds, then
you might have something to think about.

First, how is your table indexed? I would hope as a minimum you have an
index on the Fault_No and Response_no columns.

Last, yes, generally there is a more efficient way to get these results than
the correlated sub-query like you are using. This does not always give
faster results - especially when the table is small, but bench-mark it and
see.

Select the results of your sub-query into a temp table, and see if that
works better. I'm not 100% sure how oracle handles temp tables, but here is
how I would normally do it - the concept should be the same:


SELECT fault_no, max(response_no) as response_no
  INTO #temp
FROM stacy
group by fault_no


then modify your query like this:

SELECT s.fault_no, s.date_occurred, s.one_line_summary, s.category
FROM stacy s
join #temp t on s.fault_no = t.fault_no
and ((s.response_no = t.response_no) or (s.response_no is null and
t.response_no is null))

When you've got the results in #temp already, that second will limit the
results as you want them, and should be faster than the correlated subquery.
You may need to modify that a bit to be oracle specific, but try it and see
if it is faster.

Steve H.




-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 29, 2001 8:03 AM
To: DBI Users
Subject: SQL efficiency




Hi all,

With our report/response database, fault_no's can have one, multiple or
null response_no's. The SQL below returns distinct fault_no's regardless

if it has one, multiple or null response's. The SQL does the job, but
can you figure out it uses up a large amount of CPU (>60% on an Ultra
1)?
I'm only dealing with ~ 1400 rows.

Is there a better method?

SELECT s.fault_no, s.date_occurred, s.one_line_summary, s.category
FROM stacy s
WHERE (s.response_no =
(
SELECT max(response_no)
FROM stacy
WHERE fault_no = s.fault_no
 )  OR response_no is null
)


BTW: This is operating on an Oracle VIEW. I'm using Oracle 7.3.3 via
perl5.6.0/DBI1.14


Regards,

Stacy.

btrieve database driver?

[Steve Howard]

I am in early preparation for a conversion where the old system uses a
btrieve database. I have no ODBC driver for this - nor have I even been able
to find one. I don't see anything obvious like a DBD::BTRIEVE in a PPM
search.

I would like to be able to avoid the intermediate step of CSV's or Fixed
Field files if possible. Does anyone have any tip on a driver that will
allow me direct access into a BTRIEVE database from a Perl script?

Thanks,

Steve H.

RE: prepare_cached statement error???

[Steve Howard]

OK, I know I've had errors trying that before, but in trying that again
after you said that it did work like that so ignore that part (obviously
that wasn't where the mismatch error was coming from when I did that
before). however "$stmt Error\n" is showing up in the die when it says this:

select description from t_system_symbols where name_space = "COUNTRY"
and
value = ? Error

That's the error, but the error string could be obtained from $DBI::ERRSTR
or the errstr in the statement handle or database handle.

However, you are right in the array is in list context.

Steve H.

-Original Message-
From: Sterin, Ilya [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 19, 2001 3:14 PM
To: 'Brennan, Corey '; '[EMAIL PROTECTED] '
Cc: 'Wilson, Doug '; 'Steve Howard '
Subject: RE: prepare_cached statement error???


$sth->execute(@parm) or die ("$stmt Error\n");

Would work just fine.  Since its used in a list context.

-Original Message-
From: Brennan, Corey
To: [EMAIL PROTECTED]
Cc: Wilson, Doug; Steve Howard
Sent: 07/19/2001 2:08 PM
Subject: RE: prepare_cached statement error???

Yes this script is currently running under unix with no problems.  That
is
why I am stumped.  But thank you both for the help.

-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 19, 2001 3:40 PM
To: Brennan, Corey; [EMAIL PROTECTED]
Subject: RE: prepare_cached statement error???


You are saying this worked on Unix? I see one statement that I think
should
be causing you problems with matching the numbers of parameters expected
with the number you have provided:

$sth->execute(@parm) or die ("$stmt Error\n");

That should be trying to execute with a single value: the length of
@parm. I
doubt that's what you are after. Modify it like this:

$sth->execute(@parm[0..$#parm]) || die ("$DBI::ERRSTR\n");

(Thanks Michael Chase on this list for teaching me the $# notation)

You can also make your SQL statement a little more readable by using a
qq{}
quote notation, and make it less susceptible to forgetting a back slash
like
this:

$statement =qq{select description from t_system_symbols where
name_space = "COUNTRY" and value = ?};

And when all of that has been correct, I have still run into this error
message from Access (It has nothing to do with Windows or Perl...it's
Access). Sometimes I can get around this bug by going against everything
I've ever been taught and changing the select query to SELECT *
FROM.

However, when I say that, the only time I work with access is in a
migration
off of access to another database system.

Hope this helps.

Steve H.


-Original Message-
From: Brennan, Corey [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 19, 2001 12:32 PM
To: [EMAIL PROTECTED]
Subject: prepare_cached statement error???


Hi all,

I am having trouble getting a script ported over to NT from Unix.  This
syntax works on the Unix side but when I try it on NT I get the
following
error:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Too
few parameters. Expected 3. (SQL-07001)(DBD:st_execute/SQL
Execute err=-1) at C:\Program
Files\ipc\pricing_tool\maxgen\ESR_PwrReport.pl
line 257.
select description from t_system_symbols where name_space = "COUNTRY"
and
value = ? Error

The code looks like this:

$statement =("select description from t_system_symbols where
name_space = \"COUNTRY\" and value = ?");
$row = &get_sql_data($statement, $country_temp);
$country = $row->[0];

sub get_sql_data {

my ($stmt, @parm) = @_;

my $sth = $dbh->prepare_cached($stmt) or die ("$stmt\n");
$sth->execute(@parm) or die ("$stmt Error\n");
my $row = $sth->fetchrow_arrayref();
undef $sth;

return $row;
}

Any help would be greatly appreciated.

Thanks in advance
---
Corey Brennan

RE: prepare_cached statement error???

[Steve Howard]

You are saying this worked on Unix? I see one statement that I think should
be causing you problems with matching the numbers of parameters expected
with the number you have provided:

$sth->execute(@parm) or die ("$stmt Error\n");

That should be trying to execute with a single value: the length of @parm. I
doubt that's what you are after. Modify it like this:

$sth->execute(@parm[0..$#parm]) || die ("$DBI::ERRSTR\n");

(Thanks Michael Chase on this list for teaching me the $# notation)

You can also make your SQL statement a little more readable by using a qq{}
quote notation, and make it less susceptible to forgetting a back slash like
this:

$statement =qq{select description from t_system_symbols where
name_space = "COUNTRY" and value = ?};

And when all of that has been correct, I have still run into this error
message from Access (It has nothing to do with Windows or Perl...it's
Access). Sometimes I can get around this bug by going against everything
I've ever been taught and changing the select query to SELECT * FROM.

However, when I say that, the only time I work with access is in a migration
off of access to another database system.

Hope this helps.

Steve H.


-Original Message-
From: Brennan, Corey [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 19, 2001 12:32 PM
To: [EMAIL PROTECTED]
Subject: prepare_cached statement error???


Hi all,

I am having trouble getting a script ported over to NT from Unix.  This
syntax works on the Unix side but when I try it on NT I get the following
error:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 3. (SQL-07001)(DBD:st_execute/SQL
Execute err=-1) at C:\Program Files\ipc\pricing_tool\maxgen\ESR_PwrReport.pl
line 257.
select description from t_system_symbols where name_space = "COUNTRY" and
value = ? Error

The code looks like this:

$statement =("select description from t_system_symbols where
name_space = \"COUNTRY\" and value = ?");
$row = &get_sql_data($statement, $country_temp);
$country = $row->[0];

sub get_sql_data {

my ($stmt, @parm) = @_;

my $sth = $dbh->prepare_cached($stmt) or die ("$stmt\n");
$sth->execute(@parm) or die ("$stmt Error\n");
my $row = $sth->fetchrow_arrayref();
undef $sth;

return $row;
}

Any help would be greatly appreciated.

Thanks in advance
---
Corey Brennan

RE: DBI-MSAccess problem

[Steve Howard]

Most times this is due to your setup, and not with DBI. The folder have the
perl script in must be able to execute the scripts - setting this up is
different between different web servers - you'll have to check out the
configuration on this on your own.

If the folder cannot execute scripts or executables, then when the file is
accessed over the web, the web server - instead of knowing it is to be
executed, just assumes it to be "anything I don't recognize" and downloads
it to the browser.

Hope this helps.

Steve H.

-Original Message-
From: Pallavi Patil [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 19, 2001 1:45 PM
To: [EMAIL PROTECTED]
Subject: DBI-MSAccess problem


1)I had created Access database and register it.
2)Then I had created html form with three fields name,
email and Submit button.
3)Copied example perl fiel from net(given
below)->changed DSN name->changed mdb file name
4)And mentioned this perl file path in action tag of
form
5)DBI and DBD are installed on my m/c.

When I click on Submit button, File download dialog
box comes.. with options like you want to open perl
file or save it.

If I keep Apache server running or not it doesnt make
any difference. I would appreciate, if you suggest
solution for this problem.

Thanks in advanced.
Pallavi.

PERL FILE.

#!/usr/bin/perl
# Jean Lambert - 28 may 2000
# Last updated : 6 jan 2000
# Example for DBI and ODBC connection to MS Access

print "Content-type:text/html\n\n";

use CGI qw(:standard);  # Must be used to get the
param() function
use DBI;# Must be used for connecting to databases
engine

$name = param("name"); # Gets the workOrder field from
the HTML form
$email = param("email"); # Gets the Email field from
the HTML form

$comment = param("comment"); # Gets the Comment field
from the HTML form
# This formats the incoming data from the comment text
zone
$comment =~ s/\n/ /g;   # Replaces newlines with spaces
$comment =~ s/\r//g;# Replaces hard returns with
nothing
$comment =~ s/\cM//g;   # Delete ^M's

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
= localtime();
$mon += 1; # Ajust the month to a 1 to 12 format
instead of a 0 to 11 format
$year += 1900; # $year is nb of years since 1900, so
add up to 1900
$currentDate = "$year/$mon/$mday $hour:$min:$sec";

#
# This section is for appending the data to the
database.
sub new_record {
# This function add a record to the db
my ($currentDate, $name, $email, $comment) = @_;#
Get arguments

my ($dbh, $sth, $sql, $rv);
$dbh = DBI->connect( q{DBI:ODBC:DataBase},
{RaiseError => 1,
PrintError => 1,
AutoCommit => 1} );

$sql = q{INSERT INTO FirstTable VALUES (?,?,?,?)};
$sth = $dbh->prepare( $sql );

$sth->bind_param( 4, $comment, DBI::SQL_LONGVARCHAR
); #Allows to transfer data to Access memo field (more
than 255 char)

$ rv =
$sth->execute($currentDate,$name,$email,$comment);

my $success = 1;
$success &&= $rv;

$dbh->disconnect;
return $success;
}
# This function appends the data to the db
if (new_record($currentDate,$name,$email,$comment)) {

#
# This section prints a thanks-for-being-so-nice
message

print <
Thank you $name for filling the comment.
J. Lambert
EndHTML
}
else {
print <
-- Erreur !
EndHTML
exit;
}


__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/

RE: Checking for the existence of a certain row.

[Steve Howard]

OK, one more yes-no. I tried this for performance and as expected, it is
faster than count on MS and Sybase (and count is very fast on MS). You might
see if there is a variation of it you can use with the Oracle Decode
functionI can't answer if there is or not, however in either case even
though this is probably faster, it won't be portable between some dbms's.

SELECT CASE
WHEN EXISTS(SELECT somecolumn FROM customers WHERE columnname ='thisvalue')
then 1
ELSE 0
END

(I'm installing Oracle on Solaris right now. I'm anxious to get my hands on
it. I've only been off of mainframes for a couple of years, and haven't used
it, but hear some interesting things, like trim() in the where clause causes
indexes to not be used, and count causes a table scan. I'm sure it's all
accurate, but it certainly poses some challenges I've never thought about
having to deal with).

Anyway, just add that one to the possibilities of testing for the existence
from within a Perl script.

Steve H.




-Original Message-
From: Steven Lembark [mailto:[EMAIL PROTECTED]]
Sent: Saturday, July 07, 2001 6:16 PM
To: [EMAIL PROTECTED]
Subject: RE: Checking for the existence of a certain row.


- Steve Howard <[EMAIL PROTECTED]> on 07/07/01 17:54:18 -0500:

> do a
> SELECT COUNT(*) FROM  WHERE ...
>
> A count is almost always faster than actually returning that row, and
> requires even less network bandwidth to return the result. Of course, it a
> result of 1 is returned - the row exists.

Don't try this in Oracle, it immediately degenerates into a
table scan.  MySql may handle this differently but selecting
the key (or the first field with restriction on the PK) will
be equally fast w/o fewer risks.

sl

Comparison not producing what is expected.

[Steve Howard]

I have put together a script that was intended to move all existing
non-clustered indexes in a SQL 7 database to the secondary filegroup.
However, I am getting unexpected results in a comparison. Here is a snippet
from what I am doing that contains my print statement (To verify that I'm
getting the values for comparison that I need) and the output that shows it
is not acting as expected. Any help is appreciated:

foreach (@table) {
print outfile "\n\n\n--$_ :\n\n";

   $selecth = $dbh->prepare(qq{sp_helpindex "$_"}) || die "can't prepare
sp_helpindex\n$dbh::errstr\n";

   $selecth->execute() || die "Can't execute sp_helpindex\n$dbh::errstr\n";

my ($row, $name, $descr, $keynames, $create, $drop);

eval{
 $selecth->bind_columns(undef, \($name, $descr, $keynames));
  while ($row = $selecth->fetchrow_arrayref) {
   my @keyarray = split /, /, $keynames;
   $keynames = '['. join('], [', @keyarray).']';
   $countone = grep /^nonclustered/i, $descr;
   $counttwo = grep /unique/i, $descr;
   $countthree = grep /primary key/i, $descr;

   $create = qq{CREATE NONCLUSTERED INDEX \[$name\] \n\tON
$_($keynames) ON SECONDARY\n}
   if ($countone == 1 and $counttwo == 0 and
$countthree == 0);
   $create = qq{CREATE UNIQUE NONCLUSTERED INDEX \[$name\]
\n\tON $_($keynames) ON SECONDARY\n}
   if ($countone == 1 and $counttwo == 1 and
$countthree == 0);
   $create = qq{ALTER TABLE $_ \n\tadd constraint
$name PRIMARY KEY NONCLUSTERED($keynames) on SECONDARY\n}
   if ($countone == 1 and
$countthree == 1);
   $drop = qq{DROP INDEX $_.\[$name\]\n} if ($countone == 1
and $countthree == 0);
   $drop = qq{alter table $_ drop constraint
$name\n}
 if ($countone == 1 and $counttwo ==
1 and $countthree == 1);
print
"$name,\t$countone\t$counttwo\t$countthree\n$drop$create"
if ($countone == 1 and $counttwo == 1 and
$countthree == 1) ;
   print outfile $drop.$create;
   }
}
 }


I see my indent format went bad when I pasted that. However that's the
comparison. I need to handle the nonclustered primary keys different from a
nonclustered unique index. That's why I go through the pains of the
comparisons. Here is an example of two different results that are printed
showing something behaving different from what I expect:

one output:


CMS_FTEXT_COLUM0,   1   1   1
alter table dbo.[CMS_FTEXT_COLUM] drop constraint CMS_FTEXT_COLUM0
ALTER TABLE dbo.[CMS_FTEXT_COLUM]
add constraint CMS_FTEXT_COLUM0 PRIMARY KEY
NONCLUSTERED([COLUMN_ID]) on SECONDARY

A second output:

MKA_FEVENT_MAIL0,   1   1   1
alter table dbo.[MKA_FEVENT_MAIL] drop constraint MKA_FEVENT_MAIL0
ALTER TABLE dbo.[MKA_FEVENT_MAIL]
add constraint MKA_FEVENT_MAIL0 PRIMARY KEY
NONCLUSTERED([FIRM_EVENT_UNO], [MAIL_UNO]) on SECONDARY


another output:

GLT_JRNL_Z0,1   1   1
alter table dbo.[GLT_JRNL_Z] drop constraint GLT_JRNL_Z0
CREATE UNIQUE NONCLUSTERED INDEX [GLT_JRNL_Z0]
ON dbo.[GLT_JRNL_Z]([STATUS], [JE_NUMBER]) ON SECONDARY


Perousing the outfile (over 800 tables and over 1500 nonclustered indexes, I
haven't been through every one yet) it looks as if it always get the right
statement for $drop, but although about 90% of the $create lines that are
printed are the right one, there are some like the third example which
should use an alter table to add back a primary key constraint that instead
just create a unique nonclustered index. You can see from the output that
all three values are 1, so how can it be doing this? What am I missing? how
can it make the right evaluation most of the time, but not all of the time?


Steve H.

RE: Checking for the existence of a certain row.

[Steve Howard]

do a
SELECT COUNT(*) FROM  WHERE ...

A count is almost always faster than actually returning that row, and
requires even less network bandwidth to return the result. Of course, it a
result of 1 is returned - the row exists.

Steve H.

-Original Message-
From: Steven Lembark [mailto:[EMAIL PROTECTED]]
Sent: Saturday, July 07, 2001 11:54 AM
To: [EMAIL PROTECTED]
Subject: Re: Checking for the existence of a certain row.


- James Kufrovich <[EMAIL PROTECTED]> on 07/06/01 17:08:56 -0400:


>   I'd like to check for the existence of data in a row of a database
> (MySQL), if given the value of a primary key.  ("Is there already a row in
> the database that has this value as a key?") I don't care what data is in
> the row, or if more than one row (!!) is found.  I'd hope that whatever
> method I use will stop searching the database after it finds the first
> match.
>
>   What would be the best way to go about this? Is there a special
> method that can do this, or would I have to use selectrow_array (or
> fetchrow_array or one of those) and then see if it finds anything?  I
> suppose I can call 'finish' as soon as a match is found, if the method (or
> whatever) doesn't stop by itself.  I'd appreciate any tips. Thanks.

select keyfield1, keyfield2,... keyfieldN from tablename;

If the list of keyfield's is your PK then this should return rather quickly.

RE: bind columns

[Steve Howard]

Ok. Good enough, and good explanation for why. That's exactly what I was
looking for. Guess I'll keep typing that extra line  :-).  Thanks much.

Steve H.

-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 04, 2001 2:58 PM
To: Steve Howard; DBI USERS
Subject: Re: bind columns


Perl has to copy every column value returned by fetchrow() to the
corresponding element of @array.  With bind_columns(), no copying is
required.  That is a major performance improvement if you have more than a
few rows.
--
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.
- Original Message -----
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "DBI USERS" <[EMAIL PROTECTED]>
Sent: Wednesday, July 04, 2001 11:22
Subject: bind columns


> Is there any performance advantage of using a:
>
> $sth->bind_columns(undef, \(@array[0..$#cols]));
> while ($row = sth->fetchrow_arrayref) {}

I normally use
   while ( $sth -> fetch ) {}
with $dbh -> {RaiseError} = 1.  Once you've bound the parameters, there's no
need to copy even the reference.

> instead of skipping the bind columns and just doing:
> while (@array = sth->fetchrow) {}
>
>
> I just wonder why I normally type an extra line (Looking for a reason
other
> than that's the way I first learned it).

bind columns

[Steve Howard]

Is there any performance advantage of using a:

$sth->bind_columns(undef, \(@array[0..$#cols]));
while ($row = sth->fetchrow_arrayref) {}

instead of skipping the bind columns and just doing:
while (@array = sth->fetchrow) {}


I just wonder why I normally type an extra line (Looking for a reason other
than that's the way I first learned it).

Any tips?

Steve H.

RE: Please help for BDI for Win32

[Steve Howard]

If you have winzip for windows, it can handle tar files just fine. If you
don't, you can download it at www.winzip.com

Or ifyou have PPM in your installation, you should be able to install DBI
from the internet by just typing:

PPM INSTALL DBI

from your DOS command prompt.

Steve H.

-Original Message-
From: Mojdeh Ghiaie [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 04, 2001 5:29 AM
To: [EMAIL PROTECTED]
Subject: Please help for BDI for Win32


Hi,

I am working with Perl5.004 on Win ME and have a
database on VisualFoxpro6.0. So I need to connect to
the database, but unfortunately I did not find the
source of DBI for my purpose, because all of them were
available in tar files not zipe files and windows.
Please help me and let me know how can I solve my
problem.

Thanks in advanced

Mojdeh

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/

RE: Mysql DBI Select Syntax ?

[Steve Howard]

In this case, I really think the question is one of database design. In the domain 
table you are creating to enumerate the types of degrees (a2), I would add a "rank" 
column so I could evaluate on the rank column, and join back to this domain table in 
queries where this evaluation needs to be made. 
Once you have a numeric rank, see if you can then make your evaluation. As it is, the 
rank is being determined by alphabetic order.

Steve H.


-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 4:40 AM
To: Steve Howard; [EMAIL PROTECTED]
Subject: Re: Mysql DBI Select Syntax ?


Dear Steven,

Thanks for your opinion.

Here is my complete example ,

my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
 CREATE TABLE a1 (diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;

my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
my $sth = $dbh->prepare( qq{
 CREATE TABLE a2 (diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;


my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth =
repare( qq{
SELECT  a1.diploma   a2.diploma 
FROM a1,a2
WHERE  a1.diploma <= a2.diploma
 });
$sth->execute;
$dbh->disconnect;

I'd like to compare a1.diploma and a2.diploma,  and my ideal  rule is doctor > master 
> university > junior_college >  .

But the result is university > senior_high > junior_high > junior_college >  master > 
doctor


Is there any method let me get my ideal  rule is doctor > master > university > 
junior_college >  senior_high > junior_high
 

Thanks in advance.


Sincerelly

Tom Wu


- Original Message - 
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "About-tw.com ??" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, July 03, 2001 10:59 AM
Subject: RE: Mysql DBI Select Syntax ?


I'm not completely sure I know what you're asking. If you're wanting to put a 
numerical equivalent to the possible string values, in MySQL you can use a CASE 
statement, like this:

SELECT case
WHEN diploma = 'junior_high' THEN 1
WHEN diploma = 'senior_high' THEN 2
WHEN diploma = 'junior_college' THEN 3
WHEN diploma = 'university' THEN 4
WHEN diploma = 'master' THEN 5
ELSE 6
END 
AS DIPLOMA
FROM Tablename

You can embed some version of that to get a numerical return from a table enumerated 
as you have said, however, it 
still shouldn't return as you have put in your WHERE clause. You would still
have to use:
WHERE diploma = 'senior_high'

If you only wanted Senior high grads.

Is this what you are asking?

Steve Howard


-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 02, 2001 3:16 PM
To: [EMAIL PROTECTED]
Subject: Mysql DBI Select Syntax ?


Dear All,


my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
 my $sth = $dbh->prepare( qq{
 CREATE TABLE $table_name (
 diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});


When I do the following procedure

my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{SELECT *FROM $table_nameWHERE
diploma = 2});
$sth->execute;
my $diploma  = $sth -> fetchrow_array ;
$dbh->disconnect;

I can get $diploma = "senior_high"


Now here is my problem , How could  I get the value of the $diploma = 2   ?

I'll really appreciated if someone can help me.

RE: SQL query

[Steve Howard]

What is the difference between the two records that cause the multiple lines
to be returned? Is it a date, or something else? If you are looking for
something to guarantee only the latest row is returned, you can use a
SELECT...INTO  and select into a temp table grouped by the key, and using a
MAX() then join to that, (Usually more efficient, but not
always) or you can use a subquery in your SQL statement to limit to only
that one. Examples (assuming a column named ThisDate as the difference)

Select fault_no, MAX(ThisDate) as ThisDate INTO #temp
FROM report_response
GROUP BY fault_no

SELECT r.fault_no ,r.one_line_summary FROM report_response r
JOIN #temp t on r.fault_no = t.fault_no and r.ThisDate = t.ThisDate
WHERE (r.reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC


Example of the second one I put forward:
SELECT r.fault_no ,r.one_line_summary FROM report_response r
WHERE (r reported_by LIKE '%J BLOGGS%' )
and r.ThisDate = (SELECT MAX(ThisDate) WHERE fault_no = r.fault_no)
order by fault_no DESC


however, for either of them to work, you need a way to distinguish between
the row you want, and the row(s) you don't.

Enjoy,

Steve H.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 3:31 AM
To: DBI Users
Subject: SQL query



Hi all,

I have a table (report_response) which has (among others)
fault_no and response_no fields. Now a fault_no can have
multiple response_no's.

The thing is, when I issue the following SQL:

SELECT fault_no ,one_line_summary FROM report_response
WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC

my returned list displays:

1355 Glish leftovers on sagitta
1350 Site phones
1350 Site phones

See those multiple occurances of 1350? This means there are 2 responses
to the fault_no = 1350. How can I fudge the SQL
to select the last response_no submitted?

Regards,

Stacy.

BTW: Using DBI:1.14 with Oracle 7.3.3

RE: Mysql DBI Select Syntax ?

[Steve Howard]

I'm not completely sure I know what you're asking. If you're wanting to put a 
numerical equivalent to the possible string values, in MySQL you can use a CASE 
statement, like this:

SELECT case
WHEN diploma = 'junior_high' THEN 1
WHEN diploma = 'senior_high' THEN 2
WHEN diploma = 'junior_college' THEN 3
WHEN diploma = 'university' THEN 4
WHEN diploma = 'master' THEN 5
ELSE 6
END 
AS DIPLOMA
FROM Tablename

You can embed some version of that to get a numerical return from a table enumerated 
as you have said, however, it still shouldn't return as you have put in your WHERE 
clause. You would still have to use:
WHERE diploma = 'senior_high'

If you only wanted Senior high grads. 

Is this what you are asking?

Steve Howard


-Original Message-
From: About-tw.com ?? [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 02, 2001 3:16 PM
To: [EMAIL PROTECTED]
Subject: Mysql DBI Select Syntax ?


Dear All,


my $dbh = DBI->connect("DBI:mysql:x",,, {RaiseError => 1 });
 my $sth = $dbh->prepare( qq{
 CREATE TABLE $table_name (
 diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});


When I do the following procedure

my $dbh = DBI->connect("DBI:mysql:x",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{SELECT *FROM $table_nameWHERE
diploma = 2});
$sth->execute;
my $diploma  = $sth -> fetchrow_array ;
$dbh->disconnect;

I can get $diploma = "senior_high"


Now here is my problem , How could  I get the value of the $diploma = 2   ?

I'll really appreciated if someone can help me.

RE: Values getting out of order between binding and execute

[Steve Howard]

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.




Your best bet is to execute the script under the Perl debugger.  'perldoc
perldebug' will help get you started.



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


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

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 usual

RE: Values getting out of order between binding and execute

[Steve Howard]

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.




Your best bet is to execute the script under the Perl debugger.  'perldoc
perldebug' will help get you started.



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


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

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 

Values getting out of order between binding and execute

[Steve Howard]

I'm working on a replication between heterogenous data sources program. I
seem to be so close, all transactions are well tracked, and everything seems
to be good in that respect, but in querying the recorded transactions from
the publisher, and putting those values into the subscriber I am running
into a problem with the columns getting out of order. Am I using the wrong
type of data structure here? Someone give me some feedback:

 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.

#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};

$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]));

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.


What am I misunderstanding? How would I keep them in the same order between
binding and inserting?

Thanks in advance,

Steve Howard
Sr. DBA DTO.

RE: Connecting to MySQL

[Steve Howard]

Just a thought, if the defaults are not working and:
1.  you've verified you are trying to connect to the right server,
2.  you have network connectivity to the right server from the server where
the scripts are running,
3.  you have proper name resolution (since your script is trying to connect
by name and not by IP address),
4.  you have DBI, and DBD::MySQL all installed correctly,
5.  you have checked to make sure your MySQL daemon is running,
6.  have you run a "netstat -a" on the machine where the mysql daemon is
running to be sure it is listening on the default port?


There's not much more than that that can go wrong. If all of that is
checked, and you are trying to connect on the port where MySQL is listening,
you might be to the point of re-installing DBI, or DBD::MySQL...but check to
be sure all those parts are in place before resorting to a re-install.

Anyone know of anything I missed?

Steve Howard

-Original Message-
From: Juan [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 19, 2001 8:10 PM
To: [EMAIL PROTECTED]
Subject: Connecting to MySQL


Hi,

I'm trying to connect MySQL using the following code:

#!/usr/bin/perl -w

use CGI qw(:standard);
use DBI();


print header;
print start_html("mysql");

print h1("Lista de Medicamentos/Cosméticos");


$dbh = DBI->connect("DBI:mysql:fol","fol","passfrr27");
$sth = $dbh->prepare("SELECT CODE,DESCRIPTION FROM PRODUCT LIMIT 0, 30");
$sth->execute or die "Error: ".$sth->errstr();

print p("=");
print p("Number of records: $sth->rows");

while(($cod,$desc) = $sth->fetchrow_array) {
  print "$cod - $desc\n";
}

$dbh->disconnect;


print end_html;


PLEASE!!  What am I doing wrong???

TIA,
Juan.

RE: Connecting to MySQL

[Steve Howard]

Juan,

I don't see location or port # in your connection string. Here is an example
of connecting to MySQL using default MySQL settings for port, and with the
MySQL daemon (service) running on the local machine. You don't have to
format it the same way, but you have to get all the elements into your
connection string:


use DBI;
  my $database_name = 'intra_data';
  my $location  = 'localhost';
  my $port_num  = '3306'; # This is default for mysql


  # define the location of the sql server.
  my $database  = "DBI:mysql:$database_name:$location:$port_num";
  my $db_user   = "sa";
  my $db_password   = "pass";

  # connect to the sql server.
  my $dbh   = DBI->connect($database,$db_user,$db_password);





The $location and $port_num on the line defining $database are what you seem
to be missing.

Hope this helps,

Steve Howard
-Original Message-
From: Juan [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 19, 2001 8:10 PM
To: [EMAIL PROTECTED]
Subject: Connecting to MySQL


Hi,

I'm trying to connect MySQL using the following code:

#!/usr/bin/perl -w

use CGI qw(:standard);
use DBI();


print header;
print start_html("mysql");

print h1("Lista de Medicamentos/Cosméticos");


$dbh = DBI->connect("DBI:mysql:fol","fol","passfrr27");
$sth = $dbh->prepare("SELECT CODE,DESCRIPTION FROM PRODUCT LIMIT 0, 30");
$sth->execute or die "Error: ".$sth->errstr();

print p("=");
print p("Number of records: $sth->rows");

while(($cod,$desc) = $sth->fetchrow_array) {
  print "$cod - $desc\n";
}

$dbh->disconnect;


print end_html;


PLEASE!!  What am I doing wrong???

TIA,
Juan.

RE: handling where condition for char(nnn) data types

[Steve Howard]

Comment only on one portion:


I would think most other databases will do the same. Otherwise,
who would like to to count manually the blanks needed at the end
of the text literal in a where condition?


Not necessarily true. in the area of what other DBMS's do with trailing
blanks in where clauses, so far as MS SQL is concerned (for example) the
matching stops with the last non-blank character in the where condition in
CHAR and NCHAR datatypes. So for the purpose of the where clause:

WHERE Column1 = 'This Char'

will return all rows where Column1 is 'This Char' no matter how many
trailing blanks it has to fill out the CHAR or NCHAR data column.

Your design is of course up to you, but as for all DBMS's behaving as you
say, they don't.

Steve Howard

-Original Message-
From: Richard Chen [mailto:[EMAIL PROTECTED]]
Sent: Sunday, June 17, 2001 3:43 PM
To: M.W. Koskamp
Cc: [EMAIL PROTECTED]
Subject: Re: handling where condition for char(nnn) data types


On Sun, Jun 17, 2001 at 10:40:32PM +0200, M.W. Koskamp wrote:
>
> > use DBD::Oracle qw(:ora_types);
> > $sth->bind_param(1,'foo',{ ora_type => ORA_CHAR });
> >
> > But I don't really like this work around that much because it
> > made my code not portable for such basic data types.
>
> It's not a workaround it makes perfect sense here.
> No way to know if interfaces of other databases add padding chars
themselves
> (or need them).

I would think most other databases will do the same. Otherwise,
who would like to to count manually the blanks needed at the end
of the text literal in a where condition?

> I use varchars for database fields instead. Solves the issue too.
>

This is true. However, this may not be your choice. In my case,
I encountered this when I tried to do 'make test' while installing
Template Toolkit CPAN package. The tests creates precisely such
columns in the testing tables which make some tests fail.

So my question is not about how best to design the tables. It is
about what do you do in a portable way when you are faced
with a given situation.

Thanks for your help, though.

Richard

RE: Copy records from other database.

[Steve Howard]

Krung,

The fastest way to do a table transfer is one of two methods (depending on
your DBMS, and whether or not the target table already exists).

If you do not have timestamp columns, or any non-transferable columns, and
Source and Target databases can establish a direct connection between the
two, and the columns are in the same order, then you can use:

INSERT INTO Target..Table
SELECT * FROM Source..Table

Granted, I used the naming convention of the DBMS I use most, so you might
check the naming convention of the DBMS you are using to see how database
and table are designated, and if you are going to a different server, there
may be an additional step still. (like Server.Database..Table)

If the target table does not already exist, you might check your DBMS docs
and see if a SELECT...INTO is supported. This looks like:

SELECT * INTO Target..Table
FROM Source..Table


That command creates the destination table, then copies all the contents
into it.

If you have nontransferrable columns (Like Timestamp columns), then there is
not really a shortcut per-se. You will either have to back up and restore,
or you can transfer it by having your PERL script query your system
catalogues, and build the query excluding the Timestamp column. queries to
system catalogues differ quite a bit between DBMS's, but if you do this sort
of thing quite a bit, then it is probably worth your time to build a PERL
script to build large transfer scripts like the one you are describing. You
didn't tell us what DBMS you are using, but just in case, the type of query
you would use in an MS SQL server would be:

SELECT name FROM syscolumns
WHERE id = object_id()
and type != 128
order by colorder

(--Going by memory on what to exclude...might not be right. Check docs to be
sure)


>From there, use your PERL to build those results into a $columnlist and
build this query:

INSERT INTO Target..Table ($columnlist)
SELECT $columnlist FROM Source..Table

Again, using the naming convention of Database..Tableuse the naming
convention your DBMS uses.

Of course, if target and source cannot connect directly to each other, then
you have to use the PERL script to process each line individually, but you
can still use the query of system catalogues to generate the $columnlist and
save you from having to type out 150 column names.


Hope this helps.

Steve Howard

-Original Message-
From: Krung Saengpole [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 29, 2001 12:24 AM
To: [EMAIL PROTECTED]
Subject: Copy records from other database.


Hi all,

I want to transfer record from one table in first database to another table
in second database. Can I use DBI to do it w/o INSERT... statement? Because
of many fields of my table (around 150 fields), or can use INSERT statement
easily with some method?

Any helps would be gracefully thank.

Krung

RE: Database with W2k

[Steve Howard]

I'm not sure I am seeing everything that is required in your connection
string:


$dbh = DBI->Connect("dbi:mysql:logs","xperl","002255") or die("Where is
connection:",DBI::errstr);

You are using DBI:mysql:logs  but where is the location and port
number?

This is an example of the connection info as I connect to MySQL using WIN2K.
You don't have to use the same structure, but I find it easy to read this
way. The items that are not in your connection string are the last two
variables  I have defined as part of the my $database line.


  use DBI;
  my $database_name = "test";
  my $location  = "localhost";
  my $port_num  = "3306"; # This is default for mysql


  # define the location of the sql server.
  my $database  = "DBI:mysql:$database_name:$location:$port_num";
  my $db_user   = "sa";
  my $db_password   = "sa";

  # connect to the sql server.
  my $dbh   = DBI->connect($database,$db_user,$db_password);



Hope this gets you going. Let me know if it doesn't

Steve Howard


-Original Message-
From: XPerl [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 28, 2001 12:08 PM
To: Steve Howard; [EMAIL PROTECTED]
Subject: Re: Database with W2k


>Could you include the script you are running when the >error is thrown?

Here is the script..

Thanks.


- Original Message -
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "XPerl" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, May 28, 2001 7:42 PM
Subject: RE: Database with W2k


Could you include the script you are running when the error is thrown?

Steve Howard

-Original Message-
From: XPerl [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 28, 2001 11:31 AM
To: [EMAIL PROTECTED]
Subject: Database with W2k


Hi to all,

I want to use Mysql database with Perl on a w2k professional system.

Firstly I installed the mysql-3.23.38 to the system. I started mysql, create
some databases and tables as ordinary.

After this installation process, I use the activeperls package manager to
install the DBI module and to install the DBD-Mysql module. I installed two
of the modules as ordinary.

But when I write a script and wish to connect a database I give an error,
that the perl debuuger can't locate the "auto/DBI/Connect.al" file.

Have you any idea, about this problem?
What is wrong with the modules?
And of-course, I wanna ask you that how can I solve this problem?

I started to work with these modules and mysql and of-course w2k newly.

Thanks for your interest...
Sorry for my bad language..

RE: Database with W2k

[Steve Howard]

Could you include the script you are running when the error is thrown?

Steve Howard

-Original Message-
From: XPerl [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 28, 2001 11:31 AM
To: [EMAIL PROTECTED]
Subject: Database with W2k


Hi to all,

I want to use Mysql database with Perl on a w2k professional system.

Firstly I installed the mysql-3.23.38 to the system. I started mysql, create
some databases and tables as ordinary.

After this installation process, I use the activeperls package manager to
install the DBI module and to install the DBD-Mysql module. I installed two
of the modules as ordinary.

But when I write a script and wish to connect a database I give an error,
that the perl debuuger can't locate the "auto/DBI/Connect.al" file.

Have you any idea, about this problem?
What is wrong with the modules?
And of-course, I wanna ask you that how can I solve this problem?

I started to work with these modules and mysql and of-course w2k newly.

Thanks for your interest...
Sorry for my bad language..

RE: SQL Server

[Steve Howard]

I use DBD::ODBC almost exclusively in WIN32 environments using a variety of
DBMS's. It works beautifully with MS SQL 7 (which is actually my main area
of focus).

Have fun.

Steve Howard

-Original Message-
From: Alisa Mills [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 8:07 AM
To: [EMAIL PROTECTED]
Subject: SQL Server


I am trying to port an application from Unix to Windows 2000.  The Unix
version used Oracle, and the Windows 2000 version will use MS SQL Server
2000.  I found a DBI for ODBC, ADO, and a very old one for MS SQL
Server.  Does anyone know of a DBI that will work with MS SQL Server
2000?

Thanks in advance.

Ci-Ci
[EMAIL PROTECTED]

RE: Connection is busy

[Steve Howard]

I've encountered this problem before while using DBD::ODBC. It usually is
how I am dealing with the results of a previous query. The way to handle it
is to open another connection. Works fine like that.

Steve Howard

-Original Message-
From: Michael Peppler [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 11:59 AM
To: [EMAIL PROTECTED]
Subject: Re: Connection is busy


roberto l writes:
 > And since this is dependant on the protocol changing the odbc driver
won't work. So
 > what would be the best workaround?

Depends.

I think some ODBC drivers have support for this "under the covers",
possibly by opening a second connection for you. I don't know much
about ODBC, unfortunately.

DBD::Sybase gets around the problem (not very nicely) by opening a new
connection for the sth if the dbh already has an active sth.

Michael

 > Michael Peppler wrote:
 >
 > > Sterin, Ilya writes:
 > >  > I would imaging that that is either the SQLServer config or ODBD
config that
 > >  > is not allowing multiple connections.
 > >
 > > Actually it's not allowing multiple statement handlers on the same
 > > connection. This is the default for the TDS protocol (the underlying
 > > protocol that MS-SQL and Sybase use to communicate between the client
 > > and the server.)
 > >
 > > Michael
 > >
 > >  > -Original Message-
 > >  > From: roberto l
 > >  > To: [EMAIL PROTECTED]
 > >  > Sent: 05/17/2001 10:03 AM
 > >  > Subject: Connection is busy
 > >  >
 > >  > We've recently ported an application from MSaccess to SQL Server 7
and
 > >  > now many programs are failing producing the following error:
 > >  >
 > >  > [Microsoft][ODBC SQL Server Driver]
 > >  > Connection is busy with results for another hstmt
 > >  > (SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
 > >  >
 > >  > Should I use another ODBC driver?
 > >  >
 > >  > Any suggestion will be greatly appreciated. The platform: perl
5.6.0, nt
 > >  > 4.0 sp 6 and sql server 7.
 > >  >
 > >  > bests
 > >  >
 > >  >
 > >  >
 > >  >
 > >  > --
 > >  > De duobus malis, minus est semper eligendum
 > >  >
 > >
 > > --
 > > Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
 > > http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
 > > International Sybase User Group - http://www.isug.com
 > > Sybase on Linux mailing list: [EMAIL PROTECTED]
 >
 > --
 > De duobus malis, minus est semper eligendum
 >
 >

--
Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: [EMAIL PROTECTED]

RE: Using perl to connec to ACCESS

[Steve Howard]

If you're trying to use a dsn, I'm assuming you're using ODBC. If so, You'll
need to use DBI and connect using DBD::ODBC. You configure your DSN in your
ODBC data sources, and refer to it when you make a connection. Once the
connection is made, then you can define the statement handle (like it looks
like you're trying to do with the $dsn = qq{ line).

Once the connection, or connections are made, running the updates is just
like with any other dbi mod.

Steve Howard

-Original Message-
From: Jorge L. Allen [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 14, 2001 4:35 PM
Cc: [EMAIL PROTECTED]
Subject: Using perl to connec to ACCESS



It is first time connecting to Microsoft Access, and I cannot even  make a
simple connection.

The database is running and connected to the web using dreamweaver.
But I need to write perl scripts to update it.

use DBI;

$dsn = qq(dbi:Microsoft Access
  Driver:D\:Inetpub\\wwwroot\\temp\\doc_archive_test.mdb);
my $dbh = DBI->connect($dsn);


This is the error message:

Can't connect (dbi:Microsoft Access
Driver:d:\\Inetput\wwwroot\temp\doc_archive_test.mdb), no database driver
specified and DBI_DSN env var not set at 

FW: sql table joins

[Steve Howard]

-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 10:46 PM
To: De Simone, Andrew (CAP, FGI)
Subject: RE: sql table joins


Even if data resides on separate servers, data integrity can be maintained
in MS SQL by making use of  Insert, update, and delete triggers. This is
done with some regularity in the environment I work with. There are times
when data is distributedit doesn't always have to reside on the same
server, or in the same database. This is probably not one of those
casesbut there are times, and it can be done with good design work.

Steve Howard

-Original Message-
From: De Simone, Andrew (CAP, FGI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 3:31 PM
To: [EMAIL PROTECTED]; '[EMAIL PROTECTED]'
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: sql table joins


Even if the servers don't support replication you could take data from the
one database and populate a table on the other by creating your own replica.
You could then add a
foreign key constraint to this table or use a query to check the data.  Just
remember that if it is being used as a foreign key, you can't drop the data
and replace it, you will
need to do update/inserts.  This could give the app a way of validating the
data entered or even give them a parts lookup.

-Original Message-
From: Brett W. McCoy [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 1:21 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: sql table joins


On Tue, 1 May 2001 [EMAIL PROTECTED] wrote:

> different databases on different servers

Yikes!  That does introduce extra problems for you!  Yeah, the only way
you can really handle that is via external scripting, unless your servers
support some kind of replication|mirroring|distributed querying or
something along those lines.

I guess your big problem is that you can't verify your data integrity
until after the data has already been entered.  How big of a gap in time
does this occur, between data entry and data validation?

-- Brett
   http://www.chapelperilous.net/btfwk/

"Ninety percent of baseball is half mental."
-- Yogi Berra

RE: sql table joins

[Steve Howard]

If the tables are in two different databases on the same server, the naming
format is:

Database.Owner.Table.

therefore you can do a join in MS like this:

SELECT value1 FROM database1.dbo.table1 l
JOIN database2.dbo.table2 r ON l.somecolumn = r.somecolumn.

(dbo is the default owner. if dbo is the owner, you can reference it as:

Database..Table)
If they are on two different servers, there are two ways to do this in MS
SQL (If you are usnig 7.0 or later, one way using 6.5)

The first way is to create a linked server definition. This is not
difficult, but is a little more involved than I want to go into for this
message. (Read the SQL Server Books Online that are installed with MS SQL to
get the instructions for creating Linked server definitions). Once the
Linked server definition is created, you can to a distributed query using a
4 part naming convention that is SERVER.DATABASE.OWNER.TABLE and it is a
simple as

SELECT Value1 FROM Myserver.Database1..Table1 l
JOIN Otherserver.Database2..Table2 r on l.somecolumn = r.somecolumn.

If you are working ad-hoc in 7.0 or later, you can use an openrowset
function and join to the results in the openrowset function. You can do that
from PERL, but if it is something you will use frequently, just create a
linked server rather than use the OPENROWSET.

All of these things are contained in MS SQL Server Books Online, but I'll
lend a hand if you need more help getting started.

Sincerely,

Steve Howard

PS. Don't use the old SQL Syntax for joins - it limits what you can do with
SQL too much.



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 10:38 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: sql table joins


Hi Sumit,
The databases are both microsoft sql. As you say it looks like DBI does not
support what I would like to do when the tables are in different databases.
I will just have to continue what I have right now which is read in from
one, store in a hash and the use the hash keys to query the second database.
the only problem with this is that it ran a little slow and I felt getting
SQL to do this compare would be faster.Thanks any way
Willie

Willie McSweeney
Memory Component Engineer
EMC,
Ovens,
Co.Cork, Ireland.
Tel +00353-21-4281412
Fax +00353-21-4281898
Email <[EMAIL PROTECTED] >


> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, May 01, 2001 4:24 PM
> To:   [EMAIL PROTECTED]
> Subject:  RE: sql table joins
>
>
>
>
>
>
> Hello Willie,
>
> >If I do something like
> >$dbh = DBI->connect ('dbi:ODBC:TotalBoards', 'user', 'password') or die
> >"could not CONNECT $!";
> >$dbh_aml = DBI->connect ('dbi:ODBC:AML', 'user', 'password') or die
> "could
> >not CONNECT $!";
> >
> >$sth = $dbh->prepare(qq{SELECT TotalBoards.EMC_PN FROM TotalBoards,
> >AMLMaster WHERE TotalBoards.SYMPTOM_CODE LIKE ? AND TotalBoards.EMC_PN =
> >AMLMaster.AMLPN AND TotalBoards.REC_DATE BETWEEN ? AND ?}) or die
> "Couldn't
> >prepare statement: " . $dbh->errstr;
> >
> >I get an error "invalid object name AMLMaster". This is I guess because
> $dbh
> >is the connection to TotalBoards and knows nothing about the AMLMaster
> >table. I have another $dhh_aml connecting to AMLMaster. My question is
> how
> >do you write a prepare statement as above that works. Can you get $dbh to
> >connect to both tables at the same time???
> >Willie
>
> If I guess it correctly, the table AMLMaster is in the AML Database/DSN
> and
> TotalBoards Database/DSN, right. If so you will have to have a database
> link created between the 'TotalBoards' and 'AML' databases. I don't think
> you can do what you are doing with out a database link or some thing like
> that between 'TotalBoards' and 'AML'. Also i don't think ODBC supports
> such
> a thing.
>
> What databases are these two? If it's oracle then you can create a
> database
> link using the command 'CREATE DATABASE LINK ...' in SQL Plus.
>
> Hope this helps.
>
> Regards,
>
> Sumit.
> **
> **
>
> Just because something doesn't do what you planned it to do doesn't mean
> it's useless.
>  -Thomas A. Edison
> **
> **
>

FW: Extract data from MS Excel Spreadsheets. Can it be done?

[Steve Howard]

-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 10:05 PM
To: Kutler, Christopher
Subject: RE: Extract data from MS Excel Spreadsheets. Can it be done?


If you're just wanting to get the data out of the excel spreadsheet into a
more usable, and more manipulable format, Perl might not be the easiest way
to do that. If you have access to MS SQL 7.0 learn to use DTS - it is very
easy, and very, very good at moving data between any two OLE-DB compliant
data sources. I use this quite often when source data or translation table
are built in Excel (a lot of people like to use that) and I need it in SQL
to actually make use of it. From MS SQL, it is also possible to create a
linked server definition, or use an OPENROWSET to access Excel. The table
names will be the names of the sheets as listed on the tab at the bottom of
the page with a $ on the end of it.

I would recommend taking the data out of EXCEL first if you need to do much
in the way of manipulation or use of the data.

Steve Howard

-Original Message-
From: Kutler, Christopher [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 6:39 AM
To: '[EMAIL PROTECTED]'
Subject: Extract data from MS Excel Spreadsheets. Can it be done?


Hi,

Does anyone know whether it is possible to connect to an Excel spreadsheet
and extract data in a way similar one would do with an Access database? If
so, how?

thanks in anticipation

Chris Kutler




---
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity
to whom they are addressed.  If you have received this email in error please
notify the system manager.

RE: MS SQL Server Connectivity

[Steve Howard]

What OS are you using? If it is an MS Operating System, then all you really
need is DBI and DBD::ODBC. You'll have to get your DSN configured, and make
your connection right. If you need more details, I can probably help

If you are using a different OS, someone else will probably have to help.

Steve Howard

-Original Message-
From: Joel Divekar [mailto:[EMAIL PROTECTED]]
Sent: Saturday, April 28, 2001 4:28 AM
To: [EMAIL PROTECTED]
Subject: MS SQL Server Connectivity


Hi All

How to make Perl to talk to MS SQL Server ?

Regards

Joel


--
QuantumLink Communications, Bombay, India

RE: Force unique input to field from web form into Oracle primary key field...

[Steve Howard]

If I understand what you are wanting to do, why not just have the file in
the ACTION arguement of the FORM tag first perform the search against the
database to see if the string is unique (SELECT COUNT(*) FROM $database
WHERE $column = $value) , then based on the results of that search  (which
will be either 0 if it does not exist, or 1 if it does) call one of two
subroutines within that perl file. One of the subroutines contains the HTML
to inform the user that the value is not unique, and also containing a link
to return to the input form page. The second subroutine is called if no
identical value appears in the database, and this second subroutine contains
the insert to the database, and the HTML to inform the user that the
contents of the form have been successfully input to the database.

If you prefer your option 1 (Probably would be my preference), then perform
the same search on the database, and based on the results ( 0 or 1) of that
count search, either insert as the value stands, or append something onto
the end. However, if this is done, you might want to plan for the
possibility that many duplicates of the input value could be input, and
build your routine to handle it.

Steve Howard



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Sunday, April 22, 2001 2:59 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Force unique input to field from web form into Oracle primary
key field...



Help???

This must be a common thing to do but have not  seen an example to
reference.

Must  create a web input form dialogue box ( dbi/oracle/CGI module )
which allows user to input a string.   String will be placed in
table if it is unique.  String must be unique since it
is to be placed into a primary key field.  How would one  force
or promt the user to enter a unique string by either:

1.) appending some characters onto the string before being inserted
into database ( in case there is an identicle string in the table
already - meaning the database requires a search for that string
 on the table in question before each insert )

OR

2.) gracefully, have the form respond to user that the string is already in
 the database (meaning a search must happen once the string is entered
form
the form before attempting to place it into database.   User must then
pick a new
  string if search comes back positive. Or user could then use the
string
  already found in the database as his form input ( upon being alerted
  after selecting the already present string).  At that point the string
is
  not inserted into database but used nonetheless as part of the users
  choice" to then do other operations with it.

OR

3.) any idea is welcome similar to or combining ideas from 1) & 2).

RE: joining > 2 tables with differing # of rows while using binding columns

[Steve Howard]

I reread your explanation - sorry for sounding like I was talking down to
you. I got so involved in translating your syntax that I forgot the first
part of your e-mail. Still, using ANSI syntax for joins is the answer, it's
just getting the conditions right to do what you need. I'll still help if I
can, and if I didn't insult you too badly with my carried-away explanation.

Steve Howard

-Original Message-
From: Michelle Kobza-Road Runner [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 20, 2001 5:27 PM
To: [EMAIL PROTECTED]
Subject: joining > 2 tables with differing # of rows while using binding
columns


Hi,

I am trying to pull date from 3 different tables to generate a specifically
formatted text file. I can successfully join two tables using the ANSI LEFT
JOIN, but when I try to add a third this does not work. I have than moved to
using the older perl script  " from a,b,c" . This seems to join the data but
the data from the third table is out of order or not placed correctly.

Some background:

Sun Solaris server, SQL 7.5

One database, three tables with following primary fields I am cross
referencing. The first two tables have the same number of rows with primary
keys ExamID and QnAIndex.  The third table does not have the same number of
rows and only holds data that has an InstID. Primary keys to match are
ExamID and InstID

Table 1:

ExamQuestionInfo
ExamID
QnAIndex
InstID

Table 2:
ExamID
QnAIndex

Table 3:
Exam ID
InstID

When  I run my script if an Exam ID does not exist in Table 3 that row is
not printed. Plus, the InstId is being place on rows that do not have an
instruction. My goal is to print all the rows from Table 1 and Table 2 even
if that do not have an InstID associated with the ExamId.  My script
currently is as follows:

#statement handle to query table data
my $sth2 = $dbh->prepare(qq{ SELECT  ans.ExamID, ans.QnAIndex, que.QtnText,
que.ExamQnAType, ans.QnAOrder, ans.QnASubOrder, ans.AnsText, ans.CorrectAns
, que.RatSurl, que.DepSurl, que.InstId, inst.InstText FROM ExamQuestionInfo
que, ExamAnswerInfo ans,  ExamInstGrpInfo inst WHERE que.ExamID = ans.ExamId
AND que.ExamID = inst.ExamId  AND que.QnAIndex = ans.QnAIndex AND que.InstId
= inst.InstId ORDER BY que.ExamID, que.QnAIndex }) or
die ( "Cannot prepare statement: ", $dbh->errstr(), "\n" );

$sth2->execute() or
die ( "Cannot execute statement: ", $sth2->errstr(), "\n" );

#associate Perl variables to output columns
my $rv2 = $sth2->bind_columns(\( $ExamId, $QnAIndex, $QtnText, $ExamQnAType,
$QnAOrder, $QnASubOrder, $AnsText, $CorrectAns, $RatSurl, $DepSurl, $InstId,
$InstText));


Any suggestions, advice very much appreciated.

Michelle

RE: joining > 2 tables with differing # of rows while using binding columns

[Steve Howard]

You can't do what you're trying to do using the old SQL syntax like you are
using. Try it like this:



SELECT
ans.ExamID, ans.QnAIndex, que.QtnText, que.ExamQnAType,
ans.QnAOrder, ans.QnASubOrder, ans.AnsText, ans.CorrectAns,
que.RatSurl, que.DepSurl, que.InstId, inst.InstText
FROM

ExamQuestionInfo que
LEFT OUTER JOIN ExamAnswerInfo ans on que.QnAIndex = ans.QnAIndex
AND que.ExamID = ans.ExamId
  LEFT OUTER JOIN ExamInstGrpInfo inst on que.ExamID = inst.ExamId
AND que.InstId = inst.InstId

ORDER BY que.ExamID, que.QnAIndex, inst.InstId



I'm not really clear on what you're trying to do with the first two tables
though. If you are wanting all que rows whether or not there are ans rows,
then keep the left join. But, if you want only matches between these two
tables, then change the LEFT OUTER to an INNER (Outer and INNER are optional
words in most DBMS's, but since I don't know which DBMS you're using, I
can't guarantee it is in your DBMS, so I spelled it all out).

Also, if a match does not exist, you might physically get NULL printed in
the column where no match exists. If you don't want the word NULL in your
result set, use a CASE statement (if your DBMS has one) to put another word,
or a blank space in that column.

And, (Just in case you're not familiar with ANSI JOIN Syntax) if you want to
add a where clause, it goes between the last join condition and the ORDER BY
clause.

If this doesn't work, reply back to me, tell me what DBMS you're using, and
what is not working, and I'm sure we can get the results you need.

Hope this helps,

Steve Howard


-Original Message-
From: Michelle Kobza-Road Runner [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 20, 2001 5:27 PM
To: [EMAIL PROTECTED]
Subject: joining > 2 tables with differing # of rows while using binding
columns


Hi,

I am trying to pull date from 3 different tables to generate a specifically
formatted text file. I can successfully join two tables using the ANSI LEFT
JOIN, but when I try to add a third this does not work. I have than moved to
using the older perl script  " from a,b,c" . This seems to join the data but
the data from the third table is out of order or not placed correctly.

Some background:

Sun Solaris server, SQL 7.5

One database, three tables with following primary fields I am cross
referencing. The first two tables have the same number of rows with primary
keys ExamID and QnAIndex.  The third table does not have the same number of
rows and only holds data that has an InstID. Primary keys to match are
ExamID and InstID

Table 1:

ExamQuestionInfo
ExamID
QnAIndex
InstID

Table 2:
ExamID
QnAIndex

Table 3:
Exam ID
InstID

When  I run my script if an Exam ID does not exist in Table 3 that row is
not printed. Plus, the InstId is being place on rows that do not have an
instruction. My goal is to print all the rows from Table 1 and Table 2 even
if that do not have an InstID associated with the ExamId.  My script
currently is as follows:

#statement handle to query table data
my $sth2 = $dbh->prepare(qq{ SELECT  ans.ExamID, ans.QnAIndex, que.QtnText,
que.ExamQnAType, ans.QnAOrder, ans.QnASubOrder, ans.AnsText, ans.CorrectAns
, que.RatSurl, que.DepSurl, que.InstId, inst.InstText FROM ExamQuestionInfo
que, ExamAnswerInfo ans,  ExamInstGrpInfo inst WHERE que.ExamID = ans.ExamId
AND que.ExamID = inst.ExamId  AND que.QnAIndex = ans.QnAIndex AND que.InstId
= inst.InstId ORDER BY que.ExamID, que.QnAIndex }) or
die ( "Cannot prepare statement: ", $dbh->errstr(), "\n" );

$sth2->execute() or
die ( "Cannot execute statement: ", $sth2->errstr(), "\n" );

#associate Perl variables to output columns
my $rv2 = $sth2->bind_columns(\( $ExamId, $QnAIndex, $QtnText, $ExamQnAType,
$QnAOrder, $QnASubOrder, $AnsText, $CorrectAns, $RatSurl, $DepSurl, $InstId,
$InstText));


Any suggestions, advice very much appreciated.

Michelle

RE: MS ACCESS Date Fields

[Steve Howard]

I'm not positive if your actual statement looks like your model you gave to
us, but the main thing I see wrong with your model is how you are using
BETWEEN. If you have the Northwind Traders example database, here is an
example query to do basically the same thing:

SELECT * FROM Orders
WHERE RequiredDate BETWEEN '01/01/1996' AND '08/15/1996'


See if that syntax works better for you. If it's something more than that,
reply back, and I'm sure we can get that working.


Steve Howard.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 18, 2001 2:23 PM
To: [EMAIL PROTECTED]
Subject: MS ACCESS Date Fields


Hello,
i'm using Perl/DBI/CGI to connect to an MS ACCESS
database on the same
server. I can use SELECT and fetch data and pass it to
the browser, works fine. But
now I need to use some kind of BETWEEN sql statement that
will let me filter
records for a specific period of time. Something like:
"SELECT * from table
WHERE x=y AND date x BETWEEN y". I don't know how to use
the filter with
the ACCESS dates records. I am also using the Format
Format(tbl1.date,
'mm-dd-yy') command to get ride off the extra 00:00:00
that I would get if I don't
use format.
Please any ideas?

Thanks,
Miguel

RE: Complex SQL statments - Do they work?

[Steve Howard]

Chris,

There are a lot of possibilities there. If you are using ODBC the first
thing that comes to mind is whether or not one of the columns you are
querying is a text data type. I've used queries with similar complexity on
SQL 6.5 and 7.0, so complexity is not the issue. If you are getting an
error, though that might be helpful - particularly if your error deals with
what I assume is a date column (dd.full_date). Anyway some indication of
what kind of behavior you are getting would be helpful.

BTW. Very, very nice SQL work - and from reading through the joins, it looks
like nice design work as well.

Steve Howard


-Original Message-
From: Kutler, Christopher [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 17, 2001 11:37 AM
To: 'DBI User Group'
Subject: Complex SQL statments - Do they work?


Hello All

I've tried the following SQL statement which does work via SQL Server 6.5.
However, it does't seem to work when I run the same statment through DBI:

select p.pieceref, d.document_id, dd.full_date,
grant_description, heading,
convert(varchar(255), document_type_description) as document_description,
physical_format,
number_of_folios_etc, language = CASE
 when english_indicator = 1 then 'English'
when french_indicator = 1 then 'French'
when Latin_indicator = 1 then 'Latin'
 END, convert(varchar(255), document_note) as document_note,
names_indicator = CASE names_indicator when 1 then 'includes names of
individuals' END,
goods_indicator = CASE goods_indicator when 1 then 'includes record of goods
assessed' END
 from list_heading as lh
inner join piece_heading as ph on lh.heading_id = ph.heading_id
inner join piece as p on ph.piece_id = p.piece_id
left outer join document as d on p.piece_id = d.piece_id
left outer join document_grant as dg on d.document_id = dg.document_id
inner join grant_ as g on dg.grant_id = g.grant_id
inner join document_date as dd on d.document_id = dd.document_id
order by p.pieceref

Any help would be appreciated.

thanks

Chris

RE: :OBDC

[Steve Howard]

Mike,

In your control panel is an applet named ODBC Data sources. YOu need to
configure a DSN there (the machine on which your PERL application will run)
and point it to the access database that you want to access in your program.
The name you give to this DSN in the first page of your configuration of
this DSN is the DBI_DSN you are talking about that goes in the place of
YOUR_DSN_HERE in your code sample. The configuration of the DSN will differ
a little bit depending on which MDAC version you are using, but all of them
are pretty simple to configure.

Hope this helps.

Steve Howard

-Original Message-
From: Vasquez, Mike [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 27, 2001 5:54 PM
To: '[EMAIL PROTECTED]'
Subject: DBD::OBDC


I want to be able to access an Access db that resides on another machine.
Can I access this small database using DBD::OBDC.  If so, how and where do I
set up the following:

 DBI_DSN The dbi data source, e.g. 'dbi:ODBC:YOUR_DSN_HERE' DBI_USER

 DBI_USER
 DBI_PASS
 ODBCHOME

The DBI_USER and DBI_PASS I understand.  I'm not sure about the DBI_DSN.
How would one set this variable?

Mike (a newbie)

RE: Newbie Trying to fix

[Steve Howard]

Without looking at your code, I'd just guess your connection information is
not set right. Here's as example of how to connect to a MySQL database on
the local machine named intra_data using the "sa" user account with a
password of "sa". Edit as necessary. After you connect, you should be able
to prepare, and run your queries.



 use DBI;
  my $database_name = "intra_data";
  my $location  = "localhost";
  my $port_num  = "3306"; # This is default for mysql


  # define the location of the sql server.
  my $database  = "DBI:mysql:$database_name:$location:$port_num";
  my $db_user   = "sa";
  my $db_password   = "sa";

  # connect to the sql server.
  my $dbh   = DBI->connect($database,$db_user,$db_password);


Have fun,

Steve Howard

-Original Message-
From: Glenn Emery [mailto:[EMAIL PROTECTED]]
Sent: Saturday, March 24, 2001 8:22 PM
To: [EMAIL PROTECTED]
Subject: Newbie Trying to fix


Good Day,

Please understand I am very new to this..

I have recieved some perl scripts that hook to a Mysql database.

I was using "use DBI;" but ended up changing to "use Mysql;" after getting
numerous errors that stated "DBI->connect(supermall) failed: Access denied
for user: '@localhost' to database 'supermall' at
/var/www/cgi-bin/supermall/admin/add.cgi line 61
Couldn't connect to database!".
I am now able to hook to the database but after changing to "use Mysql" I
now get errors that state "Mysql::prepare: Not defined in Mysql and not
autoloadable (last try prepare) at /var/www/cgi-bin/supermall/admin/add.cgi
line 337" , is there something I can do about "prepare"?

Thank You!

Glenn

RE: Next - Previous Buttons

[Steve Howard]

>From the sql syntax I'm guessing you're using MySQL - ? Could you tell us
what DBMS you are using - That makes a difference. Or have you tried
printing the SQL statement that has been generated after your if-elsif-else
that builds the statement? If you can print that (maybe even to logfile) you
might run it from your query window to see that you're getting the results
you think. (I'd love to give an answer, but we really need to know what DBMS
you're using, and knowing how you know you're getting results of 4 would
also be helpful).

Steve Howard

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf
Of MikeBlezien
Sent: Monday, March 19, 2001 10:44 PM
To: [EMAIL PROTECTED]
Subject: Next - Previous Buttons


Hello All,

I know this is more of a Perl question then DBI, but I posted this a couple
of
days ago the one the Perl list without any results. What I am trying to do
is
limit a search result to 5 per page. Below is the bulk of the script minus
all
the HTML stuff. If someone maybe to lend some assitance, I would greatly
appreciated, more then you know. Been at this most of the day! When testing,
I
know I'm getting a results of 4, but when the first results pages loads(set
the
$Page = 0), it doesn't show any results, if I comment out the LIMIT clause,
then
it display all the results. Everything else works perfectly! :)

TIA
###

my $cgi = new CGI;
my $Page = $cgi->param('page') || "0";
my $State = $cgi->param('start');
my $Zipcode = $cgi->param('zipcode');
my $City = $cgi->param('city');
my $State = $cgi->param('State');
my $Category = $cgi->param('categories');
   $Category or error("Unable to process search request. You must select a
Category","No Category Selected");
my $KeyWords = $cgi->param('keywords');
my $MaxPerPage = $conf{'limit_display'}; # set to 5
my $CatCode = substr($Category,0,2);


   if ($KeyWords) {
   $KeyWords =~ s![\'\"]!!;
   $KeyWords =~ s!_!\\\_!;
   $KeyWords =~ s!%!\\\%!;
   $KeyWords =~ s!^\s+!!;
   $KeyWords =~ s!\s+$!!;
 my @words = split(' ', $KeyWords);
 $Searchwords = join(' ',@words);
 $Searchwords_q = quote("$Searchwords");
 }

my $CatDisplay = CategoryDisplay($Category);
my $City_q = quote($City);

# Start Building Search Query
$sql = qq|SELECT bi.bus_name,bi.address,bl.city,bl.state,
  bl.zipcode,h.hr_descript,bi.phone,bi.comments,bi.unique_url,
  CONCAT(bi.contact_fname," ",bi.contact_lname) AS name|;
   if (defined($Searchwords)) {
$sql .= qq| FROM bus_info bi,bus_search bs,bus_loc bl,hours h
WHERE bi.category = '$Category'
AND MATCH bs.keywords AGAINST ($Searchwords_q)
OR (bs.cat_prefix = '$CatCode' AND bs.bus_id = bi.info_id)|;
if ($Zipcode) {
   $sql .= qq| AND bl.zipcode LIKE '$Zipcode%'|;
 }
elsif ($City) {
   $sql .= qq| AND bl.city = $City_q|;
 }
elsif ($State !~ /ALL/) {
   $sql .= qq| AND bl.state = '$State'|;
 }
   } # close if ($Seachwords)
  else
   {
$sql .= qq| FROM bus_info bi,bus_loc bl,hours h WHERE bi.category
='$Category'|;
 if($Zipcode) {
   $sql .= qq| AND bl.zipcode LIKE '$Zipcode%'|;
 }
   elsif ($City) {
   $sql .= qq| AND bl.city = $City_q|;
 }
elsif ($State !~ /ALL/) {
   $sql .= qq| AND bl.state = '$State'|;
 }
   }

$sql .= qq| AND bi.info_id = bl.loc_id AND h.hr_code = bi.hrs_open
GROUP BY bi.bus_name ASC|;
   if ($Page == 0) {
   $sql .= qq| LIMIT $MaxPerPage|;
 } else {
   $sql .= qq| LIMIT $Start,$MaxPerPage|;
}

$sth = query($sql) or dbError();
my $data = $sth->fetchall_arrayref({});
$rows = $sth->rows;
$rows or error("Sorry, no search results where found","No Results Found!");


my $total_results = $rows
my $NextPage = ($Page + 1);
my $PrevPage = ($Page - 1);
my $PreviousPage = $PrevPage > 0 ? $PrevPage : "1";
my $start_row = (($Page) * $MaxPerPage);

# A bunch HTML stuff

# The mini forms to generate the Next or Previous Page.






RESULTS
if ($State !~ /ALL/i) {
print<
RESULTS
} else {
print<
RESULTS
 }
print<











RESULTS
if ($State !~ /ALL/i) {
print<
RESULTS
 } else {
print<
RESULTS
 }
print<









RESULTS
Mike(mickalo)Blezien

Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225) 686-2002
=

Re: Help with Access Datbase

[Steve Howard]

Paul,

Very quickly, assuming you have made a connection to the database: to insert
data, a very very simple example:

To select data:




my $sql_statement = "SELECT lastname, firstname, title FROM Tablename ORDER
BY lastname desc, title";

my $sth = $dbh->prepare($sql_statement);

  my ($last, $first, $title);

$sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
  $sth->bind_columns(undef, \$last, \$first, \$title);
while ($row = $sth->fetchrow_arrayref) {

# do something here
}


To insert the data is very easy, just prepare an insert statement - you
don't need all the bind_columns etc. Read perldoc dbi or other sources for
other ways to prepare and execute - that is just a very simple example.

Also, if I may stick my nose inif you are building a web site, and
transactions are not important to you (They must not be, or you wouldn't be
using access) might I recommend that you look at something like MySQL- it
will be much faster, and heavier duty and much free-er. When your web site
grows bigger, or if you want transactions, then you can look at a
heavier-duty back-end. (ok, my nose is back out of your business now).

Anyway, hope this helps get you started. Have fun.

Steve Howard





- Original Message -
From: "Paul Castiglione" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 19, 2001 7:00 PM
Subject: Help with Access Datbase


Hello

I am new to this new group I have desing several static website and I am
now going to try an website with an install databse it will be a ms Access
database. My question to this group is "Where can I find some samples of
source code for add records and listing records" Again thank for any help
you may be able to give me

Paul A. Castiglione

RE: MS Access

[Steve Howard]

I actually use dbd::odbc quite often to access an Access database (Although
I am usually migrating something OFF of the Access database rather than
using Access). It's not really any trick. You need to set up a DSN in your
ODBC Data Sources. That is very easy if you are on an MS platform (Just In
Case - It's in your control panel). If you are on a Linux platform, you can
get the iODBC drivers to access an Access database.

Once you get the DSN configured, and pointing to the Access database you
wish to access, all there is to it code wise is something like this:

  use DBI;
  my $dsn   = "dsn_name";
  my $database  = "DBI:ODBC:$dsn";
  my $db_user   = "";
  my $db_password   = "";

  # connect to the Access db.
  my $dbh = DBI->connect($database,$db_user,$db_password);

You should be there.


Steve Howard

-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 19, 2001 2:06 PM
To: Alex; [EMAIL PROTECTED]
Subject: Re: MS Access


Microsoft provides ODBC drivers for Access.  That should mean you can use
DBD::ODBC with DBI to work with Access.  Exactly how useful this is for you
depends on you platform.

Without knowing what you have already tried, and what problems you had, it
will be difficult for anyone on the list to make any suggestions.

--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
- Original Message -
From: "Alex" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 19, 2001 11:31 AM
Subject: MS Access


> Sorry to bother you but I feel pretty dumb by searching the net and
> reading page after page without understanding anything. I use perl and
> mysql alot and I would like to access a microsoft Acess MDB file just
> like I access a mysql database.
>
> What would I have to do? I just don't get it. Every driver I installed
> just gives me new hints an trying other installs and so on. Is there any
> page that explains actually what to do? I can't be the first person with
> this problem.

RE: Perl - Time Tracking

[Steve Howard]

Ted,

Have you tried the date-calc module available for PERL? You should be able
to do this with this module, and some good programming on your part.

Steve Howard

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ted
Hilts
Sent: Monday, March 19, 2001 10:42 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Perl - Time Tracking


This is my second perl problem.

I have this ftp routine and want to be able to measure the time
associated with each step in the routine.  You can see a routine snippet
on the list where I submitted the first problem and it is called Perl
Problem.

Before each $ftp step I want to be able to set up a condition so that
when the step completes I can determine how long the $ftp step took.  So
I want an ongoing determination of the time in the form of time
durations, not just a print out of what time it is.  By this means I can
monitor the routine and become aware of any problems.  Also, I would
like to be able (withing the perl code) to detect any time durations
exceeding a specified amount.

Thanking you in advance for any help.

Bye-thanks_TED

RE: A little Mysql question

[Steve Howard]

The question is perhaps a little more complex than you intended it to be.
There are a number of different types of joins, and two different types of
syntaxes. The examples I have seen given back to you are the Old syntax for
inner or cross joins. (actually a cross join performed with a where
table1.column=table2.column is a cross join, it is just limited so that it
acts like an inner join).

Assumming you want only an inner join (where only rows are returned where
there is a match for the condition in both queried columns) you can use the
syntax like

SELECT table1.column1, table2.column2
FROM table1, table2
WHERE table1.column1 = table2.column2

however, this syntax is the "old" syntax and using it makes it easy to
create ambiguous queries. Also, if you need to perform an outer join (other
than a cross join) you will need to go with something closer to the "New" or
ANSI syntax. MySQL supports very close to the New or ANSI syntax (except you
cannot leave the word "inner" out and have it default to an inner join):

SELECT Table1.column1, Table2.column2
FROM Table1 INNER JOIN table2
ON table1.column1 = table2.column2

One advantage of using the second syntax is for situation where you need to
perform an outer join. You could turn the previous into a left outer join by
only changing the word to designate the join type from "inner" to "Left"
before the join keyword like this:

SELECT Table1.column1, Table2.column2
FROM Table1 LEFT JOIN table2
ON table1.column1 = table2.column2

Very quickly, a left join would return all column1's from table1, and only
return a value for table2.column2 if it matched on the join criteria. A
Right outer join would return all values from table2.column2, and only
return a value from column1.column1 if it matched on the join criteria.
Different types of joins, however is something you'll have to study your
documentation, and play with in order to master.

In short, you can use the old syntax, but it is very clumsy, and very
limited. You would be well advised to learn the ANSI syntax for joins, and
use it. It will make your programs more portable, your queries less
ambiguous, and when you grow accustomed to the syntax, it will make complex
queries MUCH MUCH easier to read, and (if necessary) to debug.

Steve Howard


-Original Message-
From: Dexter Coehlo [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 15, 2001 3:29 PM
To: [EMAIL PROTECTED]
Subject: A little Mysql question



Hi folks,

whats the syntax to select from 2 tables using MySQl.

I tried  select from regusers,unregusers where email="name"


Dexter

RE: [Fwd: how to check to a database to see if i need update or insert]]

[Steve Howard]

OK, the re-preparing was what I didn't know about. actually, when I cut his
query, and pasted it in, I forgot to put the double quotes around the $host
to make it "$host". I do run into situations where someone has put a space
in a column name or put a & or #. Usually these are in access databases that
I am migrating to SQL. It handles them ok when I use something like:

my $column = "D&T Asset #";
my $select = qq{SELECT "$column" from $table};


or


my $select = qq {SELECT [$column] from $table};

and in this case if we had

$column = "Joe's Diner";

then:

my $select = qq{SELECT "$column" from $table};

still works without blowing up on the apostrophe in $column.

but having to re-prepare is something I had not considered before.


-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 15, 2001 11:38 PM
To: Steve Howard; Xiaoxia Dong; [EMAIL PROTECTED]
Subject: Re: [Fwd: how to check to a database to see if i need update or
insert]]


Comments below.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
- Original Message -
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "Michael A. Chase" <[EMAIL PROTECTED]>; "Xiaoxia Dong"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, March 15, 2001 5:35 PM
Subject: RE: [Fwd: how to check to a database to see if i need update or
insert]]


> I'm not sure if the reason for this is oracle specific, or something
coming
> from PERL, but I know I hate placeholders, and have yet to run into a
> situation why they are needed (although I will acknowledge that they are
> preferred by many). If I understand the cause of the error to be the
single
> or double quote nested within other quotes, would it not be easier to use:
>
> $sql = qq{UPDATE uptime SET up_time=$uphours
>  WHERE hostname = $host and

^

>  startdate between
>  (TO_DATE('$yy:$month:$sday:$shour:$sminute:00',
>  ':MM:DD:HH24:MI:SS') and
>  TO_DATE('$yy:$month:$sday:$shour:$sminute:59',
>  ':MM:DD:HH24:MI:SS')
>  )};
>
> and thus eliminate the need for placeholders here? or am I missing
something
> else in this?

You missed something and left in the exact same problem that started this
thread; the value in $host is not a valid column name.  You can put as many
single quotes (') as you want inside double quotes (") or vice-versa.  The
problem is that by pasting literal strings into SQL instead of using
placeholders you leave yourself wide open to this problem and worse.
$dbh->quote() can reduce the problem a bit, but it is not guaranteed to work
if some sick person sends you non-ASCII characters.

The real reason for placeholders, though is that they allow you to prepare a
statement once and execute it many times.  Preparing tends to be expensive
in time and resources so you want to do it as seldom as possible.  Normally
you prepare() a statement once before a loop and then execute() it many
times inside the loop.  Even when you don't repeatedly execute a statement
in one instance of your program, Oracle caches SQL it prepares and can
re-use the execution plan if it sees the exact same SQL (including spaces
and capitalization) again.

At one site I tuned, the system was re-parsing the same query very
frequently because the program was using a literal string for the user name
as part of login processing.  When the query was changed to use a bind
variable login time dropped from most of a minute to under ten seconds.

Run 'perldoc DBI' and read the sections marked 'Placeholders and Bind
Values', 'Performance', 'do' and, 'bind_param' for examples and discussions
of why placeholders are a good idea.

RE: [Fwd: how to check to a database to see if i need update or insert]]

[Steve Howard]

Michael,

I'm not sure if the reason for this is oracle specific, or something coming
from PERL, but I know I hate placeholders, and have yet to run into a
situation why they are needed (although I will acknowledge that they are
preferred by many). If I understand the cause of the error to be the single
or double quote nested within other quotes, would it not be easier to use:

$sql = qq{UPDATE uptime SET up_time=$uphours
 WHERE hostname = $host and
 startdate between
 (TO_DATE('$yy:$month:$sday:$shour:$sminute:00',
 ':MM:DD:HH24:MI:SS') and
 TO_DATE('$yy:$month:$sday:$shour:$sminute:59',
 ':MM:DD:HH24:MI:SS')
 )};

and thus eliminate the need for placeholders here? or am I missing something
else in this?





-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 15, 2001 5:24 PM
To: Xiaoxia Dong; [EMAIL PROTECTED]
Subject: Re: [Fwd: how to check to a database to see if i need update or
insert]]


This is one of those cases where placeholders would save you a lot of grief.
The value inserted into your SQL for $host is not a valid column name.  Even
if you quote it, something like "Joe's_PC" would kill it all over again.

Run 'perldoc DBI' to read the fine manual.  The sections on placeholders,
bind_param(), and execute() would be particularly interesting in this case.

See my suggested code below.

--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
- Original Message -
From: "Xiaoxia Dong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 15, 2001 6:53 AM
Subject: [Fwd: [Fwd: how to check to a database to see if i need update or
insert]]


> > > I am new to perl DBI. I am trying to do something that like machine
> > > uptime, i read all those in from a set of
> > > file. Everytime, if i found machine done, i need to insert a new
record,
> > > otherwise, i just need to update
> > > the existing record in the oracle database. How can i do it?
> > >
> > > Suppose i know i just need to update, i have the following sql
> > > statement:
> > >
> > >$sql = "UPDATE uptime SET up_time=$uphours
> > > WHERE hostname = $host and
> > > startdate between
> > > (TO_DATE('$yy:$month:$sday:$shour:$sminute:00',
> > > ':MM:DD:HH24:MI:SS') and
> > > TO_DATE('$yy:$month:$sday:$shour:$sminute:59',
> > > ':MM:DD:HH24:MI:SS')
> > > )";

   $dbh -> {RaiseError} = 1;
   my $fmt = ':MM:DD:HH24:MI:SS';
   my $ymdhm = "$yy:$month:$sday:$shour:$sminute";
  my $sth = $dbh -> prepare(
 "UPDATE uptime SET up_time = ?
 WHERE hostname = ? and
startdate between (TO_DATE( ?,'$fmt') and TO_DATE(
?,'$fmt')" );
   $sth -> execute( $uphours, $host, "$ymdhm:00", "$ymdhm:59" );

> > > when i try to execute this statement, it gave following messages:
> > > DBD::Oracle::st execute failed: ORA-00904: invalid column name (DBD
> > > ERROR: OCIStmtExecute) at upora.pl line 135.
> > > UPDATE uptime SET up_time=212.6333
> > > WHERE hostname = twister and
> > > startdate between
> > > TO_DATE('2001:3:8:5:34:00',
> > > ':MM:DD:HH24:MI:SS') and
> > > TO_DATE('2001:3:8:5:34:59',
> > > ':MM:DD:HH24:MI:SS')
> > >
> > > ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute)after get
into
> > > uptimeOracle
> > > how can i correct this one?

RE: Reusable code for binding columns.

[Steve Howard]

Thanks to all for the further discussion on this. After this tonight, going
back to my original question about reusable code for binding columns, this
makes for a slicker solution than I even though would come from asking the
question (and I know some of you are slick)  :-)

That would mean in my subroutine, I can do something like (shorthand
version, but still critique it if it needs it):

my $select = qq{SELECT $columnlist FROM $ini->{sourcedb}..$table};
my $columns = $columnlist;

# Allow for the possibility of non-standard column names

  $columns =~ s/ //;
  $columns =~ s/,/ /;
my $selprep = $source->prepare($select) || print logfile "Can't prepare:
$select \n";
  $selprep->execute() || print logfile "Can't Execute $select \n";

# and the solution to my original question:

  $rc = $sth->bind_columns(\@column{qw($columns)});


And be able to refer to any number of columns by names still recognizable as
the original column name (minus any spaces that might have been in the
source), and still even have the original column list in tact to work with
later.

Cool!  That's even better than I exected as a solution.

Thanks a lot to all of you who responded, and contributed.

Steve Howard

-Original Message-
From: James Maes [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 09, 2001 11:28 PM
To: Sterin, Ilya; Matthew O. Persico; [EMAIL PROTECTED]
Subject: Re: Reusable code for binding columns.




still need an ='s sign for anonymouse arrays

@hash = {'hi_there','bye_there','over_there'};
$hash{'bye_there'} = "testing";
print $hash{'bye_there'};


On Friday 09 March 2001 23:10, Sterin, Ilya wrote:
> That's a good explanation, since I myself understood everything except the
> @ usage for this expression.  Now I can see, but why doesn't this work
with
> my perl5.6
>
> use strict;
> my @hash{'hi_there','bye_there','over_there'};
> $hash{'bye_there'} = "testing";
> print $hash{'bye_there'};
>
> Any ideas, it comes with...
> syntax error at test.pl line 2, near "@hash{"
> Execution of test.pl aborted due to compilation errors.
>
> -Original Message-
> From: Matthew O. Persico [mailto:[EMAIL PROTECTED]]
> Sent: Friday, March 09, 2001 11:56 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Reusable code for binding columns.
>
> "Thomas A. Lowery" wrote:
> > OK Randal, how does this work?  I put it in code and see it WORKS, but
my
> > brains hurts trying to understand it.
> >
> > > $rc = $sth->bind_columns(\@column{qw(one two three four five)});
>
> Step back a bit.
>
> An array element is $array[0].
>
> An array slice is @array[1,2,3,4], for example.
>
> A hash element is $hash{'hi_there'}. I know you don't NEED the quotes,
> but they make sense for the purposes of the demo.
>
> A hash slice is @hash{'hi_there','bye_there','over_there'} for example.
>
> 
> As I understand it the reason that a HASH slice is defined with an ARRAY
> indicator (@) is that in this context, @ is NOT an array indicator -
> it's a LIST indicator. The type of list is defined by the brackets; []
> is an array, a list of elements, {} is a hash, a list of paired
> elements.
> 
>
> Therefore, @column{'one', 'two', 'three', 'four', 'five'} is a hash
> slice, which is simply a list of the values at these keys of the hash
> %column.
>
> Typing all the quotes is a PITA, so we use the "quote word" operator to
> simplify things to
>
> @column{qw(one two three four five)}
>
> Now, according to the docs (http://www.perldoc.com/cpan/DBI.html)
>
>   bind_columns
>
> $rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
>
>   Calls /bind_col for each column of the SELECT statement. The
> bind_columns method will die
>   if the number of references does not match the number of fields.
>
> So, in order to get a list_of_refs, you put \ before the list. This does
> NOT create a reference to the whole list, as you'd might expect. Rather
> the "ref"-ness gets distributed to each element of the list, creating a
> list of REFs.
>
> Personally, I think this is a perfect example of the "beauty" of Perl.
> Visually, \@column{qw(one two three four five)} LOOKS like a ref of a
> list. Bit, I don't think there IS such a thing.
>
> \@foobar IS a ref of an ARRAY.
> \%baba is a ref of a HASH.
> \(1,2,3,4) is a ref of a LIST. But what's the point of that? If you want
> an ARRAY ref, you can

RE: Limiting the number of records selected.

[Steve Howard]

Bill,

I'm sure somebody else will have something more specific than this for your
needs, but let me give it a shot with the Syntax of the DBMS I use all the
time (Which, also does not have a limit keyword). Do you have a TOP keyword?
If so, you can probably do something like:

SELECT TOP 10 Column
FROM DB..Table
WHERE Column NOT IN
(SELECT TOP 20 Column
FROM DB..Table
ORDER BY Column)
ORDER BY Column


That would give you records #21-30 of the full result set (If you have a TOP
keyword).

Hopefully something like this will help.

Steve Howard


-Original Message-
From: Bill OConnor [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 09, 2001 4:48 PM
To: [EMAIL PROTECTED]
Subject: Limiting the number of records selected.


I can do this easily with MySQL but doing it with
Oracle has not been that obvious to me.  I want to use
a subset of the selected rows on a webpage.  For
instance if the query returns 100 rows I want show
just 10 of them on the page, 11-20 on the next etc.
Is it possible to specify the range of rows returned
from the select as part of the select statement?

I think I said that right.

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/

Reusable code for binding columns.

[Steve Howard]

Sorry, let me clarify, the INSERT...SELECT statement in this e-mail works
just fine. The $columnlist is built properly, and that is not a problem -
Maybe I confused the issue when I included it. I was just trying to expound
on what I was doing and maybe show to what extent I am trying to make the
scripts reusable.

The reason I need to bind columns is when I need to
scrub/convert/converge/split and/or print to file as intermediate steps. The
code I'm trying to make reusable is the part that selects this, and the
obstacle I run into is in building the bind_columns list without knowing at
the time I write the script how many columns are going to be returned by the
select statement. If I can just get the bind_columns built so that it works,
and this is reusable, then my scripting time is greatly reduced.

Sorry I was nor more clear on the first e-mail.

Steve Howard


-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 07, 2001 9:16 PM
To: [EMAIL PROTECTED]
Subject: Reusable code for binding columns.


Does anyone have any ideas on how to make this work:

I write a lot of scripts to do migrations. I try to make as much of my code
reusable as possible. Straight table copies are no problem after I query the
system tables to get the table, and column names, and build something like:

INSERT INTO $ini->{targetdb}..$table ($columnlist) SELECT $columnlist FROM
$ini->{sourcedb}..$table

My problem comes with finding a way to build reusable code for a subroutine
to deal with tables that will not go straight across. The obstacle is in
this statement:

$row = $select->bind_columns(undef, \$column1, \$column2..

Again, I can get the column names by querying the system catalogs.  I don't
necessarily have to bind them by anything resembling their column name, I
only need a way to reference them. So once I get the number of columns into
the script, how can I then assign variable, or hash key names so that I can
build a bind_columns statement that can work?

I may be just too close to this to see something obvious - whatever the
case, I would greatly appreciate any ideas that will help with this.

Thanks,

Steve Howard

Reusable code for binding columns.

[Steve Howard]

Does anyone have any ideas on how to make this work:

I write a lot of scripts to do migrations. I try to make as much of my code
reusable as possible. Straight table copies are no problem after I query the
system tables to get the table, and column names, and build something like:

INSERT INTO $ini->{targetdb}..$table ($columnlist) SELECT $columnlist FROM
$ini->{sourcedb}..$table

My problem comes with finding a way to build reusable code for a subroutine
to deal with tables that will not go straight across. The obstacle is in
this statement:

$row = $select->bind_columns(undef, \$column1, \$column2..

Again, I can get the column names by querying the system catalogs.  I don't
necessarily have to bind them by anything resembling their column name, I
only need a way to reference them. So once I get the number of columns into
the script, how can I then assign variable, or hash key names so that I can
build a bind_columns statement that can work?

I may be just too close to this to see something obvious - whatever the
case, I would greatly appreciate any ideas that will help with this.

Thanks,

Steve Howard

RE: Can you recomend a DBMS?

[Steve Howard]

It depends on everything from the size of the database(s) you plan to need
on the backend of your web-site to the size of your budget, and what OS you
will be using for your database server. There is no one right answer for
this. Consider how critical your data is, what you can afford whether or not
replication will be necessary (for reporting or to create a standby
server).you need to make a good determination of your needs before
anyone can really give an intelligent recommendation of a DBMS.

Steve Howard

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Barry Jeapes
Sent: Monday, March 05, 2001 5:22 AM
To: [EMAIL PROTECTED]
Subject: Can you recomend a DBMS?


Hi All,

Im in the very early stages of developing an intranet website where I
have scope to
choose which DBMS software I want to install and use.

Since this is a new area of web development for me I have little/no
experience of which DBMS' are good and bad.  Of course I could just try
a few, but as always time scales dont allow me to mess around finding
which are suitable / unsuitable from scratch.
Any of you guys willing to recommend some software or even suggest some
that arent up to much?
Maybe there are some independant websites around that review DBMS'?  I
havnt found any.

It will be installed onto a UNIX system (Solaris).  The intranet is
currently served by Apache.

Your advice / help will be much appreciated.

Cheers,

Barry.

RE: DBI install help

[Steve Howard]

It sounds like you are behind a proxy. You need to get either the proxy
name, or the proxy address If this is the case, then at the command prompt
(not PPM, but rather at the shell command prompt) type

set http_proxy=http://proxy

(if the name of your proxy is not "Proxy" change that name to either the
name or the IP address of your proxy)

Once this environment variable is set, the PPM module will know how to get
out to the internet to find the packages you are searching for.

you might confirm that the environment variable is set for the proxy by
typing:

echo %http_proxy%

it should echo the definition you put in for the environment variable.

See if this fixes the PPM not finding anything.

Steve Howard


-Original Message-
From: Prabhakar, V [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 02, 2001 4:27 PM
To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE: DBI install help


I typed PPM at the command prompt and then type search to see the modules
that can be installed. I get an empty list. Thus I do not have DBI to
install. I have attached the output from PPM

PPM> search
Packages available from http://www.ActiveState.com/packages:

PPM> set
Commands will be confirmed.
Temporary files will be deleted.
Case-insensitive searches will be performed.
Package installations will continue if a dependency cannot be installed.
Screens will not pause.
Current PPD repository paths:
ActiveState Package Repository: http://www.ActiveState.com/packages
Packages will be installed under: C:\Perl
Packages will be built under: C:\TEMP
PPM>

Why don't I get modules for install from ActiveState?

-V Prabhakar

-Original Message-
From: Randy Peterman [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 02, 2001 5:15 PM
To: [EMAIL PROTECTED]
Subject: Re: DBI install help


You need to install ActiveState Perl, don't mess with doing things the other
way unless you want to do some very weird tweaking of your system.  You
should download the latest version from active state and then at the command
prompt type PPM.  From there type install DBI
then type install DBI::YourDBpackagehere

This should also allow you to install any packages you previously had
installed as well.  DBI is definitely available for ActiveState.

If that doesn't work then ActiveState perl is not installed.

Randy Peterman
Alt-N Technologies
www.altn.com

Helping the World Communicate.
- Original Message -
From: "Prabhakar, V" <[EMAIL PROTECTED]>
To: "'Michael A. Chase'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, March 02, 2001 3:53 PM
Subject: RE: DBI install help


> Ok I picked up the online doc on PPM and tried it. My current repository
is
> pointing to www.AcitveState.com/packages. There is no DBI module there and
> hence would like to point my repository to a site which has the PPD file.
I
> could find the sites which have the zip files (with source) for DBI
module.
>
> As the email says, for active perl, using PPM seems to be the recommended
> procedure and hence I am looking for prebuilt DBI package to install on my
> WNT system.
>
> Any help will be appreciated.
>
> -V Prabhakar
>
>
> -Original Message-
> From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
> Sent: Friday, March 02, 2001 3:33 PM
> To: Prabhakar, V; [EMAIL PROTECTED]
> Subject: Re: DBI install help
>
>
> You're not reading the fine manual that came with your ActiveState Perl.
> Particularly the parts on PPM.
> --
> Mac :})
> ** I normally forward private database questions to the DBI mail lists. **
> Give a hobbit a fish and he'll eat fish for a day.
> Give a hobbit a ring and he'll eat fish for an age.
> - Original Message -
> From: "Prabhakar, V" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, March 02, 2001 9:22 AM
> Subject: DBI install help
>
>
> > I download the DBI zip file and I am trying to install it on my system.
I
> am
> > getting linker error while trying to make the file. I have attached the
> > output of the makefile and output of my perl -V command.
> >
> > Please let me know what am I doing wrong.
>
>

FW: Possible Stupid DB-User trick???

[Steve Howard]

Looking at your syntax again, I think the reason the single quotes on the
string fixed your problem is not because of ANSI QUOTED identifiers (Like I
said before) nor because the ODBC connection wants single quotes around a
string but because of your syntax. You are using double quotes around your
entire statement, and trying to use them as well on your strings (Is this an
accurate portrayal of your code?) thus if you did a

print "$insertSql \n"

after you define your statement, you probably will see that $insertSql is
actually only:

INSERT INTO Cohorts
(Agency,IssueYear,Amort,OriginalTerm,NetCpn,FactorDate,
 NumPools, OrigBal, CurBal, WAC, WALA, WAM, WARM,
CPR_1mo,CPR_3mo,CPR_1yr,CPR_Life )
 VALUES
(


with no values passed. And it gets confusing to you when you try to use dots
to concatenate. It is SO much neater if you use this syntax:

$insertSql = qq{INSERT INTO Cohorts
(Agency,IssueYear,Amort,OriginalTerm,NetCpn,FactorDate,
 NumPools, OrigBal, CurBal, WAC, WALA, WAM, WARM,
CPR_1mo,CPR_3mo,CPR_1yr,CPR_Life )
 VALUES
("GOLD",1993,"FIXED",180,5.50,#07/01/93#,
1,4.00,4.00,6.05,4,176,172,NULL,NULL,NULL,NULL)};

(Double q's and curly brackets - that's hard to see in the e-mail)

Try that in the future, and see if it makes it easier to put your SQL
statements together.

Steve Howard

-Original Message-
From: Millman, Phillip
To: '[EMAIL PROTECTED]'
Sent: 03/01/2001 9:11 AM
Subject: Possible Stupid DB-User trick???

I'm using Win32::ODBC (On NT) to attach to a Access 2000 DB. (The
problem
exists on 97 as well).

$insertSql = "INSERT INTO Cohorts
(Agency,IssueYear,Amort,OriginalTerm,NetCpn,FactorDate,
 NumPools, OrigBal, CurBal, WAC, WALA, WAM, WARM,
CPR_1mo,CPR_3mo,CPR_1yr,CPR_Life )
 VALUES
("GOLD",1993,"FIXED",180,5.50,#07/01/93#,
1,4.00,4.00,6.05,4,176,172,NULL,NULL,NULL,NULL)"

my code reads...

 if ($speedDB->Sql($insertSql)) {
($ErrNum, $ErrTxt, $ErrCon) = $speedDB->Error();
if ( $ErrNum == -1605 ) {
  print STDERR "Dup Key Error *** Record Num: ".($numInserted +
1)."
... Skipped".EOLn;
} else {
  print "~ ABORT Run!!!  ".($numInserted + 1).EOLn;
  print "~ ".$insertSql.EOLn;
  croak "~ Insert SQL Failed.".EOLn."Error:
".Win32::ODBC::Error().EOLn."Note: ".$ErrTxt;
}

The error message reads..

~ ABORT Run!!!  1
~ INSERT INTO Cohorts
(Agency,IssueYear,Amort,OriginalTerm,NetCpn,FactorDate,
 NumPools, OrigBal, CurBal, WAC, WALA, WAM, WARM,
CPR_1mo,CPR_3mo,CPR_1yr,CPR_Life )
 VALUES
("GOLD",1993,"FIXED",180,5.50,#07/01/93#,
1,4.00,4.00,6.05,4,176,172,NULL,NULL,NULL,NULL)
~ Insert SQL Failed.
Error: [-3010] [1] "[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 2."
Note: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected
2.


What is the second parameter if any?  The Docs don't have one.
Thanks!!!
--
Phillip Millman
UBS Warburg
1285 Avenue of the Americas
New York, NY 10019
V: 212-713-4725

RE: (Fwd) Problem Configuring DBI

[Steve Howard]

WWW.activestate.com has activestate's binary version of PERL for win32
environments (That's what I use) You will definitely be glad you did so when
you switch to this version. Install the active state version.

After you are installed, if you are behind a Proxy, then from a command
prompt issue this command:

set http_proxy=http://Proxyname

only replace "proxyname" with either the name, or IP address of your proxy.

Issue

echo %http_proxy%

to be sure the variable is set.

Next, from that command prompt type:

ppm

and return. You will get a PPM> prompt. To be sure your machine is
configured to allow PPM to get to the internet, type:

search dbi

If you are configured right, it should return at least one site where dbi is
available. Just issue:

install dbi

PPM will do all the work for you. After the dbi is installed, search, and
install your dbd the same way. for example:

install dbd::odbc

will install the interface that will allow you to use ODBC connections to
databases in you scripts - again, all the work is done for you by the PPM.

It's that simple - provided you have the Activestate port of PERL.

Have Fun,

Steve Howard

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 01, 2001 10:11 AM
To: Tim Bunce
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: (Fwd) Problem Configuring DBI



On NT I would just untar the file to perl\lib.  It should extract to a
folder  called DBI-  Then run Makefile.PL, nmake(or dmake) etc in that
folder.

The easiest way to install this module in nt is to run 'ppm install DBI'.

Chris






Tim Bunce
  cc: [EMAIL PROTECTED]
 Subject: (Fwd) Problem
Configuring DBI
03/01/01
09:00 AM






- Forwarded message from Mamta Singh <[EMAIL PROTECTED]> -

From: Mamta Singh <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Problem Configuring DBI
Date: Thu, 1 Mar 2001 17:42:41 +0530

I have installed perl in my Win NT machine. I have now downloaded
DBI-1.14.gz from CPAN. now where to install the DBI. I mean in which folder
of PERL should i install it. I don't want to do the FTP and i am working
with latest version or PERL.

--
Mamta Singh
Netacross Ltd
B-65,Okhla Phase 1
New Delhi - 110020
Ph: 91 6812931-36 Ext 2024

Are You Across Yet?


__
IMPORTANT NOTICE
This e-mail is confidential, may be legally privileged and is for the
intended recipient only.  Access, disclosure, copying, distribution or
reliance on any or all of its contents by anyone else is prohibited and may
be an offence.  Please delete if obtained in error and e-mail a
confirmation
to the sender. Please note that this is without prejudiced communication
and
nothing contained herein shall be construed as statements made on behalf of
NetAcross Ltd. and/or as creating any legally binding obligations on
NetAcross Ltd.


- End forwarded message -

RE: Possible Stupid DB-User trick???

[Steve Howard]

Another thing: Check your settings on your DSN configuration. Setting Ansi
Quoted Identifiers "on" causes everything in double quotes to be seen as a
column name. You can get some additional flexibility on this by setting the
ANSI quoted identifiers off. That may not be your problem, but I have run
into that in the past. Just thought it might help.

Steve Howard

-Original Message-
From: Millman, Phillip [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 01, 2001 11:33 AM
To: '[EMAIL PROTECTED]'
Subject: RE: Possible Stupid DB-User trick???


The problem is my own idiocy... Strings need a single tick instead of a
double tick.

Duh

It sure feels great when I stop banging my head against the wall.

P

> -Original Message-
> From: Millman, Phillip [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 01, 2001 11:12 AM
> To: '[EMAIL PROTECTED]'
> Subject: Possible Stupid DB-User trick???
>
>
> I'm using Win32::ODBC (On NT) to attach to a Access 2000 DB.
> (The problem
> exists on 97 as well).
>
> $insertSql = "INSERT INTO Cohorts
> (Agency,IssueYear,Amort,OriginalTerm,NetCpn,FactorDate,
>  NumPools, OrigBal, CurBal, WAC, WALA, WAM, WARM,
> CPR_1mo,CPR_3mo,CPR_1yr,CPR_Life )
>  VALUES
> ("GOLD",1993,"FIXED",180,5.50,#07/01/93#,
> 1,4.00,4.00,6.05,4,176,172,NULL,NULL,NULL,NULL)"

[SNIP]

> --
> Phillip Millman
> UBS Warburg
> 1285 Avenue of the Americas
> New York, NY 10019
> V: 212-713-4725
>

RE: Problem with CHAR Data Type in ORACLE

[Steve Howard]

It sounds like the trailing blanks are causing it to not match. I know MS
SQL has a rtrim() function. do you have anything comparable in Oracle? (I'm
sure you do). If so, and this is the problem, try something like:

SELECT RTRIM(name) FROM junk WHERE RTRIM(id)='1001'

(That's MS Syntax, but I'm sure Oracle will have a similar way of doing it.)


Steve Howard


-Original Message-
From: Guru Prasad [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 11:59 PM
To: [EMAIL PROTECTED]
Subject: Problem with CHAR Data Type in ORACLE



Dear Friends,

I am unable to get any records using SELECT command. I tried the following
SQL statement.

"select name from junk where id='1001'"

the field 'id' is of type CHAR(5). I didn't get any records ( when i am
sure that the data is available for '1001' ). If i changed the data type
to VARCHAR(5), it is working fine. Why is it so ?

Is there any solution for this one. Any patchup ?. I can't use VARCHAR(5)
b'coz in our CLIENT system, they use only 'CHAR'.

I am using DBI Version 1.14.

Any help would be greatly appreciated.

Thanx in Advance.

guru.
bk SYSTEMS.

P.S: Don't ask me to use "select name from junk where id like '1005%'", as
i know this works but it is not an elegant way of doing it.

RE: Using DBI when MySQL is on another server

[Steve Howard]

Glad to help :)

-Original Message-
From: David Coley [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 9:19 PM
To: [EMAIL PROTECTED]
Subject: RE: Using DBI when MySQL is on another server


Hey guys I would just like to say thanks... Rinke and Steve both remind my
thick skull that athough the damon is on a differnt machine I still need to
install the mysql client on the app machine (doh).  Just goes to show you...
there's always something you forget.

David Coley

-Original Message-
From: Reinke Bonte [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 10:05 PM
To: David Coley
Subject: Re: Using DBI when MySQL is on another server


I reply private, because I'm not an expert with MySQL. For Oracle at least,
you need to have the Oracle client already installed on the same machine
where you install DBD::Oracle. This is because the installation needs some
header files from the Oracle client. It's probably the same with MySQL:
although the MySQL server is on remote machine, you need a client on the
local machine.


I hope that helps, at least to rephrase your question to make it
understandable for the experts.


-ren


- Original Message -
From: "David Coley" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 27, 2001 10:58 AM
Subject: RE: Using DBI when MySQL is on another server


> >From the two responces I've gotten back, I know I didn't phrase this
right.
>
> My problem is when I get to the point:
>
> Where is your MySQL installed? Please tell me the directory that
> contains the subdir 'include'. [/usr/local]
> Cannot find one of include/mysql/mysql.h, include/mysql.h in /usr/local at
> lib/D
>
> I need to tell it that MySQL is located on a differnt server and not the
> local machine... is there a way around this?  And still give me access to
> DBI on this machine?
>
> David Coley
>
>
> -Original Message-
> From: Sterin, Ilya [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 26, 2001 9:44 PM
> To: David Coley; [EMAIL PROTECTED]
> Subject: RE: Using DBI when MySQL is on another server
>
>
> Read DBD::mysql for proper connect statement.  One of them should have a
> specified location of mysql which can be a network address I believe.
>
> Ilya Sterin
>
> -Original Message-
> From: David Coley [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 26, 2001 9:31 PM
> To: [EMAIL PROTECTED]
> Subject: Using DBI when MySQL is on another server
>
>
> I've looked through the docs on this so I thought I'd go to the horse
mouth.
> We use MySQL however it is on a different server than the Application will
> be running.  I want to use DBI with MySQL support in order to help handle
my
> perl calls to MySQL, however when I get to the line asking for where MySQL
> is installed I do not know what to do.  Can DBI run on a different server
> than the on that MySQL is install on?  I figure it must be able to since
you
> can access MySQL from differnt Servers.
>
> Any help appreciated.  Sorry if this question has been answered, I have
not
> found any archives of the list.
>
> David Coley
>
>

RE: Using DBI when MySQL is on another server

[Steve Howard]

David,

I apologize, I just realized I was responding only to you, and not cc'ing
the rest of the group. Here is the connection info cut straight out of a
call-tracking app I wrote. The dbi, and dbd::odbc are installed on the local
machine, and the MySQL daemon is running on another machine. This is exactly
how it worked. substitute your own IP address and port number for where your
MySQL daemon is running, and of course, the user and password information
and it should connect, and work.

Steve Howard

 use DBI;
  my $database_name = 'intra_data';
  my $location  = '10.17.191.100';
  my $port_num  = '3306'; # This is default for mysql



  # define the location of the sql server.
  my $database  = "DBI:mysql:$database_name:$location:$port_num";
  my $db_user   = "sa";
  my $db_password   = "sa";

  # connect to the sql server.
  my $dbh   = DBI->connect($database,$db_user,$db_password);



my $sql_statement = "SELECT call_no FROM calltrac ORDER BY call_no DESC
LIMIT 1";

my $sth = $dbh->prepare($sql_statement);

  my ($call_no);

$sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
  $sth->bind_columns(undef, \$call_no);
  my $row;

while ($row = $sth->fetchrow_arrayref) {


#you get the idea

-Original Message-
From: David Coley [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 8:58 PM
To: [EMAIL PROTECTED]
Subject: RE: Using DBI when MySQL is on another server


>From the two responces I've gotten back, I know I didn't phrase this right.

My problem is when I get to the point:

Where is your MySQL installed? Please tell me the directory that
contains the subdir 'include'. [/usr/local]
Cannot find one of include/mysql/mysql.h, include/mysql.h in /usr/local at
lib/D

I need to tell it that MySQL is located on a differnt server and not the
local machine... is there a way around this?  And still give me access to
DBI on this machine?

David Coley


-Original Message-
From: Sterin, Ilya [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 9:44 PM
To: David Coley; [EMAIL PROTECTED]
Subject: RE: Using DBI when MySQL is on another server


Read DBD::mysql for proper connect statement.  One of them should have a
specified location of mysql which can be a network address I believe.

Ilya Sterin

-Original Message-
From: David Coley [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 9:31 PM
To: [EMAIL PROTECTED]
Subject: Using DBI when MySQL is on another server


I've looked through the docs on this so I thought I'd go to the horse mouth.
We use MySQL however it is on a different server than the Application will
be running.  I want to use DBI with MySQL support in order to help handle my
perl calls to MySQL, however when I get to the line asking for where MySQL
is installed I do not know what to do.  Can DBI run on a different server
than the on that MySQL is install on?  I figure it must be able to since you
can access MySQL from differnt Servers.

Any help appreciated.  Sorry if this question has been answered, I have not
found any archives of the list.

David Coley

FW: MS SQL 7 and identity columns insert problem

[Steve Howard]

Sorry, a possibly significant typo in my originalautocommit is left on
the default (ON)- not off.

Thanks,

Steve Howard

Has anyone been successful in doing explicit inserts into columns with
identity properties from PERL?  I would appreciate any help, or any
experience anyone has had with this.

I am using dbi with dbd::odbc, and MS SQL 7.0. autocommit is left on the
default (off). I have thus far tried unsuccessfully two different ways to do
this.
The first way is as follows:

$sth = $dbh->prepare(qq{SET identity_insert $target..$table ON});
$sth->execute() || print "$target..$table does not have the identity
property \n";

my $insert=qq{INSERT INTO $target..$table ($columnlist) SELECT
$columnlist FROM $source..$table};
my $sth = $dbh->prepare($insert);
$sth->execute || die "Can't execute ($insert): $dbh->errstr";



This prints the message I specified for all tables that do not have identity
property, and the insert works perfectly on them. When it hits a table with
an identity column, even though I have set identity insert on for that
table - and that is session-specific normally - I still get the error
telling me:

Cannot insert explicit value for identity column in table
 when IDENTITY_INSERT is set to off.



The second way I have tried (Doesn't really make sense that a table property
can be set here, but if it can, I don't have it right) is to connect as so:

my $dbh = DBI->connect($dsn, $db_user, $db_password,
{IDENTITY_INSERT=>1});

This does not produce an error - it just doesn't work.

In our case a DTS package to do this migration will be much less flexible,
and much more work for the number of databases, and possible situations than
what we have almost worked out. I would appreciate any help as this is too
close to perfection to be hung on this detail at this point in the project.



Thanks,



Steve Howard

MS SQL 7 and identity columns insert problem

[Steve Howard]

Has anyone been successful in doing explicit inserts into columns with
identity properties from PERL?  I would appreciate any help, or any
experience anyone has had with this.

I am using dbi with dbd::odbc, and MS SQL 7.0. autocommit is left on the
default (off). I have thus far tried unsuccessfully two different ways to do
this.
The first way is as follows:

$sth = $dbh->prepare(qq{SET identity_insert $target..$table ON});
$sth->execute() || print "$target..$table does not have the identity
property \n";

my $insert=qq{INSERT INTO $target..$table ($columnlist) SELECT
$columnlist FROM $source..$table};
my $sth = $dbh->prepare($insert);
$sth->execute || die "Can't execute ($insert): $dbh->errstr";



This prints the message I specified for all tables that do not have identity
property, and the insert works perfectly on them. When it hits a table with
an identity column, even though I have set identity insert on for that
table - and that is session-specific normally - I still get the error
telling me:

Cannot insert explicit value for identity column in table
 when IDENTITY_INSERT is set to off.



The second way I have tried (Doesn't really make sense that a table property
can be set here, but if it can, I don't have it right) is to connect as so:

my $dbh = DBI->connect($dsn, $db_user, $db_password,
{IDENTITY_INSERT=>1});

This does not produce an error - it just doesn't work.

In our case a DTS package to do this migration will be much less flexible,
and much more work for the number of databases, and possible situations than
what we have almost worked out. I would appreciate any help as this is too
close to perfection to be hung on this detail at this point in the project.



Thanks,



Steve Howard