Stack Overflow
Stack Overflow

Questions tagged [dbi]

DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used. For the equivalent R package, use the tag r-dbi.

When Perl code needs access to a relational database, it usually accomplishes this by using the DBI CPAN module.

DBI's role is similar to that of JDBC for Java, or ODBC, in that it provides a set of common functions, classes and methods used to talk to databases, with a collection of specific backend implementations known as DBI drivers that implement this for particular types of database systems.

These drivers are modules with a name that starts with DBD::.

Many such modules are available on CPAN; popular ones include:

Unrelated tags

Please re-tag questions about the R package for database access with the tag .

1357 questions
35
votes
3 answers

How can I fetch the last row I inserted using DBI?

How can I fetch the last row that was inserted using DBI (DBD::mysql)? Code sample: my $sth = $dbh->prepare('INSERT INTO a ( x, y, z ) VALUES ( ?, ?, ? )'); $sth->execute( $x, $y, $z ); How can I get access to the data that was inserted by the…
Alan Haggai Alavi
  • 72,802
  • 19
  • 102
  • 127
23
votes
1 answer

Why does DBI's do method return "0E0" if zero rows were affected?

I ran into a problem when running code similar to the following example: my $rows = $dbh->do('UPDATE table SET deleted=NOW() WHERE id=?', undef, $id) or die $dbh->errstr; if (!$rows) { # do something else } Since the docs state that do returns…
simbabque
  • 53,749
  • 8
  • 73
  • 136
20
votes
7 answers

How do I know how many rows a Perl DBI query returns?

I'm trying to basically do a search through the database with Perl to tell if there is an item with a certain ID. This search can return no rows, but it can also return one. I have the following code: my $th = $dbh->prepare(qq{SELECT bi_exim_id FROM…
The.Anti.9
  • 43,474
  • 48
  • 123
  • 161
20
votes
8 answers

How can I print the SQL query executed after Perl's DBI fills in the placeholders?

I'm using Perl's DBI module. I prepare a statement using placeholders, then execute the query. Is it possible to print out the final query that was executed without manually escaping the parameters and dropping them into the placeholders? Thanks
aidan
  • 9,310
  • 8
  • 68
  • 82
19
votes
6 answers

How can I handle unicode with Perl's DBI?

My delicious-to-wp perl script works but gives for all "weird" characters even weirder output. So I tried $description = decode_utf8( $description ); but that doesnt make a difference. I would like e.g. “go live” to become “go live” and not…
edelwater
  • 2,650
  • 8
  • 39
  • 67
17
votes
3 answers

How can I fetch a single count value from a database with DBI?

The following code seems to be just too much, for getting a single count value. Is there a better, recommended way to fetch a single COUNT value using plain DBI? sub get_count { my $sth = $dbh->prepare("SELECT COUNT(*) FROM table WHERE..."); …
szabgab
  • 6,202
  • 11
  • 50
  • 64
16
votes
11 answers

Which Perl database interface should I use?

Is CPAN DBI the best database interface to use in Perl for general database use? Are there some better options?
David
  • 14,047
  • 24
  • 80
  • 101
15
votes
2 answers

WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version

As I have seen here, Beginning with MySQL 8.0.34, the automatic reconnection feature is deprecated. I have a lot of Perl scripts that connect to a MySQL database with something like: my $dbh = DBI->connect(…
Javier Elices
  • 2,066
  • 1
  • 16
  • 25
14
votes
6 answers

How can I get column names and row data in order with DBI in Perl?

I'm using DBI to query a SQLite3 database. What I have works, but it doesn't return the columns in order. Example: Query: select col1, col2, col3, col4 from some_view; Output: col3, col2, col1, col4 3, 2, 1, 4 3, 2, 1, 4 3, 2, 1,…
Benjamin Oakes
  • 12,262
  • 12
  • 65
  • 83
13
votes
1 answer

how to find ODBC driver-specific arguments

Is there a way to programmatically discover the named arguments for ODBC connections? For instance, using the "ODBC Driver 17 for SQL Server", the ODBC Data Source Administrator (in Windows) allows for authentication using a "Login ID" and…
r2evans
  • 141,215
  • 6
  • 77
  • 149
13
votes
4 answers

Does executing a statement always take in memory for the result set?

I was told by a colleague that executing an SQL statement always puts the data into RAM/swap by the database server. Thus it is not practical to select large result sets. I thought that such code my $sth = $dbh->prepare('SELECT million_rows FROM…
planetp
  • 14,248
  • 20
  • 86
  • 160
12
votes
3 answers

disk I/O error with SQLite

I have a (tiny) dynamic website that is (roughly) a Perl CGI script using a SQLite database. Package DBI is the abstraction layer used in Perl. About one week ago, I started to see this error message: disk I/O error(10) at dbdimp.c line 271 Since…
kevinarpe
  • 20,319
  • 26
  • 127
  • 154
12
votes
12 answers

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…
Yevgeny Simkin
  • 27,946
  • 39
  • 137
  • 236
12
votes
3 answers

Perl DBI insert multiple rows using mysql native multiple insert ability

Has anyone seen a DBI-type module for Perl which capitalizes, easily, on MySQL's multi-insert syntax insert into TBL (col1, col2, col3) values (1,2,3),(4,5,6),...? I've not yet found an interface which allows me to do that. The only thing I HAVE…
Jim
  • 1,499
  • 1
  • 24
  • 43
12
votes
7 answers

perl DBI and placeholders

I have this query select * from table where ID in (1,2,3,5...) How is it possible to build this query with the DBI using placeholders ? for example : my @list = (1, 2, 3, 4, 5); my $sql = "select * from table where ID in…
smith
  • 3,232
  • 26
  • 55
1
2 3
…
90 91