dbi

How can I get a list of indices on a SQL table using Perl?

How can I get a list of the indices on a table in my sybase database using Perl? The goal is to "copy" all the indices from a table to a nearly identical table. Is $dbh->selectarray_ref('sp_helpindex $table') the best I can do? ...

Why does Apache complain that my mod_perl program "disconnect invalidates 1 active statement handle"?

disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) The following code which grabs data from MySQL gets executed successfully, but will cause Apache to generate the above message in its error log: my $driver = "mysql"; my $server = "localhost:3306...

How can I write a subroutine for DBI inserts with varying number of values?

Hi! I'm doing a lot of insert queries, and I think it would be best to write a subroutine for it. Something like insertRow($table, @stuff_to_insert). But how can I make the subroutine dynamic when it comes to the @stuff_to_insert, which can be anything from 1-5 arguments? ...

How can I write a subroutine for DBI updates with varying number of arguments?

Hi! I'm writing a subroutine for DBI updates, and are having some trouble figuring out how to add placeholder and stuff... I have this: sub row_update { my $table = shift; my %updates = @_; my $placeholders = ... $dbh->do("UPDATE $table SET (foo) WHERE (bar)") etc... } Any ideas? I just want a simple update function where...

How do I handle a varying number of items from a database query?

Effectively a duplicate of: How can I display data in table with Perl The accepted answer there applies here. So do some of the alternatives. I am trying to run raw database queries from Perl program and display results to the user. Something like select * from table. I want to display the information in a HTML table. The columns i...

How can I use DBI to execute a "\copy from remote table" command in Postgres?

I need to copy from a remote postgres server to a local one. I cannot use any ETL tools; it must be done using Perl with DBI. This data will be large so I don't want to use "select from source" and "insert into local". I was looking to use COPY to create a file but this file will be created on the remote server. I can't do that either. I...

Ruby: Mysql timestamp/datetime problem

Is there solution for '0000-00-00 00:00:00' problem, without changing table? I have "[]" in this query: dbh.select_all("select j.n, j.name, j.dsc, j.flag, j.td from job j where j.td='0000-00-00 00:00:00'") I look solution for this: http://rubyforge.org/tracker/index.php?func=detail&aid=22243&group_id=234&atid=967 ...

Ruby Connecting to SQL Server 2005

I want to connect to a sql server 2005/db with ODBC through ruby using windows authentication. I have the gems dbi-0.4.1 and dbd-odbc-0.2.4 installed. What do I need to do for a connection string? So far I haven't been able to get it to work. DBI.connect("DBI:ODBC:Data Source=#{server};Integrated Security=SSPI;Initial Catalog=#{db}") ...

How can I have DBIC persistent database connection in mod_perl?

I am using mod_perl for my web application. Currently, I plan to use a mysql database across the network. In every CGI request to display_customer_transaction.cgi, my script will Open up database connection across network Perform query on the database using SQL statement Analysis the data retrieved from database Print out the data in H...

Is there a non-relational equivalent of DBI in Perl?

The Perl DBI module lets me connect to many different types of SQL database transparently. Is there an equivalent Perl module for non-relational key-value pair databases? For example, an interface that might let me start developing with a BerkeleyDB (or even just a Perl hash?) but switch to something like memcachedb or CouchDB or even ...

Can I display the output of MySQL using CGI?

HI, I have written a program in Perl using the DBI module. Can I display the output of MySQL using CGI? If so please help me. program: #!/usr/bin/perl -w use DBI; print "Content-type:text/html\n\n"; $db_handle = DBI->connect("dbi:mysql:database=CYP1B1;host=localhost:192.168.3.93;") or die "Couldn't connect to database: $DBI::e...

How do I check if more than one record has been returned from DBI query in Perl?

Hi, I searched for this on the site already but couldn't locate anything that answered my question. Which brings me to the details: I am querying database using a simple select query, realistically (given the scenario) it should never return more than about 5 to 6 records. I just want to check if more than one has been returned. And if...

Where to put ruby .gem files so that Shoes.setup can find them?

A lot of questions have been asked about gem support in Shoes, but none have answered where to put them. I've got Shoes Raisins 1134 on Windows XP, and I've downloaded dbi-0.4.1.gem and am trying to get the following to work: Shoes.setup do gem 'dbi' end require 'dbi' Shoes.app ... end When I run this, I get the dialog that sa...

Why can't I disconnect my database handles with DBD::DB2?

My perl script is slow but works. I am considering throwing it out and building a true DB2 application with proper binds and such; however, in the meantime I want this to work as a place holder. I looked at this documentation and it mentions: $dbh->disconnect(); However I cannot use it because it throws this error. Can't locate...

How can I make hundreds of simultaneously running processes communicate with a database through one or few permanent sessions?

Long speech short: How can I make hundreds of simultaneously running processes communicate with a database through one or few permanent sessions? The whole story: I once built a number crunching engine that handles vast amounts of large data files by forking off one child after another giving each a small number of files to work on. Fil...

Why does Perl's DBI complain about "Fetch attempted on unopen cursor"?

Here is my script: $db_handle=DBI->connect("$dbstr", "", "", {RaiseError => 0, AutoCommit => 0, PrintError => 1}) || die "Connect error: $DBI::errstr" ; $result=$db_handle->selectrow_array("set isolation to dirty read"); Note: $dbstr is a valid database name. I am not a database programmer. What am I doing wrong which is causing the...

How can I avoid the program quitting when Perl's DBI encounters an error preparing a statement?

I'm making a script that goes through a table that contains all the other table names on the database. As it parses each row, it checks to see if the table is empty by select count(*) cnt from $table_name Some tables don't exist in the schema anymore and if I do that select count(*) directly into the command prompt, it returns...

Python equivalent of perl's dbi/DBD::Proxy access? (Perl DBI/DBD::Proxy for Python)

I have a perl script that interfaces with an existing database (type of database is unknown) through the DBI module, that I would like to access in python 2.6 on winXP. The perl code is: use DBI; my $DSN = "DBI:Proxy:hostname=some.dot.com;port=12345;dsn=DBI:XXXX:ZZZZZ"; my $dbh = DBI->connect($DSN); Can this be translated into a pyth...

How can I get a row count in DBI without running two separate calls to process?

I'm running DBI in Perl and can't figure out how, when I run a prepared statement, I can figure out if the returned row count is 0. I realize I can set a counter inside my while loop where I fetch my rows, but I was hoping there was a less ugly way to do it. ...

Perl+Postgresql: a function does not return a value if RAISE NOTICE is present

Hi, I noticed that when I call a PL/PgSQL or PL/Perl function from a Perl script using DBI, it does not return a value if a RAISE NOTICE or elog(NOTICE) is used in the function. To illustrate: A simple table: CREATE TABLE "public"."table1" ( "fld" INTEGER ) WITHOUT OIDS; A simple function: CREATE OR REPLACE FUNCTION "public"."fun...