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,
yours
The users_old table has 120,000 rows and the users_new has 910,000 rows.
/yours
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,
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
yours
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 :)
/yours
Right, no worries, but a good point to
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
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
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.
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
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
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
)
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
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
DEALLOCATE usr
/ISQL Script
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
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( SQL );
select o.name
from sysobjects o, sysusers u
where
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
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
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}});
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
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
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:
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
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
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
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 -QDBCC
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
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
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
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
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
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
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
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
do a
SELECT COUNT(*) FROM tablename 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
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
of a certain row.
- Steve Howard [EMAIL PROTECTED] on 07/07/01 17:54:18 -0500:
do a
SELECT COUNT(*) FROM tablename 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
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
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
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
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
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.
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
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
$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
.
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
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
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
.
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
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
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
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
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
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
= "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
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
-Origin
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, Marc
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
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
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
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.
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???
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
. 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
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
63 matches
Mail list logo
