RE: Does perl has DBD for MS Access?
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: ::massive sql query using dbi - please help::
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, 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: Copying image data from Sybase to Mssql or vice versa
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: Column Names
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 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: OLE exception
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: Quick Question
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 ...
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
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: pasted 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. /pasted 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
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: 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
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: selector screws do in ODBC 0.28
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
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? ISQL Script 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 /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 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: selector screws do in ODBC 0.28
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 o.type = 'U'
and u.uid = o.uid
and u.name = ?
SQL
you then execute, and use the while loop to cursor through the results. So
long as you have an active cursor, the connection is busy with the results
of this statement. From within the while loop, you call the Perms subroutine
which attempts to use the same database handle to attempt to insert. It
cannot execute this insert because this connection is busy. This is normal
behavior using ODBC, and the error you are receiving back is an ODBC error.
To get rid of this error, do not disconnect the $hDB2 database handle, and
edit the subroutines to use the $hDB2 database handle for their inserts or
updates. That way, the while loop can process the results in the $hDB
database handle, and the updates are made in a handle that is not busy with
the results of the previous statement.
This should work - it is a method I use quite frequently to process
something based on results of a previous select. See if it works for you.
Steve H.
-Original Message-
From: Tim Harsch [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 28, 2001 6:07 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: selector screws do in ODBC 0.28
Hi Jeff,
I have a program listed below that shows what I think
is a bug.
you can call the script with these parameters:
-G user_name -D database -r
uncomment the line that is a select user_name() and
you will get this error:
The following actions have been performed:
DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server
Driver]Connection is busy with results for another
hstmt (SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
at t line 68.
DBD::ODBC::db do failed: [Microsoft][ODBC SQL Server
Driver]Connection is busy with results for another
hstmt (SQL-S1000)(DBD: st_execute/SQLExecute err=-1)
at t line 68.
use DBI;
my $h = { RaiseError =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( SQL );
select o.name
from sysobjects o, sysusers u
where o.type = 'U'
and u.uid = o.uid
and u.name = ?
SQL
$sth-execute( $user );
my $c=0;
while( my $aref = $sth-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.
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
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
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 'BRUser 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?
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: 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.
RE: Perl DBI for SQL Server 7 ?
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.
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.
another performance question.
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:
direct deref snip
while ($row = $selecth-fetch) {
$inserth-execute(@$row);
}
/direct deref snip
bind_columns snip
$selecth-bind_columns(undef, \(@val[0..13]));
while ( $selecth-fetch) {
$inserth-execute(@val);
}
/bind_columns snip
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: another performance question.
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
RE: dbi and dbcc on WinNT 4.0
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 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
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
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
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
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.
RE: DBI-MSAccess problem
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: 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???
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
btrieve database driver?
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: Checking for the existence of a certain row.
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 [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.
Comparison not producing what is expected.
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.
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 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. 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
bind columns
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: SQL query
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(datecolumn) 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 ?
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: 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.
Values getting out of order between binding and execute
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:
aside I am writing this from home, I work on this project at work. the
code I am giving here is hend typed into the e-mail, so I'm not going back
to use strict or -w, and I might miss a semicolon, but the part I am having
trouble with is between binding and execute, and the concept I am using to
get it there./aside
#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: Values getting out of order between binding and execute
In this case, the publishing DB is MS SQL 7.0, so I have created the
$columnlist using a:
SELECT name FROM syscolumns WHERE id = object_id('$tablename')
ORDER BY colorder
I use those results to build $columnlist, then use it both for the select,
and for the insert queries to keep that order the same. I actually derive
@columns from this using:
my @columns = split /, /, $columnlist;
Some parts of the script lend themselves to use of the array, but the
derived queries I preferred the $columnlist. I then use the information in
the tracking database to build $where.
I used something similar to create both the scripts that create and layout
the tracking database (based on keys if they exist, or if necessary, all
columns), create and install the publisher database, and create and install
the triggers that record the replicatable transaction into the tracking
database.
The thing I notice that really catches my attention is the difference you
had in the bind_columns method. You used:
\( @array[0 ... $#cols]
That looks like just a different way of expressing what I was by using the
scalar function, but I want to make sure: I am not so familiar with $#cols
notation.
yours
Your best bet is to execute the script under the Perl debugger. 'perldoc
perldebug' will help get you started.
/yours
That's good advice. I'll try that when I get back into work tomorrow. Who
knows, after I iron the head dents out of my desk I might even see something
obvious and stupid. I'm just hung, and since my Perl is definitely my
weakest point in this, I thought maybe I was doing something out of whack in
the way I was getting data from the bind_columns to the execute().
Thanks,
Steve Howard
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 9:16 PM
To: Steve Howard; DBI USERS
Subject: Re: Values getting out of order between binding and execute
- Original Message -
From: Steve Howard [EMAIL PROTECTED]
To: DBI USERS [EMAIL PROTECTED]
Sent: Thursday, June 28, 2001 18:55
Subject: Values getting out of order between binding and execute
aside I am writing this from home, I work on this project at work. the
code I am giving here is hend typed into the e-mail, so I'm not going back
to use strict or -w, and I might miss a semicolon, but the part I am
having
trouble with is between binding and execute, and the concept I am using to
get it there./aside
That's fine for quick and dirty to get something started, but for permanent
work, I strongly recumbent '-w' and 'use strict;'
#after tracking database has been queried for transactions,
# and tables with replicatable transactions are identified, and
# table structure has been determined by queries to system catalogues.
$select = qq{SELECT
$columnlist
FROM $table
WHERE $where};
my $values = ,?x scalar(@columns);
$values =~ s/,//;
my $insert = qq{INSERT INTO $repldb.$dbo.$table
($columnlist)
VALUES
($values)};
$subscrh = $subscriber-prepare($insert) || die qq(Can't
prepare\n$insert\n$subscriber::errstr\n};
You should use $DBI::errstr instead of $subscriber::errstr. The later
refers to $errstr in package 'subscriber' which I don't think is what you
meant. The same applies below to $publisher::errstr and $subscrh::errstr.
$selecth = $publisher-prepare($select) || die qq{Can't
prepare\n$select\n$publisher::errstr\n};
$selecth-execute() || die qq{Can't
execute\n$select\n$publisher::errstr\n};
my ($row, @valarray);
$selecth-bind_columns(undef,(\@valarray[0 .. scalar(@columns)-1]));
The reference operator (\) is distributive, so I normally write that as:
$sth - bind_columns(
\( @array[0 ... $#cols], $scalar, $scalar, @array2[1..3] ) );
That that allows me to pass lots of different references without worrying
about missing a '\'.
while ($row = $selecth-fetchrow_arrayref) {
$subscrh-execute(@valarray[0 .. scalar(@columns)-1])) || die qq{Can't
execute\n$insert\n$subscrh::errstr};
}
I thought I was binding an ordered array, but it errors out usually giving
a
type mismatch or truncation error. When I print this out, I find that
print @valarray[0..scalar(@columns)-1];
doesn't usually print the values in the order I expected them to be in the
array, so I can only assume that varchar values are trying to be inserted
into datetime columns etc.
I didn't see how you created $columnlist. I'd check where you do that very
carefully to see if it's giving the columns in the order you are expecting.
Your best bet is to execute the script under the Perl debugger. 'perldoc
perldebug' will help get you started.
What am I misunderstanding? How would I keep them in the same order
between
binding and inserting?
I'd check the two table's definitions again to make sure the columns with
the same names have the same characteristics.
--
Mac :})
** I
RE: Values getting out of order between binding and execute
Excuse me, I meant to say I queried the publisher database's system
catalogues and created the subscriber database based on that, not the
create and install the publisher database like I said. Late at night
:-)
Steve
-Original Message-
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 10:50 PM
To: Michael A. Chase
Cc: DBI USERS
Subject: RE: Values getting out of order between binding and execute
In this case, the publishing DB is MS SQL 7.0, so I have created the
$columnlist using a:
SELECT name FROM syscolumns WHERE id = object_id('$tablename')
ORDER BY colorder
I use those results to build $columnlist, then use it both for the select,
and for the insert queries to keep that order the same. I actually derive
@columns from this using:
my @columns = split /, /, $columnlist;
Some parts of the script lend themselves to use of the array, but the
derived queries I preferred the $columnlist. I then use the information in
the tracking database to build $where.
I used something similar to create both the scripts that create and layout
the tracking database (based on keys if they exist, or if necessary, all
columns), create and install the publisher database, and create and install
the triggers that record the replicatable transaction into the tracking
database.
The thing I notice that really catches my attention is the difference you
had in the bind_columns method. You used:
\( @array[0 ... $#cols]
That looks like just a different way of expressing what I was by using the
scalar function, but I want to make sure: I am not so familiar with $#cols
notation.
yours
Your best bet is to execute the script under the Perl debugger. 'perldoc
perldebug' will help get you started.
/yours
That's good advice. I'll try that when I get back into work tomorrow. Who
knows, after I iron the head dents out of my desk I might even see something
obvious and stupid. I'm just hung, and since my Perl is definitely my
weakest point in this, I thought maybe I was doing something out of whack in
the way I was getting data from the bind_columns to the execute().
Thanks,
Steve Howard
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 9:16 PM
To: Steve Howard; DBI USERS
Subject: Re: Values getting out of order between binding and execute
- Original Message -
From: Steve Howard [EMAIL PROTECTED]
To: DBI USERS [EMAIL PROTECTED]
Sent: Thursday, June 28, 2001 18:55
Subject: Values getting out of order between binding and execute
aside I am writing this from home, I work on this project at work. the
code I am giving here is hend typed into the e-mail, so I'm not going back
to use strict or -w, and I might miss a semicolon, but the part I am
having
trouble with is between binding and execute, and the concept I am using to
get it there./aside
That's fine for quick and dirty to get something started, but for permanent
work, I strongly recumbent '-w' and 'use strict;'
#after tracking database has been queried for transactions,
# and tables with replicatable transactions are identified, and
# table structure has been determined by queries to system catalogues.
$select = qq{SELECT
$columnlist
FROM $table
WHERE $where};
my $values = ,?x scalar(@columns);
$values =~ s/,//;
my $insert = qq{INSERT INTO $repldb.$dbo.$table
($columnlist)
VALUES
($values)};
$subscrh = $subscriber-prepare($insert) || die qq(Can't
prepare\n$insert\n$subscriber::errstr\n};
You should use $DBI::errstr instead of $subscriber::errstr. The later
refers to $errstr in package 'subscriber' which I don't think is what you
meant. The same applies below to $publisher::errstr and $subscrh::errstr.
$selecth = $publisher-prepare($select) || die qq{Can't
prepare\n$select\n$publisher::errstr\n};
$selecth-execute() || die qq{Can't
execute\n$select\n$publisher::errstr\n};
my ($row, @valarray);
$selecth-bind_columns(undef,(\@valarray[0 .. scalar(@columns)-1]));
The reference operator (\) is distributive, so I normally write that as:
$sth - bind_columns(
\( @array[0 ... $#cols], $scalar, $scalar, @array2[1..3] ) );
That that allows me to pass lots of different references without worrying
about missing a '\'.
while ($row = $selecth-fetchrow_arrayref) {
$subscrh-execute(@valarray[0 .. scalar(@columns)-1])) || die qq{Can't
execute\n$insert\n$subscrh::errstr};
}
I thought I was binding an ordered array, but it errors out usually giving
a
type mismatch or truncation error. When I print this out, I find that
print @valarray[0..scalar(@columns)-1];
doesn't usually print the values in the order I expected them to be in the
array, so I can only assume that varchar values are trying to be inserted
into datetime columns etc.
I didn't see how you created $columnlist. I'd check where you
RE: Connecting to MySQL
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: Connecting to MySQL
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: Connection is busy
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: SQL Server
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: MS SQL Server Connectivity
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...
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: MS ACCESS Date Fields
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?
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
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: Next - Previous Buttons
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 MikemickaloBlezien
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.
table border="0" width="3%" cellpadding="0"
tr
td width="3%"p align="left"
form action="/cgi-bin/search1.cgi" method="post"
input type="hidden" name="categories" value="$Category"
input type="hidden" name="city" value="$City"
RESULTS
if ($State !~ /ALL/i) {
printRESULTS;
input type="hidden" name="state" value="$State"
RESULTS
} else {
printRESULTS;
input type="hidden" name="state"
RE: MS Access
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: A little Mysql question
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]]
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 = "DT 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]]
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: Limiting the number of records selected.
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.
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
RE: Possible Stupid DB-User trick???
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
FW: Possible Stupid DB-User trick???
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: Problem with CHAR Data Type in ORACLE
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
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
RE: Using DBI when MySQL is on another server
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
