Re: [GENERAL] undefined behaviour for sub-transactions?
Tim Bunce wrote: No doubt someone will quote the relevant parts. (And no doubt the relevant parts will say "it depends" :) I believe, the "no doubt" part is showing your age, aka experience. :-)
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
On Wed, Nov 30, 2005 at 10:35:17AM -0800, Tyler MacDonald wrote: > > > This is something I've been meaning to address for a while. I was > > thinking of something like: > > > > $schema_name = $dbh->current_schema > > I really like this. I read your exchange with Steffen Goeldner and > that prompted me to take a look at the get_info method. I expected either > SQL_DATABASE_NAME (16) or SQL_CATALOG_NAME (10003) to tell me the current > database name, but I guess I was wrong; on both MySQL and Pg, > SQL_DATABASE_NAME returns nothing; under MySQL, SQL_CATALOG_NAME returns > 'Y', under Pg, 'N'. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgetinfo.asp > > Why not help save the world and help me add current_schema() to the DBI > > and send implementations to the authors of drivers you're using? > > SQL_DATABASE_NAME is such a self-explanitory label that I have to > believe this is the right place to put the information we're after, but I > could be way off-base. See above url. The wording isn't very clear: : A character string with the name of the current database in use, if the : data source defines a named object called "database". Note In ODBC : 3.x, the value returned for this InfoType can also be returned by : calling SQLGetConnectAttr with an Attribute argument of SQL_ATTR_CURRENT_CATALOG. but since it's saying that SQL_DATABASE_NAME == SQL_ATTR_CURRENT_CATALOG and a 'catalog' is usually not the same thing as a 'schema' (catalogs contain schemas) it's clear that SQL_DATABASE_NAME isn't what you're after. It may work for some databases and drivers (since the whole catalog vs schema area is a bit of a mess) but it wouldn't be portable. > I'd be willing to task myself with producing patches > for the Pg, MySQL, and SQLite2 drivers to either make use of that attribute, > or provide a current_schema (or maybe current_database? under Pg at least, > schemas and db's are different) method. Which way is the right way to go here? Generally DBI drivers should "do what the database vendors own ODBC driver does" (or what the SQL 2003 standard mandates). Though it seems GetSessionInfo isn't part of ODBC, it is in the SQL1999 and later standards. I think the "right way to go here" is to add $dbh->get_session_info($type) in a very similar way to how get_info() is handled. Patches welcome. Tim.
Re: [GENERAL] undefined behaviour for sub-transactions?
On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote: > On Tue, Nov 29, 2005 at 07:44:05PM +, Tim Bunce wrote: > > On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote: > > > PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest > > > of > > > the transaction, whereas under MySQL and SQLite2 the transaction is > > > allowed > > > to continue. > > > > PostgreSQL is non-standard (and inconvenient) in this respect. > > The inconvenience I'll grant, but the non-standard claim I think > needs some justification. When the database encounters an error in a > transaction, it is supposed to report an error. An error in a > transaction causes the whole transaction to fail: that's what the > atomicity rule of ACID means, I think. The fact that an individual statement has failed and returned an error (such as a duplicate key on insert) does not mean that the whole transaction has failed (ie been implicitly rolled back). The application may choose to explicitly rollback after it is informed that a statement has failed, or it could try an alternative action. I believe that's the case for Oracle, DB2, and Ingres (showing my age) but I don't have standards docs to hand - nor the time to read them :) No doubt someone will quote the relevant parts. (And no doubt the relevant parts will say "it depends" :) Tim. > I actually am sort of > unconvinced that SQLite's transactions are real ones -- I just did > some playing around with it, and it seems that any error allows you > to commit anyway. Certainly, MySQL's support of transactions is > occasionally pretty dodgy, unless you use the strict mode. > > But it's worth knowing that in Pg 8.1 and later, you can wrap such > things in a subtransaction and get out of it that way. > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > I remember when computers were frustrating because they *did* exactly what > you told them to. That actually seems sort of quaint now. > --J.D. Baldwin
"Best free DB for a web-based Perl app" response results...
As a word doc... The most convincing and point-addressing comments were from Simon W. [EMAIL PROTECTED] - first paragraph. The clear choice from these responses is Postgres because of its internal strength over MySql; most notably, in that MySql can easily degrade to a standstill with 3 concurrent users, at least one doing big queries, whereas Postgres keeps on sailing. Also, it's more free, for now anyway. A commitment to Postgres now might get us grandfathered in later on when they've given up "free" for "money" (for some crazy reason). There was cross-agreement that Postgres is slower than MySql, but I guess that's the price paid for having more behind the scenes - more not so up-front, functionality. Also, Postgres is much more Oracle-like in syntax than MySql, so if we should ever graduate to big-daddy-Oracle, we can do that easier. (See attached file: DB_Compar.doc) Thanks much to respondants and list managers!!! - John Armstrong - Sacramento
Re: [GENERAL] undefined behaviour for sub-transactions?
Jaime Casanova <[EMAIL PROTECTED]> wrote: > >Either way the end result is that some database drivers poison a > > transaction if there's any error, others are selective about which errors > > are fatal and which are not, and still others just don't care at all. > that is a mis-conception... a transaction *must* be atomic (all or nothing)... > the reason some databases act that bad is because they don't support > savepoints, and because postgres does it doesn't need that > awfulness... OK, maybe I should have s/poison/behave properly with/. :-) - Tyler
Re: [GENERAL] undefined behaviour for sub-transactions?
On 11/30/05, Tyler MacDonald <[EMAIL PROTECTED]> wrote: > Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > The inconvenience I'll grant, but the non-standard claim I think > > needs some justification. When the database encounters an error in a > > transaction, it is supposed to report an error. An error in a > > transaction causes the whole transaction to fail: that's what the > > atomicity rule of ACID means, I think. I actually am sort of > > unconvinced that SQLite's transactions are real ones -- I just did > > some playing around with it, and it seems that any error allows you > > to commit anyway. Certainly, MySQL's support of transactions is > > occasionally pretty dodgy, unless you use the strict mode. > >Either way the end result is that some database drivers poison a > transaction if there's any error, others are selective about which errors > are fatal and which are not, and still others just don't care at all. > that is a mis-conception... a transaction *must* be atomic (all or nothing)... the reason some databases act that bad is because they don't support savepoints, and because postgres does it doesn't need that awfulness... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
[OT] Web hosting companies that offer Perl and MySQL or Postgres?
On Wed, 30 Nov 2005 09:38:37 -0500, Randall Perry wrote: Hi Randall http://quadrahosting.com/hosting/unix.html -- Cheers Ron Savage, [EMAIL PROTECTED] on 1/12/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: [GENERAL] undefined behaviour for sub-transactions?
On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote: > But it's worth knowing that in Pg 8.1 and later, you can wrap such > things in a subtransaction and get out of it that way. Shouldn't that be 8.0 and later? That's when savepoints were introduced. Or are you referring to something else? -- Michael Fuhr
Re: [GENERAL] undefined behaviour for sub-transactions?
On Tue, Nov 29, 2005 at 07:44:05PM +, Tim Bunce wrote: > On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote: > > PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of > > the transaction, whereas under MySQL and SQLite2 the transaction is allowed > > to continue. > > PostgreSQL is non-standard (and inconvenient) in this respect. The inconvenience I'll grant, but the non-standard claim I think needs some justification. When the database encounters an error in a transaction, it is supposed to report an error. An error in a transaction causes the whole transaction to fail: that's what the atomicity rule of ACID means, I think. I actually am sort of unconvinced that SQLite's transactions are real ones -- I just did some playing around with it, and it seems that any error allows you to commit anyway. Certainly, MySQL's support of transactions is occasionally pretty dodgy, unless you use the strict mode. But it's worth knowing that in Pg 8.1 and later, you can wrap such things in a subtransaction and get out of it that way. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin
Re: Problem with Oracle 10.2 and Perl dbd/dbi interface
Can you tell us which version of perl you are running and wether it is 10.2 personal, enterprise or standard or is it just the client? "Marty Martindale" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > There is a problem in Oracle 10.2 that surfaces after you install the latest > Perl, dbd-oracle and dbi on a windows box. The symptom is that sqlplus and > sqlplusw no longer function, they return almost immediately to the command > prompt. This is a know but undocumented problem in oracle. I submitted a > tar to get the information. The problem file is 'oraociei10.dll' was not > compiled at the correct level. Renaming this file will get sqlplus and > sqlplusw functioning again. The file is located in Perl\bin'. > > There is also a problem with the dbi installation if you have blanks in the > path name. The symptom of this is an error message when the license > agreement is being displayed. > > >
Re: Web hosting companies that offer Perl and MySQL or Postgres?
I have used www.1and1.com for over a year at $10 per month. Hardy Merrill <[EMAIL PROTECTED]> wrote: Sorry, I know this is off (DBI) topic, but it does relate to Perl and a database. I'm about to start developing an app with Perl DBI with the thought that eventually I'd like to put the app on the web - for which I plan to get a website hosting company, like Yahoo or whatever. So I'm trying to decide which database to use - I want to use one that I know will be supported by the webhosting company I choose. I've used both MySQL and PostgreSQL and have no problem using either. I know Yahoo hosts websites and their "Standard" and "Premium" packages both offer Perl and MySQL. Are there some web hosting companies that offer Perl and Postgres? TIA. Hardy Merrill Allen Guan 713-369-0269 (o) 713-502-2333 (c) 281-489-2314 (h) - Yahoo! Personals Single? There's someone we'd like you to meet. Lots of someones, actually. Yahoo! Personals
Re: [GENERAL] undefined behaviour for sub-transactions?
Andrew Sullivan <[EMAIL PROTECTED]> wrote: > The inconvenience I'll grant, but the non-standard claim I think > needs some justification. When the database encounters an error in a > transaction, it is supposed to report an error. An error in a > transaction causes the whole transaction to fail: that's what the > atomicity rule of ACID means, I think. I actually am sort of > unconvinced that SQLite's transactions are real ones -- I just did > some playing around with it, and it seems that any error allows you > to commit anyway. Certainly, MySQL's support of transactions is > occasionally pretty dodgy, unless you use the strict mode. Either way the end result is that some database drivers poison a transaction if there's any error, others are selective about which errors are fatal and which are not, and still others just don't care at all. The end goal of DBIx::Transaction is to hide these differences from the application so that transactions behave in a consistent way despite what driver or driver options you're using, so on that note I've uploaded DBIx-Transaction-0.002 to PAUSE, which will take the "lowest common denominator", having any erronious query poison the entire transaction. > But it's worth knowing that in Pg 8.1 and later, you can wrap such > things in a subtransaction and get out of it that way. Nifty! :) Cheers, Tyler
Re: Proxy question?
On Tue, 2005-11-29 at 11:55 -0600, Scott T. Hildreth wrote: > Whoops, trace file was to big... attached is the first 200 lines. > > On Tue, 2005-11-29 at 11:39 -0600, Scott T. Hildreth wrote: > > Perl: 5.008003(i686-linux) > > OS : linux (2.4.24-abi) > > DBI : 1.48 > > DBD::mysql : 2.9008 > > DBD::Sponge : 11.10 > > DBD::Proxy : 0.2004 > > > > Use of uninitialized value in subroutine entry at > > /usr/local/lib/perl5/site_perl/5.8.3/i686-linux/DBD/Proxy.pm line 533. > > Use of uninitialized value in subroutine entry at > > /usr/local/lib/perl5/site_perl/5.8.3/i686-linux/DBD/Proxy.pm line 533. > > Use of uninitialized value in subroutine entry at > > /usr/local/lib/perl5/site_perl/5.8.3/i686-linux/DBD/Proxy.pm line 533. > > Use of uninitialized value in subroutine entry at > > /usr/local/lib/perl5/site_perl/5.8.3/i686-linux/DBD/Proxy.pm line 533. > > > > ...this is a grep from the trace log, why are the NUM_OF_FIELDS 'undef' and > > not 0? Not sure what is going on here, I upgraded to 1.49 and still no luck. Running in the debugger, I see the NUM_OF_FIELDS value is set to '0' and from other servers I get the 'undef'. Will have to investigate more, for now I wrap an eval around the process and capture the uninitialized warnings. Just thought I would give an update, in case this problem was keeping somebody up at night. :-) > > > > STORE DBI::st=HASH(0x85a8bc8) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x85a8e98) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x85ae1d4) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x8397b18) 'NUM_OF_FIELDS' => undef > > STORE DBI::st=HASH(0x8482598) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x8488e6c) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x8482838) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x8482658) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x848141c) 'NUM_OF_FIELDS' => undef > > STORE DBI::st=HASH(0x8488f44) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x848ead4) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x848d7e8) 'NUM_OF_FIELDS' => undef > > STORE DBI::st=HASH(0x848cb90) 'NUM_OF_FIELDS' => '1' > > STORE DBI::st=HASH(0x848762c) 'NUM_OF_FIELDS' => undef > > > > > > DBI Trace Level 9 attached, > > > > > > Thanks, > > > > STH > > -- Scott T. Hildreth <[EMAIL PROTECTED]>
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
Tim Bunce <[EMAIL PROTECTED]> wrote:
> > sub execute {
> > my $self = shift;
> > my $rv = eval { DBI::st::execute($self, @_); };
>
> I'd probably say:
>
> my $rv = eval { $self->SUPER::execute(@_) };
Yeah, maybe... I adopted that idiom because this doesn't work:
--snip--
sub close_transaction {
my $self = shift;
my $method = shift;
my $code = $self->SUPER::can($method);
$self->{private_DBIx_Transaction_Level} = 0;
$self->clear_transaction_error;
$self->transaction_trace($method);
my $rv = $code->($self, @_);
return $rv;
}
--snip--
My commit() method would end up calling
$self->close_transaction('commit'), with would then call my commit() method
again, instead of DBI::db's... so I changed it to read
my $code = DBI::db->can($method);
And everything's happy again.
> Very few databases support information_schema.
*grumble*
> The generic portable fallback is "select 1 from $table where 1=0" - if that
> statement can be executed without error then the table exists.
... but only if you're not already in the middle of a transaction,
which means setting up state counters and having to refresh them constantly
if this is the sort of table that can come and go as it pleases... I suppose
I could do that in this particular application since it's *very* unlikely
that multiple people/apps will be engaging in schema management on the same
database in paralell, but:
> This is something I've been meaning to address for a while. I was
> thinking of something like:
>
> $schema_name = $dbh->current_schema
I really like this. I read your exchange with Steffen Goeldner and
that prompted me to take a look at the get_info method. I expected either
SQL_DATABASE_NAME (16) or SQL_CATALOG_NAME (10003) to tell me the current
database name, but I guess I was wrong; on both MySQL and Pg,
SQL_DATABASE_NAME returns nothing; under MySQL, SQL_CATALOG_NAME returns
'Y', under Pg, 'N'.
> Why not help save the world and help me add current_schema() to the DBI
> and send implementations to the authors of drivers you're using?
SQL_DATABASE_NAME is such a self-explanitory label that I have to
believe this is the right place to put the information we're after, but I
could be way off-base. I'd be willing to task myself with producing patches
for the Pg, MySQL, and SQLite2 drivers to either make use of that attribute,
or provide a current_schema (or maybe current_database? under Pg at least,
schemas and db's are different) method. Which way is the right way to go
here?
I also noticed that the SQLite2 DBD driver doesn't even return
SQL_DBMS_VERSION... hmmm, might have to add that too...
- Tyler
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
On Wed, Nov 30, 2005 at 04:25:41PM +0100, Steffen Goeldner wrote: > Tim Bunce wrote: > > > > [...] > > > > Why not help save the world and help me add current_schema() to the DBI > > and send implementations to the authors of drivers you're using? > > I'd like to remark that SQL/CLI has a more general function > > GetSessionInfo( ConnectionHandle, InfoType, ... ) > > Data TypeCode Information Type > --- - > --- > USER and CURRENT_USER CHARACTER(L)47 CURRENT USER > CURRENT_DEFAULT_TRANSFORM_GROUP CHARACTER(L) 20004 CURRENT DEFAULT > TRANSFORM GROUP > CURRENT_PATHCHARACTER(L) 20005 CURRENT PATH > CURRENT_ROLECHARACTER(L) 20006 CURRENT ROLE > SESSION_USERCHARACTER(L) 20007 SESSION USER > SYSTEM_USER CHARACTER(L) 20008 SYSTEM USER > > (similar to GetInfo()). Translated to DBI , this would be > > $value = $dbh->get_session_info( $info_type ); Great. It should be straightforward implement by copying how get_info() works. Any volunteers? Tim.
Re: DBI->connect() fails, Proxy, Oracle
On Wed, Nov 30, 2005 at 05:50:07PM +0200, Claude wrote: > Hi, > > I am new to DBI, and that may explain my question. > > Using DBI::Proxy, I am trying to connect remotely to an Oracle > database whose datasource looks like this: > > 'jdbc:oracle:thin:@127.0.0.1:1521:abc' > > I tried passing a string to the call DBI->connect($ds, ..): > > $ds=DBI:Proxy:hostname=host.com;port=1521;dsn=... > > Now that call always fails for a reason that I don't understand, with > the following error(s): > > clip -- > Argument " " isn't numeric in repeat (x) at I believe this is fixed in DBI-1.49 (just released). Tim.
Re: What's the best "free" DB for a web-based app?
You'd be remiss not to look at Firebird. Mysql5, Firebird 1.5+ and Postgres are what you should be comparing and testing in my opinion.
RE: What's the best "free" DB for a web-based app?
Wow, that's a tough one. It really depends on what you want/need from
your database. What's your recoverability liability? How scalable do
you need your app? Platform/OS requirements?
Being from primarily an Oracle background (as far as DBs go), I'd say
overall "Oracle". Free? Yes! There's now a free Express Edition
("XE") currently for Linux and Winders (beta) which can be downloaded
from http://www.oracle.com/technology/products/database/xe/index.html
Like everything there are caveats, and I believe a big one to be that
DBD::Oracle doesn't yet play will this version of Oracle (see previous
threads on this list although I haven't been paying that close attention
to it myself since we use the full-blown Enterprise Edition). It's also
limited in DB size and what features are supported. See the above link
for more info.
I've also used MySQL 4.x at home. Being an Oracle-type, I found it to
be lacking in backup/recovery as well as scalability, but my tests
admittedly weren't very scientific. For home use, I've switched my
MySQL DBs to PostgreSQL 8.0 (haven't taken the 8.1 leap yet).
PostgreSQL? Quickly, it's as close feature-wise to "enterprise" as
full-blown pay-up-the-gazoo Oracle as I've seen. Plus, you can write
your DB procs in Perl if you want. :)
DBI-wise, I have no real preference for any of the three. SQL-wise, I
much prefer Oracle, with PostgreSQL coming in a close second, with MySQL
off in a distant 3rd. Probably because the syntax of PostgreSQL is very
close to Oracle. :)
I know this e-mail is way too generic to be of any real use, but thought
I'd chime in on some areas that might be important for your decision
like recoverability, scalability and flexibility.
HTH! GL!
Rich
-Original Message-
From: John Armstrong [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 29, 2005 7:02 PM
To: dbi-users@perl.org
Cc: cgiapp@lists.erlbaum.net
Subject: What's the best "free" DB for a web-based app?
Hi - I'm soon to be doing a Perl app on the Internet, that'll need
database. We want the db to be as "free" as possible, but still fully
multi-user (web-based). Would the best route be MySql on Linux? Random
access files? Something else? We want no licensing obligations (no
Oracle,
Sequal Server, etc.). We want to go with Perl because it's the best
programming language invented by humans.
Re: What's the best "free" DB for a web-based app?
Thanks much for your cautions, Tim, but no harm done at all. Looks like most people endorsed MySql. SQL Lite is totaling a little weak, and Postgres is as good as MySql, just has more of an emphasis on db internals rather than (MySql's) GUI. MySql's freeness might be getting shakey, except when under Linspire (Linux). For this app, the design version is in Access, prod on Linux. Circa 6 concurrent users, 24 tables, 3 lookup tables of which are updated/supplied by federal and CA state agencies, linked to, not internal to the app. Tim Bunce <[EMAIL PROTECTED]To: John Armstrong <[EMAIL PROTECTED]> com>cc: dbi-users@perl.org, cgiapp@lists.erlbaum.net Subject: Re: What's the best "free" DB for a web-based app? 11/30/2005 03:27 AM I'd just like to ask anyone thinking of replying to this thread to *please* only do so if you can add something useful that's not already been said, and do so politely, with consideration for the validity of other peoples views. Tim. On Tue, Nov 29, 2005 at 05:02:23PM -0800, John Armstrong wrote: > > Hi - I'm soon to be doing a Perl app on the Internet, that'll need > database. We want the db to be as "free" as possible, but still fully > multi-user (web-based). Would the best route be MySql on Linux? Random > access files? Something else? We want no licensing obligations (no Oracle, > Sequal Server, etc.). We want to go with Perl because it's the best > programming language invented by humans. >
DBI->connect() fails, Proxy, Oracle
Hi,
I am new to DBI, and that may explain my question.
Using DBI::Proxy, I am trying to connect remotely to an Oracle
database whose datasource looks like this:
'jdbc:oracle:thin:@127.0.0.1:1521:abc'
I tried passing a string to the call DBI->connect($ds, ..):
$ds=DBI:Proxy:hostname=host.com;port=1521;dsn=...
Now that call always fails for a reason that I don't understand, with
the following error(s):
clip --
Argument " " isn't numeric in repeat (x) at
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/DBD/Proxy.pm
line 63 (#1)
(W numeric) The indicated string was fed as an argument to an operator
that expected a numeric value instead. If you're fortunate the message
will identify which operator was so unfortunate.
Uncaught exception from user code:
DBI
connect('hostname=host.com;port=1521;dsn=DBI:jdbc:oracle:thin:@127.0.0.1:1521:isc','batman',...)
failed: Cannot log in to DBI::ProxyServer: Unexpected EOF from server at
/usr/lib/perl5/vendor_perl/5.8.0/RPC/PlClient.pm line 79.
at ./test0.pl line 39
DBI::connect('DBI','DBI:Proxy:hostname=host.com;port=1521;dsn=DBI:jdbc...','batman','secret','HASH(0x82d7e38)')
called at ./test0.pl line 39
clip --
The first error (warning?) points to the following line:
my ($err,$state) =
($errmsg =~ s/ \[err=(.*?),state=(.*?)\]//) ? ($1,$2) : (1,5 x ' ');
Can I ignore this error?
Am I using the wrong driver (DBI::Proxy), should I installed another
driver?
Thanks for your time!
--
Claude
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
Tim Bunce wrote: > [...] > > Why not help save the world and help me add current_schema() to the DBI > and send implementations to the authors of drivers you're using? I'd like to remark that SQL/CLI has a more general function GetSessionInfo( ConnectionHandle, InfoType, ... ) Data TypeCode Information Type --- - --- USER and CURRENT_USER CHARACTER(L)47 CURRENT USER CURRENT_DEFAULT_TRANSFORM_GROUP CHARACTER(L) 20004 CURRENT DEFAULT TRANSFORM GROUP CURRENT_PATHCHARACTER(L) 20005 CURRENT PATH CURRENT_ROLECHARACTER(L) 20006 CURRENT ROLE SESSION_USERCHARACTER(L) 20007 SESSION USER SYSTEM_USER CHARACTER(L) 20008 SYSTEM USER (similar to GetInfo()). Translated to DBI , this would be $value = $dbh->get_session_info( $info_type ); Steffen
Re: Web hosting companies that offer Perl and MySQL or Postgres?
> Sorry, I know this is off (DBI) topic, but it does relate to Perl and a > database. > > I'm about to start developing an app with Perl DBI with the thought that > eventually I'd like to put the app on the web - for which I plan to get a > website hosting company, like Yahoo or whatever. So I'm trying to decide > which database to use - I want to use one that I know will be supported by the > webhosting company I choose. I've used both MySQL and PostgreSQL and have no > problem using either. I know Yahoo hosts websites and their "Standard" and > "Premium" packages both offer Perl and MySQL. > > Are there some web hosting companies that offer Perl and Postgres? We do: http://systame.com/html/macwebhosting/ -- Randall Perry sysTame Xserve Web Hosting/Co-location/Leasing QuickTime Streaming Mac Consulting/Sales http://www.systame.com/
Web hosting companies that offer Perl and MySQL or Postgres?
Sorry, I know this is off (DBI) topic, but it does relate to Perl and a database. I'm about to start developing an app with Perl DBI with the thought that eventually I'd like to put the app on the web - for which I plan to get a website hosting company, like Yahoo or whatever. So I'm trying to decide which database to use - I want to use one that I know will be supported by the webhosting company I choose. I've used both MySQL and PostgreSQL and have no problem using either. I know Yahoo hosts websites and their "Standard" and "Premium" packages both offer Perl and MySQL. Are there some web hosting companies that offer Perl and Postgres? TIA. Hardy Merrill
Re: What's the best "free" DB for a web-based app?
I'd just like to ask anyone thinking of replying to this thread to *please* only do so if you can add something useful that's not already been said, and do so politely, with consideration for the validity of other peoples views. Tim. On Tue, Nov 29, 2005 at 05:02:23PM -0800, John Armstrong wrote: > > Hi - I'm soon to be doing a Perl app on the Internet, that'll need > database. We want the db to be as "free" as possible, but still fully > multi-user (web-based). Would the best route be MySql on Linux? Random > access files? Something else? We want no licensing obligations (no Oracle, > Sequal Server, etc.). We want to go with Perl because it's the best > programming language invented by humans. >
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
On Tue, Nov 29, 2005 at 04:05:26PM -0800, Tyler MacDonald wrote:
> Tim Bunce <[EMAIL PROTECTED]> wrote:
> > PostgreSQL is non-standard (and inconvenient) in this respect.
>
> I chatted with Mischa (my work's resident DB guru) about this, and
> according to him, the error behaviour when you attempt to SELECT from a
> table that does not exist is "undetermined" in the SQL standard, so it
> really is the individual DBMS' choice. I think that's actually worse; all of
> these DBMs are behaving completely differently but still "correctly" on such
> a basic SQL operation due to a lack of standard!
I was thinking more generally: the failure of a statement within a
transaction (such as an insert getting a duplicate key error) usually
rolls-back just that statement and does not abort the whole transaction.
If that's not true for PostgreSQL then that's certainly inconvenient.
> > There isn't, as far as I know, except to accept the 'lowest common
> > denominator'. In this case that means forcing a rollback if any
> > statement fails.
>
> > execute() is sufficient if the driver doesn't also supply it's own do()
> > because DBI's default do() calls execute(). But some drivers do supply
> > their own do() method (for good reasons).
>
> Fair enough. So what I've done, is modified DBIx::Transaction to
> mark a transaction error if any query in the transaction returns false;
>
> --snip--
> sub execute {
> my $self = shift;
> my $rv = eval { DBI::st::execute($self, @_); };
I'd probably say:
my $rv = eval { $self->SUPER::execute(@_) };
> if($@) {
> $self->{Database}->inc_transaction_error;
> die "[EMAIL PROTECTED]";
> }
> if(!$rv) {
> $self->{Database}->inc_transaction_error;
> }
> return $rv;
> }
> --snip--
>
> (and similar logic for db::do()).
>
> The package I'm working on that uses DBIx::Transaction now also
> checks for the existance of a table before attempting to manipulate it.
See note above. I think that's just one example of a more general issue.
> I was considering using the "table_info" method for this, but
> there's a problem there; I don't know how to ask DBI what database/catalog
> name I am currently working in, and "undef" is documented as returning
> tables in *every* database, not just the current one.
This is something I've been meaning to address for a while. I was
thinking of something like:
$schema_name = $dbh->current_schema
> So what I've done instead is defaulted to this query to check for a
> table:
>
> SELECT 1 FROM information_schema.tables WHERE table_name = ?
>
> ... then for MySQL,
>
> SHOW TABLES LIKE ?
>
> ... and SQLite2,
>
> SELECT 1 FROM sqlite_master WHERE type ='table' AND name = ?
>
> The default query does work for postgres, and I'm told I can expect
> it to work with M$SQL, Sybase, and Oracle as well. Can you think of any DBI
> drivers I should expect it to *not* work with?
Very few databases support information_schema.
The generic portable fallback is "select 1 from $table where 1=0" - if that
statement can be executed without error then the table exists.
> DB::Introspector claims to help you do this, so I might just use it.
> Although it does a lot more stuff that I don't need, and I only see MySQL,
> Oracle, and Postgres subclasses for it. Is there a better module or method
> out there?
I don't know off-hand.
Why not help save the world and help me add current_schema() to the DBI
and send implementations to the authors of drivers you're using?
Tim.
Re: What's the best "free" DB for a web-based app?
On 2005-11-29 18:13:04 -0800, Darren Duncan wrote: > At 5:02 PM -0800 11/29/05, John Armstrong wrote: > >Hi - I'm soon to be doing a Perl app on the Internet, that'll need > >database. > > If you want something that's trivially easy to use, try SQLite. [...] > Its also faster than anything else for some types of usage, and unlike many > typical MySQL setups, it is fully transactional, ACID compliant, and safe. > > Certain kinds of usage with many writing processes may be slow, though, since > only one active writer can access a SQLite database at once. Even worse, when a table is locked by one process, attempts to access it from another result in an error. So it is entirely possible that an application works fine during testing but bombs out spectacularly with a higher workload. There are ways around that, but that default behaviour is surprising for anyone who's used other RDBMSs before. hp -- _ | Peter J. Holzer| In our modern say,learn,know in a day |_|_) | Sysadmin WSR | world, perhaps being an expert is an | | | [EMAIL PROTECTED] | outdated concept. __/ | http://www.hjp.at/ |-- Catharine Drozdowski on dbi-users. pgpvb0THgf0gK.pgp Description: PGP signature
RE: [cgiapp] What's the best "free" DB for a web-based app?
This is a pretty difficult to answer question. How heavy do we expect the usage to be? What kind of system will it be running on? In general, I've found that MySQL has the best GUI applications. The MySQL Query Browser makes writing scripts and executing arbitrary SQL statements on the database a breeze. I've heard arguments that postgresql is a better database platform; the claim is that postgres is more functional and runs faster (I don't know how this changed with MySQL 5). If your site is going to be real light traffic, then you can use SQLite, which is file-based. Writes to the DB require file locking but reads are concurrent. Push comes to shove, a well designed program will not have a lot of dependency on the database. All of your SQL statements should be aggregated in a single place, and if you use DBI (and fairly vanilla syntax) then you can completely change the platform just by changing the DSN. Josh -- Josh Danziger [EMAIL PROTECTED] -Original Message- From: John Armstrong [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 29, 2005 8:02 PM To: dbi-users@perl.org Cc: cgiapp@lists.erlbaum.net Subject: [cgiapp] What's the best "free" DB for a web-based app? Hi - I'm soon to be doing a Perl app on the Internet, that'll need database. We want the db to be as "free" as possible, but still fully multi-user (web-based). Would the best route be MySql on Linux? Random access files? Something else? We want no licensing obligations (no Oracle, Sequal Server, etc.). We want to go with Perl because it's the best programming language invented by humans. - Web Archive: http://www.mail-archive.com/cgiapp@lists.erlbaum.net/ http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2 To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
Tyler MacDonald wrote: Tim Bunce <[EMAIL PROTECTED]> wrote: PostgreSQL is non-standard (and inconvenient) in this respect. I chatted with Mischa (my work's resident DB guru) about this, and according to him, the error behaviour when you attempt to SELECT from a table that does not exist is "undetermined" in the SQL standard, so it really is the individual DBMS' choice. I think that's actually worse; all of these DBMs are behaving completely differently but still "correctly" on such a basic SQL operation due to a lack of standard! Hope you don't mind my chirping up ... Specifying behaviour down to the detail level of executing app errors would be enough to keep a bunch of DBMS vendors from EVER adopting a standard. It's not like Perl. It's way more like COBOL. Hard enough to agree on what to do when the app follows the rules. The ODBC solution was to allow any DB to express its abilities in crazy detail, through SQLGetInfo. What kinds of joins it supported, what kinds of transaction, and then let the app do or not do what it had to ... Does DBI have something like that? There isn't, as far as I know, except to accept the 'lowest common denominator'. In this case that means forcing a rollback if any statement fails. How about having DBI implement a consistent nested-transaction interface that compliant DBMS's can support? -- Some people think the glass is half-full. Some people think the glass is half-empty. Engineers think, "This glass is twice as big as it needs to be."
RE: Problem with Oracle 10.2 and Perl dbd/dbi interface
Just to get more information, I presume you are talking about the ActiveState perl and DBD::Oracle installed via ppm from their site? I'm also presuming you installed the Oracle Instant client along with (which they do now, but you can skip it, if you have the full client). Please confirm, so we have better information. I have installed their client, but said "no" when they went to install the instant client...and I don't (yet) seem to have problems (other than the new Database install on Windows injects PERL5LIB environment variables which screws up my configuration badly...) Regards, Jeff -Original Message- From: Martindale, Marty (NGIT) [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 29, 2005 2:21 PM To: 'dbi-users@perl.org' Subject: Problem with Oracle 10.2 and Perl dbd/dbi interface There is a problem in Oracle 10.2 that surfaces after you install the latest Perl, dbd-oracle and dbi on a windows box. The symptom is that sqlplus and sqlplusw no longer function, they return almost immediately to the command prompt. This is a know but undocumented problem in oracle. I submitted a tar to get the information. The problem file is 'oraociei10.dll' was not compiled at the correct level. Renaming this file will get sqlplus and sqlplusw functioning again. The file is located in Perl\bin'. There is also a problem with the dbi installation if you have blanks in the path name. The symptom of this is an error message when the license agreement is being displayed.
