Creating a new db using DBI
All - Using MySQL under Linux. I'd like to create a new database using the DBI. I thought I could connect to the test database, create the new database, assign grants, then reconnect to the new database. I'm trying to connect to the test database without a username or password and I'm getting an error: Access denied for user user@hostname. I'm trying to use the following connect call: $database = "test"; $databaseString = "DBI:mysql:$database:$ip:$port"; $dbh = DBI->connect($databaseString, "", ""); or die "..."; Any suggestions? Thanks, Mark
Remote MySQL Backups
All - Accessing 20 MySQL databases under Win2k via DBI from a Win2k box. After extracting all of the data from each of the remote databases, I'd like to remotely dump the tables for backup. If I was local on each machine, I could use mysqldump, however, I was wondering if there was a DBI trick I didn't know about. Thought about using the telnet module to perform these tasks, however, would prefer to do it under DBI. Any suggestions? Thanks, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
Populating indexed versus non indexed tables
All - I need to populate a series of tables using MySQL under Win2k. The source data will be in ~20 files. In terms of time, is it best to populate the tables and create the indexes afterwards? Or, should I create the indexes when I create the tables? I know that it takes a little longer populating an indexed table. But is it still faster than creating indexes afterwards? Some of the tables might be fairly large (a few million records each). Thanks, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
DBI, Access, and DATETIME
All - I'm trying to use DBI to read a MS Access db. One of the columns in
the DB is a Date/Time type. I'm trying to query on a range of times.
The following works in MySQL off the command line where S_TIME is a DATETIME
column, so I thought the MS Access call would be the same:
select * from sa where (S_TIME > (timestamp'2001-12-04 13:00:00'))
Trying to talk to Access, I used the following:
$sth_select = $dbh_MSAccess->prepare("SELECT * FROM $table where (S_TIME >
(timestamp('2001-12-04 13:00:00') ))");
However, I get the following error (it doesn't know what the timestamp
function is):
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Undefine
d function 'timestamp' in expression. (SQL-37000)(DBD: st_execute/SQLExecute
err
=-1) at D:\Agile\Projects\FBCB2\Apr_02\Data\accToMySql_vload2.pl line 171.
SELECT execute ERROR for sa table: DBI::db=HASH(0x236cc78)->errstr
Is this a quirk with Access - seemed to work ok with MySQL.
Any suggestions/workarounds?
Thanks,
Mark
-
Mark Riehl
Agile Communications, Inc.
Email: [EMAIL PROTECTED]
Office: (732) 528-9305
Fax: (732) 528-9308
Cell: (732) 996-7630
Transferring from Access to MySQL
All - We've got several Access databases that have ~2 million records and each record has ~20 fields. Looking for the most efficient way to do this. As far as I can tell, I have two options: 1. Open a connection to each database, select * from the Access table, then push each row into MySQL. 2. Export the table from Access, then use a load data file to pull it into MySQL. Each of the exported files is ~ 1 GB. Any other suggestions? How would you do this? Thanks, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
Float vs. double in MySQL
All - Running under Win2k. Ran into a strange situation storing floating point numbers. Trying to store the following data in a column in a table: (1007575930.907, 1007575930.951, 1007575931.029). Originally they were stored as floats, but, noticed that what ended up in the table was not the data that we inserted. I created a table with two columns, one float, one double. When I store the numbers above (once as a float, once as a double), the float value seems to be rounded, can't quite see what's happening. Here's what's actually in the database: +++ | float_col | double_col | +++ | 1007575936.000 | 1007575930.907 | | 1007575936.000 | 1007575930.951 | | 1007575936.000 | 1007575931.029 | +++ These numbers aren't too large to fit into a float - any idea what's causing the problem? Am I missing something obvious? Thanks for the help, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
FW: Dynamic DSN generation?
All - We've got the task of porting a large number (possibly several hundred) of Access databases to MySQL, all under Win2k. We can access the Access data using DBI, no problems there. The only step that isn't automated in our process is the DSN creation. Is there a way to automate the DSN creation without having to run the ODBC Data Source Administrator for each database? Thanks, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
Placeholders and select
All - Running ActiveState under Win2k, database is MySQL.
$sth_node = $dbh->prepare(qq{SELECT NODE_HOSTNAME, NODE_ADMIN_USERNAME, FROM
NODE WHERE (NODE_ID = ?)
VALUES (?)} ) ||
warn "Error at prepare: $dbh->errstr";
(following is inside a loop)
$sth_node->execute($thisRouter) <<<- Line 141
or warn "Can't execute SQL statement : $dbh->errstr";
Uncaught exception from user code:
execute called with 1 bind variables when 2 are needed at ... line
141.
Why does it say that I need 2 bind variables? I know that there is an
issues with selects, placeholders, and NULLs, but I don't think that this is
the issue here.
Any suggestions?
Thanks for the help,
Mark
-
Mark Riehl
Agile Communications, Inc.
MySQL Question
All - Running ActiveState under Win2k. I've got a Tk script that acts as a front end for SQL queries, and I'm using dump_results. print "\$sql_statement = $sql_statement\n"; my $sth = $dbh->prepare($sql_statement); # the sql result is store in $sth $sth->execute() or die "Can't execute SQL statement : $dbh->errstr"; my $csv; my $SH = IO::Scalar->new(\$csv); my $rows = $sth->dump_results(80, "\n", ",", $SH); $SH->close; I get the following in the console: $sql_statement = SELECT * FROM network Database handle destroyed without explicit disconnect at C:/Perl/site/lib/Tk.pm line 217. Tk::Error: Not a GLOB reference at C:/Perl/site/lib/DBI.pm line 614. [\&main::run_query] Tk callback for .button Tk::__ANON__ at C:/Perl/site/lib/Tk.pm line 217 Tk::Button::butUp at C:/Perl/site/lib/Tk/Button.pm line 111 (command bound to event) I establish the connection and successfully make the query - I can see the results when I have the trace on, but I can't seem to track down the problem. Works fine locally, problem using it remotely. Account, password, and permissions are correct, no firewalls. Any suggestions? Thanks, Mark - Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] Office: (732) 528-9305 Fax: (732) 528-9308 Cell: (732) 996-7630
dump_results() Question
All - I'm running under ActiveState on Win2k and using MySQL. I'm using dump_results() to print the results of a database query. I need to print the same result to multiple files with different delimiters and formats. Questions 1. Is there a way to use the same statement handle result set without calling another execute()? Do I need an execute() for each dump_results() call? I'd like to make one execute call, and write the results to multiple files. 2. I'm trying to tab delimit the file, and used the following statement: my $rows = $sth->dump_results(80, '\n', '\t', $SH); However, I end up with '\t' (single quote \t single quote) as my delimiter, not an actual tab. Any suggestions? Thanks for the help, Mark
MySQL DBI Error
All - I'm using ActiveState, MySQL, and Win2k. I'm trying to insert data
into the following table:
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| nodeID | tinyint(3) unsigned | YES | | NULL| |
| rxNetTime| mediumint(9)| YES | | NULL| |
| radioLat | float | YES | | NULL| |
| radioLon | float | YES | | NULL| |
| radioHeading | float | YES | | NULL| |
| antIndex | tinyint(3) unsigned | YES | | NULL| |
| rxPower | tinyint(3) unsigned | YES | | NULL| |
| dataRecord | blob| YES | | NULL| |
+--+-+--+-+-+---+
The statement I'm using is the following:
$sql_statement = "INSERT INTO rxPkt VALUES(\'$nodeId\', \'$rxNetTime\',
\'$rxLat\', \'$rxLon\', \'$rxHeading\', \'$antIndex\', \'$rxPower\',
\'$bin_msg\')";
And I get the following error:
-> prepare for DBD::mysql::db (DBI::db=HASH(0x210496c)~0x2104924 'INSERT
INT
rxPkt VALUES('1', '1', '1.1104488373', '3.3301544189',
'5.54866790
. >รณ?QRLt?.8?@UUT@??...
..
!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]
_`abc')')
etting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x1a7f118) at unpack_v6.pl line 522.
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x1d158a8)~INNER)
<- DESTROY= undef at unpack_v6.pl line 523.
-> execute for DBD::mysql::st (DBI::st=HASH(0x1a7f118)~0x2103d64)
-> dbd_st_execute for 02103d58
ou have an error in your SQL syntax near
'()*+,-./0123456789:;<=>?@ABCDEFGHIJKL
NOPQRSTUVWXYZ[\]^_`abc')' at line 1 error 1064 recorded: You have an error
in y
ur SQL syntax near
'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`ab
')' at line 1
<- dbd_st_execute -2 rows
!! ERROR: 1064 'You have an error in your SQL syntax near
'()*+,-./012345678
:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abc')' at line 1'
I'm a little confused because this same call is currently working for nearly
the same table (same contents, different table name). The last field in the
record is a binary file, ~150 bytes long.
Not quite what the syntax error is that it is complaining about. Any
suggestions?
Thanks,
Mark
Signal Woes
All - I'm under Win2k with ActiveState binary 629, DBD-MySQL 1.22, and DBI
1.14. I can catch signals w/o a problem before I connect to the database.
However after I connect and immediately disconnect, I can't catch signals
anymore.
I know that signals aren't 100% supported under Windows. However, I'm not
trying to do anything too difficult, just catch a CTRL-C from the keyboard.
I'm just wondering why it works fine if my connect/disconnect statements are
commented out.
Anyone have any ideas?
Thanks for the help,
Mark
###
#!perl -w
use strict;
use diagnostics;
use DBI;
my $quit = 0;
use sigtrap qw(handler my_handler normal-signals error-signals);
while ($quit == 0) {
print "in while()\n";
# Comment out the next two lines and it works fine.
my $result = query_db();
print $result;
my $i = 0;
for ($i = 0; $i < 2; $i++) {
sleep (1);
print ". ";
}
}
sub query_db {
# Build the database connection string.
my $dbstring = "DBI:mysql:database01:127.0.0.1:3306";
# Connect to the database.
my $dbh = DBI->connect($dbstring,"user","password")
or die "Can't connect to mysql on 127.0.0.1: $DBI::errstr\n";
# Assume result from the db is named result.
my $result = "Test";
print "Disconnecting \$dbh\n";
$dbh->disconnect()
or warn "Disconnection failed: $DBI::errstr\n";
return ($result);
}
sub my_handler {
print "Caught the CTRL-C\n";
++$quit;
}
--
Mark Riehl
Agile Communications, Inc.
Email: [EMAIL PROTECTED]
Creating a new database using DBI?
All - I'm using MySQL, Win2k, and ActiveState Perl. Is there a way to create a new database (not a table) using DBI? Or, do I need to use mysqladmin, create, ... I know that I wouldn't have a database name to use in the connect string, so would I be able to connect? If this is possible, anyone have any examples? Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] Business: (732) 528-9305 Fax: (732) 528-9308 Cell: (732) 996-7630
RE: PPM available for DBD-Oracle 1.12 (OCI8)
Bill - Looks like you dropped a /ppm off the repository path in your original mail (look at Ilya's original post). Here's what just worked for me on a Win2k machine: D:\>ppm PPM interactive shell (2.1.5) - type 'help' for available commands. PPM> set repository XMLPROJ http://www.xmlproj.com/PPM PPM> search DBD-Oracle* Packages available from http://www.xmlproj.com/PPM: DBD-Oracle-1_12 [1.12] DBD-Oracle 1.12 Packages available from http://ppm.ActiveState.com/cgibin/PPM/ppmserver.pl?urn:/ PPMServer: DBD-Oracle [1.06] Oracle database driver for the DBI module DBD-Oracle8 [1.06] Oracle 8 database driver for the DBI module PPM> install DBD-Oracle-1_12 Install package 'DBD-Oracle-1_12?' (y/N): y Installing package 'DBD-Oracle-1_12'... Bytes transferred: 98144 Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] Business: (732) 528-9305 Fax: (732) 528-9308 Cell: (732) 996-7630 > -Original Message- > From: Thater, William [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, November 20, 2001 9:13 AM > To: Etienne Marcotte > Cc: [EMAIL PROTECTED]; Ilya Sterin > Subject: Re: PPM available for DBD-Oracle 1.12 (OCI8) > > > Etienne Marcotte wrote: > > >ppm install DBD-Oracle-1_12 > > > >she made a typo it's 1_12 > > > still doesn't find it. > > > > > > >Etienne > > > >bill thater wrote: > > > >>Ilya Sterin wrote: > >> > >>>PPM is now available for DBD-Oracle 1.12 compiled with > Oracle 8 libs. > >>> > >>>This is how you can install... > >>> > >>>You must set your repository to http://www.xmlproj.com/PPM > by doing... > >>> > >>>ppm set repository http://www.xmlproj.com > >>> > >>> > >>>Then you can install with > >>> > >>>ppm install DBD-Oracle-0_12 > >>> > >>> > >>>Big thanks to Maarten Koskamp for compiling this version. > >>> > >>>Ilya > >>> > >>OK, looks like i'm having a caffeine free moment here, but it's not > >>finding a PPD file. is the set command right? it's not > the same as the > >>one for the DBI PPM install. > >> > >>-- > >>-- > >>Bill "Shrek" Thater ORACLE DBA > >>Telergy,Inc. [EMAIL PROTECTED] > >> > > >>You gotta program like you don't need the money, > >>You gotta compile like you'll never get hurt, > >>You gotta run like there's nobody watching, > >>It's gotta come from the heart if you want it to work. > >> > > >>Every program is a part of some other program, and rarely fits. > >> > > > > > -- > -- > Bill "Shrek" Thater ORACLE DBA > Telergy,Inc. [EMAIL PROTECTED] > -- > -- > You gotta program like you don't need the money, > You gotta compile like you'll never get hurt, > You gotta run like there's nobody watching, > It's gotta come from the heart if you want it to work. > -- > -- > Every program is a part of some other program, and rarely fits. > > > >
DBI and Signals
All - I'm running under Win2k. I know that signals aren't totally supported
under Windows, but I've got some questions related to DBI.
In the following script, I can't seem to catch the SIG INT after a
connection to the database is made (even w/o making any queries). Without
the connect calls, I can catch a SIG INT without a problem.
Right now, the only way to stop this script is to use the task manager and
kill it - is there a better way to do this?
Thanks,
Mark
###
#!perl -w
use strict;
use diagnostics;
use DBI;
my $quit = 0;
$SIG{INT} = \&catch_int;
while (!$quit) {
print "in while()\n";
# Comment out these two lines and it works fine.
##
my $result = query_db();
print $result;
##
my $i = 0;
for ($i = 0; $i < 10; $i++) {
sleep (1);
print ". ";
}
}
sub query_db {
# Build the database connection string.
my $dbstring = "DBI:mysql:database01:127.0.0.1:3306";
# Connect to the database.
my $dbh = DBI->connect($dbstring,"user","password")
or die "Can't connect to mysql on 127.0.0.1: $DBI::errstr\n";
# Assume result from the db is named result.
my $result = "Test";
print "Disconnecting \$dbh\n";
$dbh->disconnect()
or warn "Disconnection failed: $DBI::errstr\n";
return ($result);
}
sub catch_int {
my $sig = shift;
print "Caught $sig\n";
$quit++;
}
--
Mark Riehl
Agile Communications, Inc.
Email: [EMAIL PROTECTED]
Business: (732) 528-9305
Fax: (732) 528-9308
Cell: (732) 996-7630
RE: mod_perl and DBI
Ilya/Doug - Thanks for the info. Sorry for not being too clear. Our application will have an upload of a large (~5 MB) XML file, it will need to be parsed, and then stored in the database. The database will then be manipulated by another process, data will be pulled out, and an output XML file will be generated. We're using the database as a common are to share the data. One last question. When you mentioned native load facility, do you mean bypassing DBI and using the database directly (e.g., running INSERT commands from the MySQL command line rather than through DBI)? Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] > -Original Message- > From: Sterin, Ilya [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 17, 2001 1:56 PM > To: 'Wilson, Doug '; ''Mark Riehl' '; ''[EMAIL PROTECTED]' ' > Subject: RE: mod_perl and DBI > > > Right, Doug. I was not clear on the question myself. Later > after rereading > I realized the batch load part of it. You are correct that the native > loading facilities are much faster than DBI. > > Though my answer was directed towards the "This isn't online > system... and > the mod_perl vs. standard Perl interpreter" part of the question:-) > > Ilya > > -Original Message- > From: Wilson, Doug > To: 'Mark Riehl'; '[EMAIL PROTECTED]' > Sent: 10/17/01 11:24 AM > Subject: RE: mod_perl and DBI > > > Let me clarify that I think Ilya and I are answering > different questions, and not positive which question was > being asked :) > > For a prolonged process like a large SQL load, mod_perl won't help > much; but when the scripts are shorter (and other factors like > perl load/compile and database connection & statement handle > preparation time are more significant), it can be beneficial. > > > From: Wilson, Doug > > > All - We're putting together a Web-based application using > > > Perl CGI, Apache, > > > and DBI to connect to a MySQL database (all running on one > > > machine under > > > Win2k). Off the command line, I can load a source file > populate the > > > database with ~25000 records in less than 5 seconds, however, > > > using CGI and > > > the same DBI code takes ~30 seconds. This isn't an online > > > system and won't > > > receive many hits - only one or two users at a time. > > > > Native load facilities are usually quicker than the equivalent > > DBI code. mod_perl won't help much, if at all. Best solution > > might be to have your CGI download the file and use the native > > load facility. >
mod_perl and DBI
All - We're putting together a Web-based application using Perl CGI, Apache, and DBI to connect to a MySQL database (all running on one machine under Win2k). Off the command line, I can load a source file populate the database with ~25000 records in less than 5 seconds, however, using CGI and the same DBI code takes ~30 seconds. This isn't an online system and won't receive many hits - only one or two users at a time. Question - If I switch to mod_perl, can I expect to see an increase in speed (specifically in the DBI portion) with only one or two users? Or, will I only see an increase in speed if I had a large number of simultaneous users? Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
Anyone using PostgreSQL?
All - Just curious if anyone is successfully using DBI with PostgreSQL under Linux? I don't see a lot of traffic about it and may need to use it in the near future. Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
DBI and Signals
All - I'm running Perl 5.6.0 and DBI 1.14 under Win2k. I've got a problem
with signals. It seems as if I can't catch any signals (e.g., Ctrl-C) after
making a connection to my database (MySQL). I need to manually use the Task
Manager (Ctrl - Alt - Del) to stop the process.
I've deleted all unnecessary code, including the database query itself (yes,
the same problem occurs if a query is left in place). If I comment out the
connect and disconnect statements, I don't have any problems catching the
signal.
I need this set up inside the while loop because this code will eventually
be part of a server, accepting client connections from Perl and C++ clients.
Any suggestions?
Thanks,
Mark
*
Here's the code I'm running (it will run if you adjust $dbstring, user, and
password):
#!perl -w
use strict;
use diagnostics;
use DBI;
my $quit = 0;
$SIG{INT} = \&catch_int;
while (!$quit) {
print "in while()\n";
my $result = query_db();
print $result;
sleep(2);
}
sub query_db {
# Build the database connection string.
my $dbstring = "DBI:mysql:database01:127.0.0.1:3306";
# Connect to the database.
my $dbh = DBI->connect($dbstring,"user","password")
or die "Can't connect to mysql on 127.0.0.1: $DBI::errstr\n";
# Assume result from the db is named result.
my $result = "Test";
print "Disconnecting \$dbh\n";
$dbh->disconnect;
return ($result);
}
sub catch_int {
my $sig = shift;
print "Caught $sig\n";
$quit++;
}
--
Mark Riehl
Agile Communications, Inc.
Email: [EMAIL PROTECTED]
Easiest way to tell if a table already exists?
All, What's the easiest way to check and see if a table already exists in a database? I'm running MySQL on a Win2k box. The New Riders MySQL book has the following statements: SELECT COUNT(*) FROM table_name SELECT * FROM table_name WHERE 1=0 Each statement will succeed if the table exists. Any other way (other than calling $dbh->tables() and looping through the results? Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
DBI in sys design
All, I'm developing an app in C++ that requires database access. Two of the big constraints are that my system must be portable (Win2k, Linux, and Solaris) and be able to connect to a variety of different databases (Oracle and MySQL are the first two). As I see it, I have two options. 1. Is this possible - can I embed DBI into a C++ app so that I have access to DBI from C++? 2. Would it be easier to write a small Perl app with the DBI interface that will accept a socket connection from the C++ app - kind of like a middleware between the C++ app and the database interfaces? I'd prefer option 1. Any suggestions? Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
Embedding DBI into a C++ app?
All, Looking through some examples in the v3 Camel book of the Embed module and I have a few questions: 1. Can I embed the DBI module into a C++ app? I'd love to have the DBI flexibility. 2. If so, any tricks I need to know about? Any other sources of info I should look at? I've gone some through some of the CPAN docs. I'm looking for a solution that will let me (easily) run the same C++ (headless daemon) app under Win2K, Linux, and Solaris that can to connect to Oracle and MySQL. If this works, it would be just what I'm looking for. Thanks, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
Looking for suggestions
All, I'm trying to design a 3-tier app (client using browser, web server with CGI/Perl/DBI, and a C++ app sitting somewhere on the LAN). In a nutshell, the user will need to browse info from a database, make some selections, and pass the data to the C++ app for calculations. Reports will then be sent back to the browser. Instead of passing data from the database to the web server and then to the C++ app, can I provide access to the database directly from the C++ app via DBI? What's the cleanest way to do this? My initial thoughts would be a Perl app on the same box as the C++ app, open a socket from the C++ app to the Perl app, and take advantage of the DBI. The large number of databases supported by the DBI really helps me out. Any ideas? Thanks for the help, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
DBI Capability Questions
All, New to Perl and DBI, but I've got a few higher level questions. Yes/no answers would be more than sufficient. I'm looking to build a Web-based app, and I'd like use CGI/Perl to act as a middleware between the user (via the browser) and a C++ app sitting somewhere on the LAN. I know that I can use Perl to open sockets to the C++ app and pass data back and forth. Questions: 1. I'd like to provide the user with results to SQL queries to a few databases (Oracle, MySQL, ...). Can the Perl DBI be set up so that it can easily switch between databases? For example, if the user has a form that sends the database type, host, database name, and port info to the Perl DBI, will the database be transparent to the user (assuming all have the same tables present)? 2. If #1 is true, can I load multiple modules to support the different databases, or would they conflict with each other? 3. Are some database interfaces better supported than others? 4. If I set up the Perl DBI using CGI on a Win 2K machine, it should work the same on a Linux or Solaris box, correct? Thanks for the help, Mark -- Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
