Re: DBD::Informix and ESQL/C 9.30.FC1
Ivan Rodriguez Aguilar wrote: Hello can somebody compile successfully DBI::DBD for informix 9.30.FC1 ESQL/C version 9.51 and Client SDK 2.70 ?? i can compile successfully the DBI module on solaris 8 for this informix's version but the DBD module can't compile Interpreting slightly - you have Informix IDS 9.30.FC1. On Solaris 8, you also have Client SDK 2.70.FC1 - or is that 2.70.UC1? I tried to compile DBD for informix under linux redhat 7.3 with client sdk 2.0 and i compiled successful ly but when i use the DBI::DBD module multiple errors are sent The main problem is ESQL/C i dont know if my version the ESQL/C serve for compile DBI::DBD I know that my english is not good very thanks for read this mail Your English is fine; I might write "Thank you very much for reading this email", but the rest is pretty much OK. To use the 64-bit version of ESQL/C (indicated by the F), you must also have a 64-bit version of Perl. Without that, you should stick with the UCx versions of ESQL/C and CSDK. Next, you have to worry about whether you have the Sun C Compiler (which will be easier) or the GNU C Compiler - and which was used to build your version of Perl. If Perl was built as a 64-bit program with the Sun C Compiler, you probably wouldn't be contacting us - I would expect it to work out of the box. One of the options to the C compiler would be -xarch=sparcv9; another might be -KPIC or -Kpic. If Perl was built with a 64-bit version of GCC, you probably need to delete the -xarch=sparcv9 and maybe (but probably not) add the -m64 option that is the equivalent for GCC. Similarly, you might need to map -KPIC into -fPIC. To do this relatively cleanly, you're either going to have to hack (a copy of) the esql script, or let DBD::Informix do it for you on the fly. Read up on the DBD_INFORMIX_ESQLLD_REMOVE_OPTIONS_REGEX and the DBD_INFORMIX_ESQLCC_REMOVE_OPTIONS_REGEX environment variables. You may need to set INFORMIXC="gcc -m64 -fPIC -g" or something similar too. There is a file about all the environment variables in the Notes sub-directory. Incidentally, if your Informix database server is not a 64-bit server, then it is largely a wasted effort building the 64-bit Perl. Which version of DBD::Informix and DBI do you have? -- Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Re: Fwd: Cannot bind a single blank space to a non-null varchar?
On Mon, 10 Mar 2003 22:55:26 -0500 "Matthew O. Persico" <[EMAIL PROTECTED]> wrote:
> Conclusion: somewhere in bind_param or whatever it is that it
> invokes, trailing blanks are being stripped before the call to the OCI.
>
> I was able to get around this by using
>
> $sth->bind_param($col_idx,
> $value,
> {TYPE => DBI::SQL_CHAR});
>
> So, my question is, why is the DBI apparently taking it upon itself to
> enforce string trimming for varchars? Shouldn't the server be
> responsible? And shouldn't the DBI behave the same for th same value
> whether it is bound or constant? Or is there something stupid I am
> missing?
http://xmlproj.com/fom-serve/cache/124.html
--
Mac :})
** I normally 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.
Fwd: Cannot bind a single blank space to a non-null varchar?
I am calling bind_param like this:
$sth->bind_param($col_idx,
$value,
{TYPE => DBI::SQL_VARCHAR});
for a column defined as varchar(8), not null
When $value == ' ' (one space) or $value == ' ' (two spaces), I get the error:
DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into
("AMGDEV"."AM_BILLING_PS_INTERFACE"."DOC_SEQ_NBR") (DBD ERROR:
OCIStmtExecute)
at am_billing_dd_load.pl line 1185.
When I hardcode a ' ' into the query (no binding), I don't get an
error. When I hardcode '' into the query, I get the error.
Conclusion: somewhere in bind_param or whatever it is that it
invokes, trailing blanks are being stripped before the call to the OCI.
I was able to get around this by using
$sth->bind_param($col_idx,
$value,
{TYPE => DBI::SQL_CHAR});
So, my question is, why is the DBI apparently taking it upon itself to enforce string
trimming for varchars? Shouldn't the server be responsible? And shouldn't the DBI
behave the same for th same value whether it is bound or constant? Or is there
something stupid I am missing?
Perl 5.6.1
DBI 1.35
DBD::Oracle 1.12
Solaris 2.7
--
Matthew O. Persico
DBD 1.35 compile problems
I am using Perl 5.6.1, DBI 1.32 and DBD::Oracle 1.12. I have decided to upgrade to DBI
1.35 BEFORE reporting a problem I am having.
I built DBI 1.35 with no problems. Then I tried to rebuild DBD::Oracle 1.12.
1) compile warning:
cc -c -I/opt/oracle/product/8.1.6/rdbms/demo
-I/opt/oracle/product/8.1.6/rdbms/public
-I/opt/oracle/product/8.1.6/plsql/public
-I/opt/oracle/product/8.1.6/network/public
-I/opt/oracle/product/8.1.6/rdbms/demo -I/opt/oracle/product/8.1.6/rdbms/demo
-I/opt/perl/lib/site_perl/5.6.1/sun4-solaris/auto/DBI -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O-DVERSION=\"1.12\"
-DXS_VERSION=\"1.12\" -KPIC -I/opt/perl/lib/5.6.1/sun4-solaris/CORE oci8.c
"oci8.c", line 267: warning: assignment type mismatch:
pointer to function(pointer to struct imp_dbh_st {struct {..} com,
pointer to function(pointer to struct imp_dbh_st {..}, in... "=" pointer to void
This is probably NOT due to DBI 1.35, but I thought I'd report it anyway.
2) testing:
Because of this in DBI.pm:
sub connect {
my $class = shift;
my ($dsn, $user, $pass, $attr, $old_driver) = my @orig_args = @_;
my $driver;
if ($attr and !ref($attr)) { # switch $old_driver<->$attr if called in old style
->>> Carp::croak("DBI->connect using 'old-style' syntax is deprecated and will be
an error in future versions");
($old_driver, $attr) = ($attr, $old_driver);
}
(which doesn't make sense to me because the croak seems to make the old-style
syntax be an error in this version!)
both t/plsql.t and Oraperl.pm fail tests.
I had to patch t/plsql.t thusly to get that to pass tests:
my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';
# The existing line:
#my $dbh = DBI->connect('', $dbuser, '', 'Oracle');
# My change:
my $dbh = DBI->connect("dbi:Oracle:",$dbuser);
but since I'm not sure how to patch Oraperl.pm that in a way that is most
flexible AND since no one in my organization uses it, I just renamed test.pl out
of the way. The original code is in Oraperl.pm causing the problem is:
my($system_id, $name, $password) = @_;
local($Oraperl::prev_warn) = $SIG{'__WARN__'} || 0; # must be local
local($SIG{'__WARN__'}) = sub { _warn($Oraperl::prev_warn, @_) };
# we still use the old style connect call with an explicit driver
my $dbh = DBI->connect($system_id, $name, $password, 'Oracle');
I have been using ORACLE_USERID=u/[EMAIL PROTECTED] for the past few releases. Is the
use of
ORACLE_SID or TWO_TASK the recommended way to go now?
Summary of my perl5 (revision 5.0 version 6 subversion 1) configuration:
Platform:
osname=solaris, osvers=2.6, archname=sun4-solaris
uname='sunos nycux-ast401 5.6 generic_105181-31 sun4u sparc
sunw,ultra-enterprise '
config_args='-d'
hint=recommended, useposix=true, d_sigaction=define
usethreads=undef use5005threads=undef useithreads=undef
usemultiplicity=undef
useperlio=undef d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
Compiler:
cc='cc', ccflags ='-I/usr/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64',
optimize='-O',
cppflags='-I/usr/local/include'
ccversion='WorkShop Compilers 4.2 30 Oct 1996 C 4.2', gccversion='',
gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
alignbytes=8, usemymalloc=y, prototype=define
Linker and Libraries:
ld='cc', ldflags =' -L/usr/local/lib -L/opt/SUNWspro/SC4.2/lib '
libpth=/usr/local/lib /opt/SUNWspro/SC4.2/lib /usr/lib /usr/ccs/lib
libs=-lsocket -lnsl -ldl -lm -lc
perllibs=-lsocket -lnsl -ldl -lm -lc
libc=/lib/libc.so, so=so, useshrplib=false, libperl=libperl.a
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' '
cccdlflags='-KPIC', lddlflags='-G -L/usr/local/lib
-L/opt/SUNWspro/SC4.2/lib'
Characteristics of this binary (from libperl):
Compile-time options: USE_LARGE_FILES
Built under solaris
Compiled at Jun 30 2002 22:00:52
%ENV:
PERL5LIB
="/am/common/lib/5.6.1:/am/common/lib/site_perl:/sa/common/lib/5.6.1:/sa/common/lib/site_perl"
@INC:
/am/common/lib/5.6.1/sun4-solaris
/am/common/lib/5.6.1
/am/common/lib/site_perl/5.6.1/sun4-solaris
/am/common/lib/site_perl/5.6.1
/am/common/lib/site_perl
/sa/common/lib/5.6.1/sun4-solaris
/sa/common/lib/5.6.1
/sa/common/lib/site_perl/5.6.1/sun4-solaris
/sa/common/lib/site_perl/5.6.1
/sa/common/lib/site_perl
/opt/perl/lib/5.6.1/sun4-solaris
/opt/perl/lib/5.6.1
/opt/perl/lib/site_perl/5.6.1/sun4-solaris
/opt/perl/lib/site_perl/5.6.1
/opt/perl/lib/site_perl
--
Matthew Persico
Vice President
Lazard
30 Rockefeller Plaza
New York, NY 10020, USA
Phone Number: 212 632 8255
Fax Number: 212 332 5904
Email: [EMAIL PROTECTED]
Help: make test failing, DBIx::Recordset 0.24, postgresql 7.2.2
Has anyone else run into this problem? I understand why it's happneing (I think), I just can't figure why no-one else has reported it and/or it hasn't already been fixed... maybe I'm missing something obvious; if so please enlighten me. It looks like pg_atoi trying to parse a string as a number and failing miserably, as it's supposed to, I gather. The question is, how to make the tests work with this new(?) behavior of pg_aoti or DBD::Pg. It worked in the somewhat distant past, I think - or it may have been when I was still on Oracle. Anyway, I've been using the module for over a year now without any real problems, but it's the one module I haven't been able to get a clean test from, and it's bugging me. :) Any help or advice is greatly appreciated. Thanks, Jim make test output (trimmed a bit) --- DBI-Version: 1.35 DBD-Version: 1.21 Creating the testtables... ok Setup Object for dbixrs1...ok SQLSelect All... ok Setup Object for dbixrs2...ok SQLSelect All... ok Setup Object for dbixrs1, dbixrs2... ok SQLSelect All... ok Setup Object for dbixrs1, dbixrs3... ok SQLSelect All... ok Setup Object for dbixrs1, dbixrs4... ok SQLSelect All... ok Setup Object for dbixrs1...ok Select id (where as hash)... ok Select id (where as string)... ok Select name... ok Select dbixrs1.name... ok Select dbixrs2.name2 id=id... ok Select dbixrs2.value2 id=id... ok Select multiply values... ok Select multiply values array ref...ok Select multiply values & operators... ok Select $valuesplit... ok Select multiply fields 1...ok Select multiply fields 2... Test terminated with fatal error! Look at test.log *** Error code 2 make: Fatal error: Command failed for target `test_dynamic' --- test.log section with errors --- Select multiply fields 2... DB: SelectWhere <+name&value1>= type = + DB: Composite Field +name&value1 DB: Composite Field processing name DB: Composite Field get name = ? DB: Composite Field processing value1 DB: Composite Field get name = ? or value1 = ? DB: Key +name&value1 gives ( name = ? or value1 = ?) bind_values = bind_types=<1 4> DB: expr is ( ( name = ? or value1 = ?)) DB: SelectWhere <$operator>=<=> type = $ DB: FLUSH Recordset id = 6 DBIx::Recordset=HASH(0x537fb0) DB: Row::DESTROY DB: 'SELECT * FROM dbixrs1 WHERE ( ( name = ? or value1 = ?)) ' bind_values= bind_types=<1 4> WARN: DBD::Pg::st execute failed: ERROR: pg_atoi: error in "Third Name": can't parse "Third Name" at blib/lib/DBIx/Recordset.pm line 1360. DB: ERROR ERROR: pg_atoi: error in "Third Name": can't parse "Third Name" not ok (ERROR: pg_atoi: error in "Third Name": can't parse "Third Name") at test.pl line 924.
Re: shortcuts for common placeholder idioms...
Paul Boutros wrote:
my $sql = qq{
INSERT INTO table
( ${\comma_separated_values(@col)} )
VALUES ( ${\comma_separated_placeholders(@col)} )
};
I'm not sure I like this, but if it is to be used, a better name might
be "comma_list" which is the BNF short notation for all comma separated
lists in SQL (e.g. select lists, value lists, etc.). Also, no reason to
have two routines or to call them twice -- have the first set an
attribute and then later read it. For example the call to
$dbh->comma_list() would both return a comma separated list of the
column names and set $dbh->{placeholders} to a string of comma separated
placeholder marks which could be used later without a second call to a
method:
my $sql = qq{
INSERT INTO table
( $dbh->comma_list(@col) )
VALUES ( $dbh->{placeholders} )
};
--
Jeff
Re: Subclass Exporter and version number
On Mon, Mar 10, 2003 at 02:39:36PM -0800, Jeff Zucker wrote: > Tim Bunce wrote: > > >On Tue, Mar 04, 2003 at 09:32:27AM -0800, Jeff Zucker wrote: > > > >>>Or run this command > >>> > >>> perl -MSQL::Statement= > >>> > >>Hmm, what am I missing? That doesn't work for me > >> > >>From memory... it needs a non-lexical $VERSION and (I think) to be > >a subclass of Exporter. > > Hmm, that's right. If I subclass Exporter, the version number trick > works. Is it worth subclassing it just for this feature? Probably, unless there's a good reason not to. I'm not sure if subclassing is really needed. I think the version check logic is nt in UNIVERSAL. See XS(XS_UNIVERSAL_VERSION) in http://search.cpan.org/src/GSAR/perl-5.6.1/universal.c But I'll leave you to onder that if you want to. Tim.
Re: shortcuts for common placeholder idioms...
On Mon, 10 Mar 2003, Jeffrey Baker wrote:
> On Mon, Mar 10, 2003 at 05:56:49PM -0500, Paul Boutros wrote:
> > > > foreach ( $cgi->param() ) {
> > > >push @cols, $_;
> > > >push @vals, $cgi -> param( $_ );
> > > > }
> > > > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> > > >" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
> > >
> > > Seeing all that 'line noise' makes me think we need a neater way.
> > >
> > > Personally I'd have written it more like
> > >
> > > my $sql = qq{
> > > INSERT INTO table
> > > ( } . join( ", ", @cols ) . qq{ )
> > > VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
> > > };
> > >
> > > but that's not really that much clearer and possibly more
> > > 'magical' to the novice who may not be familiar with 'x' as a
> > > list replication operator.
> > >
> > >
> > > But imagine the DBI provided these two functions:
> > >
> > > sub comma_separated_values { join ", ", @_ }
> > > sub comma_separated_placeholders { join ", ", ("?") x @_ }
> > >
> > > you could then write the statement like this:
> > > separated
> > > my $sql = qq{
> > > INSERT INTO table
> > > ( ${\comma_separated_values(@col)} )
> > > VALUES ( ${\comma_separated_placeholders(@col)} )
> > > };
> > >
> > > The "... ${\...} ..." is a little magical but not hard to master.
> > > It's just a cute way to evaluate expressions inside a double quoted
> > > string. The ${...} part expects a reference to a scalar so that's
> > > what the backslash before the function name is for. The backslash
> > > takes a reference to the scalar value returned by the function and
> > > the ${...} then interpolates that into the string.
> > >
> > > Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
> > >
> > > my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES ( ${\dbi_csp(@col)} )";
> > >
> > > which seems rather neater than where we started:
> > >
> > > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> > >" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
> >
> > I like it a lot: it would have really eased up my DBI learning curve.
> > Gives people an easy way to start using place-holders rather than string
> > interpolation.
>
> Really? I think the novice programmer will balk at
> ${\dbi_csv(@col)}. That is a fairly advanced use of
> references. I personally prefer the join x @cols syntax, as
> I would have no idea at first glance what dbi_csp means.
Yup, because I think:
a) the novice DBI user isn't necessarily a novice PERL programmer
b) most novices take code samples out of the perldoc and try to alter
those as little as possible
So, if I had seen an insert example with the syntax:
my $sql = qq{
INSERT INTO table
( ${\comma_separated_values(@col)} )
VALUES ( ${\comma_separated_placeholders(@col)} )
};
$sth = $dbh->prepare($sql);
$sth->execute();
I would have figured, okay create an array @col listing the columns and
pass that to these mysterious functions and hope it works. The *why* of
working is something I think novices wouldn't worry about too much.
Paul
Re: shortcuts for common placeholder idioms...
On Mon, Mar 10, 2003 at 05:56:49PM -0500, Paul Boutros wrote:
> > > foreach ( $cgi->param() ) {
> > >push @cols, $_;
> > >push @vals, $cgi -> param( $_ );
> > > }
> > > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> > >" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
> >
> > Seeing all that 'line noise' makes me think we need a neater way.
> >
> > Personally I'd have written it more like
> >
> > my $sql = qq{
> > INSERT INTO table
> >( } . join( ", ", @cols ) . qq{ )
> > VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
> > };
> >
> > but that's not really that much clearer and possibly more
> > 'magical' to the novice who may not be familiar with 'x' as a
> > list replication operator.
> >
> >
> > But imagine the DBI provided these two functions:
> >
> > sub comma_separated_values { join ", ", @_ }
> > sub comma_separated_placeholders { join ", ", ("?") x @_ }
> >
> > you could then write the statement like this:
> > separated
> > my $sql = qq{
> > INSERT INTO table
> >( ${\comma_separated_values(@col)} )
> > VALUES ( ${\comma_separated_placeholders(@col)} )
> > };
> >
> > The "... ${\...} ..." is a little magical but not hard to master.
> > It's just a cute way to evaluate expressions inside a double quoted
> > string. The ${...} part expects a reference to a scalar so that's
> > what the backslash before the function name is for. The backslash
> > takes a reference to the scalar value returned by the function and
> > the ${...} then interpolates that into the string.
> >
> > Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
> >
> > my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES ( ${\dbi_csp(@col)} )";
> >
> > which seems rather neater than where we started:
> >
> > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> >" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
>
> I like it a lot: it would have really eased up my DBI learning curve.
> Gives people an easy way to start using place-holders rather than string
> interpolation.
Really? I think the novice programmer will balk at
${\dbi_csv(@col)}. That is a fairly advanced use of
references. I personally prefer the join x @cols syntax, as
I would have no idea at first glance what dbi_csp means.
-jwb
Re: shortcuts for common placeholder idioms...
> > foreach ( $cgi->param() ) {
> >push @cols, $_;
> >push @vals, $cgi -> param( $_ );
> > }
> > my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
> >" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
>
> Seeing all that 'line noise' makes me think we need a neater way.
>
> Personally I'd have written it more like
>
> my $sql = qq{
> INSERT INTO table
> ( } . join( ", ", @cols ) . qq{ )
> VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
> };
>
> but that's not really that much clearer and possibly more
> 'magical' to the novice who may not be familiar with 'x' as a
> list replication operator.
>
>
> But imagine the DBI provided these two functions:
>
> sub comma_separated_values { join ", ", @_ }
> sub comma_separated_placeholders { join ", ", ("?") x @_ }
>
> you could then write the statement like this:
> separated
> my $sql = qq{
> INSERT INTO table
> ( ${\comma_separated_values(@col)} )
> VALUES ( ${\comma_separated_placeholders(@col)} )
> };
>
> The "... ${\...} ..." is a little magical but not hard to master.
> It's just a cute way to evaluate expressions inside a double quoted
> string. The ${...} part expects a reference to a scalar so that's
> what the backslash before the function name is for. The backslash
> takes a reference to the scalar value returned by the function and
> the ${...} then interpolates that into the string.
>
> Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
>
> my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES ( ${\dbi_csp(@col)} )";
>
> which seems rather neater than where we started:
>
> my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
>" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
I like it a lot: it would have really eased up my DBI learning curve.
Gives people an easy way to start using place-holders rather than string
interpolation.
Paul
Subclass Exporter and version number
Tim Bunce wrote: On Tue, Mar 04, 2003 at 09:32:27AM -0800, Jeff Zucker wrote: Or run this command perl -MSQL::Statement= Hmm, what am I missing? That doesn't work for me From memory... it needs a non-lexical $VERSION and (I think) to be a subclass of Exporter. Hmm, that's right. If I subclass Exporter, the version number trick works. Is it worth subclassing it just for this feature? -- Jeff
Re: shortcuts for common placeholder idioms...
On Mon, 10 Mar 2003 22:15:35 + Tim Bunce <[EMAIL PROTECTED]> wrote:
> But imagine the DBI provided these two functions:
>
> sub comma_separated_values { join ", ", @_ }
> sub comma_separated_placeholders { join ", ", ("?") x @_ }
>
> you could then write the statement like this:
> separated
> my $sql = qq{
> INSERT INTO table
>( ${\comma_separated_values(@col)} )
> VALUES ( ${\comma_separated_placeholders(@col)} )
> };
>
> The "... ${\...} ..." is a little magical but not hard to master.
> It's just a cute way to evaluate expressions inside a double quoted
> string. The ${...} part expects a reference to a scalar so that's
> what the backslash before the function name is for. The backslash
> takes a reference to the scalar value returned by the function and
> the ${...} then interpolates that into the string.
>
> Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
>
> my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES (
> ${\dbi_csp(@col)} )";
That do look purrty. I tend to use the foreach() idiom because I
originally had to do some additional processing on the column names.
I'd momentarily forgotten the 'x' operator. TMTOWTDI.
--
Mac :})
** I normally 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.
shortcuts for common placeholder idioms...
> foreach ( $cgi->param() ) {
>push @cols, $_;
>push @vals, $cgi -> param( $_ );
> }
> my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
>" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
Seeing all that 'line noise' makes me think we need a neater way.
Personally I'd have written it more like
my $sql = qq{
INSERT INTO table
( } . join( ", ", @cols ) . qq{ )
VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
};
but that's not really that much clearer and possibly more
'magical' to the novice who may not be familiar with 'x' as a
list replication operator.
But imagine the DBI provided these two functions:
sub comma_separated_values { join ", ", @_ }
sub comma_separated_placeholders { join ", ", ("?") x @_ }
you could then write the statement like this:
separated
my $sql = qq{
INSERT INTO table
( ${\comma_separated_values(@col)} )
VALUES ( ${\comma_separated_placeholders(@col)} )
};
The "... ${\...} ..." is a little magical but not hard to master.
It's just a cute way to evaluate expressions inside a double quoted
string. The ${...} part expects a reference to a scalar so that's
what the backslash before the function name is for. The backslash
takes a reference to the scalar value returned by the function and
the ${...} then interpolates that into the string.
Perhaps we could call the functions dbi_csv and dbi_csp then we'd have
my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES ( ${\dbi_csp(@col)} )";
which seems rather neater than where we started:
my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
Comments?
Tim.
Re: need some advice
On 10 Mar 2003 14:34:28 -0600 Rob Benton <[EMAIL PROTECTED]> wrote:
> OK I see what you're doing. What I would need to do is just tweak that
> a little. I would need something like:
>
> foearch ($cgi->param() ) {
> if ( ) {
> $dbh->quote($_);
> }
> $sql .= "$_,";
> $val .= "?,";
> push(@{$array_ref}, $cgi->param($_));
> }
>
> Does that make any sense?
No.
If you are using placeholders, quote() is not necessary and *should
not* be used. That is one of the advantages of placeholders.
Read up on them in the fine DBI manual.
Again read http://xmlproj.com/fom-serve/cache/49.html .
# Untested example, there is always more than one way to do it
$dbh -> {RaiseErrors} = 1; # Error checking is essential
my ( @cols, @vals ) = ();
foreach ( $cgi->param() ) {
push @cols, $_;
push @vals, $cgi -> param( $_ );
}
# Note: Under Oracle, a SQL statement that is *IDENTICAL* to a
# previously parsed statement does not have to be replanned.
my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
" VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
my $sth = $dbh -> prepare( $sql );
$sth -> execute( @vals );
$dbh -> commit();
--
Mac :})
** I normally 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: need some advice
> OK I see what you're doing. What I would need to do is just
> tweak that a little. I would need something like:
>
> foearch ($cgi->param() ) {
foreach $p(@params) {
if(param($p) ...) {
$dbh->(param($p));
To do param() instead of $cgi->param() you may need to do
use CGI qw/:standard/;
Not sure if
if($cgi->param($p) ...) would work or not, it may though.
DMuey
> if ( ) {
> $dbh->quote($_);
> }
> $sql .= "$_,";
> $val .= "?,";
> push(@{$array_ref}, $cgi->param($_));
> }
>
> Does that make any sense?
>
> On Mon, 2003-03-10 at 12:45, Ian Harisay wrote:
> > Here, I'll try and be more helpful than berating. I'm
> sorry I'm having
> > a bad day. The code below is not totally complete. Some error
> > handling needs to be added for sure. I would consider this to be
> > psuedo
> > code simply because I did not check my work for syntax. I think it
> > fairly accruate though.
> >
> > ## this assumes you want to insert all form elements
> > my $cgi = CGI->new();
> > my $dbh = DBI->connect();
> >
> > my $sql = "INSERT INTO table (";
> > my $val = "values(";
> > my $array_ref = [];
> >
> > ## build your statement assuming the form element names are ## the
> > same as your column names. foreach ( $cgi->param() ){
> > $sql .= "$_,";
> > $val .= "?,";
> > push(@{$array_ref}, $cgi->param($_));
> > }
> > $sql =~ s/,$/) /; ## strip the last comma and add a closing paren.
> > $val =~ s/,$/)/;
> >
> > my $sth = $dbh->prepare($sql.$val);
> > my $result = $sth->execute($array_ref);
> >
> > $dbh->commit(); ## if autocommit is not on.
> > Ian Harisay wrote:
> >
> > > Are you not listening to these people giving you helpful advice?
> > > Use
> > > the placeholders. I gaurantee you will be glad you did.
> C'mon man!!!
> > > Embrace the change.
> > >
> > > Rob Benton wrote:
> > >
> > >> There won't be any ['"] (read that as reg. expression)
> inside the
> > >> fields so that's not a problem. All I need to do is
> decide whether
> > >> to single-quote the variable based on its data-type. Also this
> > >> will just be a select statement.
> > >>
> > >> On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
> > >>
> > >>
> > On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
> > <[EMAIL PROTECTED]>
> > wrote:
> >
> >
> >
> > > Since you know how your table is structured :
> > >
> > > $query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
> > >
> > > Or if you wanted to do it dynamically :
> > >
> > > $query = "INSERT INTO stuff VALUES(";
> > > if($data =~ m/^\d+$/) { $query .= "$data\, "; }
> > > else { $query .= "\'$data\'\, "; }
> > >
> >
> > The original poster wanted to make sure the values
> were properly
> > quoted. If any "'" characters are in $character,
> > >>>
> > >>> Aahhh gotcha, in that case yes definitely use quote() because it
> > >>> will take care of any charcaters that could casue
> problems and not
> > >>> just single quotes ( IE "(), etc.. )
> > >>>
> > >>> Sorry for misunderstanding
> > >>>
> > >>> DMuey
> > >>>
> > >>>
> > >>>
> > the SQL you've given will not parse correctly, if the user is
> > lucky. If the user is unlucky, it could contain malicious SQL.
> >
> > DBI already includes a method for properly quoting
> values. Oddly
> > enough it is named quote(). Read the fine manual to
> learn about it.
> >
> > That said, for DBDs that support them (including DBD::Oracle),
> > placeholders are far superior.
> >
> > Again http://xmlproj.com/fom-serve/cache/49.html .
> >
> >
> >
> > >> -Original Message-
> > >> From: Michael A Chase [mailto:[EMAIL PROTECTED]
> > >> Sent: Saturday, March 08, 2003 9:02 PM
> > >> To: [EMAIL PROTECTED]; Rob Benton
> > >> Subject: Re: need some advice
> > >>
> > >> Placeholders. There are examples of using them in
> the fine DBI
> > >> and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
> > >>
> > >>
> > >
> > > http://xmlproj.com/fom-serve/cache/49.html
> > >
> >
> > --
> > Mac :})
> > ** I normally 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: need some advice
OK I see what you're doing. What I would need to do is just tweak that
a little. I would need something like:
foearch ($cgi->param() ) {
if ( ) {
$dbh->quote($_);
}
$sql .= "$_,";
$val .= "?,";
push(@{$array_ref}, $cgi->param($_));
}
Does that make any sense?
On Mon, 2003-03-10 at 12:45, Ian Harisay wrote:
> Here, I'll try and be more helpful than berating. I'm sorry I'm having
> a bad day. The code below is not totally complete. Some error
> handling needs to be added for sure. I would consider this to be psuedo
> code simply because I did not check my work for syntax. I think it
> fairly accruate though.
>
> ## this assumes you want to insert all form elements
> my $cgi = CGI->new();
> my $dbh = DBI->connect();
>
> my $sql = "INSERT INTO table (";
> my $val = "values(";
> my $array_ref = [];
>
> ## build your statement assuming the form element names are
> ## the same as your column names.
> foreach ( $cgi->param() ){
> $sql .= "$_,";
> $val .= "?,";
> push(@{$array_ref}, $cgi->param($_));
> }
> $sql =~ s/,$/) /; ## strip the last comma and add a closing paren.
> $val =~ s/,$/)/;
>
> my $sth = $dbh->prepare($sql.$val);
> my $result = $sth->execute($array_ref);
>
> $dbh->commit(); ## if autocommit is not on.
> Ian Harisay wrote:
>
> > Are you not listening to these people giving you helpful advice? Use
> > the placeholders. I gaurantee you will be glad you did. C'mon man!!!
> > Embrace the change.
> >
> > Rob Benton wrote:
> >
> >> There won't be any ['"] (read that as reg. expression) inside the fields
> >> so that's not a problem. All I need to do is decide whether to
> >> single-quote the variable based on its data-type. Also this will just
> >> be a select statement.
> >>
> >> On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
> >>
> >>
> On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey <[EMAIL PROTECTED]>
> wrote:
>
>
>
> > Since you know how your table is structured :
> >
> > $query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
> >
> > Or if you wanted to do it dynamically :
> >
> > $query = "INSERT INTO stuff VALUES(";
> > if($data =~ m/^\d+$/) { $query .= "$data\, "; }
> > else { $query .= "\'$data\'\, "; }
> >
>
> The original poster wanted to make sure the values were properly
> quoted. If any "'" characters are in $character,
> >>>
> >>> Aahhh gotcha, in that case yes definitely use quote() because it
> >>> will take care of any charcaters that could casue problems and not
> >>> just single quotes ( IE "(), etc.. )
> >>>
> >>> Sorry for misunderstanding
> >>>
> >>> DMuey
> >>>
> >>>
> >>>
> the SQL you've given will not parse correctly, if the user is
> lucky. If the user is unlucky, it could contain malicious SQL.
>
> DBI already includes a method for properly quoting values. Oddly
> enough it is named quote(). Read the fine manual to learn about it.
>
> That said, for DBDs that support them (including DBD::Oracle),
> placeholders are far superior.
>
> Again http://xmlproj.com/fom-serve/cache/49.html .
>
>
>
> >> -Original Message-
> >> From: Michael A Chase [mailto:[EMAIL PROTECTED]
> >> Sent: Saturday, March 08, 2003 9:02 PM
> >> To: [EMAIL PROTECTED]; Rob Benton
> >> Subject: Re: need some advice
> >>
> >> Placeholders. There are examples of using them in the fine
> >> DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
> >>
> >>
> >
> > http://xmlproj.com/fom-serve/cache/49.html
> >
>
> --
> Mac :})
> ** I normally 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: troubles with mysql_read_default_file
Thanks for posting the answer for the archives. Tim. On Mon, Mar 10, 2003 at 04:54:00PM +, Greg Thompson wrote: > > I found an answer to my problem regarding the use of > mysql_read_default_file and my perl script crashing. According to: > > http://www.rosat.mpe-garching.mpg.de/mailing-lists/dbi/2002-06/msg00159.html > > and a page referenced therein, MySQL v3.23.49 contains the problem > and v3.23.50 fixed it. Damn, why doesn't Debian (woody distribution) > include the .50 release yet. > > Hopefully my 8-9 hours spent seeking this answer will help someone else. > > -- Greg ThompsonNCAR/RAP
Re: Can't rebind placeholder 1 (DBD-ODBC)?
ok - i have tested with mssql from remote - and the error is there. Alex
DBD::Oracle connection problem, ORA-06401
On WinNT (service pack 6) I've installed:
ActiveState Perl 5.8 (805)
DBI 1.32 and DBD Oracle 1.12 from the ppd's at http://xmlproj.com/PPM/
When running a test connection script (see below) I get "Error while
trying to retrieve test for error ORA-06401"
I'm running against Oracle 8i (8.1.7). When using sqlplus, the host sid
is fine. My oracle home is indeed "C:\oracle\ora81"
Thanks!
Ian
#!C:\Perl\bin\perl
use DBI;
use DBD::Oracle qw(:ora_types);
$ENV{ORACLE_SID} = "dbdev1.mydomain.com";
$ENV{ORACLE_HOME} = "C:\oracle\ora81";
$dbh = DBI->connect("dbi:Oracle:dbdev1.mydomain.com:",
"reportuser", "rptpwrd");
$cursor = $dbh->prepare("SELECT TABLE_NAME FROM
USER_TABLES WHERE TABLESPACE_NAME='USER_DATA'");
$cursor->execute;
while (@row = $cursor->fetchrow_array) {
print "$row[0]\n\n";
}
$cursor->finish;
$cursor->finish;
Re: Can't rebind placeholder 1 (DBD-ODBC)?
hi Jeff
i've created a reduced .cgi (see attached - please don't hurt me about
this...). i'm currently out of office and have no MsSQL next to me.
1. create a Table "Test1",
2. create "TypeName" - varchar - NOT NULL - Primary
3. create "ProvLevel1" - money - default "0.00"
4. create "ProvLevel2" - money - default "0.00"
5. create "Action" - tinyint - default 0
INSERT INTO Test1 (TypeName,ProvLevel1,ProvLevel2,Action) VALUES ('A',0,0,0)
INSERT INTO Test1 (TypeName,ProvLevel1,ProvLevel2,Action) VALUES ('B',0,0,0)
INSERT INTO Test1 (TypeName,ProvLevel1,ProvLevel2,Action) VALUES ('C',0,0,0)
INSERT INTO Test1 (TypeName,ProvLevel1,ProvLevel2,Action) VALUES ('D',0,0,0)
INSERT INTO Test1 (TypeName,ProvLevel1,ProvLevel2,Action) VALUES ('E',0,0,0)
now - try to change the values... and press save. you will get the error (i
haven't fully tested this small example - out of office). additional to this
case i have moved to DBD-ODBC v1.04 today and DBI v1.34 - but the problem is
still here.
Alex
begin 666 money_test.cgi
M(R%C.B]P&[EMAIL PROTECTED]<-"@T*=7-E('-T
M0T*)&1B:"T^>U!R:6YT17)R;W)](#T@,3L)"2,@86-T:79A=&4@
M9F]R('!R;V=R86UM:6YG+V1E8G5G9VEN9R!O;FQY#0HD9&)H("T^('M,;VYG
M4F5A9$QE;GT@/2 Q,# P,# [#0HD9&)H("T^('M,;VYG5')U;F-/:WT@/2 P
M.PT*(R1D8F@@+3X@>V]D8F-?9&5F875L=%]B:6YD7W1Y<&5](#T@,3([(",@
M4U%,7U9!4D-(05(-"@T*#0IM>2 D86-T:6]N/7!A7!E.B!T97AT+VAT;6Q<;EQN
M(CL-"@T*"7!R:6YT("(\2'1M;#X\2&5A9#X\5&ET;&4^5&ET;&4\+U1I=&QE
M/CPO2&5A9#Y<;B([#0H)<')I;G0@(CQ"3T19/CQ#14Y415(^/$)2/CQ"4CY<
M;EQN7&XB.PT*"7!R:6YT("(\1D]232!.04U%/[EMAIL PROTECTED]/6UO;F5Y7W1E
M7!E3F%M
M92PG)[EMAIL PROTECTED],@5'EP94YA;64L#0H)"0D)"4E33E5,3"A0PT*"0EI9B H)')E9C M/GLG06-T:6]N)WT]/3 I
M('L-"@D)"7!R:6YT("(\5%(^/%1$/B9N8G-P.R1R968P+3Y[)U1Y<&5.86UE
M)WTF;F)S<#L\+U1$/CQ41#XF;F)S<[EMAIL PROTECTED](%-)6D4]
M-2!.04U%/2=,159%3#%?)')E9C M/GLG5'EP94YA;64G?2<@5D%,544])R1R
M968P+3Y[)U!R;W9,979E;#$G?2<^)FYBR=0R=4>7!E3F%M92=])R!604Q5
M13TG)')E9C M/GLG4')O=DQE=F5L,2=])SXF;F)S<#L\+U1$/CQ41#XF;F)S
M<[EMAIL PROTECTED](%-)6D4]-2!.04U%/2=,159%3#)?)')E9C M
M/GLG5'EP94YA;64G?2<@5D%,544])R1R968P+3Y[)U!R;W9,979E;#(G?2<^
M)FYB7!E3F%M92!&
M4D]-("1D8G1A8FQE('=I=&@@*$YO3&]C:RDB*3L@(R!-PT*"0D);7D@)&YA;64])')E
[EMAIL PROTECTED]/GLG5'EP94YA;64G?3L-"@D)"6UY("1L979E;#$]<&%R86TH(DQ%5D5,
M,5\D;F%M92(I('Q\("2P_*2 -"@D)"0D)"5=(15)%(%1Y<&5.
M86UE/3\-"@D)"0D)(BD[#0H)"0EM>2 D&5C=71E*"1L
M979E;#$L)&QE=F5L,BPD;F%M92D[#0H)"0DC)'-T:#DM/F)I;F1?<&%R86TH
M;BQU;F1E9BQ344Q?5D%20TA!4BD[(",@=&5L;"!$0D0M3T1"0R!T:&ES(&ES
M(&[EMAIL PROTECTED]<@T*"0D)(R1S=&@Y+3YF:6YI<[EMAIL PROTECTED]"@E]#0H))'-T:[EMAIL
PROTECTED]/F9I
M;FES:"@I.PT*#0H);7D@)&QO8V%T:6]N/2(N+VUO;F5Y7W1E'0O:'1M;%QN(CL-"@EP
Re: need some advice
Here, I'll try and be more helpful than berating. I'm sorry I'm having
a bad day. The code below is not totally complete. Some error
handling needs to be added for sure. I would consider this to be psuedo
code simply because I did not check my work for syntax. I think it
fairly accruate though.
## this assumes you want to insert all form elements
my $cgi = CGI->new();
my $dbh = DBI->connect();
my $sql = "INSERT INTO table (";
my $val = "values(";
my $array_ref = [];
## build your statement assuming the form element names are
## the same as your column names.
foreach ( $cgi->param() ){
$sql .= "$_,";
$val .= "?,";
push(@{$array_ref}, $cgi->param($_));
}
$sql =~ s/,$/) /; ## strip the last comma and add a closing paren.
$val =~ s/,$/)/;
my $sth = $dbh->prepare($sql.$val);
my $result = $sth->execute($array_ref);
$dbh->commit(); ## if autocommit is not on.
Ian Harisay wrote:
Are you not listening to these people giving you helpful advice? Use
the placeholders. I gaurantee you will be glad you did. C'mon man!!!
Embrace the change.
Rob Benton wrote:
There won't be any ['"] (read that as reg. expression) inside the fields
so that's not a problem. All I need to do is decide whether to
single-quote the variable based on its data-type. Also this will just
be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey <[EMAIL PROTECTED]>
wrote:
Since you know how your table is structured :
$query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
Or if you wanted to do it dynamically :
$query = "INSERT INTO stuff VALUES(";
if($data =~ m/^\d+$/) { $query .= "$data\, "; }
else { $query .= "\'$data\'\, "; }
The original poster wanted to make sure the values were properly
quoted. If any "'" characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it
will take care of any charcaters that could casue problems and not
just single quotes ( IE "(), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values. Oddly
enough it is named quote(). Read the fine manual to learn about it.
That said, for DBDs that support them (including DBD::Oracle),
placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally 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: need some advice
Are you not listening to these people giving you helpful advice? Use
the placeholders. I gaurantee you will be glad you did. C'mon man!!!
Embrace the change.
Rob Benton wrote:
There won't be any ['"] (read that as reg. expression) inside the fields
so that's not a problem. All I need to do is decide whether to
single-quote the variable based on its data-type. Also this will just
be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
<[EMAIL PROTECTED]> wrote:
Since you know how your table is structured :
$query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
Or if you wanted to do it dynamically :
$query = "INSERT INTO stuff VALUES(";
if($data =~ m/^\d+$/) { $query .= "$data\, "; }
else { $query .= "\'$data\'\, "; }
The original poster wanted to make sure the values were
properly quoted. If any "'" characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE "(), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values.
Oddly enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including
DBD::Oracle), placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally 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: need some advice
There won't be any ['"] (read that as reg. expression) inside the fields
so that's not a problem. All I need to do is decide whether to
single-quote the variable based on its data-type. Also this will just
be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
> >
> > On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
> > <[EMAIL PROTECTED]> wrote:
> >
> > > Since you know how your table is structured :
> > >
> > > $query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
> > >
> > > Or if you wanted to do it dynamically :
> > >
> > > $query = "INSERT INTO stuff VALUES(";
> > > if($data =~ m/^\d+$/) { $query .= "$data\, "; }
> > > else { $query .= "\'$data\'\, "; }
> >
> > The original poster wanted to make sure the values were
> > properly quoted. If any "'" characters are in $character,
>
> Aahhh gotcha, in that case yes definitely use quote() because it will take care of
> any charcaters that could casue problems and not just single quotes ( IE "(), etc.. )
>
> Sorry for misunderstanding
>
> DMuey
>
> > the SQL you've given will not parse correctly, if the user is
> > lucky. If the user is unlucky, it could contain malicious SQL.
> >
> > DBI already includes a method for properly quoting values.
> > Oddly enough it is named quote(). Read the fine manual to
> > learn about it.
> >
> > That said, for DBDs that support them (including
> > DBD::Oracle), placeholders are far superior.
> >
> > Again http://xmlproj.com/fom-serve/cache/49.html .
> >
> > > > -Original Message-
> > > > From: Michael A Chase [mailto:[EMAIL PROTECTED]
> > > > Sent: Saturday, March 08, 2003 9:02 PM
> > > > To: [EMAIL PROTECTED]; Rob Benton
> > > > Subject: Re: need some advice
> >
> > > > Placeholders. There are examples of using them in the fine
> > > > DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
> > > >
> > > http://xmlproj.com/fom-serve/cache/49.html
> >
> > --
> > Mac :})
> > ** I normally 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.
> >
> >
>
Informix 9.30.FC1
Hello can somebody compile successfully DBI::DBD for informix 9.30.FC1 ESQL/C version 9.51 and Client SDK 2.70 ?? i can compile successfully the DBI module on solaris 8 for this informix's version but the DBD module can't compile I tried to compile DBD for informix under linux redhat 7.3 with client sdk 2.0 and i compiled successful ly but when i use the DBI::DBD module multiple errors are sent The main problem is ESQL/C i dont know if my version the ESQL/C serve for compile DBI::DBD I know that my english is not good very thanks for read this mail signature.asc Description: This is a digitally signed message part
Re: need some advice
Dan Muey [EMAIL PROTECTED] wrote:
> >
> > On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
> > <[EMAIL PROTECTED]> wrote:
> >
> > > Since you know how your table is structured :
> > >
> > > $query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
> > >
> > > Or if you wanted to do it dynamically :
> > >
> > > $query = "INSERT INTO stuff VALUES(";
> > > if($data =~ m/^\d+$/) { $query .= "$data\, "; }
> > > else { $query .= "\'$data\'\, "; }
> >
> > The original poster wanted to make sure the values were
> > properly quoted. If any "'" characters are in $character,
>
> Aahhh gotcha, in that case yes definitely use quote() because it will take care of
> any charcaters that could casue problems and not just single quotes ( IE "(), etc.. )
>
> Sorry for misunderstanding
But really, as Michael has already said twice, use placeholders.
Many reasons to use them, and no reasons not to. They handle
*ALL* quoting issues and give you other good things too.
Read about placeholders in the perldocs by doing
perldoc DBI
at a command prompt, and search for 'Placeholder'.
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
>
> DMuey
>
> > the SQL you've given will not parse correctly, if the user is
> > lucky. If the user is unlucky, it could contain malicious SQL.
> >
> > DBI already includes a method for properly quoting values.
> > Oddly enough it is named quote(). Read the fine manual to
> > learn about it.
> >
> > That said, for DBDs that support them (including
> > DBD::Oracle), placeholders are far superior.
> >
> > Again http://xmlproj.com/fom-serve/cache/49.html .
> >
> > > > -Original Message-
> > > > From: Michael A Chase [mailto:[EMAIL PROTECTED]
> > > > Sent: Saturday, March 08, 2003 9:02 PM
> > > > To: [EMAIL PROTECTED]; Rob Benton
> > > > Subject: Re: need some advice
> >
> > > > Placeholders. There are examples of using them in the fine
> > > > DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
> > > >
> > > http://xmlproj.com/fom-serve/cache/49.html
> >
> > --
> > Mac :})
> > ** I normally 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.
> >
> >
troubles with mysql_read_default_file
I found an answer to my problem regarding the use of mysql_read_default_file and my perl script crashing. According to: http://www.rosat.mpe-garching.mpg.de/mailing-lists/dbi/2002-06/msg00159.html and a page referenced therein, MySQL v3.23.49 contains the problem and v3.23.50 fixed it. Damn, why doesn't Debian (woody distribution) include the .50 release yet. Hopefully my 8-9 hours spent seeking this answer will help someone else. -- Greg ThompsonNCAR/RAP
RE: need some advice
>
> On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
> <[EMAIL PROTECTED]> wrote:
>
> > Since you know how your table is structured :
> >
> > $query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
> >
> > Or if you wanted to do it dynamically :
> >
> > $query = "INSERT INTO stuff VALUES(";
> > if($data =~ m/^\d+$/) { $query .= "$data\, "; }
> > else { $query .= "\'$data\'\, "; }
>
> The original poster wanted to make sure the values were
> properly quoted. If any "'" characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE "(), etc.. )
Sorry for misunderstanding
DMuey
> the SQL you've given will not parse correctly, if the user is
> lucky. If the user is unlucky, it could contain malicious SQL.
>
> DBI already includes a method for properly quoting values.
> Oddly enough it is named quote(). Read the fine manual to
> learn about it.
>
> That said, for DBDs that support them (including
> DBD::Oracle), placeholders are far superior.
>
> Again http://xmlproj.com/fom-serve/cache/49.html .
>
> > > -Original Message-
> > > From: Michael A Chase [mailto:[EMAIL PROTECTED]
> > > Sent: Saturday, March 08, 2003 9:02 PM
> > > To: [EMAIL PROTECTED]; Rob Benton
> > > Subject: Re: need some advice
>
> > > Placeholders. There are examples of using them in the fine
> > > DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
> > >
> > http://xmlproj.com/fom-serve/cache/49.html
>
> --
> Mac :})
> ** I normally 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: need some advice
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey <[EMAIL PROTECTED]> wrote:
> Since you know how your table is structured :
>
> $query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
>
> Or if you wanted to do it dynamically :
>
> $query = "INSERT INTO stuff VALUES(";
> if($data =~ m/^\d+$/) { $query .= "$data\, "; }
> else { $query .= "\'$data\'\, "; }
The original poster wanted to make sure the values were properly
quoted. If any "'" characters are in $character, the SQL you've given
will not parse correctly, if the user is lucky. If the user is
unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values. Oddly
enough it is named quote(). Read the fine manual to learn about it.
That said, for DBDs that support them (including DBD::Oracle),
placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
> > -Original Message-
> > From: Michael A Chase [mailto:[EMAIL PROTECTED]
> > Sent: Saturday, March 08, 2003 9:02 PM
> > To: [EMAIL PROTECTED]; Rob Benton
> > Subject: Re: need some advice
> > Placeholders. There are examples of using them in the fine
> > DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
> >
> http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally 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: need some advice
Since you know how your table is structured :
$query ="INSERT INTO stuff VALUES(NULL,$num,\'$character\')";
Or if you wanted to do it dynamically :
$query = "INSERT INTO stuff VALUES(";
if($data =~ m/^\d+$/) { $query .= "$data\, "; }
else { $query .= "\'$data\'\, "; }
DMuey
> -Original Message-
> From: Michael A Chase [mailto:[EMAIL PROTECTED]
> Sent: Saturday, March 08, 2003 9:02 PM
> To: [EMAIL PROTECTED]; Rob Benton
> Subject: Re: need some advice
>
>
> On 08 Mar 2003 16:06:35 -0600 Rob Benton
> <[EMAIL PROTECTED]> wrote:
>
> > I know there's probably a better way to do what I'm trying to
> > accomplish so I'd like some opinions.
> >
> > I have 2 cgi programs. One has a form full of text boxes
> sending data
> > to the 2nd program. What I need is an easy way to add the
> > single-quotes to each variable that is not an integer
> before sending
> > the statement to my Oracle database.
> >
> > So say I had -
> >
> > $name
> > $address
> > $zip
> >
> > NAME and ADDRESS being character and ZIP being integer
> variables, and
> > I wanted to make sure the went in as
>
> Using an NUMBER column for zipcodes is just begging for
> problems. For example, what about Canadian addresses?
>
> > $statement = qw/select * from otable where name='$name' and
> > address='$address' and zip=$zip/; $sth = $dbh->prepare($statement);
> >
> > I need a way (hopefully not messy) to "interpolate" those variables
> > into the sql statement but also have them single-quoted. I could
> > probably work something out but I usually find that there's
> an easier
> > way to do things after I have made a mess of them.
>
> Placeholders. There are examples of using them in the fine
> DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
>
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally 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: Can't rebind placeholder 1 (DBD-ODBC)?
>
> > Try either:
> > (near the top of the script, after the connect)
> > $dbh->{odbc_default_bind_type} = 12 # SQL_VARCHAR (then the
> database
> > will do the conversion from type to type for you, DBD::ODBC
> will tell
> > the database that it's SQL_VARCHAR)
>
> i have tested this and haven't changed anything inside the
> UPDATE. the error result is:
>
> Software error:
> DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver]Die Verbindung ist mit Ergebnissen von einem anderen
> hstmt belegt
> (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at
> C:\InetPub\domain\cgi-bin\dir\config.cgi line 223.
>
> (this means in english something like: "the connection with
> results is occupied by another hstmt")
I'd probably need you to get it down to a small, isolated sample which
reproduces the issue, including create table statement, any relevant data,
etc and the small series of queries and updates. I'll look at it when I
can...
You shouldn't need finish on $sth9, either.
>
>
>
> > Or (with DBD::ODBC 1.0x):
> > $sth9 = $dbh->prepare(...)
> > $sth9->bind_param(n, undef, SQL_VARCHAR); # tell DBD::ODBC
> this is a
> > char.
> will not work, too. my script will not load :-(
You need to use DBI qw(:sql_types) and that should solve it.
>
> Software error:
> Bareword "n" not allowed while "strict subs" in use at
> C:\InetPub\domain\cgi-bin\dir\config.cgi line 224. Bareword
> "SQL_VARCHAR" not allowed while "strict subs" in use at
> C:\InetPub\domain\cgi-bin\dir\config.cgi line 224. Execution
> of C:\InetPub\domain\cgi-bin\dir\config.cgi aborted due to
> compilation errors.
>
> i have tryed to change the "n" to "1" - but the SQL_VARCHAR
> will not go away.
N was my example for the column number. You should user your column numbers
for the columns in question.
Jeff
Re: bind param array
On Mon, Mar 10, 2003 at 03:17:46PM +0530, murugan mohan wrote:
> Hi all,
>I am trying to know how I can use bind array. I am having Perl version 5.6 and i
> am working in windows 2k.
> what I tried is as follows.
>
> $sth = $g_dbh->prepare(q{
> BEGIN
> test_1.P1(
> ?,
> ?,
> ?,
> ?);
> END;
> });
> $sth->bind_param_array(1,[EMAIL PROTECTED]);
> $sth->bind_param_array(2,[EMAIL PROTECTED]);
> $sth->bind_param_array(3,[EMAIL PROTECTED]);
> $sth->bind_param_array(4,[EMAIL PROTECTED]);
>
> $sth->execute();
$sth->execute_array;
Tim.
bind param array
Hi all,
I am trying to know how I can use bind array. I am having Perl version 5.6 and i am
working in windows 2k.
what I tried is as follows.
$sth = $g_dbh->prepare(q{
BEGIN
test_1.P1(
?,
?,
?,
?);
END;
});
$sth->bind_param_array(1,[EMAIL PROTECTED]);
$sth->bind_param_array(2,[EMAIL PROTECTED]);
$sth->bind_param_array(3,[EMAIL PROTECTED]);
$sth->bind_param_array(4,[EMAIL PROTECTED]);
$sth->execute();
I don't think there is a syntax error in this. Is this function is supported only in
5.8 onwards.
Please let me know
Thanks & Regards
Murugan
---
smiles make others believe
---Get more from the Web. FREE MSN Explorer download
: http://explorer.msn.com
Re: Can't rebind placeholder 1 (DBD-ODBC)?
On Mon, Mar 10, 2003 at 12:26:51AM -0800, Victor A. Rodriguez wrote: > > I run through the same problem a few days ago, and I > just fetched all the rows in a hash with > fetchall_arrayref, finish()ed the query and performed > a do() again. If you fetch all the rows you should not need to call finish(). Tim.
RE: [dbi] RE: SQLSTATE ($h->state) mappings
Can't you set odbc_version to 2 as it looks like DBD::ODBC will then call rc = SQLSetEnvAttr(imp_drh->henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)odbc_version, SQL_IS_INTEGER); instead of rc = SQLSetEnvAttr(imp_drh->henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER); Asking for ODBC 2 behavior affects SQL states and you get SQL_TYPE_TIMESTAMP, SQL_TYPE_DATE etc instead of SQL_TIMESTAMP. As far as I can see DBD::ODBC supports both. Martin -- Martin J. Evans Easysoft Ltd, UK Development On 08-Mar-2003 Jeff Urlwin wrote: > Dale, > > You might want to, at least temporarily, downgrade to 0.43, which will mean > that you should build it from the source. > > Unfortunately, there were a number of code changes that went along with > moving to 3.x for correctness, so I'd be surprised if it worked as a 2.x app > anymore, but if you build from the source, you may be able to take out the > SQLSetEnvAttr in the connect function. > > Jeff > >> -Original Message- >> From: Dale Durham [mailto:[EMAIL PROTECTED] >> Sent: Friday, March 07, 2003 2:27 PM >> To: '[EMAIL PROTECTED]' >> Subject: SQLSTATE ($h->state) mappings >> >> >> Is anyone working on (or is there a way to tell) the driver >> to pass ODBC 2.x state returns instead of 3.x (given the >> current DBD::ODBC driver is a 3.x 'app')? I have thousands of >> testcase that verify the $h->state() along with other returns >> such as $h->err. Once I upgraded to newer DBI and DBD::ODBC I >> now get ODBC 3.x state returns. Through ODBC, I can pass >> SQLSetEnvAttr to tell the driver to return ODBC 2.x states. >> How can I deal with this via DBI since DBD::ODBC does not >> have a function for this? >> >> Regards, >> Dale >>
Re: Can't rebind placeholder 1 (DBD-ODBC)?
Hi Alex, --- alex <[EMAIL PROTECTED]> wrote: > Software error: > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL > Server Driver]Die > Verbindung ist mit Ergebnissen von einem anderen > hstmt belegt > (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at > C:\InetPub\domain\cgi-bin\dir\config.cgi line 223. > > (this means in english something like: "the > connection with results is > occupied by another hstmt") I run through the same problem a few days ago, and I just fetched all the rows in a hash with fetchall_arrayref, finish()ed the query and performed a do() again. The drawback is that I have to maintain the whole table in the Perl process, but it was not a big problem in this case because the result set is really tiny. Just a thought. -- Víctor A. Rodríguez __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
Re: Can't rebind placeholder 1 (DBD-ODBC)?
> Try either:
> (near the top of the script, after the connect)
> $dbh->{odbc_default_bind_type} = 12 # SQL_VARCHAR
> (then the database will do the conversion from type to type for you,
> DBD::ODBC will tell the database that it's SQL_VARCHAR)
i have tested this and haven't changed anything inside the UPDATE. the error
result is:
Software error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Die
Verbindung ist mit Ergebnissen von einem anderen hstmt belegt
(SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at
C:\InetPub\domain\cgi-bin\dir\config.cgi line 223.
(this means in english something like: "the connection with results is
occupied by another hstmt")
> Or (with DBD::ODBC 1.0x):
> $sth9 = $dbh->prepare(...)
> $sth9->bind_param(n, undef, SQL_VARCHAR); # tell DBD::ODBC this is a
> char.
will not work, too. my script will not load :-(
Software error:
Bareword "n" not allowed while "strict subs" in use at
C:\InetPub\domain\cgi-bin\dir\config.cgi line 224.
Bareword "SQL_VARCHAR" not allowed while "strict subs" in use at
C:\InetPub\domain\cgi-bin\dir\config.cgi line 224.
Execution of C:\InetPub\domain\cgi-bin\dir\config.cgi aborted due to
compilation errors.
i have tryed to change the "n" to "1" - but the SQL_VARCHAR will not go
away.
Alex
Re: Database browsing
On Mon, 10 Mar 2003 08:40:39 +0200, Victor A. Rodriguez wrote: >Hi all, Hi Victor, again See also DBIx::Simple. It, too, is a very clever module. -- Cheers Ron Savage, [EMAIL PROTECTED] on 10/03/2003 http://savage.net.au/index.html
Re: Database browsing
On Mon, 10 Mar 2003 08:40:39 +0200, Victor A. Rodriguez wrote: >Hi all, Hi Victor >I'm looking for database browsing modules that allow to edit and >delete and >modify and ... records. I just found one module in SourceForge called >DBIx::Browse (at http://dbix-browse.sourceforge.net there's a very >good Consider DBIx::Recordset. I've seen a tut for it online, but all I have as a URI is: http://www.perl.com/pub/a/2001/02/dbix.html?wwwrrr_20010320.txt I've written a tut for it myself, but never published it. It's a very clever and tricky module, with extremely hard-to-understand docs. It stores output (from selects) into globs, so you get scalars, arrays and hashes populated simultaneously. Amazing! But in my limited understanding of Perl 6, such globs will not be supported. You need to check up on things like that. Ask an expert. >demo). It seems pretty good, but what EXACTLY I'm looking for is a >clone of >MSODSC (Microsoft Office Web Component), just in case anyone knows >it. I don't know this tool. I suspect no-one will have written a Perl look-a-like, but I could be wrong. After all, if you're going to write a tool in Perl, why copy MS when you can do a good job instead? >arise ...) and client/server, so I'm trying to move processing and >security >to the server side using Perl and DBI. Good idea. You'll need CGI::Session, perhaps? I recommend it highly. >- Does anyone knows of any module that works this way ?? DBIx::AdminEngine looks good, because I'm writing it, and because I'm biased. It is not, repeat not, worth releasing yet, since it only works in read-only mode. It is a vendor-independent replacement for an old tool of mine: myadmin.cgi (which is MySQL-specific). See tut # 35: http://savage.net.au/Perl-tutorials.html Feel free to rip off as much code from myadmin.cgi as you want. DBIx::AdminEngine uses DBIx::SQLEngine, which you should also investigate. And what is the ETA (Estimated Time of Arrival) for DBIx::AdminEngine? Don't ask. I have 4 part-time jobs just to pay the bills, so progress is slow. The next db to be supported will be Postgres. MySQL is already supported. I'm also writing auxiliary documentation for DBIx::SQLEngine, for my own education, some of which the author of DBIx::SQLEngine is incorporating into his own docs. You're welcome to a copy of DBIx::AdminEngine such as it is, but I'll be doing all the coding myself. The main program uses CGI::Explorer (another module of mine), but of course you don't have to use that :-)). If there's any interest, I'll put it up on my web site in a day or two. Of course, there could well be many other such modules floating about. Your post is a good way of fishing for them. >- Anyone interested in start a new project or modify an existing one >?? >(just in case there's no Perl replacement for MSODSC). > >Sorry for the long post, and thanks for your time and patience. Better long than too short. You should always spell out details for us to better grasp what you're doing. -- Cheers Ron Savage, [EMAIL PROTECTED] on 10/03/2003 http://savage.net.au/index.html
