dbi

perl, dbi with sql statement with a like condition

hi all, in my code i must do a simple sql query with a like condition. i've do in this way my $out = "/Users/zero/out.log"; my $filename = "/Users/zero/data.txt"; my $dbh = DBI->connect("DBI:Oracle:sid=$sid;host=$host;port=$port", $user, $pwd) or die "Couldn't connect to database: " . DBI->errstr; my $query = "select SOMETHING from SO...

How do I update records only if I find a duplicate or otherwise insert data?

In the code below there is a hash which contains records with fields like name, pid, type and time1. pid and name are repetitive fields which contain duplicates. I duplicate found update the fields which need modification else insert, here name and pid have duplicates (repetitive fields). The rest are unique. Also I have a unique field...

perl dbi handlers interacting with each other

I'm having a weird issue where I have 2 dbi handlers and they are interacting with each other in some non-obvious manner. Specifically, I set up 2 handlers, one for an insert, and one calling a stored proc, and if I call them like $spHandler->execute($cusip); $insertHandler->execute('2010-05-01', '36200A3C1', 595795,'X', 3); Then th...

How to selectively start and end a persistent Apache::DBI for perl from the client/browser?

This question is related to our web-based application, which uses perl, apache, Apache::DBI and a database (MySQL, SQLite, or anything). We know that Apache::DBI is used to create persistent db connections. These connections live in memory from the time the Apache web server starts to the time it shuts down. My Question is: Is it poss...

How to determine connection state of Perl DBI database handler

How to determine connection state of Perl DBI database handler(is connection opend)? Something like .NET SqlConnection.State == Open. May be something like defined($dbh->do("some nop sql")) but can't find sql nop statement to use. ...

Perl modules: MySQL vs DBI

A lot of our automated processes use perl and need to access our MySQL DBs. I hate to admit it, but up until recently we haven't really done much benchmarking with the majority of our processes. One of our devs setup a test to compare the performance of "use MySQL" vs "use DBI" with the following pseudocode: for ($i = 1; $i <= 1000; $...

Determine MySQL state with Perl's DBI module

I'm using a Perl script to dump the contents of a MySQL db. The Perl module I'm using is CPAN's DBI. Is there any way to tell if the state of the db has changed since the last dump so that I don't need to re-dump the db? ...

sp_tables call taking more time which causes blocking

Hi, We could see that during our perl program runs which basically connects to SQLserver to insert/delete/update data, the below is called very frequently sp_tables @table_name='NOXXTABLE'. We see that for many SPID's the call happens a lot of times. On running sp_tables @table_name='NOXXTABLE' in SQLserver we can see that it returns n...

sql server invalid precision on exists check query

Using sql server 2008 I am getting and invalid precision value error in the following perl script: use DBI; $idx = '12345'; $query = 'if exists (select * from tbl where idx = ?) select top 10 * from tbl'; my $h = $dbh->prepare($query) or die "Couldn't prepare query: " . $dbh->errstr; $h->execute($idx) or die "Couldn't execute statement:...

Perl DBI fetchall_hashref

Consider the following table: mysql> select * from vCountryStatus; +-------------+------------+------+---------+--------+-----------------+ | CountryName | CountryISO | Code | Status | Symbol | CurrencyName | +-------------+------------+------+---------+--------+-----------------+ | Brazil | BR | 55 | LIVE | BRL ...

Perl Module Method Calls: Can't call method "X" on an undefined value at ${SOMEFILE} line ${SOMELINE}.

All over the place, especially in DBI, I see this message come up all the time. It's confusing, because the first thing that comes to mind is that the arguments I'm passing the function are set to undef (or something similar), but it's clearly not the case. Given a module and a corresponding script... Module: ./lib/My/Module.pm packag...

Perl DBI with mysql: how to get return value from a stored procedure?

Does DBD::mysql implement the bind_param_inout method? I am getting the following error messages when trying it out: DBD::mysql::st bind_param_inout failed: Output parameters not implemented [for Statement "call spCreateTransactionRecord(?, ?)" with ParamValues: 0=Null!, 1=Null!] at ./db.pl line 23 My code: #!/usr/bin/pe...

Why does MySQL DATE_FORMAT print a blank result?

For the past couple of hours I've been trying to format a MySQL timestamp using DATE_FORMAT and it doesn't do anything! Perl Code: use CGI; use DBI; my $q = new CGI; # Database connection goes here my $sth_select = $dbh->prepare( "SELECT DATE_FORMAT(timestamp, '%m/%d/%y') FROM foo" ); $sth_select->execute() || die "Unable to ex...

When to use $sth->fetchrow_hashref, $sth->fetchrow_arrayref and $sth->fetchrow_array?

I know that: $sth->fetchrow_hashref returns a hashref of the fetched row from database, $sth->fetchrow_arrayref returns an arrayref of the fetched row from database, and $sth->fetchrow_array returns an array of the fetched row from database. But I want to know best practices about these. When should we use fetchrow_hashref and when s...

perl dbi rollback not working

Hello, i am using this approach. If there is an error in the sql, rollback only happens for the first id of the asset_group. Rest of the ids are ignored. Am i doing it the right way? my $sql = "sql batch that update and insert depending on the condition"; $dbh->{RaiseError} = 1; $dbh->{PrintError} = 0; $dbh->{AutoCommit} = 0; m...

How do I get the results of a MySQL query from Perl's DBI?

I am doing the following, and getting "1" which I assume means the statement wend well. But I would like the result instead. What's wrong? #!/usr/bin/perl use strict; use DBI; my $host = "test"; my $database = "dd"; my $port = 3306; my $user = "uuu"; my $pw = "ppp"; my $mysql = DBI->connect("DBI:mysql:database=$database;host=$host;p...

Why is using DBI's variable-binding causing a MySQL query to fail?

For some reason, using DBI's bind parameter feature for the below AES key is causing a query to fail to find any rows. use strict; use warnings; use DBI; my $dbh = DBI->connect('dbi:mysql:database=thedb;host=localhost'); my $aes_key = 'X`ku@wC_BI\SgY[S%/<iaB>&VXd5zDA+'; print length($aes_key), "\n"; my $test = $dbh->selectrow_hashref...

Using Perl bind_param with SQL IN statement

Possible Duplicate: Is there SQL parameter binding for arrays? I was wondering if there is anyway to use bind_param with SQL IN statements. According to perl documentation bind_param_array cannot be used as well. Has anyone come across the same situation? http://search.cpan.org/perldoc?DBI#bind_param_array ...

Perl -- DBI selectall_arrayref when querying getting Not Hash Reference

Hi I am very new to perl (but from a c# background) and I am trying to move some scripts to a windows box. Due to some modules not working easily with windows I have changed the way it connects to the DB. I have an sqlserver DB and I had a loop reading each row in a table, and then within this loop another query was sent to select diff...

Multiple prepared statements disrupt a transaction using DBD::Sybase

In my Perl script, I use DBD::Sybase (via DBI module) to connect to a SQL Server 2008. The base program as below runs without problem: use DBI; # assign values to $host, $usr, $pwd my $dbh = DBI->connect("dbi:Sybase:$host", $usr, $pwd); $dbh->do("BEGIN TRAN tr1"); my $update = $dbh->prepare("UPDATE mytable SET qty = ? where name = ?");...