Re: SQL Parser...
Hi, Addi Jamshidi wrote: I am looking for a way to parse a given SQL statement and as output to received the parsed tree so I can in turn present that as XML. Basically given a SQL statement I would like to parse it and get its parsed tree in terms of XML. Others have asked about this. I'm cc'ing the [EMAIL PROTECTED] list in case someone's already made such a module. Does your SQL Parser make it possible for me to do this? With limitations, yes. It only does a small subset of SQL, but what it does it can return as hash that could easily be used to generate XML. Also is your SQL Parser flexible to allow adding to the grammer to for example support DB2 SQL, etc., etc.? It should be. Where it isn't, I'm certainly willing to make it so. One point: I need this parser to run under MS .NET framework (i.e. no Java virtual machine). You're out of my depth there, I don't know much about .NET or Java. You do know my module is a Perl module? But I hear .NET does Perl so maybe you can get it work under .NET. Since SQL::Parser is pure perl (no C code) it should be one of the easier ones to get working under .NET. If you do that, please let me know. Thank you so much in advance for any info you can provide (including licensing, etc.). It's licensed under either the GPL or the Artistic License (which is open source but allows use in proprietary products under most circumstances). -- Jeff
Re: Migrating from ? to Oracle
Hi Wiliam, You will regret it if you take that path. See if you can't (A) use one of the free development ones:- http://otn.oracle.com/software/products/oracle9i/index.html or (B) get your test machine to connect to the real Oracle (using a test schema or database maybe). Your main problems will be with dates and date calcs - but the worst part will be you and your development people missing out on the Oracle skills they're going to accumulate during development - you know - those intangible things like "why did it take 10 times longer when I did it that way..." kinda stuff. Chris Friday, February 6, 2004, 4:25:44 AM, you wrote: WS> Hi WS> I'm going to be working on a Perl app using a database through the DBD WS> set of drivers. The end production machine will run Oracle for which WS> there is already DBD::Oracle. WS> However, we don't have access to Oracle locally and wish to use a WS> replacement for the bulk of the development process before moving over WS> to the production servers. WS> Because both databases can be accessed using the DBI divers then it WS> would seem logical that the same application, if coded correctly, could WS> be used with a number of databases without changing much, if any, of WS> the code. This is the theory I'm working with. WS> However, I know in reality that small changes to SQL syntax will have WS> to be made. WS> What I'm basically asking is, what is the best database to use as a WS> development database before migrating over to Oracle? Is MySQL the most WS> similar in terms of syntax? Or can I stick with my preferred choice of WS> Postgresql? Are there any big gotchas to watch out for switching WS> between both databases or if I stick to fairly standard SQL syntax and WS> functions I should be OK. If there is a list of differences between the WS> functions of the various DBI drivers that would be great. WS> Thanks for your help. WS> Wil WS> -- WS> Wiliam Stephens WS> FBA MEDIA WS> Tel: 01970 636412 WS> Mob: 07968 325637 WS> http://www.fbagroup.co.uk/
Re: can you help
Bryson, Stewart wrote: I appreciate your help Jeff. The pragma you specified does not correct the issue I'm getting the following error when I use your DBD::AnyData module: Wide character in print at /usr/local/lib/perl5/5.8.0/sun4-solaris/IO/Handle.pm Hmm, I'm guessing it's an issue with perl 5.8.0. Did you try with just "no warnings" as well as with "no warnings 'utf8'"? -- Jeff
Re: Building 32bit DBD-Oracle under 64bit Oracle install
On Thu, Feb 05, 2004 at 09:14:13AM -0800, Lin Feng wrote: > Sean, > > I just tried it. It made no difference in my case: the Makefile generated > are exactly the same. > > Env: Solaris 2.8; Oracle 10.1.0.2.0; Perl 5.8.3 > > BTW, 'perl Makefile.PL' reports that it cannot determine correct Oracle version. > I guess it does not know what to do with the two gidit in '10'. > > Any one else seen this? You're the first to mention Oracle 10. Please fix it and send me a patch (diff -u Makefile.PL.old Makefile.PL) Thanks! Tim.
Re: Building 32bit DBD-Oracle under 64bit Oracle install
On Thu, Feb 05, 2004 at 04:21:40PM +, Sean Kelly wrote: > Hi, > > I think I've cracked it. The Makefile.PL in 1.15 is much better than the > one from 1.14 at detecting whether you need the lib32 Oracle directory from > a 64bit Oracle install but I think there is one flaw. > > On line 74 $::opt_r is set to a default value of "build". This value is > causing $build_target to always be set to "build" at line 343 no matter > what the logic on the two preceeding lines decide. > > If I set $::opt_r = '' on line 74 then running "perl Makefile.PL" chooses > the lib32 directory correctly and "make" runs cleanly. Uggh. I changed the logic around a couple of times and obviously left messed up. Well spotted. Fixed now. Thanks. Anyone in a 64bit or mixed 32/64bit environment should try it. Tim.
Re: can you help
Bryson, Stewart wrote:
I'm getting the following error when I use your DBD::AnyData module:
Wide character in print at
/usr/local/lib/perl5/5.8.0/sun4-solaris/IO/Handle.pm
line 439 (#1)
(W utf8) Perl met a wide character (>255) when it wasn't expecting
one.
That's not an error, it's a warning - meaning that it's purely advisory
and doesn't necessarily impact the script. It's a warning from Perl,
not from DBI or the DBD.
This warning is by default on for I/O (like print) but can be
turned off by no warnings 'utf8';
In other words, put this at the top of your script to eliminate the
display of the warning:
no warnings utf8;
AFAIK, DBD::AnyData doesn't care whether or not the information is in utf8.
Below is the code I'm executing:
$ad_dbh->func(
'lead_detail',
'DBI',
$orac_dbh,
{sql=>$sql},
'ad_import');
$ad_dbh->func(
'lead_detail',
'CSV',
'/tmp/CustomLeadService_filtered_leads.csv',
'ad_export');
The $orac_dbh is an oracle DBI handle. This module works great, but it
looks like I need to somehow prepare a handle for an extra wide
character. Would you know where in your code to start looking?
I think the "no warnings" statement should fix it, but do let me know if
I'm wrong. I'm cc'ing the [EMAIL PROTECTED] listserv in case someone
with Unicode tuits has something to add.
Stewart W. Bryson | Database Architect
KnowledgeStorm, Inc.
[EMAIL PROTECTED]
--
Jeff
Migrating from ? to Oracle
Hi I'm going to be working on a Perl app using a database through the DBD set of drivers. The end production machine will run Oracle for which there is already DBD::Oracle. However, we don't have access to Oracle locally and wish to use a replacement for the bulk of the development process before moving over to the production servers. Because both databases can be accessed using the DBI divers then it would seem logical that the same application, if coded correctly, could be used with a number of databases without changing much, if any, of the code. This is the theory I'm working with. However, I know in reality that small changes to SQL syntax will have to be made. What I'm basically asking is, what is the best database to use as a development database before migrating over to Oracle? Is MySQL the most similar in terms of syntax? Or can I stick with my preferred choice of Postgresql? Are there any big gotchas to watch out for switching between both databases or if I stick to fairly standard SQL syntax and functions I should be OK. If there is a list of differences between the functions of the various DBI drivers that would be great. Thanks for your help. Wil -- Wiliam Stephens FBA MEDIA Tel: 01970 636412 Mob: 07968 325637 http://www.fbagroup.co.uk/
Re: Building 32bit DBD-Oracle under 64bit Oracle install
Sean, I just tried it. It made no difference in my case: the Makefile generated are exactly the same. Env: Solaris 2.8; Oracle 10.1.0.2.0; Perl 5.8.3 BTW, 'perl Makefile.PL' reports that it cannot determine correct Oracle version. I guess it does not know what to do with the two gidit in '10'. Any one else seen this? Thanks, Lin Sean Kelly <[EMAIL PROTECTED]> wrote: Hi, I think I've cracked it. The Makefile.PL in 1.15 is much better than the one from 1.14 at detecting whether you need the lib32 Oracle directory from a 64bit Oracle install but I think there is one flaw. On line 74 $::opt_r is set to a default value of "build". This value is causing $build_target to always be set to "build" at line 343 no matter what the logic on the two preceeding lines decide. If I set $::opt_r = '' on line 74 then running "perl Makefile.PL" chooses the lib32 directory correctly and "make" runs cleanly. Thanks, -- Sean Kelly - Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online
Building 32bit DBD-Oracle under 64bit Oracle install
Hi, I think I've cracked it. The Makefile.PL in 1.15 is much better than the one from 1.14 at detecting whether you need the lib32 Oracle directory from a 64bit Oracle install but I think there is one flaw. On line 74 $::opt_r is set to a default value of "build". This value is causing $build_target to always be set to "build" at line 343 no matter what the logic on the two preceeding lines decide. If I set $::opt_r = '' on line 74 then running "perl Makefile.PL" chooses the lib32 directory correctly and "make" runs cleanly. Thanks, -- Sean Kelly <[EMAIL PROTECTED]>
Re: Named placeholders in DBD::Oracle - bug report
On Thu, Feb 05, 2004 at 10:50:46AM +, Charles Jardine wrote: > > I have just downloaded DBD::Oracle 1.15. I discover that the > patch below, which was intended to fix this problem, is not > included in the new release. I am sorry that I did not find > the time to check this at the release-candidate stage. > > Is there a reason why the patch was not included? Have I made > some kind of mistake? Nope. Just an oversight on my part. I'm drowning in assorted problem reports etc and there was only so much I could do for the last release. > If not, would it be useful if I re-worked the patch for > version 1.15. That would be great. Thanks. Tim.
Re[2]: DBI bug - trailling blank LINES truncated from bind data
Hi Mac, I'm not talking about spaces - I'm talking about lines. in perl speak - specifically - put this into a bind variable then into a table:- "foo\r\n\r\n" then when you get it out again, you get this:- "foo" ... and even if I *was* talking about spaces, it's still a bug for the same query to have 2 different effects, depending on whether I append nothing to a string or not :-) Also - I'm guessing that when the data is UTF-8, there'd be times when trimming an "\r" or "\n" or " " could screw up the data big time too? Chris Thursday, February 5, 2004, 9:42:18 PM, you wrote: MAC> On 02/04/2004 09:39 PM, [EMAIL PROTECTED] wrote: >> Executing this:- >> >> UPDATE BLAH SET TRANSLATION=? WHERE ROWID=? >> >> used always to work for me, but after a recent upgrade, I notice that >> trailing blank lines are getting truncated during this update. >> Executing this statement instead, and the problem goes away:- >> >>UPDATE BLAH SET TRANSLATION=? || '' WHERE ROWID=? >> >> I'm using DBI 1.32 afaik (from the "man" page) with the latest oracle >> database on RedHat ES 3.0. >> >> Using the former version of Oracle on RedHat 9, there was no bug (both >> "update" statements perform the same - nothing gets truncated). >> >> Using an update statement without BIND variables works fine on >> everything - eg:- >> >> UPDATE BLAH SET TRANSLATION='line with >> >> trailing blank lines >> >> ' WHERE ROWID='jkasdfkjhdfjk' >> >> I tried a range of different connect strings and other stuff, but >> after 3 hours of experimentation, I've narrowed it down to the above >> info. nothing else I could find/do has any effect on the problem. >> >> I think this is probably a bug (either in oracle, perl, RedHat or >> DBI!) but if anyone knows otherwise, or has any other suggestions for >> me to try (besides adding ||'' all thought my code!) please let me >> know! MAC> The Oracle OCI library trims trailing spaces for VARCHAR bind variables. MAC> It has been that way via DBI for at least a couple years. The MAC> workaround is to call $sth->bind_param( 1, SQL_CHAR ) before the first MAC> execute() call. The bind variable type is remembered for future MAC> execute() calls. MAC> See ora_ph_type in http://search.cpan.org/dist/DBD-Oracle/Oracle.pm as MAC> well.
Re: Named placeholders in DBD::Oracle - bug report
Tim Bunce wrote:
Thanks for the info and the patch!
Tim.
On Fri, Oct 17, 2003 at 02:07:46PM +0100, Charles Jardine wrote:
The problem:
---
When Oracle matches placeholder names passed to OCIBindByName()
with the actual placeholders in the SQL or PL/SQL, it uses
a case-insensitive comparison. This means that Oracle regards
the placeholders :a and :A as the same.
On the other hand, DBD::Oracle uses case-sensitive comparison
when matching placeholder names, and regards :a and :A as
different.
This has at least two undesirable effects.
I have just downloaded DBD::Oracle 1.15. I discover that the
patch below, which was intended to fix this problem, is not
included in the new release. I am sorry that I did not find
the time to check this at the release-candidate stage.
Is there a reason why the patch was not included? Have I made
some kind of mistake?
If not, would it be useful if I re-worked the patch for
version 1.15.
1. If the statement contains two named placeholders which
differ only in letter_case, they will each contribute
to the count in $sth->{NUM_OF_PARAMS}, despite the fact
that this is meant to be a count of _distinct_
placeholders.
2. The name passed to $sth->bind_param... must match in
letter case at least one of the occurences of the
placeholder in the statement. This is not what an
OCI programmer would expect. Many of the examples in
the OCI manual have the names passed to OCIBindByName()
in upper case, and those in the PL/SQL in lower case.
Below is a tiny perl program which demonstrates this.
The proposed solution:
-
I suggest that DBD::Oracle's handling of placeholder name
comparisons should be changed to be case-insensitive.
Below is a patch to dbdimp.c (version 1.14) which achieves
this by lower-casing the name both at preparse time and at
bind time.
#!/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect('dbi:Oracle:', 'foo', 'bah',
{RaiseError=>1, PrintError=>0, AutoCommit=>0});
my $sth = $dbh->prepare(
"SELECT decode(:a, :A, 'same', 'different') from dual");
# The following prints 2, indicating that DBD::Oracle
# thinks that the above has two distinct placeholders
print $sth->{NUM_OF_PARAMS}, "\n";
$sth->bind_param(':a', 'lower');
$sth->bind_param(':A', 'upper');
$sth->execute;
# The following prints 'same', indicating that Oracle
# can't see the difference between the placeholders
print +($sth->fetch)->[0], "\n";
$sth->finish;
$dbh->disconnect;
=
--- dbdimp.c.orig Fri Oct 17 13:08:17 2003
+++ dbdimp.cFri Oct 17 13:08:49 2003
@@ -812,7 +812,7 @@
} else if (isALNUM(*src)) { /* ':foo' */
while(isALNUM(*src))/* includes '_' */
- *dest++ = *src++;
+ *dest++ = toLOWER(*src), src++;
style = ":foo";
} else {/* perhaps ':=' PL/SQL construct */
/* if (src == ':') *dest++ = *src++; XXX? move past '::'? */
@@ -1321,7 +1321,7 @@
SV **phs_svp;
STRLEN name_len;
char *name = Nullch;
-char namebuf[30];
+char namebuf[32]; /* ':' + 30 char name + '\0' */
phs_t *phs;
/* check if placeholder was passed as a number */
@@ -1329,7 +1329,13 @@
if (SvGMAGICAL(ph_namesv)) /* eg if from tainted expression */
mg_get(ph_namesv);
if (!SvNIOKp(ph_namesv)) {
+ int i;
name = SvPV(ph_namesv, name_len);
+ if (name_len>31)
+ croak("Placeholder name too long (%s)", neatsvpv(ph_namesv,0));
+ for (i=0; i
===
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: DBI bug - trailling blank lines truncated from bind data
On 02/04/2004 09:39 PM, [EMAIL PROTECTED] wrote: Executing this:- UPDATE BLAH SET TRANSLATION=? WHERE ROWID=? used always to work for me, but after a recent upgrade, I notice that trailing blank lines are getting truncated during this update. Executing this statement instead, and the problem goes away:- UPDATE BLAH SET TRANSLATION=? || '' WHERE ROWID=? I'm using DBI 1.32 afaik (from the "man" page) with the latest oracle database on RedHat ES 3.0. Using the former version of Oracle on RedHat 9, there was no bug (both "update" statements perform the same - nothing gets truncated). Using an update statement without BIND variables works fine on everything - eg:- UPDATE BLAH SET TRANSLATION='line with trailing blank lines ' WHERE ROWID='jkasdfkjhdfjk' I tried a range of different connect strings and other stuff, but after 3 hours of experimentation, I've narrowed it down to the above info. nothing else I could find/do has any effect on the problem. I think this is probably a bug (either in oracle, perl, RedHat or DBI!) but if anyone knows otherwise, or has any other suggestions for me to try (besides adding ||'' all thought my code!) please let me know! The Oracle OCI library trims trailing spaces for VARCHAR bind variables. It has been that way via DBI for at least a couple years. The workaround is to call $sth->bind_param( 1, SQL_CHAR ) before the first execute() call. The bind variable type is remembered for future execute() calls. See ora_ph_type in http://search.cpan.org/dist/DBD-Oracle/Oracle.pm as well. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: Problem using variable as a table name (prepare) - SOLVED
Hi, there!
I tried the code and the result was the same.
But then, I remembered having problems with the LANG environment variable -
my system, a RH9 machine, has LANG set to en_US.UTF-8 - I set it to en_US,
ran the program again and it worked perfectly !!
So, my problem is solved!
Thank you for your help!!
Paulo
> -Mensagem original-
> De: Michael A Chase [mailto:[EMAIL PROTECTED]
> Enviada: terca-feira, 3 de Fevereiro de 2004 17:37
> Para: Paulo Ferreira
> Cc: [EMAIL PROTECTED]
> Assunto: Re: Problem using variable as a table name (prepare)
>
>
> On 02/03/2004 01:38 AM, Paulo Ferreira wrote:
> > The value comes from and is chomped...
> >
> > The code is as follows:
> >
> > print "Entre o mes/ano (AAM) : ";
> > chomp(my $mes_ano=);
> >
> > The value entered is YYM, that is 2 digits for the year and one
> digit for
> > the month, being the last 3 months represented by the letters a
> to c. For
> > example, January 2002 is represented as 021, and November 2003 is
> > represented as 03b.
>
> Chomp() might not be removing enough. I'd suggest something like:
>
># Untested
>my $mes_ano = "";
>my $ano;
>while ( ! length( $mes_ano ) ) {
> print "Entre o mes/ano (AAM) : ";
> $_ = ;
> ( $ano, $mes_ano ) = ( $1, "$1$2" ) if /^(\d\d)([\dabc])\s*$/;
> # More tests on $ano if desired
>}
>
> That way you can be certain that nothing invalid has been entered. It
> also untaints the value.
>
> --
> Mac :})
> ** I usually forward private questions to the appropriate mail list. **
> Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
> Give a hobbit a fish and he eats fish for a day.
> Give a hobbit a ring and he eats fish for an age.
>
Re: W32.Novarg.A@mm
Most modern viruses, including this one, fake From addresses. Tim. On Thu, Feb 05, 2004 at 09:21:32AM +0100, [EMAIL PROTECTED] wrote: > Hi Tim, > > recently I got a mail from our administration that a '[EMAIL PROTECTED]' > send me a mail with 'message.zip' attached to it containing the mail worm > [EMAIL PROTECTED] > > I can't belive you sent it. What about that? > > bye mike > >
W32.Novarg.A@mm
Hi Tim, recently I got a mail from our administration that a '[EMAIL PROTECTED]' send me a mail with 'message.zip' attached to it containing the mail worm [EMAIL PROTECTED] I can't belive you sent it. What about that? bye mike
DBD::SQLite under Windows
In comp.lang.perl.modules, Message-ID: <[EMAIL PROTECTED]>, Eric Bohlman has just posted this comment: -><8- DBD::SQLite was crashing perl under Windows (ActiveState build 629 on Win98SE) whenever ChopBlanks was set. The crash was occurring in sqlite_st_fetch() in dbdimp.c when if (chopBlanks) free(val); was executed. After some futzing around, I discovered that changing val = strdup(val); to val=strcpy(malloc(len+1),val); cleared up the problem. Is it possible that ActivePerl is built with a custom malloc() and free(), but that strdup() is internally using a different allocator that isn't compatible with the custom free()? The compilation was done with MSVC 6.0. BTW, once that change was made, it compiled and tested just fine with the latest release (2.8.11) of the SQLite source. -><8- -- Cheers Ron Savage, [EMAIL PROTECTED] on 5/02/2004 http://savage.net.au/index.html
