CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Retrieve data from MSSQL database and Insert it into MYSQL database

 



subodhkeskar
New User

Jun 13, 2002, 1:33 PM

Post #1 of 2 (3091 views)
Retrieve data from MSSQL database and Insert it into MYSQL database Can't Post

Hi Friends,

I have some problem with my code. If anybody can help me with this i will really appreciate it.

Here i am trying to retrieve all the data from a MSSQL database and inserting it in MYSQL database using a Perl Script (DBI). Till now i have successfully retrieved the data from the tables of the MSSQL database but i am unable to insert it into the MYSQL database. It is giving the following error.....

DBD::mysql::st execute failed: No Database Selected at abc.pl line 45.

Hood Server connected successfullyHelloCustomer ID = 10807, Name = Gresham Publications, Address1 = P.O. Box 807, Address2 = , City = Kill Devil Hills, State = NC,Zip = 27948, Phone = 2524415091


I think i am unable to connect the second database ...

DBD::mysql::st execute failed: No Database Selected at abc.pl line 45.

or i think the way i have written the query ( in the highlighted are below) is wrong.

Can any body please help me with this problem. Please see the highlited part of the code below. I am sure error lies here only.

#!/usr/local/bin/perl
use DBI();

# Connect To Database
# * The DBI interface to MySQL uses the method "connect" to make a
# * connection to the database. It takes as it's first argument
# * the string "DBI:mysql:database:hostname", where database is equal
# * to the name of your database, and hostname to the server that it's
# * located on. The second and third arguments, respectively, should
# * be your account username and password. The connection is assigned
# * to a variable that is used by most other methods in the module.
$db = DBI->connect("DBI:Sybase:server=blue", platuser, gobigbird);

# Execute a Query
# * executing a query is done in two steps. First,
# * the query is setup using the "prepare" method.
# * this requires the use of the variable used to
# * initiate the connection. Second, the "execute"
# * method is called, as shown below.
$query = $db->prepare("SELECT id, name,addr1,addr2,city,state,zip,phone FROM cus
tomer");
$query->execute;

# How many rows in result?
# * the "rows" method using the variable name the
# * query was executed under returns the number
# * of rows in the result.
$numrows = $query->rows;

# Display Results
# * the fetchrow_array method executed on the
# * query returns the first row as an array.
# * subsequent calls return the other rows in
# * sequence. It returns zero when all rows have
# * been retrieved.
while (@array = $query->fetchrow_array)
{
($field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8) = @array;


$dbh = DBI->connect("DBI:mysql:host=hood", pinndb, webdb)|| die "Connect failed:$DBI::errstr\n";

print"Hood Server connected successfully";
$sql = "INSERT into custdata values (\"$field1\", \"$field2\", \"$field3\", \"$field4\", \"$field5\",\"$field6\",\"$field7\", \"$field8\")";
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->finish;
$dbh->disconnect;


# print "Customer ID = $field1, Name = $field2, Address1 = $field3, Address2 = $field4, City = $field5, State = $field6,Zip =$field7, Phone = $field8 \n";
}

# Cleaning Up
# * with the DBI module, it is a good idea to clean up by
# * explicitly ending all queries with the "finish" method,
# * and all connections with the "disconnect" method.
$query->finish;
$db->disconnect;

exit;


Kanji
User / Moderator

Jun 16, 2002, 5:15 PM

Post #2 of 2 (3087 views)
Re: [subodhkeskar] Retrieve data from MSSQL database and Insert it into MYSQL database [In reply to] Can't Post

As the error says, there's no database selected to insert your data into. You need to change this ...


Code
$dbh = DBI->connect("DBI:mysql:host=hood", pinndb, webdb)


... to something like ...


Code
$dbh = DBI->connect("DBI:mysql:host=hood;database=dbname", pinndb, webdb)


... where dbname is the name of your db (perhaps pinndb?).
--k.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives