Re: Mass Update
On Fri, 22 Jun 2001, Michael A. Chase <[EMAIL PROTECTED]> wrote,
> If the DBD layer doesn't support placeholders, SQL that uses them will
> generate an error that may or may not indicate that that is the problem.
Well, I suppose all DBD modules must support the placeholders at
the interface/syntax level (not necessarily at the implementation
level). That's one of the points I use DBI, it provides generic
interface, or general means, to access the database.
The syntax,
$sth = $dbh->prepare('SELECT * FROM A_TABLE WHERE ID > ? OR ID < ?');
runs fine using DBD::mysql, although no real advantage in the sense
of placeholder usage. But I don't have to change anything (except
the DSN part) when switching to ther RDBMS.
__END__
--
s::a::n->http(www.trabas.com)
Re: Re: DBD::Sybase - (Not) Chopping Blanks
Klaus Dittrich writes:
>
> Hello,
>
> here some more informations and an example for out missing-trailing-
> blank-problem.
>
> Versions:
> DBD::Sybase 0.22 (will soon be tested with 0.91)
> Perl 5.00503
> DBI::1.13
First the issue here is clearly that Sybase does this before it even
reaches the client.
Second, the client has no way of discerning if a data item is supposed
to be a varchar() or a char(), so there is no easy work-around
here. My suggestion for that would be to do some post processing in
the client using $sth->{PRECISION}->[] to get the
maximum size, and padding with blanks yourself.
Now, as an explanation of what happens: When you use
the order by clause, and when you don't have an index on the fields of
the order by Sybase creates a worktable for you, and I suspect that
Sybase will always use varchar() types instead of char() types in the
worktable, and this causes the result you see.
I created a unique clustered index on gewinnspiel_nr and the problem
went away for me (using ASE 12.0 on Solaris, though I suspect that
this doesn't matter).
Michael
--
Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
*Looking for new project to tackle starting 8/1/01*
SQL Query behaving differently through DBI than through MySQL prompt
Hello everybody, I'm working on some code that generated this query: select csGroups.ID as groupid,csUsers.UserName as username,csGroups_lang.Title as grouptitle,csUsers.ID as uid from csGroups_lang,csUsers,csGroups,csUsers_Groups where csGroups.ID = csGroups_lang.ID and csUsers.ID = csUsers_Groups.UserID and csGroups.ID = csUsers_Groups.GroupID and (csUsers.ID IN (1,2)) order by username asc limit 0,10 Through the DBI/DBD::Mysql interface I get this error: Unknown column 'csUsers_Groups.UserID' in 'where clause' But when I run it from the MySQL command prompt it runs cleanly. Does anyone have any insight into why this is acting this way? -Stephen
Re: Wierd DBD-ADO NULL behavior
On a hunch, I switched to DBD-ODBC - and the problem vanished!
Now - a followup question -
What are the pros and cons of using
1. DBD-ADO and a Microsoft Data Link connection string
versus
2. DBD-ODBC and defining a system DSN
How do their behaviors differ? Which is more efficent? Connection pooling
issues, etc.
I'm sure this has been discussed many times - could someone please point me
to a document where I can read about this issue?
Thanks!
- Original Message -
From: "Bodo Eing" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "Garrison G. Lutz" <[EMAIL PROTECTED]>
Sent: Friday, June 22, 2001 4:24 AM
Subject: Re: Wierd DBD-ADO NULL behavior
> From: "Garrison G. Lutz" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Subject:Wierd DBD-ADO NULL behavior
> Date sent: Wed, 20 Jun 2001 15:35:55 -0400
>
> Hi,
>
> I do not use DBD-ADO nor MS SQL, so I can only guess, but
>
> >
> > I am using DBD-ADO (latest version) and DBI 1.14 to connect to MS SQL
Server 2000 and execute a User stored procedure called "Step_Two". I use a
hash to hold the parameter values like this:
> >
> > if (CONDITION) { $hash{'key'} = undef; }
> > else { $hash['key'} = 'value'; }
> >
> > $query = '{call Step_Two(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}';
> > @placeholders = (..., $hash{'param'}, );
> >
> > $sth = $dbh->prepare($query);
> > $sth->execute(@placeholders);
> >
> > if the CONDITION is met, I need the stored procedure to receive a NULL
value for the parameter so that it can update the database with NULL values.
> >
> > However, if at least one of the parameters is NULL, I get the following
error:
> >
> > DBD::ADO::st execute failed: Can't execute statement '{call Step_Two(?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}': Lasterror: -2146824580: OLE
exception from "ADODB.Command": Parameter object is improperly defined.
Inconsistent or incomplete information was provided. Win32::OLE(0.1501)
error
> 0x800a0e7c in METHOD/PROPERTYGET "Execute"
> >
> > undef for a value should translate into NULL, so what am I doing wrong??
>
> In fact, NULLs returned from your database always translate to Perl's
> undef, but vice versa things get more sophisticated, especially if
> placeholders are used. If one of your placeholder parameters is
> passed to a WHERE clause, you may even run into extra trouble (see
> the DBI pod for this). My suggestion is the following approach:
>
> 0. Does your underlying database scheme allow NULLs ? If yes
>
> 1. Hard code some values (with appropriate quoting) including one or
> more NULLs into your statement and test if it works, like
>
> $query = '{call Step_Two('foo', 'bar', NULL, ., 'baz')}';
> $sth = $dbh->prepare($query);
> $sth->execute;
> If this does not work, I have to leave it to the rest of the list.
>
> 2. If it works, try to interpolate your values into your statement
> with the quote()-method, which will translate undef'ed values into
> unquoted NULLs:
>
> $query = "{call Step_Two($dbh->quote($hash{'param1'}), $dbh-
> >quote($hash{'param2'}).)}";
>
> ..
>
> 3. If this does not work or if you still want to stick to
> placeholders, try saying
>
> $query = '{call Step_Two(?, ?, NULL, ., ?)}';
> $sth = $dbh->prepare($query);
> $sth->execute(@values_not_containing_the_undefined_one);
>
> If this works, you can check your parameter hash for undefined values
> and generate an appropriate placeholder-style statement on the fly,
> saying;
>
> my @parameter_names = qw(your parameter names here in correct order);
> my @placeholders = map { if (defined $parameters{$_}) {'?'} else
> {'NULL'} } @parameter_names;
> my $query = '{call Step_Two(' . join (', ', @placeholders) . ')}';
>
> ### now get the values without the undef'ed ones
>
> for (@parameter_names) {
> push @values, $parameters{$_} if defined $parameters{$_};
> }
>
> ### now try
>
> $sth = $dbh->prepare($query);
> $sth->execute(@values);
>
> > Thanks!
>
> As said above, all this is just a guess, but your desparation
> justified a try...
>
> Bodo
> [EMAIL PROTECTED]
>
>
>
Re: Mass Update
> "Mike" == MikeBlezien <[EMAIL PROTECTED]> writes: Mike> Yes, where using MySQL version 3.23.37 w/DBI 1.14 This is a case where using Perl and DBI is massive overkill. You can do this with one line of MySQL SQL: UPDATE TABLE Users SET Password = ENCRYPT(Password) WHERE Id > 1; (Assuming this is on a system with the `crypt' function; please see info node '(mysql)Miscellaneous Functions'.) Also, you might want to add "LOCK/UNLOCK TABLE" around that. Having a swiss army chainsaw doesn't mean you have to use it. :) t.
bind_columns and other optimizations
Re the recent email about using bind_columns because it's more efficient:
I was going to ask why binding would be more efficient, then I RdTFM and
realized that the person was talking about binding OUTPUT, not input, and
now I've learned something new, so thank you.
The cheetah book says that bind_columns is really useful when you've got
lots of rows o' data, because you don't have to assign the variables a
million times, since you've got those references to play with.
I'm actually writing an API to access a database, where all of the DBI is
hidden inside my methods. (Yes, I know I should be using {Alzabo, Tangram,
SPOPS, }) Am I right in assuming that here bind_columns isn't as useful?
I'm never going to have more than a few rows (definitely < 100) per SQL
statement, but my statements may get called many times.
For this sort of application, are there any other optimizations I should be
aware of? I've already figured out, for example, that within my methods,
using prepare_cached is almost always a good idea. I got a 100% speedup by
changing prepares to prepare_cacheds. (I'm using finish so I don't get the
"active $sth" warning on a select. I realize that if I have too many
statements, I could break the max. open cursors limit. If that happens, is
there a guideline for which statement handles don't need to be cached? Given
that I don't know who's going to call which functions more often, so I can't
know (although I can guess) which handles will be used more often?)
Thanks,
Amir Karger
Curagen Corporation
DBD:Oracle Install problem on Tru64
Hi every one: I am trying to install DBD:Oracle on a Tru64 box and getting some errors when doing the "make test" step. I have searched out google to find out the reason for this error but all i got was the problem with LD_LIBRARY_PATH. I doubt if thats the case with my problem. Below is a listing of steps which i took to get to the "make test" part. Also listed are the environment variables Script started on Fri Jun 22 10:16:40 2001 # env HOME=/ LD_LIBRARY_PATH=/oracle/app/9ias/product/1021/lib:/oracle/app/9i as/product/1021/rdbms/lib/:/oracle/app/9ias/product/1021/network /jre11/lib/alpha/native_threads:/usr/shlib:/usr/lib:/usr/ccs/lib LOGNAME=oracle ORACLE_HOME=/oracle/app/9ias/product/1021 ORACLE_SID=lpldev ORACLE_USERID=lpldba/xxx PATH=/oracle/app/9ias/product/1021/bin:/sbin:/usr/sbin:/usr/bin: /usr/ccs/bin:/usr/bin/X11:/usr/local:/usr/local/bin SHELL=/bin/sh SHLVL=1 TERM=vt100 TWO_TASK=lpldev USER=root #perl Makefile.PL Using DBI 1.18 installed in /usr/lib/perl- 5.005/lib/site_perl/5.005/alpha-dec_osf/auto/DBI Configuring DBD::Oracle ... >>> Remember to actually *READ* the README file! Especially if you have any problems. Using Oracle in /oracle/app/9ias/product/1021 Found header files in rdbms/demo. Found /oracle/app/9ias/product/1021/rdbms/demo/demo_rdbms.mk Found /oracle/app/9ias/product/1021/otrace/demo/atmoci.mk Found /oracle/app/9ias/product/1021/precomp/demo/proc/demo_proc. mk Using /oracle/app/9ias/product/1021/rdbms/demo/demo_rdbms.mk Reading /oracle/app/9ias/product/1021/rdbms/demo/demo_rdbms.mk. Reading /oracle/app/9ias/product/1021/rdbms/lib/env_rdbms.mk. Deleting ORA_NLS = $(ORACLE_HOME)/ocommon/nls/admin/data/ because it is not already set in the environment and it can cause ORA-01019 errors. Deleting ORA_NLS33 = $(ORACLE_HOME)/ocommon/nls/admin/data/ because it is not already set in the environment and it can cause ORA-01019 errors. Discovering Oracle OCI build rules... Warning: Oracle build rule discovery failed (256) Oracle oci build command: cc -L/oracle/app/9ias/product/1021/lib/ - L/oracle/app/9ias/product/1021/rdbms/lib/ -o DBD_ORA_EXE DBD_ORA_OBJ.o -lclntsh -lc ld: Can't read file: DBD_ORA_OBJ.o (Successful) *** Exit 1 Stop. Unable to interpret Oracle oci build commands. Using fallback approach. System: perl5.00503 osf1 vijay.xko.dec.com t5.1 577 alpha Compiler: cc -O4 -fprm d -ieee -std -DLANGUAGE_C Linker: /usr/bin/ld Oracle makefiles would have used these definitions but we override them: CC: cc CFLAGS: $(GFLAG) $(OPTIMIZE) $(CDEBUG) $(CCFLAGS) $(QACCFLAGS) $(PFLAGS)\ $(SHARED_CFLAG) $(USRFLAGS) [$(GFLAG) -O3 -fast -fp_reorder -U_FASTMATH -O3 - fast -fp_reorder -U_FASTMATH $(GEMC_FLAGS) -std1 -DOSF1 - DA_OSF -readonly_strings -ieee -noansi_alias -D_INTRINSICS - DARCH_EV56 -arch ev56 -tune ev6 $(QACCFLAGS) - I/oracle/app/9ias/product/1021/rdbms/demo - I/oracle/app/9ias/product/1021/rdbms/public - I/oracle/app/9ias/product/1021/plsql/public - I/oracle/app/9ias/product/1021/network/public $(LPFLAGS) $(SHARED_CFLAG) $(USRFLAGS)] build: $(CC) $(LDFLAGS) -o $(EXE) $(OBJS) $(OCISHAREDLIBS) [ cc -L$(LIBHOME) - L/oracle/app/9ias/product/1021/rdbms/lib/ -o $(EXE) $(OBJS) - lclntsh -lc] LDFLAGS: -L$(LIBHOME) -L$(RDBMSLIB) [-L$(LIBHOME) - L/oracle/app/9ias/product/1021/rdbms/lib/] Linking with /oracle/app/9ias/product/1021/rdbms/lib/ssdbaed.o /oracle/a pp/9ias/product/1021/rdbms/lib/defopt.o -lclntsh -lc [from $(SSDBED) $(DEF_OPT) $(OCISHAREDLIBS)] Checking if your kit is complete... Looks good Warning: By default new modules are installed into your 'site_lib' directories. Since site_lib directories come after the normal library directories you must delete any old DBD::Oracle files and directories from your 'privlib' and 'archlib' directories and their auto subdirectories. Using DBD::Oracle 1.07. Using DBI 1.18 installed in /usr/lib/perl- 5.005/lib/site_perl/5.005/alpha-dec_osf/auto/DBI Writing Makefile for DBD::Oracle *** If you have problems, read the README and README.help files *** (Of course, you have read README by now anyway, haven't you?) # make mkdir blib mkdir blib/lib mkdir blib/lib/DBD mkdir blib/arch mkdir blib/arch/auto mkdir blib/arch/auto/DBD mkdir blib/arch/auto/DBD/Oracle mkdir blib/lib/auto mkdir blib/lib/auto/DBD mkdir blib/lib/auto/DBD/Oracle mkdir blib/man1 mkdir blib/man3 cp Oraperl.pm blib/lib/Oraperl.pm cp ocitrace.h blib/arch/auto/DBD/Oracle/ocitrace.h cp Oracle.pm blib/lib/DBD/Oracle.pm cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm cp oraperl.ph blib/lib/oraperl.ph cp Oracle.h blib/arch/auto/DBD/Oracle/Oracle.h cp dbdimp.h blib/arch/auto/DBD/Oracle/dbdimp.h /usr/bin/perl -p -e "s/~DRIVER~/Oracle/g" < /usr/lib/perl- 5.005/lib/site_perl/5.005/alpha-dec_osf/auto/DBI/Driver.xst > Oracle.xsi /usr/bin/perl -I/usr/lib/perl-5.005/lib/5.0
Re: DBI Connect Failure
> I'm studying the DBI module to get smarter on this. I was not able to > complete installation on the Pg module (I have pg95perl5-1.2.0), because > I couldn't find Pg.pm modules in the right places. Tried to reinstall, > but it failed on make because libpq.so.1 is missing from > usr/local/pgsql/lib. Tried checking www.cpan.org today to make sure that > I have the correct Pg module, but it was down. You can get the latest version of DBD::Pg from 'http://dbi.symbolstone.org/index.html' which is probably what you want. I don't think that you want be be using pg95perl5/Pg.pm. When you get DBD::Pg it will make you set POSTGRES_INCLUDE, and POSTGRES_LIB which tell DBD:Pg where to find libpq.so and other things that are required to build DBD::Pg. By the by what version of postgres are you using? Can you connect to it with psql? What operating system/version are you using? > I got no errors on the PgSQL.pm install, so it would be easier for me to > use that, presuming I can get DBD running correctly. I don't know about that... > Tried using perl documentation, but got only the usage index. I can't > run perldoc, because I get a warning: Superuser must not run ~/perldoc > without security audit and taint checks. perldoc will not let you run it as root. Create another user and then run perldoc as that user || you can try "su -c nobody 'perldoc DBI'" and see if that works. > I have a colleague who's betting this is a total waste of time, given > the ignorance I've just demonstrated. But as long as I do the other > stuff I'm supposed to be doing, trying this out still beats learning MS > Access. It is not a waste of time, for we all have to start somewhere. And as long as you learn something, I would consider it a worthwhile exercise. Later, Rudy
Re: DBI Connect Failure
Rudy,
I'm studying the DBI module to get smarter on this. I was not able to
complete installation on the Pg module (I have pg95perl5-1.2.0), because
I couldn't find Pg.pm modules in the right places. Tried to reinstall,
but it failed on make because libpq.so.1 is missing from
usr/local/pgsql/lib. Tried checking www.cpan.org today to make sure that
I have the correct Pg module, but it was down.
So naturally the database connection for DBI/DBD::Pg failed, but at
least this time I connected to DBI.pm, w/ response "can't locate
DBD/Pg.pm in @INC."
I have DBD installed, but I didn't set the environment variables for
DBI_DSN, DBI_USER, DBI_PASS and ODBCHOME when I installed it, and I only
know 2 out of the 4 anyway. Again, more studying.
I got no errors on the PgSQL.pm install, so it would be easier for me to
use that, presuming I can get DBD running correctly.
Tried using perl documentation, but got only the usage index. I can't
run perldoc, because I get a warning: Superuser must not run ~/perldoc
without security audit and taint checks.
I have a colleague who's betting this is a total waste of time, given
the ignorance I've just demonstrated. But as long as I do the other
stuff I'm supposed to be doing, trying this out still beats learning MS
Access.
Thanks all of you for the tips!
Christine
Rudy Lippan wrote:
>
> Christine,
>
> >
> > However I still can't connect to my sample Postgres database.
> >
>
> >From the code you posted, it does not look like you are using DBI, but
> rather PgSQL. You would probably be far better off using DBI, for PgSQL is
> considered 'alpha quality' || at least that is what the README on
> CPAN says.
>
> If you have DBI/DBD::Pg installed, try this:
>
> use DBI;
> my $dbh = DBI->connect('DBI:Pg:dbname=mydb','','') || die $DBI::errstr;
>
> Later,
>
> Rudy
Problem Compiling DBD::Oracle 1.07 with Oracle 7.3.4 on HPUX11.00
I'm having trouble compile DBD::Oracle 1.07 against v7.3.4. It compiles and works correctly in the same environment if I change my ORACLE_HOME to point to a v8.1.7 installation. With 7.3.4 it complains about an 'invalid member of struct or union' in line 740 of dbdimp.c Thanks for any help. My environment: HPUX 11.00 (aCC ANSI C compiler) perl 5.6.1 (Rebuilt to the spec listed in the DBI/DBD README's) Oracle 7.3.4 (8.1.7 is also installed on the server) Summary of my perl5 (revision 5.0 version 6 subversion 1) configuration: Platform: osname=hpux, osvers=11.00, archname=PA-RISC2.0 uname='hp-ux goahp70 b.11.00 u 9000800 501706567 unlimited-user license ' config_args='-Ubincompat5005 -Uinstallusrbinperl -Dprefix=/opt/perl5.6 -Duseshrplib -Accflags=+z -Aprepe nd:ldflags= -lcl -lpthread -de' 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 =' -D_HPUX_SOURCE +z -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -Ae', optimize='-O', cppflags='-D_HPUX_SOURCE -Aa +z' ccversion='A.11.01.00', 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='ld', ldflags =' -lcl -lpthread -Wl,+vnocompatwarnings -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib libs=-lnsl -lnm -lndbm -ldld -lm -lc -lndir -lcrypt -lsec perllibs=-lnsl -lnm -ldld -lm -lc -lndir -lcrypt -lsec libc=/lib/libc.sl, so=sl, useshrplib=true, libperl=libperl.sl Dynamic Linking: dlsrc=dl_hpux.xs, dlext=sl, d_dlsymun=undef, ccdlflags='-Wl,-E -Wl,-B,deferred ' cccdlflags='+z', lddlflags='-b +vnocompatwarnings -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: USE_LARGE_FILES Built under hpux Compiled at Jun 20 2001 13:44:13 @INC: /opt/perl5.6/lib/5.6.1/PA-RISC2.0 /opt/perl5.6/lib/5.6.1 /opt/perl5.6/lib/site_perl/5.6.1/PA-RISC2.0 /opt/perl5.6/lib/site_perl/5.6.1 /opt/perl5.6/lib/site_perl . Output from make: Script started on Thu Jun 21 12:14:45 2001 # ll total 1324 -r--r--r-- 1 root sys 26686 Jun 5 17:47 Changes -r--r--r-- 1 root sys674 Jan 18 05:48 MANIFEST -rw-r--r-- 1 root sys 42976 Jun 21 11:59 Makefile -r-xr-xr-x 1 root sys 38897 Jun 5 17:47 Makefile.PL -rw-r--r-- 1 root sys 102702 Jun 21 11:45 Makefile.old -rw-rw-rw- 1 root sys 22824 Jun 21 11:59 Oracle.c drwxr-xr-x 2 root sys 1024 Jun 18 09:23 Oracle.ex -r--r--r-- 1 root sys 2402 May 2 2000 Oracle.h -rw-rw-rw- 1 root sys 28616 Jun 21 12:00 Oracle.o -r--r--r-- 1 root sys 38857 Jun 5 17:47 Oracle.pm -r--r--r-- 1 root sys 2393 Jul 11 2000 Oracle.xs -rw-rw-rw- 1 root sys 12073 Jun 21 11:59 Oracle.xsi -r--r--r-- 1 root sys 27603 Jun 5 15:47 Oraperl.pm -r--r--r-- 1 root sys 9886 Feb 23 07:00 README -r--r--r-- 1 root sys 12057 Feb 23 07:01 README.clients -r--r--r-- 1 root sys 7892 May 2 2000 README.explain -r--r--r-- 1 root sys 15497 May 2 2000 README.help -r--r--r-- 1 root sys 14574 Feb 2 09:34 README.hpux -r--r--r-- 1 root sys 7900 Apr 6 07:56 README.java -r--r--r-- 1 root sys119 May 2 2000 README.login -r--r--r-- 1 root sys 2559 May 2 2000 README.longs -r--r--r-- 1 root sys 4963 May 2 2000 README.sec -r--r--r-- 1 root sys 1907 Aug 11 2000 README.win32 -r--r--r-- 1 root sys664 May 2 2000 README.wingcc -rw-r--r-- 1 root sys 1332 Jan 18 06:08 Todo drwxrwxrwx 6 root sys 96 Jun 21 11:59 blib -r--r--r-- 1 root sys 54669 Jun 5 17:47 dbdimp.c -r--r--r-- 1 root sys 9715 Jun 5 15:46 dbdimp.h drwxr-xr-x 2 root sys 96 Jun 18 09:23 hints -rw-rw-rw- 1 root sys 7712 Jun 21 11:59 mk.pm -r--r--r-- 1 root sys 5743 May 2 2000 oci.def -r--r--r-- 1 root sys 17514 Jun 5 15:54 oci7.c -r--r--r-- 1 root sys 51338 Jun 5 17:49 oci8.c -r--r--r-- 1 root sys 11129 Oct 25 2000 ocitrace.h -r--r--r-- 1 root sys 62260 May 2 2000 ora_explain.PL -r--r--r-- 1 root sys 1292 May 2 2000 oraperl.ph -rw-rw-rw- 1
Re: Mass Update
On Fri, 22 Jun 2001 07:13:48 -0700, "Michael A. Chase" <[EMAIL PROTECTED]> wrote: Yes, where using MySQL version 3.23.37 w/DBI 1.14 >>If the DBD layer doesn't support placeholders, SQL that uses them will >>generate an error that may or may not indicate that that is the problem. Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 =
Re: Mass Update
If the DBD layer doesn't support placeholders, SQL that uses them will generate an error that may or may not indicate that that is the problem. -- Mac :}) ** I normally forward private database questions to the DBI mail lists. ** Give a hobbit a fish and he'll eat fish for a day. Give a hobbit a ring and he'll eat fish for an age. - Original Message - From: "Hasanuddin Tamir" <[EMAIL PROTECTED]> To: "Michael A. Chase" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, June 22, 2001 05:21 Subject: Re: Mass Update > On Fri, 22 Jun 2001, Michael A. Chase <[EMAIL PROTECTED]> wrote, > > > You didn't mention what database you are using, so I don't know if you can > > use placeholders. > > It's harmless to use placeholders for RDBMSs don't support them.
Re: Mass Update
On Fri, 22 Jun 2001, Michael A. Chase <[EMAIL PROTECTED]> wrote, > You didn't mention what database you are using, so I don't know if you can > use placeholders. It's harmless to use placeholders for RDBMSs don't support them. __END__ -- s::a::n->http(www.trabas.com)
Re: Mass Update
On Fri, 22 Jun 2001 04:33:05 -0700, "Michael A. Chase" <[EMAIL PROTECTED]> wrote: Thank you, I appreciate your assistance. >>I suggest you read the fine manual (perldoc DBI). If you are concerned with >>efficiency, you should be using $sth->bind_columns() and $sth->fetch() to >>retrieve the passwords and ids one row at a time. Prepare the update >>statement outside the loop with placeholders (assuming DBD::??? supports >>them, run 'perldoc DBD::???' to find out) and then execute the update >>statement inside the loop after you have calculated the new password. >> >>With only 540 rows, efficiency isn't going to matter much, but it would be >>good practice. Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 =
Re: Mass Update
You didn't mention what database you are using, so I don't know if you can
use placeholders.
Your original example wouldn't put the correct passwords back into the
correct rows because there is no relationship between $i and the id value in
the database for each row password was taken from. Without an ORDER BY
clause, the order the rows are returned in is not guaranteed, you might also
have some skipped id values.
I suggest you read the fine manual (perldoc DBI). If you are concerned with
efficiency, you should be using $sth->bind_columns() and $sth->fetch() to
retrieve the passwords and ids one row at a time. Prepare the update
statement outside the loop with placeholders (assuming DBD::??? supports
them, run 'perldoc DBD::???' to find out) and then execute the update
statement inside the loop after you have calculated the new password.
With only 540 rows, efficiency isn't going to matter much, but it would be
good practice.
Untested example:
my $dbh = DBI -> connect( "dbi:???:???", $user, $pwd,
{ PrintError => 0, RaiseError => 1, AutoCommit => 1 } );
my $sthU = $dbh -> prepare( "UPDATE xxx SET password = ? WHERE id = ?" );
my $sthS = $dbh -> prepare( "SELECT id, password FROM xxx WHERE id >
1" );
$sthS -> execute;
my ( $id, $password );
$sthS -> bind_columns( \( $id, $password ) );
while ( $sthS -> fetch ) {
$salt = ???;
$sthU -> execute( crypt($password, $salt), $id );
}
$dbh -> disconnect;
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
- Original Message -
From: "MikeBlezien" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, June 21, 2001 20:15
Subject: Mass Update
Got a problem I am trying to figure out the best way to handle, using Perl
w/DBI
We have a table with a Password column, that accidentally had all the
Password
entered incorrectly. They where suppose to be encrypted before entered into
the
table, approximate 540 passwords.
What I need to do is pull all the passwords from the table, all except the
very
first row, id #1. The id column is an auto incremented column. So I would
need
to do:
SELECT Password FROM Users WHERE id > 1
my @password = $sth->fetchrow_array();
Now what I need to do is do a mass UPDATE back into the "Users" table after
encrypting the password, something like this:
for (my $i; $i < @password; $i++) {
# The $salt value is done here
my $pass = crypt($password[$i], $salt);
# Now do the mass Update here back into the "Users" table with the new $pass
# variable
}
I figure this would be best handle with a place holder or bind_column, but
not
real sure the best way to do this, and keep it efficient. Any help would be
much
appreciated ;)
RE: Max connection of Perl-DBI
Dear Mr. Sterin, I'm using ORACLE-DBD to connect with my Oracle8i. After 100 simultaneous process/connection, the software and database (listener) is hangs and sometimes return a message: 'return zero length value'. We limit the simultaneous connection to our database up to 200, which is far beyond the troubled state. Is it the Perl-DBI which had the limit? Yours Truly, Alex Kusuma Tjahjana Phone: +62 +21 +515 3268 / 69 email: [EMAIL PROTECTED] -Original Message- From: Sterin, Ilya [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 11:01 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Max connection of Perl-DBI Please don't send emails not pertaining to the development of DBI to [EMAIL PROTECTED] If you read the directions before signing up, you would have read the purpose of each list. See comments below... > -Original Message- > From: Alex Kusuma Tjahjana [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 20, 2001 5:56 AM > To: [EMAIL PROTECTED] > Subject: Max connection of Perl-DBI > > > Dear all, > > I have a little problem regarding connection on a Perl-DBI. > > I already settle a remote database connection from my web server to > my database server. After several time, my software and my web server > is stop responding requet from clients. My software heavily rely on the > database connection. Yes, the traffic between my web and database What database are you using? It's hard to tell from your message:-) > connection is very dense. And it seems that after the number of > Perl process (about 100 simultaneous processes) on my web server, > the software and the database is stop responding. Stops responding? You mean hangs or errors out? > > PS: the connection has been running well for 1 year and several months > and all this time, nothing happened. However, it is true that > current load is very peak that usual. > > My question: Is Perl-DBI have an upper limit of simultaneous > database connection? If there is any, what is limit and how much? Your database software should have a limit of simultaneous connections and you will probably need to change that. What is the error message you are getting? > > Thank you. > If there is any further question or information you need to know, > do not hesitate to contact me. > > > Yours Truly, > > Alex Kusuma Tjahjana > Phone: +62 +21 +515 3268 / 69 > email: [EMAIL PROTECTED]
Re: DBD::Sybase - (Not) Chopping Blanks
Hello, here some more informations and an example for out missing-trailing- blank-problem. Versions: DBD::Sybase 0.22 (will soon be tested with 0.91) Perl 5.00503 DBI::1.13 Table Definition: Data_located_on_segmentWhen_created -- -- default Jul 30 1999 1:34PM Column_nameType Length Prec Scale Nulls Default_name Rule_name Identity -- -- -- - - -- -- gewinnspiel_nr numeric 3 4 0 0 NULL NULL 0 beschreibung char 30 NULL NULL 1 NULL NULL 0 gueltig_vondatetime8 NULL NULL 0 NULL NULL 0 gueltig_bisdatetime8 NULL NULL 0 NULL NULL 0 fragen char 255 NULL NULL 1 NULL NULL 0 anzahl_der_fragen numeric 2 2 0 0 NULL NULL 0 richtige_antworten char 40 NULL NULL 1 NULL NULL 0 anzahl_fuer_ziehungnumeric 3 4 0 0 NULL NULL 0 punkte_trostpreis numeric 5 9 0 0 NULL NULL 0 ziehung_am datetime8 NULL NULL 1 NULL NULL 0 preisvergabe_amdatetime8 NULL NULL 1 NULL NULL 0 neu_vonchar8 NULL NULL 1 NULL NULL 0 neu_am datetime8 NULL NULL 1 NULL NULL 0 geaendert_von char8 NULL NULL 1 NULL NULL 0 geaendert_am datetime8 NULL NULL 1 NULL NULL 0 Goal: Fixed-Length-Output for Loading into another Database (Teradata) (works fine this way with Oracle and Teradata itself) SQL 1 without Order-By: select isnull(convert(char(4 ), gewinnspiel_nr ), space(4)) ,isnull(convert(char(8 ), gueltig_von , 112), space(8)) ,isnull(convert(char(8 ), gueltig_bis , 112), space(8)) ,isnull(convert(char(255), fragen ), space(255)) from gewinnspiel Result: 1 1999082819991010Frage1: was ist BLA? Frage 2: Wer ist BLA ? Frage 3: Wo ist Bla ? 10 1999082819991010 I.E. no trailing Blanks are truncated. SQL 2 with Order-By: select isnull(convert(char(4 ), gewinnspiel_nr ), space(4)) ,isnull(convert(char(8 ), gueltig_von , 112), space(8)) ,isnull(convert(char(8 ), gueltig_bis , 112), space(8)) ,isnull(convert(char(255), fragen ), space(255)) from gewinnspiel order by gewinnspiel_nr Result: 11999082819991010Frage1: was ist BLA? Frage 2: Wer ist BLA ? Frage 3: Wo ist Bla ? 101999082819991010 I.E. all trailing Blanks are truncated. Data is fetched by fetchrow_array(); Trace shows the data is already truncated when sent from DBI. Chop-Blanks = 0 does not help. Workaround: We now use a workaround by appending a character to each field and chopping this one before output. Thanks for any help. Regards, Klaus Dittrich -- Klaus Dittrich Mannesmann Mobilfunk GmbH Duesseldorf, [EMAIL PROTECTED] -- Original Nachricht -- >Klaus Dittrich writes: > > > > > > -- Original Nachricht -- > > > > >I don't know the best answer for you question, > > >But I wonder, why don't you use an int or tinyint here for > > >the gewinnspiel_nr and then just (s)printf '%04d' in Perl? > > > > I thought of that also, but we tried to use a generic Perl > > programm that is given a table-specific Sql to execute > > (works really fine for Oracle and Teradata). > > > > I just wondered about the behavior of DBD::Sybase when > > using an Order-By-Clause (why does it chop the Blanks only > > when
Re: Wierd DBD-ADO NULL behavior
From: "Garrison G. Lutz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject:Wierd DBD-ADO NULL behavior
Date sent: Wed, 20 Jun 2001 15:35:55 -0400
Hi,
I do not use DBD-ADO nor MS SQL, so I can only guess, but
>
> I am using DBD-ADO (latest version) and DBI 1.14 to connect to MS SQL Server 2000
>and execute a User stored procedure called "Step_Two". I use a hash to hold the
>parameter values like this:
>
> if (CONDITION) { $hash{'key'} = undef; }
> else { $hash['key'} = 'value'; }
>
> $query = '{call Step_Two(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}';
> @placeholders = (..., $hash{'param'}, );
>
> $sth = $dbh->prepare($query);
> $sth->execute(@placeholders);
>
> if the CONDITION is met, I need the stored procedure to receive a NULL value for the
>parameter so that it can update the database with NULL values.
>
> However, if at least one of the parameters is NULL, I get the following error:
>
> DBD::ADO::st execute failed: Can't execute statement '{call Step_Two(?, ?, ?, ?, ?,
>?, ?, ?, ?, ?, ?, ?, ?, ?)}': Lasterror: -2146824580: OLE exception from
>"ADODB.Command": Parameter object is improperly defined. Inconsistent or incomplete
>information was provided. Win32::OLE(0.1501) error
0x800a0e7c in METHOD/PROPERTYGET "Execute"
>
> undef for a value should translate into NULL, so what am I doing wrong??
In fact, NULLs returned from your database always translate to Perl's
undef, but vice versa things get more sophisticated, especially if
placeholders are used. If one of your placeholder parameters is
passed to a WHERE clause, you may even run into extra trouble (see
the DBI pod for this). My suggestion is the following approach:
0. Does your underlying database scheme allow NULLs ? If yes
1. Hard code some values (with appropriate quoting) including one or
more NULLs into your statement and test if it works, like
$query = '{call Step_Two('foo', 'bar', NULL, ., 'baz')}';
$sth = $dbh->prepare($query);
$sth->execute;
If this does not work, I have to leave it to the rest of the list.
2. If it works, try to interpolate your values into your statement
with the quote()-method, which will translate undef'ed values into
unquoted NULLs:
$query = "{call Step_Two($dbh->quote($hash{'param1'}), $dbh-
>quote($hash{'param2'}).)}";
..
3. If this does not work or if you still want to stick to
placeholders, try saying
$query = '{call Step_Two(?, ?, NULL, ., ?)}';
$sth = $dbh->prepare($query);
$sth->execute(@values_not_containing_the_undefined_one);
If this works, you can check your parameter hash for undefined values
and generate an appropriate placeholder-style statement on the fly,
saying;
my @parameter_names = qw(your parameter names here in correct order);
my @placeholders = map { if (defined $parameters{$_}) {'?'} else
{'NULL'} } @parameter_names;
my $query = '{call Step_Two(' . join (', ', @placeholders) . ')}';
### now get the values without the undef'ed ones
for (@parameter_names) {
push @values, $parameters{$_} if defined $parameters{$_};
}
### now try
$sth = $dbh->prepare($query);
$sth->execute(@values);
> Thanks!
As said above, all this is just a guess, but your desparation
justified a try...
Bodo
[EMAIL PROTECTED]
