Re: most common kinds of cross-database queries

[Daniel Kasak]

On Wed, 2007-08-01 at 18:29 -0700, Darren Duncan wrote:

> What do you think would be the most common scenarios of a cross-database 
> query?
> 
> Or why would data be in multiple databases, and what kinds of ways 
> are most likely for it to be brought together in a common query?

In our case, we are migrating from a legacy SQL Server database to
MySQL. We're doing it in bits and pieces. MS Access is making this quite
easy for us ( because of it's cross-database query support ).

I think our situation wouldn't be too uncommon, particularly amongst
people who use open-source software ( ie I assume there's a general
trend to migrate from the big commercial DB servers to open-source
solutions ).

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

Re: most common kinds of cross-database queries

[Amos Shapira]

On 02/08/07, Darren Duncan <[EMAIL PROTECTED]> wrote:
>
> What do you think would be the most common scenarios of a cross-database
> query?


Speaking only theoretically for now but one "use case" I can vaguely imagine
would be something we are working on right now:

We have a large SQL Server database with many individual records and we are
now building a PostgresQL 8.1 database on another machine to contain
statistics about these records (e.g. for each hour in the life of the
database, how many records of each type were created or updated).  One is
derived from the other and contains aggregates of records but maybe when
we'll get the web interface to dig the stats database we might want to be
able to say "list all records in the main db which were counted by a
particular record in the stats DB".

HTH,

--Amos

most common kinds of cross-database queries

[Darren Duncan]

At 9:03 AM +1000 8/2/07, Daniel Kasak wrote:

Nice suggestion ( we do this from Excel sometimes ), but I'm actually
planning on *competing* with Access, and not using it to do the heavy
lifting for me. ie this is for a generic query engine, and not for a
specific task I have in mind.


As a tangent to that other cross-database thread, I'm hoping for some 
input that can help me determine priorities in my own implementation 
under Muldis DB.


What do you think would be the most common scenarios of a cross-database query?

Or why would data be in multiple databases, and what kinds of ways 
are most likely for it to be brought together in a common query?


For example, is it more common for the multiple databases with the 
same schema but different data, such as to implement partitioning or 
clusters, or are they all full replication for redundancy and 
performance, or do they tend to be all different from each other and 
just associate at their edges?


For example, what relational operations tend to be the most common 
between databases, eg: unions, joins to display data from both, using 
one to filter data from the other.


In common scenarios, is usually the same DBMS product normally used 
for all the databases, or are they more likely to be different 
products driving each one?


For those replying, please just answer the question in a generic 
sense for now, without regard for particulars of my project.


Thank you. -- Darren Duncan

Re: Guidance on setting up multiple database handles to be used in one script

[Henri Asseily]

Defining connections in one place is one thing.
Using the same connection from different places is another.

The first point, defining connections in one place, is something  
quite trivial that is unnecessary to discuss. Basically it means  
specifying the dsn string, and that can be done in a dozen ways  
without a problem.


However, taking a connection that was created in one central place  
and using it in a bunch of other places is a more complicated issue.

The situation depends on your setup.
Say that the creator of the connection is code A, and that code B and  
C use that connection.


Case 1:
Code A, B and C reside inside the same process, i.e. are run by the  
same Perl interpreter inside the same process. For example, you could  
have a mod_perl process initializing a connection (A) and then  
calling that connection each time a page is requested (B, C, etc...).  
The only problem in this case is that when the connection goes down,  
any subsequent use of the connection fails.

Solution:
Create something like DBIx::HA to reconnect seamlessly by utilizing  
the swap_inner_handle() method of DBI. Basically if you see that the  
connect goes down, you create a new one and swap its inner handle  
with the old one, effectively making the original one active again,  
unbeknownst to the codebase. So say A connects, then B uses it  
successfully, then it goes down and C sees a failure. C does the  
swap_inner_handle magic and reuses the connection, and after that B  
can use it again without noticing anything happened.
In simpler words, assume you have a global $dbh and at some point it  
becomes bad. You create a local $dbh2, connect, and when you're happy  
with it, you swap_inner_handle between $dbh and $dbh2. From that  
point on, $dbh is good and $dbh2 is bad. Then you can keep going with  
your code that uses the $dbh global variable. It's perfectly elegant.


Case 2:
Code A, B and C do NOT reside inside the same process. That's much  
more difficult. As Ross says below, you can cache the database handle  
in some way. Or you can use DBI::Gofer. One way to think about  
DBI::Gofer is that you have a central process (think of it as a  
daemon) that runs all the queries, and you connect to it from your  
script, give it the sql you want, and it gets you back your data. In  
effect it's a centralized DBI "server". So Code A resides inside that  
server, and code B and C use DBI::Gofer to communicate with the DBI  
server. You can communicate using a number of protocols (stream,  
http, ssh...) so you can have that DBI server in the same machine or  
on another machine (or cluster).


H

On Aug 1, 2007, at 11:11 PM, John Costello wrote:


On Tue, 31 Jul 2007, Russ wrote:


Over time the need to connect to the same database in different
scripts became apparent.  To do this I began by creating a package
that contained all the $dbh handles (not advisable) as if one fails
then your whole script is no good.   So I then split them into
individual packages for each connect handler.  Again not good if you
need to connect to multiple databases in your script, but aren't
dependent on all of them having to work.

So is there a good way to manage database connections only.  So that
multiple scripts can use the same connection  but it is defined in  
the

one place.  Should I explore DBI::Ingres.  or is there another easier
way, other than defining the connection in each script I create.



If I understand right, you want to create you database handle  
($dbh) and
then have multiple scripts use that same $dbh, right?  If so, read  
on; if

not, the talks linked below may still be useful.

Tim talked about caching database handles (and other things) and their
gotchas in his Advanced DBI talk, which he posted to the list earlier.

Two things come to mind off the top of my head, both really based  
on Tim's

talks, which I recommend that you peruse:





One approach would be to have a parent script that creates the  
database
handle and then manages all of the other scripts.  This assumes  
several

things about what you are doing, and may not be practical to you.  I
suppose you could launch a separate script that maintains the $dbh and
allows the other scripts to communicate to it.

Another approach is that the scripts are called from Apache, then  
it seems

that it is possible to share the $dbh between processes.

I'm still looking at DBI::Gofer, and can't say with confidence that it
would work for this situation.

John

Re: Cross-Database query engine?

[Daniel Kasak]

On Wed, 2007-08-01 at 22:13 +0200, Jenda Krynicky wrote:

> On 25 Jul 2007 at 9:43, Daniel Kasak wrote:
> > Greetings.
> > 
> > We've been stuck for quite a while between SQL Server and MySQL, and
> > while doing cross-database queries is drop-dead simple in MS Access,
> > it's relatively painful in Perl.
> > 
> > Are there any solutions at present for running queries across
> > different database servers? If not, I may well write one ...
> 
> What about linking all those tables into an MS Access database and 
> then
> accessing that from 
> Perl?

:)

Nice suggestion ( we do this from Excel sometimes ), but I'm actually
planning on *competing* with Access, and not using it to do the heavy
lifting for me. ie this is for a generic query engine, and not for a
specific task I have in mind.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

Re: dbi access ODBC

[Samuel_Zheng]

Hi Jenda,

I think you are on the right direction. It is a brand new server running 
windows server 2003. It has not been accessed by anyone yet. The scripts 
were developed using a WindowsXP and it dose not have the any "right" 
issues. on the server, I don't know the a/c to create and try, please 
suggest some a/c to create, and I'll do that see if that eliminate the 
error.


Thanks.
Samuel

- Original Message - 
From: "Jenda Krynicky" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, August 01, 2007 3:54 PM
Subject: Re: dbi access ODBC



Hi Martin,

you directed me to an article by microsotf to fix an error which is
caused when my perl script access a access97 file.The article tell to
chk the windows temp folders. that time I got the problem fixed on my
XP machine. This time, I am having the same error on a windows server
2003. exactly the same erorr. could you please tell me how to get it
fixed? Much appreciate it.

Samuel


Does the user that the script runs under have enough permissions to
create a .ldb file in the same directory where the .mdb resides?
Acces needs to create a lock file even if you do not mean to make
changes in the database.

Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery


__ NOD32 2431 (20070801) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk

Re: Cross-Database query engine?

[Jenda Krynicky]

On 25 Jul 2007 at 9:43, Daniel Kasak wrote:
> Greetings.
> 
> We've been stuck for quite a while between SQL Server and MySQL, and
> while doing cross-database queries is drop-dead simple in MS Access,
> it's relatively painful in Perl.
> 
> Are there any solutions at present for running queries across
> different database servers? If not, I may well write one ...

What about linking all those tables into an MS Access database and 
then
accessing that from 
Perl?

Jenda

= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: Guidance on setting up multiple database handles to be used in one script

[John Costello]

On Tue, 31 Jul 2007, Russ wrote:

> Over time the need to connect to the same database in different
> scripts became apparent.  To do this I began by creating a package
> that contained all the $dbh handles (not advisable) as if one fails
> then your whole script is no good.   So I then split them into
> individual packages for each connect handler.  Again not good if you
> need to connect to multiple databases in your script, but aren't
> dependent on all of them having to work.
> 
> So is there a good way to manage database connections only.  So that
> multiple scripts can use the same connection  but it is defined in the
> one place.  Should I explore DBI::Ingres.  or is there another easier
> way, other than defining the connection in each script I create.


If I understand right, you want to create you database handle ($dbh) and 
then have multiple scripts use that same $dbh, right?  If so, read on; if 
not, the talks linked below may still be useful.

Tim talked about caching database handles (and other things) and their 
gotchas in his Advanced DBI talk, which he posted to the list earlier.

Two things come to mind off the top of my head, both really based on Tim's 
talks, which I recommend that you peruse:




One approach would be to have a parent script that creates the database 
handle and then manages all of the other scripts.  This assumes several 
things about what you are doing, and may not be practical to you.  I 
suppose you could launch a separate script that maintains the $dbh and 
allows the other scripts to communicate to it.

Another approach is that the scripts are called from Apache, then it seems
that it is possible to share the $dbh between processes.

I'm still looking at DBI::Gofer, and can't say with confidence that it 
would work for this situation.

John

Bundle::DBI Failure

[Scott T. Hildreth]

When I run 'cpan' and try to install Bundle::DBI, 
DBI::Shell fails...but more importantly PlRPC-0.2020
fails with,

Writing Makefile for RPC::PlServer
perldoc -t lib/RPC/PlServer.pm >README
No documentation found for "lib/RPC/PlServer.pm".
make: *** [README] Error 1
  MNOONING/PlRPC/PlRPC-0.2020.tar.gz
  /usr/bin/make -- NOT OK
Running make test
  Can't test without successful make
Running make install
  Make had returned bad status, install seems impossible
Failed during this command:
 MNOONING/PlRPC/PlRPC-0.2020.tar.gz   : make NO

...if I quit cpan, cd into the build directory, rerun perl Makefile.PL
make & make test, everything is okay.  Has anyone else had this occur?

Thanks.

-- 
Scott T. Hildreth <[EMAIL PROTECTED]>

Re: dbi access ODBC

[Jenda Krynicky]

> Hi Martin,
> 
> you directed me to an article by microsotf to fix an error which is
> caused when my perl script access a access97 file.The article tell to
> chk the windows temp folders. that time I got the problem fixed on my
> XP machine. This time, I am having the same error on a windows server
> 2003. exactly the same erorr. could you please tell me how to get it
> fixed? Much appreciate it. 
> 
> Samuel

Does the user that the script runs under have enough permissions to 
create a .ldb file in the same directory where the .mdb resides? 
Acces needs to create a lock file even if you do not mean to make 
changes in the database.

Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

RE: Guidance on setting up multiple database handles to be used in one script

[Rutherdale, Will]

It's not clear from your message what you want.

If you mean that you want to open a db handle or set of handles in the
same way with the same options across a number of scripts, why don't you
just create a function for that?  You can create your own Perl module
exporting that function and just call it from the different scripts.

If this doesn't solve your problem, then please specify what is needed.

-Will


> -Original Message-
> From: Russ [mailto:[EMAIL PROTECTED]
> Sent: Tuesday 31 July 2007 21:21
> To: dbi-users@perl.org
> Subject: Guidance on setting up multiple database handles to
> be used in one script
>
>
> Over time the need to connect to the same database in different
> scripts became apparent.  To do this I began by creating a package
> that contained all the $dbh handles (not advisable) as if one fails
> then your whole script is no good.   So I then split them into
> individual packages for each connect handler.  Again not good if you
> need to connect to multiple databases in your script, but aren't
> dependent on all of them having to work.
>
> So is there a good way to manage database connections only.  So that
> multiple scripts can use the same connection  but it is defined in the
> one place.  Should I explore DBI::Ingres.  or is there another easier
> way, other than defining the connection in each script I create.
>



 - - - - - Appended by Scientific Atlanta, a Cisco company - - - - - 
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is solely
intended for the named addressee (or a person responsible for delivering it to
the addressee). If you are not the intended recipient of this message, you are
not authorized to read, print, retain, copy or disseminate this message or any
part of it. If you have received this e-mail in error, please notify the sender
immediately by return e-mail and delete it from your computer.

Re: Comments on stability of SQLite for storage

[rahed]

[EMAIL PROTECTED] (Daniel Kasak) writes:

> Greetings.
>
> I'm beginning a project where I have to store some stuff on the disk
> somehow. At present, I'm using SQLite *only* for temporary storage. I
> had planned to dump data ( which is basically config data, but is
> absolutely critical in terms of keeping the project held together ) to
> either XML or CSV, but now this is getting more cumbersome that I'd
> expected.
>
> Who has some anecdotal evidence about the reliability of SQLite files
> for storing data? Should I drop the XML / CSV idea and just use SQLite?

They are reliable (sqlite files) as other files of a filesystem.
E.g. I use it for config & contents data in a production system without
any trouble.
See also
http://technology.guardian.co.uk/weekly/story/0,,2107239,00.html

-- 
Radek

Re: error

[Martin Evans]

Samuel_Zheng wrote:

Hi Martin,

I know Chinese. Just that it is not displayed properly Icannot read it. 
what more info is needed? Do you think.

I use the excel to open the ACCESS97 database and it was fine.
regards,
samuel


With a 42000 error should be some text describing the error - the syntax 
error in your SQL. If you can't get this you'll have to output the SQL 
when prepare fails and examine it to see what looks wrong. I can't 
really say anything more since you appear to have a syntax error but 
there is no associated description so the problem could be anything.


Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: 
Sent: Wednesday, August 01, 2007 3:20 AM
Subject: Re: error



Samuel_Zheng wrote:
this is the error message, Because it on a Chinese 2003 server and 
the chinese is missing some bytes. I cannot read what it says. Does 
anyone can tell what it means and suggest things to try?

thanks,
samuel

Software error:
DBI connect('btn','',...) failed: [Microsoft][ODBC Microsoft Access 
Driver] uEz (SQL-42000)(DBD: db_login/SQLConnect err=-1) at 
C:\web\validate.pl line 130




Can't really tell much from that except it is a 42000 error which is 
"Syntax error or access violation" and the problem depends on what 
ODBC API you were calling (probably SQLPrepare/SQLExecDirect). You can 
find 42000 description at:


http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#42000 



but I think you are going to have to dig deeper or learn Chinese.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

__ NOD32 2430 (20070731) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk

Re: Comments on stability of SQLite for storage

[Hildo Biersma]

Daniel Kasak wrote:

Greetings.

I'm beginning a project where I have to store some stuff on the disk
somehow. At present, I'm using SQLite *only* for temporary storage. I
had planned to dump data ( which is basically config data, but is
absolutely critical in terms of keeping the project held together ) to
either XML or CSV, but now this is getting more cumbersome that I'd
expected.

Who has some anecdotal evidence about the reliability of SQLite files
for storing data? Should I drop the XML / CSV idea and just use SQLite?


I don't see this as DBI-related, but hey...

You obviously need some form of backups of your data.  Whether you do 
that by a copy of the SQLite database files or through a dump into 
XML/CSV is entirely up to you.


If you have the desire to process or view the data through some form 
other than SQLite (e.g. grep/sort), then using CSV or other textual 
formats such as XML makes sense.


If you care about getting the data as it was at an arbitrary point in 
time, you need a real database that does transactional logging and log 
archiving.  It sounds like you need less than that, in which case just 
copying the SQLite files should be sufficient.


Whatever you do, make sure you test querying the backed-up files and 
make sure you have some sort of script that shows the difference in 
contents between archived files (in whatever form) and the current 
state.  If you archive files in non-SQLite format, make sure you can 
recreate the database from the archived files - and test this for each 
major release of your project.

Re: error

[Samuel_Zheng]

Hi Martin,

I know Chinese. Just that it is not displayed properly Icannot read it. what 
more info is needed? Do you think.

I use the excel to open the ACCESS97 database and it was fine.
regards,
samuel

- Original Message - 
From: "Martin Evans" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, August 01, 2007 3:20 AM
Subject: Re: error



Samuel_Zheng wrote:
this is the error message, Because it on a Chinese 2003 server and the 
chinese is missing some bytes. I cannot read what it says. Does anyone 
can tell what it means and suggest things to try?

thanks,
samuel

Software error:
DBI connect('btn','',...) failed: [Microsoft][ODBC Microsoft Access 
Driver] uEz (SQL-42000)(DBD: db_login/SQLConnect err=-1) at 
C:\web\validate.pl line 130




Can't really tell much from that except it is a 42000 error which is 
"Syntax error or access violation" and the problem depends on what ODBC 
API you were calling (probably SQLPrepare/SQLExecDirect). You can find 
42000 description at:


http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#42000

but I think you are going to have to dig deeper or learn Chinese.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

__ NOD32 2430 (20070731) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk

Re: error

[Martin Evans]

Samuel_Zheng wrote:

this is the error message, Because it on a Chinese 2003 server and the chinese 
is missing some bytes. I cannot read what it says. Does anyone can tell what it 
means and suggest things to try?
thanks,
samuel

Software error:
DBI connect('btn','',...) failed: [Microsoft][ODBC Microsoft Access Driver] uEz 
(SQL-42000)(DBD: db_login/SQLConnect err=-1) at C:\web\validate.pl line 130



Can't really tell much from that except it is a 42000 error which is 
"Syntax error or access violation" and the problem depends on what ODBC 
API you were calling (probably SQLPrepare/SQLExecDirect). You can find 
42000 description at:


http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#42000

but I think you are going to have to dig deeper or learn Chinese.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com