Re: date format in Oracle
At 16:50 -0700 1/10/01, jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle 8.1.7 on
solaris 5.8.
Did you actually tried out in your system ? Do I need to
set up any NLS variable in order to use the 'TZ' keyword ?
I'm afraid that Oracle's handling of timezones is primitive at best.
There is no date format modl string for time zone and the function
new_time() only recognises a very Americano-centric set of zones.
You can though use the environment variable TZ (on SVR4 systems and
BSD if memory serves) to establish the zone of the session:
TZ=GMT+8 sqlplus scott/tiger
or
$ENV{TZ} = 'PST8PDT';
$dbh = DBI-connect(DBI:Oracle:,scott,tiger);
You can find the available zones in /usr/share/lib/zoneinfo on SVR4 or BSD.
From there, you can 'mask' the timezone in your date string like so:
insert into datetest values ( to_date('Thu Sep 20 15:03:10 PDT 2001'
, 'Dy Mon DD HH24:MI:SS --- ') )
As an aside, most other RDBMSs support timezones in the data, but
Oracle is just a bit slow on this.
Regards
Paul Miller
--
-
Carib Data Limited
mailto:[EMAIL PROTECTED]
http://www.caribdata.co.uk
RH7 - Oracle 8i Enterprise - DBI - libskgxp8.so
Hi all, I have recently installed Oracle 8i enterprise (8.1.7) on a Redhat 7 system... and, using some advice from otn.oracle.com discussion forums, I am able to run a database, use svrmgrl, and sqlplus... now, I want to connect via DBI::Oracle to another oracle database that is running elsewhere on the network... but keep getting a message that libskgxp8.so can not be found... I have for the most part installed all of the components of Oracle 8i enterprise It's correct as this file does not exist anywhere on the system... only a libskgxp8.a - in $ORACLE_HOME/lib - where does this .so come from? where can I get it... The env LD_LIBRARY_PATH is set to $ORACLE_HOME/lib Any help appreciated. Neil Streeter
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
Yes. Run a connect to each DB you want to work with. You can then run queries with a where clause of 'rownum = 20' to get a sampling of a table from each DB. I'm not sure about a page count myself, but you can check the number of records in each DB in each table with a 'select count(*)' query statement. These are pretty much SQL questions for the most part. $dbh1=DBI-connect(.); $dbh2=DBI-connect(.); $dbh3=DBI-connect(.); . . $sth1=$dbh1-prepare(); . . $sth2=$dbh2-prepare(); . . $sth3=$dbh3-prepare(); . . . . $dbh3-disconnect; $dbh2-disconnect; $dbh1-disconnect; -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: date format in Oracle
Jie,
Oracle doesn't support timezones in date format strings, unfortunately.
Brgds,
Mark
jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle 8.1.7 on
solaris 5.8.
Did you actually tried out in your system ? Do I need to
set up any NLS variable in order to use the 'TZ' keyword ?
Thanks,
Jie
Anna Fong wrote:
Use Oracle's to_date function.
Example:
select ename, hiredate
from emp
where hiredate = TO_DATE('Thu Sep 20 15:03:10 PDT 2001','DY MON DD
HH24:MI:SS TZ ');
At 03:58 PM 10/01/2001 -0700, jie zhang wrote:
Does anybody know if Oracle can take the date format as 'Thu Sep 20
15:03:10 PDT 2001') ?
Thanks in advance,
Jie
Anna Q. Fong, Webmaster
California Data Exchange Center
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
Business Process Manager Email : [EMAIL PROTECTED]
EMEA Support Information Systems AIM: markvdb
Re: Correct Oracle environment Needed for DBI
Susan, My guess is that you installed DBD::Oracle with your environment pointing to 8.1.7. When you install DBD::Oracle, it is linked with the Oracle client libraries. If you run a program linked in an 8.1.7 environment, but (because of your environment variables) it picks up the dynamic libraries of an older version, you're asking for trouble. You should provide your DBD::Oracle with the client libraries it was linked with at install time. So, if you have a mixed version environment, the best way to go is : * Install DBD::Oracle against the highest version * Always keep your environment variables (ORACLE_HOME, PATH and LD_LIBRARY_PATH) pointing to this same environment. If you need to connect to other versions, use SQL*Net. Hope this helps, Brgds, Mark [EMAIL PROTECTED] wrote: I have a program that sets the Oracle environment for 8.1.7 and connects to the database just fine. If I resent the environment to 8.0.5 and try to connect to an 8.0.5 database, it fails with a OCIEnvInit error. If I leave the environment at 8.1.7 and connect to the 8.0.5 database, it succeeds. If I run from cron with no environment set, it works fine. I am obviously missing something basic about the Oracle environment the dbd needs to run. Should an environment never be set or should it always be set to 8i? Thanks Susan * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * -- Mark Vandenbroeck Mobile : +32-495-59.55.62 Business Process Manager Email : [EMAIL PROTECTED] EMEA Support Information Systems AIM: markvdb
RE: MS Access Driver for Linux
Please review the dbi-users mailing list archives. There are plenty of answers to this and similar questions there. The short answer is that there is no direct Linux driver for MS Access. You can: use DBI proxy and have DBI installed on an NT machine use EasySoft's ODBC bridge (http://www.easysoft.com) use OpenLink's ODBC bridge (http://www.openlinksw.com) Jeff Hi! I managed to successfully install DBI, DBD:ODBC and iODBC. Now all I need is a MS Access driver for linux (to access a locally stored dbd). You have a DSN-less example provided in the man. That's what I'm trying to do. Do you have any info as to where I can download a MS Access Driver? Thanks in advance. -Dmitry -//- Dmitry S.M. /* Reality is just another convincing dream */ ___ Check out TELUS Mobility to find out how you can get free wireless email. Visit www.telusmobility.com
RE: Select X number of rows
What database are you using, it's pretty easy to do with MySQL, since it supports the LIMIT clause. To find out the number of records from any DB's table, just run a select count(*) query on it. Ilya -Original Message- From: Purcell, Scott To: '[EMAIL PROTECTED]' Sent: 10/2/01 6:36 AM 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: date format in Oracle
In 8.1.6 for Solaris, browse standard.sql. There are numerous
functions, internal data types and overloaded operators that support
manipulation/display of time zone formats.
HTHYO.
Mark == Mark Vandenbroeck [EMAIL PROTECTED] writes:
Mark Jie, Oracle doesn't support timezones in date format strings,
Mark unfortunately.
Mark jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle
8.1.7 on solaris 5.8. Did you actually tried out in your system
? Do I need to set up any NLS variable in order to use the 'TZ'
keyword ?
Thanks,
Jie
Anna Fong wrote:
Use Oracle's to_date function.
Example:
select ename, hiredate from emp where hiredate = TO_DATE('Thu
Sep 20 15:03:10 PDT 2001','DY MON DD HH24:MI:SS TZ ');
[repost] DBD::CSV and csv_eol=anything
the list mysteriously and completely disappeared for roughly 10 minutes,
and I wasn't sure if my post was received or not. *head-scratching*
-=-
unable to set ;csv_eol=\015, but saving the file via bbedit to DOS
instead of Macintosh, the code works?!? what the hell?
setting csv_eol=\015 when file format is saved as Macintosh results in
this error, searching for test:
DBD::CSV::st execute failed: Missing first row at Primus
8.5GB:Applications:MacPerl 5.6.1a4 ΓΌ:site_perl:DBD:CSV.pm line 157,
GEN0 line 1.
o if fileformat is DOS and csv_eol=\015, it searches the file but
finds 0 results
o if fileformat is DOS and I SET csv_eol=\015\012 it FINDS THE 7
RESULTS PROPERLY O_o
i.e. it's only confused SOMEtimes. Somewhere, it's assuming the file is
a DOS file, even though I'm setting csv_eol properly, the question is
WHERE?
my $dbh =
DBI-connect(dbi:CSV:f_dir=${dbi_connect_dir};csv_sep_char=\t;csv_eol=\0
15\012, '', '', { RaiseError = 2 })
or die Can't connect to database: $DBI::errstr;
All well and good, except that the file is generated by a whole suite of
perl scripts running under MacPerl 5.6.1a*, (a mysql table output by SQL
embedded in an .asp page, formatted to an html table, parsed by
HTML::Parser, and tested for integrity by script # 3) and is read for
report-generation by something like 10 other scripts.
I don't want to go back through all the scripts and change the input and
output record separators unless I absolutely have to, considering one
single csv_eol=\015 is supposed to solve the problem :)
DBI 1.20
DBD::CSV 0.1027
SQL::Statement 0.1020
Text::CSV_XS 0.22
MacPerl 5.6.1a4
I can post the complete script and a smidge of sample data if you wish
--
Scott R. Godin| e-mail : [EMAIL PROTECTED]
Laughing Dragon Services |web : http://www.webdragon.net/
Re: [repost] DBD::CSV and csv_eol=anything
Scott R. Godin wrote: unable to set ;csv_eol=\015, but saving the file via bbedit to DOS instead of Macintosh, the code works?!? what the hell? AFAIK, 1. If you are on a MAC and have all MAC-formatted files, don't set csv_eol at all, DBD::CSV should do the right thing. 2. If you are on a MAC and have all DOS formatted files, set csv_eol to \015\012. 3. If you are on a MAC and have mixed files a. convert them all to one format before processing (recommended) or b. for the files that you know are DOS formatted, set csv_eol to \015\012 and do not set it all for the MAC formatted files All well and good, except that the file is generated by a whole suite of perl scripts So use the scripts to put a MAC eol in the file and forget about csv_eol in the report generating scripts. -- Jeff
(Fwd) dbish, TERM problems
- Forwarded message from Axel Rose [EMAIL PROTECTED] - Delivered-To: [EMAIL PROTECTED] Date: Tue, 2 Oct 2001 19:51:05 +0200 To: [EMAIL PROTECTED] From: Axel Rose [EMAIL PROTECTED] Subject: dbish, TERM problems Hello Tim, I know you are very busy. But perhaps you have the best idea to tackle this: rssj2:perl5/5.6.1/Term dbish Use of uninitialized value in concatenation (.) or string at (eval 3) line 16, TERMCAP line 1576. DBI::Shell 10.7 using DBI 1.14 WARNING: The DBI::Shell interface and functionality are === very likely to change in subsequent versions! Available DBI drivers: 1: dbi:ADO 2: dbi:ExampleP 3: dbi:Multiplex 4: dbi:Oracle 5: dbi:Proxy print() on closed filehandle Term::ReadLine::Stub::FOUT at /usr/local/lib/perl5/5.6.1/Term/ReadLine.pm line 173. I tried everything I know of - setting TERM, TERMCAP to various values, installing a new readline library. It looks to me as if I alway have problem in this aix environment. It's a terminfo system. Perhaps this is the reason. Thanks for your time! Axel - End forwarded message -
Re: accessing sybase oracle simultaneously
On Tue, Oct 02, 2001 at 03:27:04PM -0400, [EMAIL PROTECTED] wrote:
Does anyone have a sample code which demonstrates
using DBI to access both a remote oracle and local
sybase servers simultaneously?
Umm, how about this:
$syb = DBI-connect('dbi:Sybase:foo', ...);
$ora = DBI-connect('dbi:Oracle:bar', ...);
It's a bit tricky, but I hope you get the idea :-)
Goal is to connect perform same operations
(insert select delete) on both databases. From a
code level perspective, each insert
and update must in affect be performed twice ( once
for each datastore oracle/sybase). This would render
the databases equal in terms of information added
and removed.
I have an idea of how I might try it but wanted to
know if anyone had done it or knows exactly how,
caveats, etc. The oracle queries would be to a
remote host while the sybase queries would be on
the local host.
Take a look at DBD::Multiplex (The one shipped with the DBI is old.
Search the archives for the url of the latest, or some kind soul
will post it.)
Tim.
RE: accessing sybase oracle simultaneously
I don't have an example for Sybase, but I have done this sort of thing with
a couple of local and remote Oracles as well as multiple local Oracles.
Basically, you connect to each one as you would expect and assign a
different handle to each connection. From there on, it doesn't matter
whether they're local or remote since everything is done via DBI, which
nicely insulates you from such headaches. :)
A scrap of code:
-8-
#!/usr/local/bin/perl
use DBI;
my $sid1='PCUS';
my $username1='pcus_username';
my $password1='pcus_password1;
my $sid2='SDI';
my $username2='sdi_username';
my $password2='pinecone';
END {
$dbh1-disconnect();
$dbh2-disconnect();
}
$dbh1=DBI-connect(dbi:Oracle:.$sid1,$username1,$password1,{AutoCommit=0}
) || die Can't connect to database $s
id1:,$DBI::errstr,\n;
$dbh2=DBI-connect(dbi:Oracle:.$sid2,$username2,$password2,{AutoCommit=0}
) || die Can't connect to database $s
id2:,$DBI::errstr,\n;
-8-
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 3:27 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: accessing sybase oracle simultaneously
Does anyone have a sample code which demonstrates
using DBI to access both a remote oracle and local
sybase servers simultaneously?
Goal is to connect perform same operations
(insert select delete) on both databases. From a
code level perspective, each insert
and update must in affect be performed twice ( once
for each datastore oracle/sybase). This would render
the databases equal in terms of information added
and removed.
I have an idea of how I might try it but wanted to
know if anyone had done it or knows exactly how,
caveats, etc. The oracle queries would be to a
remote host while the sybase queries would be on
the local host.
TIA!
TIA
MSSQL Row levels locks from DBI
select * from catalog with (UPDLOCK ROWLOCK) where product = 'acs-101' gives me Table level lock (as can be seen through 'sp_lock' stored proc)! ActivePerl Build 629 DBI 1.14 W2K Any Clues? Thanks, MVRamana _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Re: MSSQL Row levels locks from DBI
Venkataramana Mokkapati writes: select * from catalog with (UPDLOCK ROWLOCK) where product = 'acs-101' gives me Table level lock (as can be seen through 'sp_lock' stored proc)! I doubt that this is a DBI issue. What do you get if you perform the same action through some other tool? Michael -- Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler [EMAIL PROTECTED] - [EMAIL PROTECTED] International Sybase User Group - http://www.isug.com
Re: Installing DBD::Informix on Win2000
On Mon, 1 Oct 2001, Scottie Thompson wrote: I recently installed DBD::Informix on a Win NT machine, which appears to be working just fine. Good. But I haven't been able to get it working in Win 2000. I haven't tried - I don't have a Win2K machine to do the trying on. Has anyone been able to do this? Background: I downloaded the Informix Client SDK 2.70, and DBD-Informix-1.00.PC1, and built it for Win NT. There were a lot of error during the nmake test, I seem to remember there was a critical change in one of the ESQL/C headers, which was documented in the notes somewhere. Additionally, the default compilation mode on NT is with a very high warning level enabled. but the module seemed to work well enough. Then, I did the same thing for Win 2000 (yes, I rebuilt it). Again, there were a lot of errors during the nmake test part, but this time when I tried to run my test script, perl crashed with a memory protection error. That is always worrying. Have you been able to identify which routine caused the trouble? Was it plain Perl or down in the DBD::Informix driver? My test script just connects to a remote database, prepares 1 sql select statement (which pulls 1 record from 1 table), executes the sql and disconnects. And the failure was on which of those statements? Scottie Thompson Network Systems Programmer Point Loma Nazarene University -- Yours, Jonathan Leffler ([EMAIL PROTECTED]) #include disclaimer.h Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN I don't suffer from insanity; I enjoy every minute of it!
Re: date format in Oracle
Thank you all very much for helping me. It looks like Oracle stores date
only in one timezone in one
database. I was able to do the format conversion using:
select to_char(sysdate, 'Dy Mon DD HH24:MI:SS PDT ') from dual;
TO_CHAR(SYSDATE,'DYMONDDHH24
Mon Oct 01 16:36:41 PDT 2001
insert into test1
values (to_date('Thu Sep 20 15:03:10 PDT 2001',
'Dy Mon DD HH24:MI:SS PDT '));
If there is datetime from other time zone, I'll have to convert or calculate
them into the PDT timezone first,
then store them.
Thanks again,
Jie
Bruce W. Hoylman wrote:
In 8.1.6 for Solaris, browse standard.sql. There are numerous
functions, internal data types and overloaded operators that support
manipulation/display of time zone formats.
HTHYO.
Mark == Mark Vandenbroeck [EMAIL PROTECTED] writes:
Mark Jie, Oracle doesn't support timezones in date format strings,
Mark unfortunately.
Mark jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle
8.1.7 on solaris 5.8. Did you actually tried out in your system
? Do I need to set up any NLS variable in order to use the 'TZ'
keyword ?
Thanks,
Jie
Anna Fong wrote:
Use Oracle's to_date function.
Example:
select ename, hiredate from emp where hiredate = TO_DATE('Thu
Sep 20 15:03:10 PDT 2001','DY MON DD HH24:MI:SS TZ ');
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: date format in Oracle
9i is a lot more timezone aware - if thats of any use. You can have a data
type of TIMESTAMP WITH [LOCAL] TIMEZONE, however I'm not sure of whether
the Oracle::DBD supports the new data types.
-Original Message-
From: jie zhang [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 3 October 2001 9:51 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; Mark Vandenbroeck
Subject: Re: date format in Oracle
Thank you all very much for helping me. It looks like Oracle stores date
only in one timezone in one
database. I was able to do the format conversion using:
select to_char(sysdate, 'Dy Mon DD HH24:MI:SS PDT ') from dual;
TO_CHAR(SYSDATE,'DYMONDDHH24
Mon Oct 01 16:36:41 PDT 2001
insert into test1
values (to_date('Thu Sep 20 15:03:10 PDT 2001',
'Dy Mon DD HH24:MI:SS PDT '));
If there is datetime from other time zone, I'll have to convert or calculate
them into the PDT timezone first,
then store them.
Thanks again,
Jie
Bruce W. Hoylman wrote:
In 8.1.6 for Solaris, browse standard.sql. There are numerous
functions, internal data types and overloaded operators that support
manipulation/display of time zone formats.
HTHYO.
Mark == Mark Vandenbroeck [EMAIL PROTECTED] writes:
Mark Jie, Oracle doesn't support timezones in date format strings,
Mark unfortunately.
Mark jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle
8.1.7 on solaris 5.8. Did you actually tried out in your system
? Do I need to set up any NLS variable in order to use the 'TZ'
keyword ?
Thanks,
Jie
Anna Fong wrote:
Use Oracle's to_date function.
Example:
select ename, hiredate from emp where hiredate = TO_DATE('Thu
Sep 20 15:03:10 PDT 2001','DY MON DD HH24:MI:SS TZ ');
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
DBI Version Problem
Pardon me if this has been covered before; I can't reach the archives, and I'm the impateint type. ;-) I'm using Perl 5.6.0 on OpenBSD 2.9; it's worked quite well for me so far. After just installing MySQL 3.23.37, I figured the smart thing to do to work with it would be to get the appropriate Perl modules installed. So I went out and grabbed DBI-1.20, which installed just fine, and then DBD-mysql-2.0902. For some reason, when I ran my perl Makefile.PL command, I got these errors: DBI object version 1.20 does not match $DBI::VERSION 1.15 at /usr/libdata/perl5/i386-openbsd/5.6.0/DynaLoader.pm line 219. BEGIN failed--compilation aborted at /usr/local/libdata/perl5/site_perl/i386-openbsd/DBI.pm line 180. Compilation failed in require at /usr/local/libdata/perl5/site_perl/i386-openbsd/DBI/DBD.pm line 1740. BEGIN failed--compilation aborted at /usr/local/libdata/perl5/site_perl/i386-openbsd/DBI/DBD.pm line 1741. Compilation failed in require at Makefile.PL line 5. BEGIN failed--compilation aborted at Makefile.PL line 5. I'm guessing that OpenBSD installed DBI-1.15 with Perl on the basic system install. In any case, how would I go about fixing this? Like I said, DBI-1.20 installed just fine, with make test giving no errors. Thanks, Alex Kirk
