Re: SQLite - Time to build a database
On Thu, 3 Jun 2010 11:55:47 +0300, Henri Asseily wrote: If you're ok with one big transaction where if it fails you have to redo it all, then just do the above. Well in the root post he did say: 1500 of these rows have faulty data. so I don't think it's OK to fail completely on an error... Unless the data gets prescreened. In Perl. -- Bart Lateur bart.lat...@telenet.be
Re: SQLite - Time to build a database
On Wed, 26 May 2010 10:46:43 +1000, Owen wrote: So I read the main database row by row, tested the validity of one column, and inserted the good rows into a new database. This process took 27 minutes on a fairly recent desktop computer. ( about 1 rows a minute or 170 a second ) I thought the whole process would take a few seconds, so I wonder if 27 minutes is a reasonable time for this database creation. Well I timed actions using SQLite some time ago and I am lead to believe that every commit produces an fsync() in the database, which takes roughly 100ms on my old 1MHz PC running Linux, and which you may expect to take 50ms on a more modern computer. 2 possible solutions are: 1) don't commit on every single row, keep an eye on the clock and commit every N seconds (I'm thinking of 1/2 minute) plus once at the very end, of course 2) edit the C source of SQLite and replace the fsync() with fflush(), which ought to be around 100 times faster, but which isn't garanteed to withstand computer crashes, if it goes down before the data is finally stored on disk - not ideal in the eye of ACID perfectionists. Anyway, with current cache sizes inside the disk, nothing is garanteed after an fsync(), anyway. (Data might be in the disk's own cache but not yet committed to the physical disk) As the database was being created, I noticed a journal file was in use though I haven't worked out what that did, yet. It SQLite's scratchpad, where it stores its intentions for each transaction. If the computer goes down before a transaction is finalized, this could likely be used to redo the action and this time, complete it. Or, roll it back properly. -- Bart Lateur bart.lat...@telenet.be
Re: anyone accessing h2 java database with DBI?
On Wed, 05 May 2010 07:06:17 +0200, Alexander Foken wrote: It doesn't seem so. But according to the web page you linked, h2 has an ODBC driver, so you should be able to use DBD::ODBC. And the web site shows several JDBC URLs, so there must be a JDBC driver, so you should be able to use DBD::JDBC. As this is apparently a database written in Java, I'd expect the most important interface to be JDBC. And yes, looking at the project's site, its home page starts with: Welcome to H2, the Java SQL database. The main features of H2 are: * Very fast, open source, JDBC API Well, duh... Look no further than JDBC, that's what I would say. -- Bart Lateur bart.lat...@telenet.be
Re: Trying to safely compare user input name against database
On Tue, 4 May 2010 13:10:52 -0400, Larry W. Virden wrote: On Tue, May 4, 2010 at 12:39 PM, Martin Hall martin.h...@oracle.com wrote: But, you can create function based indexes, which will avoid full table scans. What is a function based index? It's a database index based on the value of a function for a row, instead of on the raw column values. -- Bart Lateur bart.lat...@telenet.be
Re: parameter issues in DBD:Oracle
On Fri, 26 Mar 2010 14:32:52 -0400, Bobak, Mark wrote: Is there a way to pass an unquoted list as a parameter? I can't do it as 'in(?,?,?,?)' etc, because the number of list elements varies from execution to execution. There's a special feature in DBIx::Simple to tackle this exact case. Look up ?? in the docs at http://search.cpan.org/perldoc?DBIx::Simple -- Bart.
Re: DBI.pm package not found
On Thu, 10 Dec 2009 16:30:58 +0530, Kallol Chakraborty wrote: I am having DBI.pm package in /oracle/app/oracle/product/10.2.0/in-dwh/perl/lib/site_perl/5.8.3/i686-linux-thread-multi , which is coming with Oracle 10G . In my perl code I am using use lib /app/oracle/product/10.2.0/in-dwh/perl/lib/site_perl/5.8.3/i686-linux-thread-multi ; I assume that's not copy/pasted, as it's missing a quote use DBI; but it is giving error Can't locate DBI.pm in @INC Well, technically, the directory you ought to be including in @INC is /app/oracle/product/10.2.0/in-dwh/perl/lib/site_perl; the rest of the path is automatically included by lib.pm (it automatically adds several directories under that root). I'm guessing either DBI is trying to autoloading some other file which it cannot find, due to incomplete data in @INC; or that that installation is binary incompatible with your perl (if you're not using Oracle's perl; if you were, that directory should have been included by default). Best just install DBI and DBD::Orcale for your own perl, IMHO. -- Bart.
Re: Problems installing DBI and DBD::mysql to a local directory
On Thu, 08 Jan 2009 13:48:02 +, Martin Evans wrote: Looks like you are not the only one with this chopblanks problem: http://www.nabble.com/Bundle::Bugzilla-install-hangs-td20363858.html http://lists.mysql.com/perl/4296 http://ppm4.activestate.com/i686-linux/5.8/818/C/CA/CAPTTOFU/DBD-mysql-4.008.d/log-20080816T160904.txt so I'd doubt it is due to the way you are installing it. The second one looks identical to yours and is still unanswered. I assume the database could be doing it. The OP appears to be using a version of Mysql 5, so maybe it's something an older version of the DB does? Under specific configuration settings, perhaps? -- Bart.
Re: Mailing list
On Sat, 29 Nov 2008 17:24:44 +0530, Sureshkumar M (HCL Financial Services) wrote: Can someone send the maid's for Perl forum where I can clear all my doubts? I would like to discuss lot of doubts and get answer and get quick answers. It depends on the subject of the question. You can see a list of what's available on http://lists.perl.org/ . -- Bart.
Re: [rt.cpan.org #36395]
On Wed, 25 Jun 2008 03:12:42 -0400, [EMAIL PROTECTED] via RT wrote: Queue: DBI Ticket URL: http://rt.cpan.org/Ticket/Display.html?id=36395 Hi Tim On Tue, 2008-06-24 at 17:17 -0400, Tim_Bunce via RT wrote: Queue: DBI Ticket URL: http://rt.cpan.org/Ticket/Display.html?id=36395 The DBI is acting as defined. It's not a bug. You should avoid using an array to pass arguments to execute(). I can't image why I received this email. I have not received any others on DBI. I suggest you report it to the managet of RT. From the headers: Delivered-To: dbi-users@perl.org So, don't take it personal. -- Bart.
Re: Fw: How to Retrieve Table Name from Statement Handle
On Wed, 7 May 2008 12:41:01 -0700 (PDT), Lamb Joseph wrote: I will have to break apart the SQL statement with a regex and store it that way. Thanks for the input. More input: take a look at Ovid's article Lexing Your Data on perl.com, at http://www.perl.com/pub/a/2006/01/05/parsing.html which discusses using a tokenizer (lexer) to pull tokens, such as table names, out of SQL statements. -- Bart.
Re: DBD-ODBC and DBI 1.21
On Fri, 25 Apr 2008 07:48:35 -0700 (PDT), jbeadles wrote: This upgrade is requiring me to reload DBI and DBD-ODBC. The issue is that the DBD-ODBC install fails because it requires DBI 1.21. Googling everything I can find over the past couple of days indicates that this is a popular problem. The problem is that I can't find an Activestate compatible version of DBI 1.21, and can't get the CPAN version to make. I've looked in the Activestate folders of downloadable zips for all Perl releases, and 1.21 does not exist. I've found: 5xx - 1.14 6xx - 1.34, 1.35, 1.37, 1.48 8xx - 1.35 - 1.602 10xx - 1.602 But no 1.21. Eh, what?? It requires *at least* version 1.21 of DBI. There's no need for it to be exactly that version. You can install anything newer. -- Bart.
Re: DBI error
On Tue, 29 Jan 2008 13:44:16 -0700, Kirthi Prabhu wrote: I get the pg.pm file and I have placed that in DBD/ folder. Generally not a good sign... When I use DBI - Connect I get the following error install_driver(Pg) failed: Can't locate loadable object for module DBD::Pg in @INC I was expecting that. This means you don't have the DLL that belongs to DBD::Pg. Just copying the Pure Perl part of the module is not good enough. Just install the DBD::PG module properly, for example with PPM, instead of his halfhearted attempt, which you should not undertake unless you know very much what you're doing (in which case you wouldn't be asking about this error...) -- Bart.
Re: make test for DBI fails on Win98
On Mon, 27 Aug 2007 10:17:16 +0100, Tim Bunce wrote: Try the appended patch and let me know if it works for you. Yes, after the patch all tests pass. Funny, CPAN.pm also recently introduced similar code with similar problems. Is this a conspiracy, or just thoughtlessness? :) Win98 has other typical solutions, such as: you can't open a file for output in 2 programs at the same time. Surely there must be a way to emulate flock on Win98, using an extra lock file... That way you'd only be covered for programs written in Perl, adhering to the same convention, but probably that should suffice. Just thinking out loud... -- Bart.
make test for DBI fails on Win98
What are the plans on supporting DBI on older platforms? Since DBI 1.57 (also in 1.58 and 1.59), some tests fail on Win98 (ActivePerl 5.8.8 build 822, module compiled with MinGW 3.4.2 (+ nmake)), while all tests pass on XP. The culprit appears to be in DBI::ProfileDumper. A typical error message line: t/41prof_dump.flock() unimplemented on this platform at D:\programs\ActivePerl5.8\cpan\build\DBI-1.59\blib\lib/DBI/ProfileDumper.pm line 237. DBI::ProfileDumper on_destroy failed: flock() unimplemented on this platform at D:\programs\ActivePerl5.8\cpan\build\DBI-1.59\blib\lib/DBI/ProfileDumper.pm line 237.# Looks like you planned 15 tests but only ran 7. # Looks like your test died just after 7. -- Bart.
Re: ShowErrorStatement
On Tue, 14 Aug 2007 16:01:21 +, [EMAIL PROTECTED] wrote: To oversimplify somewhat: * PrintError prints an error message whenever there is an error. * RaiseError does the same as PrintError and also dies. I've always wondered, and now seems a good time to ask as any: why are PrintError and RaiseError not mutually exclusive? It seems silly to have both on at the same time, essentially printing the error message twice. (Yes I've tested it with a fairly recent version of DBI: 1.52). -- Bart.
Re: DBD::ODBC does not support bind_param_inout?
Sorry for the late reply, but I'm only scanning my inbox now.
On Wed, 18 Jul 2007 17:35:51 +0800, hu.darren wrote:
my $csr = $dbh-prepare(q{
BEGIN
:whoami := PLSQL_EXAMPLE_DARREN.FUNC_NP;
END;
});
but I failed with:
Can't bind unknown placeholder ':whoami' at /home/darren/perl/dbitest.pl
line 90.
Any suggestions?
It's not an ODBC problem. You didn't declare the bind parameter in
Oracle. This would fail in SQL*plus too. Try
$dbh-prepare(q{
variable whoami varchar2(40);
});
first. (untested)
--
Bart.
Re: problem with installing dbi module
On Sat, 10 Feb 2007 05:30:40 + (GMT), Jai chandru wrote: iam using pxperl 5.8.1 on windows and when i tried to install following error was reported . nmake gcc cannot be recoganised as a internal command. fatal error... PXPerl works well with MinGW, Minimalist GNU for Windows, which is GCC + tools. You should install that. You can start by getting and running the installer from http://prdownloads.sf.net/mingw/MinGW-5.0.2.exe?download, which will fetch and install everything else for you. -- Bart.
Re: DBD-ODBC.ppd
On Thu, 18 Jan 2007 11:14:11 +0100 (MET), Alexander Foken wrote: http://ppm.activestate.com/PPMPackages/zips/ http://search.cpan.org On 18.01.2007 10:32, Pamal Dias wrote: Hi, Please send me the DBD-ODBC.ppd file for perl 5.8.8. If that is not available please send me a the latest version available. Alexander didn't mention my favourite URL: Kobes' Search http://cpan.uwinnipeg.ca/search?query=DBD%3A%3AODBCmode=module The section at the bottom of the page lists known locations where you can get the PPD file. -- Bart.
Re: Retrying a fetch after an error, without restarting the whole loop?
On Wed, 8 Nov 2006 23:26:02 -0800, Jonathan Leffler wrote:
And 2), in a fetch loop, is it possible to adjust a property like
{ReadLongLen}, and retry the same fetch without restarting the whole
loop? Because this error typically happened several minutes into the
loop.
Highly unlikely. The data has been fetched - and truncated. There's not
usually a way to refetch the same row - unless you have a scroll cursor, and
DBI doesn't have support for those.
I can see that. Well I'm thinking of the following solution next:
retrieve extra data to identify the row that went wrong and collect
them, keep going on with the rest of the records, and individually fetch
the previously failed ones afterwards.
After a failure, I can go on with the next records, can't I? And
changing ReadLongLen, is that acceptable for the remainder of the loop?
--
Bart.
Retrying a fetch after an error, without restarting the whole loop?
I've been saving picture files that had been stored in a blob field in
an MS-Access database (aka an OLE Object) to files, and I've bumped
onto some LongReadLen related problems: through trial and error I
finally succeeded in making LongreadLen long enough to reliable extract
all the files. (in Access, the function LEN on such a field reports a
size that's half the number of bytes. Apparently it mistakes it for
Unicode text. I haven't found a better suited function than LEN, though
I haven't searched hard).
Anyway; as this was a process of several minutes, it took some time to
fix the script and start all over.
So I was wondering these two things:
1) What's the best way to temporarily disable RaiseError when I want to
have it enabled for the rest of the script? Say, for one SQL statement?
And 2), in a fetch loop, is it possible to adjust a property like
{ReadLongLen}, and retry the same fetch without restarting the whole
loop? Because this error typically happened several minutes into the
loop.
I think these 2 questions don't mix well. Oh well.
--
Bart.
Re: MSAccess from SQL
On Tue, 4 Apr 2006 23:40:27 -0600, Siegfried Heintze wrote: Is there an example somewhere of accessing an MSAccess database from a SuSE or RedHat machine via perl DBI? An Access database is best served on a Windows PC. In my experience, you don't even need to have Access installed for DBI to be able to work with it. The Mdbtools thing is not yet fully reliable, in my experience. (Blobs don't work well) So what you can do, is employ DBI's Proxy Server on that PC. Can you remotely access an ODBC database on a PC? I don't know, I4ve never tried. You don't want to run a database server on a PC? Then you definitely don't want to be using MS Access. -- Bart.
Re: DBD::ASAny
I see no replies to this post, so here goes... On Thu, 26 Jan 2006 15:49:09 +0800, nyem wrote: I'm using DBD::ASAny on my development machine (win2000) but when I copied them to the production server (win2003) I got this error: install_driver(ASAny) failed: Can't load 'H:/perl/site/lib/auto/DBD/ASAny/ASAny .dll' for module DBD::ASAny: load_file:The specified module could not be found at H:/perl/lib/DynaLoader.pm line 206. Compilation failed in require at (eval 7) line 3. Perhaps a required shared library or dll isn't installed where expected Does this means that I need to recompile DBD::ASAny for win2003? Or is there anything else that I forgot to tweak? It's already a hassle to install msVC just to compile this module on my machine... No, you appear to just need to copy the associated DLL as well. It needs to go under the root site/lib/auto, and the DLL you need is DBD/ASAny/ASAny.dll. While youy're there, look around if you see any other files you may need. -- Bart.
Re: Why no [dbi-users] in subject
On Sun, 13 Nov 2005 19:22:49 -0600, Jay Strauss wrote: How come this list doesn't have a [dbi-users] in the subject? That would be handy for message filtering There are various list related headers. For example: List-Id: dbi-users.perl.org That's enough for my mail client. -- Bart.
Re: ANNOUNCE:: DBIx::MyParse 0.20
On Sun, 11 Sep 2005 12:38:07 +0300, Philip Stoev wrote: * Provide the same interface to Postgresql's parser the way it is being done for MySQL, and at that time My in MyParse will no longer stand for MySQL. That sounds like the worst idea for a module name ever. It reminds me of the My Documents and My Computer on Windows. I personally find it just ridiculous. -- Bart.
Re: DBI v2 - The Plan and How You Can Help
On Wed, 17 Aug 2005 10:39:43 +0100, Tim Bunce wrote: I think it'll take years, and much actual production experience building Perl 6 modules before the community learns what works and what doesn't for a Perl 6 API (let alone implementation). So trying to pin down a properly Perl-6-ish API before Perl 6 is even through the language design process strikes me as a Very Bad Idea. I remember the early years of Perl 5 development, when a new feature was added there'd be a period of over-zealous use followed by a hangover as all the problems and edge-cases became apparent. With Perl 6 there's going to be some almighty hangovers :) Go ahead, implement it already. But it should be a side project, not the future of DBI. Like Topaz is not Perl6. http://www.perl.com/pub/a/1999/09/topaz.html -- Bart.
Re: Q: Any proven method of exporting data from Postgres as SQL and importing it into MySQL?
On Wed, 03 Aug 2005 14:37:44 +1000, Ron Savage wrote: (nothing) Please put your question in the mail body. A subject line only serves to give an idea what the post is about, now we actually have to read it as a (nontrivial) sentence. And take a look at SQL-Fairy, a project on Sourceforge... http://sqlfairy.sourceforge.net/ -- Bart.
Re: db or file access?
On Wed, 13 Apr 2005 20:06:44 +1000, Ron Savage wrote: Well, I thought I'd better put my keyboard where my mouth is, so I wrote an article on this: http://savage.net.au/Ron/html/images-in-files.html I've thought of another aspect which you didn't address in that article, and which can be very important. It is how you access the images. In db BLOBs, using DBI, there's only one way to get the images from the DBI: in one huge chunk. That's not trivial, you have to set LongReadLen to a large enough value, which always wastes a lot of memory even if you don't need it, and there's always the possibility that it still wasn't large enough. I know of no way to read a long field from a database in chunks -- unlike a file. -- Bart.
Re: inserting data coming from a hash
On Thu, 21 Apr 2005 09:36:33 -0400, Robert wrote:
This is my hash structure:
Veterans Day = {
date= '2005',
type= 'US',
federal = 'true',
active = 'true',
},
Would I just use a placeholder (?) in my statement and pass it in via that?
Use DBIx::Simple, it becomes a one-liner. Well, almost: you don't have
an entry for the holiday name.
$db-insert('mytable', {
name = $key,
date= '2005',
type= 'US',
federal = 'true',
active = 'true',
});
DBIx::Simple takes all the manual work out of your hand, prepares the
statement, exectutes it (with placeholders)... and keeps a cache of
prepared statements, so it's virtually as efficient as if you had built
it by hand.
--
Bart.
Re: db or file access?
On Wed, 13 Apr 2005 20:06:44 +1000, Ron Savage wrote: Well, I thought I'd better put my keyboard where my mouth is, so I wrote an article on this: http://savage.net.au/Ron/html/images-in-files.html It would be nice, IMHO, if your site had some sort of blog-like structure, so that people could comment on the site itself. BTW I tend to agree with you, without actually being convinced of its technological superiority. BTW one can use mod_rewrite to nicen up the URL for images. But files inside databases tend to blow up the actual database files. There's a lot of air in databases. Wasted disk space. -- Bart.
Re: DBI, MS Access, inserting/updating a Access Date/Time value
On Tue, 12 Apr 2005 23:34:09 -0700, Robb wrote: Does anyone have any idea what is up with Microsoft Access and its ridiculous Date/Time formatting options? I am guessing that MS-Access formats dates according to your settings in the Regional Settings control panel. -- Bart.
Re: A good Perl Book
On Fri, 25 Mar 2005 21:36:40 +0800, Gav wrote: what would you recomend as a good book to become an expert of sorts.? You could do worse than looking around at http://books.perl.org, or even at http://learn.perl.org if you're really new to Perl. -- Bart.
Re: Bug in DBD::Pg 1.32 with bytea columns
Just my two cents...
On Thu, 06 Jan 2005 20:31:10 +1300, Sam Vilain wrote:
The design flaw as I see it, is that the common idiom :
my $sth = $dbh-prepare(SELECT foo from bar where baz = ?);
$sth-execute($value);
while ($sth-fetch...) {
...
}
Is not a portable approach.
IMHO, it should be.
Depending on the DBD being used, you might
need to call bind_param with some arbitrary columns to let it know the
type of the data that a column has.
That ain't right. All should use pure strings, by default.
Do these database allow for inserting any byte, if they're witched to
literal mode?
If so, as Perl itself allows for defining strings in many ways, for
example using \x1B, I don't see any reason to support these double
escape mechanisms.
And if not, still, enabling the escape mechanism provided by the
database, should always require an extra step to enable it, and not ever
be enabled by default. In my opinion.
--
Bart.
Re: Is foreign_key_info() generic?
On Wed, 15 Dec 2004 12:46:16 +, Tim Bunce wrote: It's one of those things why people say Mysql isn't a proper database. This isn't the place to start that particular flame-fest, so I'd appreciate it if no one does. Sorry, I didn't mean to start a flamefest, honestly. I just wanted to give one concrete exampe for declamations people often do without a word of explanation. It doesn't actually stops me from using Mysql; but those things do annoy me. -- Bart.
Re: Is foreign_key_info() generic?
On Wed, 15 Dec 2004 01:06:38 +0100, Sebastian Riedel wrote: Does foreign_key_info behave the same for all dbd's? Especially SQLite and mysql? I never heard people entering foreign key info into Mysql databases. Is that even possible? Ah, yes, only starting with the InnoDB table type. Not with MyISAM, then, I suppose. http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html It's one of those things why people say Mysql isn't a proper database. -- Bart.
documentation bugs on LongRealLen
There's a few bugs in the code of the section of LongReadLen, even in
the latest version of DBI that is on CPAN. I quote:
If you can't be sure what value to use you could execute an
extra select statement to determine the longest value. For
example:
$dbh-{LongReadLen} = $dbh-selectrow_array{qq{
SELECT MAX(long_column_name) FROM table WHERE ...
});
$sth = $dbh-prepare(qq{
SELECT long_column_name, ... FROM table WHERE ...
});
Bug 1is a typo:
- $dbh-{LongReadLen} = $dbh-selectrow_array{qq{
+ $dbh-{LongReadLen} = $dbh-selectrow_array(qq{
Bug 2 is thinko. If your rows contain the values alpha, beta, zeta
omega in that column, $dbh-{LongReadLen} will be set to zeta. I
don't think that will buy us anything good. You want the *length* of the
*longest* string, not the string that sorts last in the word list. So,
somebody forgot about length(), or whatever the proper keyword is in
SQL.
Unfortunately that keyword is highly unportable. A Google search showed
me that octet_length() is probably what I'm after, for SQL-92. I tried
it on MS-Access using DBD::ODBC, and it failed completely. I replaced it
with the VB keyword Len, and it worked... more or less.
One word of caution, with regards to MS-Access. It turned out that
Len(), when used in Ms-Access' SQL, is actually treated as a string, so
MAX() returned the wrong result. The max value I got was about 82000,
while the actual longest string was closer to 18 bytes. What caused
it is simply that 8 gt 1.
Using a numerical context for that function, by adding 0 for example,
might have helped. I haven't tried it. It was a one shot program anyway.
--
Bart.
Re: Announcement/Request for Comments: DBIx::Call
On Fri, 26 Nov 2004 09:36:18 +0900, Thilo Planz wrote: It used to be called Oracle::Procedures, but since only the current implementation is Oracle-dependent and the interface is not (I think, but I really know only Oracle) I changed the name. I like DBIx::Procedures, with DBIx::Procedures::Oracle for your current incarnation. -- Bart.
Re: Perl Question:
On Fri, 29 Oct 2004 17:18:20 -0600, Reidy, Ron wrote:
Or (from Bart Lateur):
my $SQL = 'SELECT * FROM students WHERE id NOT IN ('
. join ',' , @ids
. ')'
;
I'd only ever do that for numerical id's. For the generic case, I4d use
placeholders, like anybody else here. (or wouldn't you?)
Simply replace
join ',', @ids
with
join(',', ('?') x @ids)
or with
join(',', map '?', @ids)
and call execute() with @ids as parameter provider.
--
Bart.
Re: DBI Installation Problem on Microsoft Windows.
On Wed, 20 Oct 2004 11:17:24 +0530, Murthy Pydikondala wrote: While I am trying to install perl DBI on Windows 2000 Professional, I am getting the following error after I executed nmake.exe 'cl' is not recognized as an internal or external command, Binary build 810 provided by ActiveState Corp. http://www.ActiveState.com ActiveState is a division of Sophos. You're trying to install an XS module from source without a C compiler. (cl is the command line C compiler from MS Visual C.) Try installing a precompiled version, with PPM. You could Google around for a more recent version of DBI for PPM than what ActiveState itself is providing, by searching from the magic words DBI ppd. You need to find the URL for a DBI*.ppd file (the star represents a glob, thus any string) compatible with your Perl version (in your case 5.8.x), and point PPM to it. For example, http://www.cedet.dk/perl/ appears to be carrying DBI version 1.43. -- Bart.
Re: DBI Installation Problem on Microsoft Windows.
On Wed, 20 Oct 2004 12:01:31 -0400, Jeff Urlwin wrote: Or, see http://ftp.esoftmatic.com/DBI 1.45, that's even better. But I don't get it, why the separate subdirectories for the perl versions? All 5.8.x versions are supposed to be binary compatible. (Except for that 5.8.1 lapse, but Activestate's build allegedly didn't include the code responsible for the incompatibility.) In other words: I see no reason at all for separate builds for each perl version. -- Bart.
Re: passing array as subquery to a SQL query
On Mon, 27 Sep 2004 12:26:03 -0700, Jeff Zucker wrote:
my $SQL = select * from students where id in (@ids);
Instead use:
my $SQL = 'SELECT * FROM students WHERE id NOT IN ('
. join ',' , @ids
. ')'
;
Because a) you want NOT IN and b) The values inside the parens of the IN
predicate must be comma-separated.
Temporarily (using local) setting $ to ,, and using code like his
original code, seems simpler to me. But, TIMTWOWTDI.
local $ = ',';
my $SQL = select * from students where id not in (@ids);
--
Bart.
Connection timeout when using Apache::DBI in mod_perl
Is there anything foreseen to disconnect a DBI connection, in a mod_perl (2) setup with Apache::DBI, in case nothing has been requested in several minutes time? Do the connections stay open forever, or does the database timeout all by itself? (I suspect it does, anyway) The only thing I see in Apache::DBI related to timeouts is ping, to check if a connection is still up. That's the opposite of what I'm after. I want to close a connection to MS Access via DBD::ODBC, on my local Windows XP machine, if I've stopped using it for several minutes. What's the database's timeout, for that setup, anyway? -- Bart.
Re: DBD::SQLite: Migration to SQLite 3.0
On Sat, 17 Jul 2004 10:51:55 +0100, Matt Sergeant wrote: Anyone who has read my journal will know that I now have DBD::SQLite ported to SQLite 3.0, which provides better datatypes, better concurrency, proper blob support, etc. I thought you also said SQLite 3 was still very buggy? -- Bart.
Re: How to insert CR/LF into Sql Server database?
On Thu, 15 Jul 2004 17:53:44 -0400, Bowen, Mark wrote: I am trying to insert multi-lne data into a text type column in SQL Server. MS says this can be accomplished with the CHAR(13) CHAR(10) functions, however I cannot get these to work in DBI. If I write my text with these functions in them, they either get inserted into the database as text, or Perl complains that it cannot find these functions in the script. If I insert \n into sql server, it comes out as an open rectangle ( ). Try \015\012, or \x0D\x0A, or even chr(13) . chr(10) (perl code) -- Bart.
Re: no DBD:MSSQL?
On Tue, 13 Jul 2004 15:47:21 -0400, Janet Goldstein wrote: Thanks to all who replied. I ended up selling out and using Win32::ODBC. Eh... why not DBD::ODBC? -- Bart.
Re: Automating binding the parameter type
On Mon, 19 Apr 2004 14:25:41 -0700, Dean Arnold wrote: Now I'm confused. Are we talking about the same processing here ? I don't think so. I am talking about fetching the type for the bind parameters for an UPDATE query, out of the TYPE attribute (array ref) for a query SELECT * FROM mytable WHERE 1=0 for the *same* table. I am not copying a schema for table. I'm simply trying to generate the various kinds of queries (SELECT, UPDATE, INSERT) for one table from the same source (specification). Do the work *once*, not once for every type of query. -- Bart.
Re: Automating binding the parameter type
Here's an example of where specifying the parameter bind type is
necessary... at least for me, as I am in a country where the decimal
point is not a dot. In MS Access, I have the following query:
UPDATE mytable SET info = ? WHERE id = ?
The type of info is, in Access terminology, a single number (float), id
is a long integer.
Now if I execute this query with the value 16021.5 for info, like this:
$sth-execute(16021.5, 12345)
then it turns out the value in the db becomes 160215. The dot is simply
ignored! OTOH, it does work with a comma! What I think happens, is that
the placeholder is treated as a string, and Access converts the string
back to a float, using a comma as a decimal point... and the dot as the
other one -- according to the locale settings..
So, I'm looking for a solution... This does work:
$sth-bind_param(1, undef, SQL_DOUBLE);
$sth-execute(16021.5, 12345);
or this:
$sth-bind_param(1, 16021.5, SQL_DOUBLE);
$sth-bind_param(1, 12345, SQL_INTEGER);
$sth-execute;
BTW I doubt if this is the correct datatype, but SQL_FLOAT doesn't seem
to work,
Now because this is a generic tool, I want to generate these type
values out of the query:
$sth = $dbh-prepare(SELECT info, id FROM mytable WHERE 1=0);
$sth-execute;
my $types = $sth-{TYPE};
$sth-finish;
$sth = $dbh-prepare(UPDATE mytable SET info=? WHERE id=?);
$sth-bind_param(1, 16021.5, $types-[0]);
$sth-bind_param(2, 12345, $types-[1]);
So, I get the impression I'm on the right track... Am I? Or is this a
coincidence? For this case, it seems to work, even though the value for
the floating point (first type) is 7, while SQL_FLOAT (which doesn't
work) is 6, and SQL_DOUBLE (which does) is 8. I don't know what this is.
--
Bart.
Re: DBD::ODBC Connection no longer working
On Mon, 19 Apr 2004 13:06:26 +1000 (EST), Dennis M. Gray wrote:
$dbh = DBI-connect('dbi:ODBC:driver=Microsoft Access Driver
(*.mdb);dbq=F:/GROUPS/BUSINESS/BSS/safeguards/systems/testing/safeguards_test.mdb')
or die Cannot connect to Safeguards Test database;
I doubt that this ever worked... You need to use backslashes for the
database path.
--
Bart.
Re: DBD::ODBC Connection no longer working
On Mon, 19 Apr 2004 12:08:40 +0200, Bart Lateur wrote: my $dbh = DBI-connect(ddbi:ODBC:$dsn, , ); Oops, sorry for the typo. my $dbh = DBI-connect(dbi:ODBC:$dsn, , ); -- Bart.
Automating binding the parameter type
I'm working on a next generation of a DBI abstraction module...
Sometimes it's necessary to specify the type of placeholder parameter,
like
use DBI ':sql_types';
$dbh = DBI-connect(...);
$sth = $dbh-prepare(...);
$sth-bind_param(1, undef, SQL_INTEGER);
$sth-bind_param(2, undef, SQL_VARCHAR);
$sth-execute($param1, $param2);
My question is, assuming I have fetched a
$sth1 = $dbh-prepare(...);
$sth1-execute();
on the same table, before, is it safe to do:
for my $i (0 .. $#fields) {
$sth-bind_param($i+1, undef, $sth1-{TYPE}[$i]);
}
or are the values that the attribute TYPE holds, not compatible with
this?
--
Bart.
Re: DBI
On Thu, 8 Apr 2004 11:42:41 +1000, Simon Jordan wrote:
What is the equivalent dbi syntax for the following win32:odbc syntax ?
@FieldNames = $O-FieldNames();
I don't know Win32::ODBC... but I guess you want
@FieldNames = @{ $sth-{NAME} };
where $sth is a DBI statement handle for a SELECT query.
--
Bart.
modifying RaisError/PrintError for one SQL statement
I've been playing a little with the module Tie::DBI, and I noticed that
it died for me, using a DBI handle I provided, on this line:
my $sth = $dbh-prepare(LISTFIELDS $table);
(line 376 in the latest Tie::DBI, 0.93)
The reason is because I connected using:
{ RaiseError = 1, PrintError = 0 }
I tried to disable RaiseError in this one line like this:
my $sth = $dbh-prepare(LISTFIELDS $table, { RaiseError = 0,
PrintError = 0 });
because, well, the DBI docs list these attributes under ATTRIBUTES
COMMON TO ALL HANDLES -- even though the synopsis for prepare() didn't
mention it. A futile attempt: it didn't work.
Question: why not? Attempting one statement that you know might fail
looks like a reasonable viewpoint.
Yes I know I might use eval for my particular case, but that won't help
for people that have PrintError on.
--
Bart.
Re: Installing dbish causing problems
On Fri, 2 Apr 2004 10:02:36 +1000 (EST), Dennis M. Gray wrote: I installed 11.91 and now get this when trying to bring up ppm: D:\ppm break_at is not exported by the Text::Reform module Can't continue after import errors at C:/Perl/site/lib/Text/Reform.pm line 43 BEGIN failed--compilation aborted at C:/Perl/site/lib/Text/Autoformat.pm line 9. Compilation failed in require at C:\Perl\bin\ppm3-bin line 12. BEGIN failed--compilation aborted at C:\Perl\bin\ppm3-bin line 12. Anyone know what I should do next? Yes. Something reverted the present module Text::Reform, 1.11, back to 1.10. I don't know what, but now ppm will no longer work. So I'll hack my way around it. Locate the Text/Reform.pm file. Make the file readable, open it in your text editor. Check that it is indeed version 1.10. Select everything. Next open the source from Text::Reform 1.10 on CPAN in your browser http://search.cpan.org/src/DCONWAY/Text-Reform-1.11/lib/Text/Reform.pm Select everything, and paste into your text editor, replacing everything that was there. Save the file. Now it's updated to 1.11, and ppm will work again normally. -- Bart.
Re: getting table structure out of MS-Access
On Wed, 24 Mar 2004 22:37:37 -0500 (EST), David N Murray wrote: I thought this worked, but when I test it now, I can't get it to work. It works for recent enough versions of DBD::ODBC. More or less. Except that column_info returns NULLABLE true for columns that can't be NULL, doubles (float) have a COLUMNS_SIZE of 53, which is the number of bits in the mantissa, not the number of digits; also for doubles the number of DECIMAL_DIGITS is undefined... (using DBI 1.37, DBD::ODBC 1.06, and Access '97) At least it gets the data type for the columns right, that and the length for VARCHAR fields. It's a start... (meager, but a start anyway) -- Bart.
getting table structure out of MS-Access
I'd like to automate exporting data out of an existing Access database, using DBI/DBD::ODBC, I guess. I need a proper description of each column in a table, in order to reconstruct a complete CREATE TABLE statement. My guess is that the func() method in DBD::ODBC could help. I've experimented a little with $sth-func($col, 'DescribeCol') but the results don't mean too much to me. I'm stuck. Help? -- Bart.
Re: getting rid of the Issuing rollback() warning
On Thu, 12 Feb 2004 10:55:56 +, Tim Bunce wrote: All I'm proposing to change is to add a way for drivers to indicate if they are in a transaction or not, and then to use that to disable the warning. That reminds me... I haven't used it in years, but I recall that last time I used DBD::ODBC combined with MS-SQL Server, in non-autocommit mode, that I had to do a rollback() after a simple SELECT statement not an update, before a disconnect, or it would complain. It looked like it did quite keep proper track on whether it was inside a transaction or not. Perhaps that has been fixed years ago. -- Bart.
Re: getting rid of the Issuing rollback() warning
On Tue, 10 Feb 2004 18:10:42 -0500, Andrew Pimlott wrote:
Is there any way to prevent the following from warning Issuing
rollback() for database handle being DESTROY'd without explicit
disconnect().?
use DBI;
my $dbh = DBI-connect('dbi:Pg:dbname=...', undef, undef,
{ RaiseError = 1, AutoCommit = 0 });
$dbh-commit();
You can do an explicit disconnect().
In many cases, you can just do that without thinking about it, by doing
it like this:
END {
$dbh-disconnect if $dbh;
}
which also does its job in case of a die().
Without testing, I doubt that this will work satisfactorily in mod_perl
and similar setups.
--
Bart.
Re: old database newbie --bad data in foxpro file?
On Thu, 13 Nov 2003 01:07:45 -0500, Michael Higgins wrote:
map { $total += $_=~m#[\r\f]+#g } @data;
This won't do what I think you want it to do. m//g in scalar context
will match once or not a t all, but never mutliple times, even if it
could match more than once. I think dropping the /g wouldn't affect the
result, in this case.
This might produce better results ($_ =~ is superfluous):
map { $total += () = m#[\r\f]+#g } @data;
Apart from the +, which confuses me a little, you also could try out
the tr/// operator:
map { $total += tr#\r\f##d } @data;
--
Bart.
Re: DBD module
On Fri, 7 Nov 2003 03:17:45 -0800 (PST), Michael A Chase wrote: You can usually find the version of a module with something like: perl -MDBD::xyz -e print $DBD::xyz::VERSION # UNIX perl -MDBD::xyz -e 'print $DBD::xyz::VERSION' # MSWin Reverse that. Doiuble quotes for windows, single quotes for Unix. This is indeed the standard way, that should work for any CPAN-standard compliant module (having a package variable $VERSION is pretty much mandatory) And it's best to add the -l switch, it'll append a newline. Linux etc: perl -MDBD::mysql -le 'print $DBD::mysql::VERSION' Windows: perl -MDBD::mysql -le print $DBD::mysql::VERSION -- Bart.
Re: DBD module
On Fri, 7 Nov 2003 09:48:12 -0500, Hardy Merrill wrote: But this doesn't work - just comes back to the command prompt without printing anything: perl -MDBD::mysql -e print $DBD::mysql::VERSION Just enable warnings, and you'll see why it doesn't work. perl -MDBD::mysql -lwe print $DBD::mysql::VERSION -- Bart.
Re: Better way to get column names with values?
On Tue, 23 Sep 2003 09:37:42 +1000, Fox, Michael wrote:
If you are not worried about the order in which the columns come back, you
could select straight into a hash and save a few lines of code
Or blend the two aproaches, use $sth-{NAME} to get an array of names in
the proper order, and use a hash to get the records.
my $names = $sth-name;
while(my $r = $sth-fetchrow_hashref) {
foreach my $name (@$names) {
print $name is $r-{$name}\n;
}
}
--
Bart.
Re: What to do with UTF-8 data?
On Thu, 11 Sep 2003 12:31:52 +0100, Steve Hay wrote: It would be cool if something akin to binmode STDOUT, ':utf8'; could be applied when sending data to the driver -- i.e. my data is in Perl's internal format, whether that be Latin-1 or UTF-8 in the case of the string at hand, and it all gets automagically upgraded to UTF-8 if necessary before insertion into the database. Oh that's easy to achieve. Just concatenate the string with an UTF-8 string, and you'll get an UTF-8 string. Perl will do the upgrading for you. Just try it: $zero_length_utf8 = pack U0; # UTF8, length ==0 $string = élève; # Latin-1 $string .= $zero_length_utf8; # upgrade to UTF8 print $string; Now the reverse is much harder... :) -- Bart.
Re: What to do with UTF-8 data?
On Wed, 10 Sep 2003 08:33:03 +0100, Steve Hay wrote: And when I retrieve the data (again without conversion) I just get octet sequences into my Perl scalars - not flagged, UTF-8 character strings as I would like. If you're *sure* that this is UTF-8, only perl doesn't flag it as such, you can set the flag yourself. In perl 5.8.x, you can use the Encode module, one of the functions documented near the bottom, here the _utf8_on(STRING). That's an inplace modifying function, so you use it like _utf8_on($string_that_should_be_utf8); For perl 5.6.x (and likely for 5.8 too), you can achieve the same effect by using pack() this way: $flagged_as_utf8 = pack U0a*, $string_that_should_be_utf8; Earlier perls than 5.6 don't have this UTF8 flag, nor do they accept the U template. -- Bart.
Re: What to do with UTF-8 data?
On Wed, 10 Sep 2003 10:40:29 +0100, Steve Hay wrote: But the question was: How can I arrange for such conversions to be performed automatically by DBI whenever it receives or returns data? Well, there are two options... either does the dtabase somewhere stores the flag indicating that some string is in UTF8, or you have to add that information yourself. For the latter, I don't know if it'll actually work, but it seems like an appropriate way to do it: add a BOM marker at the start of the string. http://www.unicode.org/unicode/faq/utf_bom.html#22 (and below) -- Bart.
Re: MySQL LIMIT statement
On 29 Jul 2003 19:08:23 +0100, Dan Rowles wrote: To go through and change all of these methods to do a $sth-bind_param(n, $val, SQL_INTEGER) is a lot of work! Er, you don't have to. It's the first occurrence of using a value for some placeholder that defines how it'll be used further on. So bind a parameter to a type first, and you can go on using the rest of the code as before. Even $sth-bind_param(3, undef, SQL_INTEGER); will do -- which is a NULL, but a field of type integer. -- Bart.
Re: MySQL LIMIT statement
On 29 Jul 2003 19:08:23 +0100, Dan Rowles wrote: 2) Does anyone know if there's a way of getting the DBI to revert to it's old behaviour of not automatically quoting placeholder-values? So MySQL cares now, does it? I'm still on MySQL 3.x, and I know that there you may quote any type of field, so 123 for an inetegr is alright. Good to know if ever we upgrade. Ouch. -- Bart.
Re: Make dbi socket.h errors....
On Mon, 28 Jul 2003 10:37:08 +0200, [EMAIL PROTECTED] wrote: anybody can help me whats the problem of dbi installation on my hpux workstation with HPUX 11.11??? Could it be that perl is compiled with ansi/c and the dbi uses gcc? If you're using a perl that was preinstalled on your system, it can indeed be that you're trying to use a different compiler to install XS modules, yes. Try to find out what compiler they used, and use the same yourself, or recompile all of perl. Which is, like, ugh. -- Bart.
Re: RFC: SQL Extensions for SQL::Statement [Long]
On Thu, 5 Jun 2003 10:50:45 +0100, Tim Bunce wrote: [quoting from the root message:] SELECT $cols FROM tbl1@$dsn1 ... JOIN tbl2@$dsn2 ... I think we're very much in the realms of inventing our own syntax here. We're just using 'standard sql' as a guide for everyones sanity. FWIW, MySQL allows the syntax SELECT * FROM database.table where table is the name of a table in the database with name database. I'm not sure how standard that is :) Both databases, the one you connect to, and the one mentioned here, must (?) reside on the same MySQL server, though. It's the only way I expect it to behave sanely, anyway. -- Bart.
Re: RFC: SQL Extensions for SQL::Statement [Long]
On Wed, 04 Jun 2003 09:15:28 -0700, Jeff Zucker wrote: For per-table connections - CONNECT TO '$dsn1' AS tbl1 CONNECT TO '$dsn2' AS tbl2 Euh... you connect to a database, not to a table, no? -- Bart.
Re: DBI Newbie
On Mon, 24 Mar 2003 05:45:59 -0800, Barlow, Neil wrote: That's is correct - I am attempting to connect a MS SQL7 DB that is not located on the Webserver. You could start by using Windows' ODBC Data Sources control panel to create a (system) DSN. Next, all you have to do is use the DSN name in your connect string. -- Bart.
Re: Strange security problem...
On Fri, 28 Feb 2003 20:21:29 -0500, Cory Rau wrote: I purposely mistyped the url with 'cgi-bin' rather than what it actually is, 'CGI-BIN' just to see what would happen. I ended up getting the entire *code* of the perl script in my web browser This is OT for perl-DBI, but... I think that your cgi-bin directory must be lying inside your normal webspace. You should move it outside, so the only way to reach it is via that ScriptAlias. -- Bart.
Re: How do I respond to a thread?
On Sat, 01 Mar 2003 12:02:15 -0800, G S wrote: I have been posting messages here by emailing to [EMAIL PROTECTED] Is there a way to post messages so that they are part of an ongoing thread? Select reply to respond. Make sure [EMAIL PROTECTED] is amongst the recipients. That's it, basically. -- Bart.
Re: How to Connect MSSQL Server from a Linux machine using Perl
On 8 Feb 2003 11:58:56 -, Jeyaraj wrote: I am new to this environment I don't know how to establish connection to SQL Server from Linux machine using Perl Pls help me in this issue It's a FAQ: http://tlowery.hypermart.net/perl_dbi_dbd_faq.html See questions 4 through 7. -- Bart.
Re: platform independence and ISNULL/nolock with MsSQL?
On Sun, 2 Feb 2003 13:20:38 +0100, alex wrote: i ask myself how to prog my select statments platform independend if i need ISNULL function on varchar fields in MsSQL2K... this will not work on a move to MySQL and so on... I don't know for sure about MS-SQL, but this works in MySQL: SELECT * FROM table WHERE field IS NULL or SELECT * FROM table WHERE field IS NOT NULL Here, the SQL keywords are in uppercase, table/field names in lower case. I think I have used this syntax in MS-SQL too. -- Bart.
Re: DBI/ODBC for windows
On Wed, 15 Jan 2003 11:20:54 -0500, Jeff Thies wrote: Shared Hosting win2000 server, with an unresponsive tech staff. I understand that there is a pure perl version of DBI. Is there a way to install DBD::ODBC just having FTP? The last win server I was able to PPM install. Yes. Install it on your local machine, make an archive of all files that got installed, upload it, and copy the contents of the archive to a LIB directory that can be seen from your script, in @INC. Are you sure you do have database access? Installing DBI and a DBD driver without database access is pretty useless. -- Bart.
What's with xmlproj.com?
I tried to check out some of the latest modules for Windows, using the URL http://xmlproj.com/PPM/. (I tried www.xmlproj.com, too.) After some time, I get a DNS lookup error. It looks like this domain has expired. If so, can anyone tell where the modules that were available there, have moved to? I'm mainly interested in DBD::mysql. -- Bart.
Re: What's with xmlproj.com?
On Wed, 15 Jan 2003 11:08:24 -0700, Sterin, Ilya wrote: Matt Sergeant is hosting xmlproj.com on his AxKit server. That server is down as well, maybe he's upgrading or something, I'll ping him in a bit. Ah, OK, now I see. See Matts' journal on http://use.perl.org. From what I gather, his own server is normally connected to the internet via DSL, and his DSL modem (router?) is broken. http://use.perl.org/~Matts/journal -- Bart.
Re: selecting a range of records from my result set
On Mon, 06 Jan 2003 09:59:41 -0700, Ian Harisay wrote: In mysql is it possible to retrieve records 50-75 from your result set? Yes. Add a LIMIT clause to your SELECT statement. SELECT * FROM table LIMIT 50,25 It works well from within DBI (but don't append the semi-colon at the end of the statement). The second number is a row count. The first number is the number of rows to skip. So this will retrieve rows 51-75. http://www.mysql.com/doc/en/SELECT.html -- Bart.
Re: but it is not intended for this build of Perl (MSWin32-x86-multi-thread) error
On Thu, 2 Jan 2003 17:09:56 +0800, pevee wrote: Now, I got the but it is not intended for this build of Perl (MSWin32-x86-multi-thread) I am using ActivePerl-5.6.633-MSWin32-x86.msi and DBI from ActivePerl 5xx (e.g. ActivePerl522) ActivePerl 5.005 and 5.6.x are not biunary compatible. The PPM packages for the platforms can be found under: 5.005: http://www.activestate.com/PPMpackages/5.005/ 5.6: http://www.activestate.com/PPMpackages/5.6/ http://www.activestate.com/PPMpackages/5.6plus/ 5.8: http://www.activestate.com/PPMpackages/5.8-windows/ DBI can also be found on http://xmlproj.com/PPM/, I assume that's for 5.6.x... but it's DBI version 1.30, while the as the 5.6plus archive contains version 1.32, so I don't think that's currently the best option. -- Bart.
Re: Error installing package 'DBD-Proxy': Could not locate a PPD file for package DB
On Tue, 31 Dec 2002 14:52:41 +0800, pevee wrote: I am lookign for Comm.pm module, anyone knows where I can find it? DBI-1.32]# Can't locate RPC/PlServer/Comm.pm in @INC (@INC contains: You must interpret the error message correctly. It's not Comm, but RPC::PIserver::Comm you're looking for. http://search.cpan.org/author/JWIED/PlRPC-0.2016/ -- Bart.
Re: Invalid Precision error
On Mon, 30 Dec 2002 00:12:40 -0500, Jeff Thies wrote: I'm getting a weird error when I try to update a text field (Access, latest ODBC and DBI) sometimes: This is a placeholder problem. Changing the script so the affected field is not a placeholder eliminates the problem. ie: set text_field=? fails *sometimes* while set text_field='$text_field' does not. Where might the problem be? In that case, it sounds like an incorrect guess of the type of variable. Try something like use DBI ':sql_types'; # to import the constants ... $sth = $dbh-prepare($sql); $sth-bind_param(1, undef, SQL_VARCHAR); $sth-execute($value); i.e. inserting an appropriate bind_param call between the prepare and the execute. where you may try one of several constants instead of the SQL_VARCHAR. See the section on DBI Constants in the DBI docs. -- Bart.
Re: Nested prepares
On Wed, 18 Dec 2002 09:49:28 -0500, John Day wrote: I would like to rewrite an application using DBI, DBD and mySQL so that I can prepare a several statements once using placeholders and then execute them within a loop without re-preparing them. I just don't get why you call these nested. They're not nested, they're in parallel. And yes, in general, it works. -- Bart.
Re: DBD::SQLite win32 binary
On Fri, 13 Dec 2002 22:36:31 -0800, Bill Kurland wrote: Does anyone know of a source for a win32 binary of DBD::SQLite? Any leads are much appreciated. A source? Do you mean the source code, or a location whjere you can download a binary from? For the latter, version 0.17 (while the version on CPAN has moved on a few notches) is available from http://www.activestate.com/PPMpackages/5.6plus/DBD-SQLite.ppd http://www.activestate.com/PPMpackages/5.6plus/MSWin32-x86-multi-thread/DBD-SQLite.tar.gz But it tends to crash while doing very basic DBI stuff with it. Even a simple DBI-connect may cause it. And even if it doesn't crash, I sometimes get a dbih_setup_fbav error for no good reason. -- Bart.
Re: SQL_LONVARBINARY question
On Fri, 06 Dec 2002 06:50:57 -0500, Brad Smith wrote: I was just looking at the 'longbin.pl' test script that is bundled with DBD::ODBC, which has raised a few questions: Hmm... I don't seem to have it, but I could find it on CPAN. http://search.cpan.org/src/JURL/DBD-ODBC-0.45_18/mytest/ 1. To place a blob in an Access database, as per the example script, is it necessary to use the Digest::MD5 module, or is it's use an optional step that is ultimately suggested? Heh?!? Don't be silly. It's not necessary at all. Digest::MD5 is module to create some kind of checksum, much like CRC but bigger (128 bit or 16 bytes, often displayed in hex (32 characters) or base64 (Jee, I don't know... 22 or so. time 4/3 and round up)) It's one way to check if all bytes in a large binary string of data is intact. Any bit that changes will result in at several bits of the checksum flipping. All small corruptions will be detected. If your data has the same length and the same checksum as calculated earlier, you may be virtually sure that your data is still intact. 2. Unlike a text field or an integer field (etc.), a blob field has both a file name and file data. When reading the data, as per the example, I see that the SELECT statement gets just 'picture'. At what point does it parse the file name from the file data? A blob is a binary string. It will only contain the contents of the file. If you want to store other features, like the file name, size, MD5 checksum, modification date, ... then you need to provide more fields for those. It's that simple. Having not ever used the Digest::MD5 module, I suspect that it is what handles all of this, but I just wanted qualified verification before I dove in and got too deep. It does nothing of the kind. -- Bart.
Re: Subtle binding bug
On Thu, 28 Nov 2002 11:29:04 +, Tony Bowden wrote:
I don't know how MySQL handles placeholders, so this is just a guess. It
may simply be interpolating the string 'nine' into the query string, in the
same way it interpolated the number 9, that is, without qotes:
INSERT INTO user (username, password)
VALUES ('bar', nine)
Goot catch. This is almost certainly what's happening.
I hadn't looked at this thread until now, but now that I do, I do
recognize the pattern. I've had the same kind of problems with Access.
What I feel that must be happening, is that the character type for a
placeholder is determined on how the data looks, the very first time it
is invoked. If at that time, it's called with nine, it is determined
as being a string, so for the rest of the lifetime of this statement
handle, parameters will be quoted. But if it looks like 9, it is
considered as a number, and it won't be quoted. Not even if you invoke
it with a string. (My bet is that if in the latter case you provide the
string null, it will be treated as NULL by the database.)
What can fix this, is having an explicit bind on the variable type,
after the pepare phase, but before the first time you execute this
statement. It certainly did help in Access. My code (adapted) looked
like this:
use DBI ':sql_types'; # to import the constants
$sth-bind_param(2, undef, SQL_VARCHAR);
(untested)
The 2 is the index of the placeholder. The undef is the actual value,
but that exact value doesn't matter much. You override it later, anyway.
(undef produces a NULL, which normally is safe)
p.s. Actually the problem I had with Access was the reverse: if the
first execute call was with undef, the placeholder got treated as a
string. The field itself was a nullable integer. If later, it got called
with a number, Access barfed because it got quoted, and Access doesn't
treat 9 and '9' as the same -- unlike MySQL, for example.
So my fix was:
$sth-bind_param(1, undef, SQL_INTEGER);
(which is a paste from my original code, so this is tested :-)
HTH,
Bart.
Re: Table Names
On Wed, 27 Nov 2002 08:07:39 -0500, Chris Rogers wrote: Thanks to all. I never imagined that I could simply issue standard sql statements. I prepared and executed DESCRIBE tablename with a fetchrow_hashref. It returned a nice hash reference containing all the information I see when I execute the same statement in mysql. Again thanks to all. And somebody wrapped it in a module, called MySQL::TableInfo. It's on CPAN. -- Bart.
Re: execute and null values in quote
On Thu, 21 Nov 2002 15:12:47 -0800, Daniel Olson wrote:
$sth = $dbh-prepare(insert into ul_info
(ul_id,ul_len,ul_wid,ul_dep,ul_tun,ul_sin,ul_und,ul_und_fr,ul_und_to,ul_edge,ul_stat,ul_skin,ul_drain_typ,ul_drain_amt,ul_drain_col,ul_drain_od,ul_stage,timestamp,clinician)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,''));
$sth-execute($UlID,$formdata-param('Len'),$formdata-param('Wid'),$formdata-param('Dep'),$formdata-param('Tun'),$formdata-param('Sin'),$formdata-param('Und'),$formdata-param('UStart'),$formdata-param('UEnd'),$formdata-param('DEdge'),$formdata-param('DEval'),$formdata-param('DSkin'),$formdata-param('DType'),$formdata-param('DAmt'),$formdata-param('DColor'),$formdata-param('DOdor'),$newstage)
|| die Can't perform execute: $DBI::errstr;
If I run this from the command line and only define three variables, it fails
with the error:
DBD::Oracle::st execute failed: called with 5 bind variables when 17 are needed at
script.cgi line 201.
You shouldn't just drop undefined values. Pass undef instead. DBI will
insert NULL for those values.
--
Bart.
Re: execute fails in loops only
On Mon, 18 Nov 2002 12:44:44 -0500 (EST), Paul Boutros wrote:
Now, that sub function, process($$$) checks the input parameters and
chooses the correct statement-handle based on that. For instance:
if ($_[0] == 532) {
if ($_[1] eq 'A') {
$sth = $sth_crosstab_all;
}
else {
$sth = $sth_crosstab;
}
}
The sub then goes ahead and does gets the data from $sth.
Just an aside: you could use a hash containing the statement handles.
For example:
$sth{532}{A} = $sth_crosstab_all;
Since you seem to want a fallback, you could do:
$sth{532}{'*'} = $sth_crosstab;
Thus:
my $sth = $sth{0+$_[0]}{$_[1]} || $sth{0+$_[0]}{'*'};
I should mention here that the queries being run are all crosstab queries
(MS-Access queries that aggregate data by two variables, much like a pivot
table in Excel).
The strange part is this:
- all queries work individually
- if I set the program running, it works for one full loop and then dies
midway through the second loop
The error message is:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Operation is not supported for this type of object. (SQL-S1000)(DBD:
st_execute/SQLExecute err=-1) at get_all_mm_data.pl line 204.
And to further add to my confusion, lines 204-206 are:
if ($_[1] eq 'T') {
$sth-execute($_[2], 0);
}
Could it be that Access doesn't like two such queries running at the
same time? Thus, perhaps you should do
$sth-finish;
at the end of your loop.
--
Bart.
Re: execute fails in loops only
On Mon, 18 Nov 2002 11:11:47 -0800 (PST), Michael A Chase wrote: I'm not sure about Access, but some underlying databases don't like multiple statements open at the same time on a single database handle. Just for testing, try opening separate database connections for each statement and see if that helps. # For example (untested): $sth_a = $dbh_a - prepare( $sql_a ); $sth_b = $dbh_b - prepare( $sql_b ); If that helps, I would cache the SQL strings, not the statement handles, and use just one $dbh. -- Bart.
Re: How to use DBI to connect to MS Access?
On Thu, 14 Nov 2002 15:51:07 -0500, Changhong Tang wrote: But I get this error: install_driver(ODBC) failed: Can't locate DBD/ODBC.pm in INC(INC You don't have DBD::ODBC installed. How do I tell if DBD::ODBC perl module is fully installed? I do have these files: c:/Perl/lib/Win32/Odbc.pm c:/Perl/site/lib/Win32/ODBC.pm That's Win32::ODBC, which is not the same as DBD::ODBC. You should be looking for filenames like .../DBD/ODBC.pm BTW IIRC this is an XS module, parts in compiled C. Hence, a DLL. My Perl version is 5.005_3 for MSWin32-x86, Activestate (or compatible)? Find the modules for your Perl version under http://activestate.com/PPMpackages/5.005/. The PPD file is from March 2000, so you won't get the most recent version either. -- Bart.
Re: Checking userid before inserting into database
On Thu, 7 Nov 2002 15:07:09 -0600 , Nguyen, David M wrote: I am writting a script using DBI::Oracle to insert user account into database, before inserting I need to verify to make sure userid is not already existed. I have script written, ran it but nothing seems happen. Can someone look into my script and provide me some correction? I'm sorry, I haven't fully studied your script, which seems to contain far more code for CGI than for DBI, but I'll give one quick tip anyway: create an index in your table on your userid field, so that it must be unique. That way, the database will complain and refuse to make a new record if a row for this userid already existed. When that happens, you can still fallback on updating the existing record. -- Bart.
Re: timeout, disconnect on DBD Oracle 9i causes ORA internal error
On Wed, 23 Oct 2002 16:54:25 +1000, Mike McCauley wrote: # Run this for a few secs, then unplug from the network. # the execute times out,tries # to disconnect, get internal error If you pull the plug, the disconnect() cannot possibly work either. -- Bart.
Re: relationship of dbi dsn to uri?
On Mon, 21 Oct 2002 10:02:44 +0100, Tim Bunce wrote: Anyone interested in following the procedures to get a dbi: scheme registered? (I don't have the time, though I'd be very happy to see it done.) My question is: why? What good would it do to us? I just can't imagine anything. -- Bart.
Re: existance of a select statement
On Thu, 26 Sep 2002 21:46:16 +0100, Tim Bunce wrote: If that may happen then it's safer to try the insert first and if that fails due to a duplicate key then do the update. At least MS Access has the problem that an autoincrement field (or whatever it is called in Access) IS incremented even if the insert fails. That means, with a 32 bit counter, that sooner or later you might get in trouble. Well, if you try one update per second, you have 68 (signed, or 136, with unsigned integers) years or so before it will overflow, so maybe it's not *that* bad. But it rubs me the wrong way. It's a time bomb. If you try 50 inserts per second, you only have a few years before it happens. -- Bart.
Re: How to execute file containing sql statements using dbi.
On Thu, 19 Sep 2002 15:10:50 -0700 (PDT), Arvind Raina wrote: My requirement is to execute a set of sql statements which are stored in a file . I wanted to know if there is any method of doing the same using dbi . If not can you suggest some methods of doing the same. What database? If it's mysql, the easiest way would be to do mysql mydb myfile from the command line (likely with command line options -u for username and -p for password). I don't understand why you'd need the involvement of DBI, at all. -- Bart.
Re: doubt
On Mon, 2 Sep 2002 20:32:28 +0530 , Agarwal, Ramakant wrote:
I want that if someone kills the process while the script is in
execution then it should log off from the database and then should exit.
Does the log off from the db happen automatically whenever a process is
killed or we do need to explicitly handle this?
I suspect the latter. You can do that by doing
END {
$dbh-disconnect if $dbh;
}
where $dbh is the DBI database handle. A code block like this will
always be executed at the end of the program run, even if it happens by
a gentle signal, the equivalent of ctrl-C.
--
Bart.
Re: dbi perl
On Fri, 30 Aug 2002 15:44:54 -0700 (MST), Michele Francis wrote: Is there a good dbi with perl book? Thanks. Can one somehow miss the plug on the DBI's official homepage? http://dbi.perl.org Yes it's a good book. In a way, it's the docs that come with DBI, plus some; but yet, it offers a gentler introduction to DBI than the docs alone do. -- Bart.
Re: Installing DBI without shell or PPM
On Wed, 21 Aug 2002 15:13:49 -0700, Jim Clark wrote: I know this isn't the best way to move modules between machines but my options are limited. Can anyone help point me to what files I may be missing or what else I may need to do to update the web server to use these files? Check for the DLL files under autoload, which is a directory with a structure similar to lib and site/lib. -- Bart.
Re: FYI, I'll be away from dbi-users et. al. for a while
On Wed, 21 Aug 2002 01:57:11 +0100, Tim Bunce wrote: I will be vanishing off the face of the internet for almost three weeks. The silence will be golden (well, for me at any rate :) and much of the backlog when I get back is likely to discarded without ceremony. Sshhh! Too late! Now you've warned them. You'll never get such a huge amount of questions in your personal mailbox as these next three weeks, all begging for your personal attention. :-) -- Bart.
Re: Source of current DBI
On Mon, 19 Aug 2002 12:50:40 -0400, Gary Gauthier wrote: Where can I download the most recent DBI source? I'm back from vacation and still rying to track down the memory leak issue. CPAN would be my first place to look. Second place is the DBI homepage, http://dbi.perl.org. Hmm... nothing there. CPAN, then: http://www.cpan.org/modules/by-module/DBI/. Latest version is 1.30, of July 18th 2002. -- Bart.
Re: zero-length string
On Tue, 30 Jul 2002 15:16:18 -0400, Nick Hoffman [UWO] wrote: I'm trying to insert into a table where only 3 fields (1 being the primary key) are required, yet I get the following error when I execute: --- Died while executing: [Microsoft][ODBC Microsoft Access Driver] Field 'Volunteers.Email' cannot be a zero-length string. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1) --- However, when I look at the design view of the table Volunteers (Access2000 DB), the Email field is not required. To Access, there is a difference between a zero length string, and a NULL. The latter should be acceptable, according to what you say, while the former isn't. So: if you find a zero length string, replace it with undef. that should be enough. -- Bart.
