Re: Unicode and Sybase univarchar
On 04.06.2010 15:41, Dave Rolsky wrote: On Fri, 4 Jun 2010, Alexander Foken wrote: That's why I proposed to switch to DBD::ODBC: It is well tested and supports Unicode as good as the ODBC driver does. And as I said in private email, that's not really feasible for us. Yes, it would cause you a lot of work, starting by distributing an ODBC manager and an ODBC driver. Perhaps too much work. Think of it as a last resort. But for patching Unicode support into DBD::Sybase, DBD::ODBC could be helpful, both by showing how to patch DBD::Sybase, and by providing a Unicode-aware way to the database on the development machine. Now you "just" need to find someone who is willing and has the time to patch DBD::Sybase ... ;-) Which is why I'm hoping we can pay Michael to work on this. Throwing money at the problem is definitly a good idea! And it would be great if that work would end on CPAN (and not in a secret private branch). Clearly, he's the most qualified. Otherwise, I might have to do this, which is a scary thought. Well, I also was scared by DBD::ODBC, and I must admit that I still don't understand all of it. I'm still happy that MJE took over the stalled DBD::ODBC development and improved my patch (and many other parts of DBD::ODBC). A first step to patching DBD::Sybase would be like my first step with DBD::ODBC: Just make binding input parameters and fetching results work with Unicode. Don't care about Unicode in the SQL statement (use placeholders, so you don't need Unicode there), don't care about binding output parameters, connect strings, user names, passwords, private functions. "Steal" the Unicode tests from DBD::ODBC and make DBD::Sybase pass those tests without breaking the existing tests. Alexander -- Alexander Foken mailto:alexan...@foken.de http://www.foken.de/alexander/
Re: Issue with prepared statement
Lethal Possum wrote:
Hello,
I am trying to create a prepared statement for the following query in
MySQL:
INSERT IGNORE INTO foo (group, value) (SELECT 101, value FROM bar
WHERE value LIKE '%something%');
So far my Perl code looks something like this:
my $group = 101;
my $pattern = '%something%';
my $query = 'INSERT IGNORE INTO foo (group, value) (SELECT ?, value
FROM bar WHERE value LIKE ?);';
my $sth = $dbh->prepare($query);
$sth->execute($id, $pattern) or die($sth->errstr);
It does not insert anything into the table foo even though there are
values in table bar that match the pattern. So my question is: what am
I doing wrong. I have other, simpler prepared queries in my program
that work just fine but this one does not do anything and does not
trigger any error as far as I can see.
Are ? parameters allowed in a nested SELECT?
Are they allowed not in the WHERE clause?
Are they allowed with the LIKE operator?
Thanks in advance for your help.
Tom
I haven't ever tried binding column names so I am not sure about that
piece. I don't have access to a db at the moment but for the LIKE
portion maybe try something such as this output indicates. I believe it
is what I've done in the past. It implies excluding % in $pattern and
moving it to the select statement.
perl -e "print q{INSERT IGNORE INTO foo (group, value) (SELECT ?, value
FROM bar WHERE value LIKE '%'||?||'%'};"
Re: Issue with prepared statement
Hello, Generally, there is no gain to using bind parameters if you don't know the columns to be projected in advance in SELECT and your LIKE regex is also dynamic. It can't prepare a query execution plan and cache it for later use, since the basic constructs of your query keep changing. I am not even sure you can use a placeholder for a column to be SELECT'ed. - Ashish On Sat, Jun 5, 2010 at 2:33 AM, Lethal Possum wrote: > Hello, > > I am trying to create a prepared statement for the following query in > MySQL: > > INSERT IGNORE INTO foo (group, value) (SELECT 101, value FROM bar > WHERE value LIKE '%something%'); > > So far my Perl code looks something like this: > > my $group = 101; > my $pattern = '%something%'; > my $query = 'INSERT IGNORE INTO foo (group, value) (SELECT ?, value > FROM bar WHERE value LIKE ?);'; > my $sth = $dbh->prepare($query); > $sth->execute($id, $pattern) or die($sth->errstr); > > It does not insert anything into the table foo even though there are > values in table bar that match the pattern. So my question is: what am > I doing wrong. I have other, simpler prepared queries in my program > that work just fine but this one does not do anything and does not > trigger any error as far as I can see. > > Are ? parameters allowed in a nested SELECT? > Are they allowed not in the WHERE clause? > Are they allowed with the LIKE operator? > > Thanks in advance for your help. > > Tom > >
Issue with prepared statement
Hello, I am trying to create a prepared statement for the following query in MySQL: INSERT IGNORE INTO foo (group, value) (SELECT 101, value FROM bar WHERE value LIKE '%something%'); So far my Perl code looks something like this: my $group = 101; my $pattern = '%something%'; my $query = 'INSERT IGNORE INTO foo (group, value) (SELECT ?, value FROM bar WHERE value LIKE ?);'; my $sth = $dbh->prepare($query); $sth->execute($id, $pattern) or die($sth->errstr); It does not insert anything into the table foo even though there are values in table bar that match the pattern. So my question is: what am I doing wrong. I have other, simpler prepared queries in my program that work just fine but this one does not do anything and does not trigger any error as far as I can see. Are ? parameters allowed in a nested SELECT? Are they allowed not in the WHERE clause? Are they allowed with the LIKE operator? Thanks in advance for your help. Tom
RE: Make Test Failed For DBD-Oracle 1.24
Hi John, I have set the ORACLE_HOME & LD_LIBRARY_PATH to point to the directory of 32bit Oracle client. Is that what you mean? However, still failed at make test. The output of perl Makefile.PL, make and make test listed below: # perl Makefile.PL Using DBI 1.611 (for perl 5.008003 on PA-RISC1.1-thread-multi) installed in /opt/perl/lib/site_perl/5.8.3/PA-RISC1.1-thread-multi/auto/DBI/ Configuring DBD::Oracle for perl 5.008003 on hpux (PA-RISC1.1-thread-multi) Remember to actually *READ* the README file! Especially if you have any problems. Installing on a hpux, Ver#11.00 Using Oracle in /opt/instantclient_10_2 DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR) Oracle version 10.2.0.4 (10.2) Looks like an Instant Client installation, okay Your LD_LIBRARY_PATH/SHLIB_PATH env var is set to '/opt/instantclient_10_2:/opt/instantclient_10_2' Oracle sysliblist: Found header files in /opt/instantclient_10_2/sdk/include. WARNING: If you have trouble, see README.hpux.txt... you may have to build your own perl, or go hunting for libraries WARNING: If you have trouble, try perl Makefile.PL -l Checking for functioning wait.ph System: perl5.008003 hp-ux bertha b.11.00 u 9000800 136901587 unlimited-user license Compiler: cc -fast +Onolimit +Opromote_indirect_calls +DAportable +DS2.0 -D_POSIX_C_SOURCE=199506L -D_REENTRANT -Ae -D_HPUX_SOURCE -Wl,+vnocompatwarnings -DNO_HASH_SEED -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/bin/ld Sysliblist: Linking with -lclntsh. Checking if your kit is complete... Looks good LD_RUN_PATH=/opt/instantclient_10_2 Using DBD::Oracle 1.24. Using DBD::Oracle 1.24. Using DBI 1.611 (for perl 5.008003 on PA-RISC1.1-thread-multi) installed in /opt/perl/lib/site_perl/5.8.3/PA-RISC1.1-thread-multi/auto/DBI/ Writing Makefile for DBD::Oracle *** If you have problems... read all the log printed above, and the README and README.help.txt files. (Of course, you have read README by now anyway, haven't you?) # make cp Oracle.pm blib/lib/DBD/Oracle.pm cp oraperl.ph blib/lib/oraperl.ph cp dbdimp.h blib/arch/auto/DBD/Oracle/dbdimp.h cp ocitrace.h blib/arch/auto/DBD/Oracle/ocitrace.h cp Oraperl.pm blib/lib/Oraperl.pm cp Oracle.h blib/arch/auto/DBD/Oracle/Oracle.h cp lib/DBD/Oracle/Object.pm blib/lib/DBD/Oracle/Object.pm cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm cp lib/DBD/Oracle/GetInfo.pm blib/lib/DBD/Oracle/GetInfo.pm /usr/bin/perl -p -e "s/~DRIVER~/Oracle/g" /opt/perl/lib/site_perl/5.8.3/PA-RISC1.1-thread-multi/auto/DBI/Driver.xs t > Oracle.xsi /usr/bin/perl /opt/perl/lib/5.8.3/ExtUtils/xsubpp -typemap /opt/perl/lib/5.8.3/ExtUtils/typemap -typemap typemap Oracle.xs > Oracle.xsc && mv Oracle.xsc Oracle.c cc -c -I/opt/instantclient_10_2/sdk/include -I/opt/perl/lib/site_perl/5.8.3/PA-RISC1.1-thread-multi/auto/DBI -D_POSIX_C_SOURCE=199506L -D_REENTRANT -Ae -D_HPUX_SOURCE -Wl,+vnocompatwarnings -DNO_HASH_SEED -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -fast +Onolimit +Opromote_indirect_calls +DAportable +DS2.0-DVERSION=\"1.24\" -DXS_VERSION=\"1.24\" +Z "-I/opt/perl/lib/5.8.3/PA-RISC1.1-thread-multi/CORE" -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\"10.2.0.4\" Oracle.c field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0 cc -c -I/opt/instantclient_10_2/sdk/include -I/opt/perl/lib/site_perl/5.8.3/PA-RISC1.1-thread-multi/auto/DBI -D_POSIX_C_SOURCE=199506L -D_REENTRANT -Ae -D_HPUX_SOURCE -Wl,+vnocompatwarnings -DNO_HASH_SEED -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -fast +Onolimit +Opromote_indirect_calls +DAportable +DS2.0-DVERSION=\"1.24\" -DXS_VERSION=\"1.24\" +Z "-I/opt/perl/lib/5.8.3/PA-RISC1.1-thread-multi/CORE" -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\"10.2.0.4\" dbdimp.c field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0 cc -c -I/opt/instantclient_10_2/sdk/include -I/opt/perl/lib/site_perl/5.8.3/PA-RISC1.1-thread-multi/auto/DBI -D_POSIX_C_SOURCE=199506L -D_REENTRANT -Ae -D_HPUX_SOURCE -Wl,+vnocompatwarnings -DNO_HASH_SEED -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -fast +Onolimit +Opromote_indirect_calls +DAportable +DS2.0-DVERSION=\"1.24\" -DXS_VERSION=\"1.24\" +Z "-I/opt/perl/lib/5.8.3/PA-RISC1.1-thread-multi/CORE" -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\"10.2.0.4\" oci8.c field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0field found :0x0 Running Mkbootstrap for DBD::Oracle () chmod 644 Oracle.bs rm -f blib/arch/auto/DBD/Oracle/Oracle.sl LD_RUN_PATH="/opt/instantclient
