looping through an sql query using win32::odbc 
Author Message
 looping through an sql query using win32::odbc

I have been using the win32 odbc module to write the contents on a file
into an access database unfortunately it will only write the first line of
that file...What I am having trouble figuring out is how to construct a
loop which will read the next line of that file and then run the sql to
import it into access.  It seems inefficient to do it this way but after
trying to open another $DSN and running into permission errors when
writing that data this seems the logical solution.  Below is the script I
have put together.  Any suggestions would be appreciated.

Bob

#!/usr/bin/perl

use Win32::ODBC;

$data = "C:/Perl/down.txt";
# open reads and closes files

close(FILE);

# separates each line of the file and breaks each line up into an array

   $_ = $line;

Quote:
}

#open database

$DSN ="Bob";
if (!($db = new Win32::ODBC($DSN))){
   print "Error connecting to $DSN\n";
   print "Error: " . Win32::ODBC::Error() . "\n";
   exit;

Quote:
}

# execute SQL command

$sql = "INSERT INTO test (1,2,3) VALUES ($ok[0],'$ok[1]','$ok[2]');";
if ($db->Sql($sql)){
   print "SQL failed.\n";
   print "Error: " . $db->Error() . "\n";
   $db->Close();
   exit;
 }

#  close the connection to the database:
$db->Close();



Mon, 06 Mar 2000 03:00:00 GMT  
 looping through an sql query using win32::odbc

[cc'd automagically to original author]


Quote:
>I have been using the win32 odbc module to write the contents on a file
>into an access database unfortunately it will only write the first line of
>that file...What I am having trouble figuring out is how to construct a
>loop which will read the next line of that file and then run the sql to
>import it into access.  It seems inefficient to do it this way but after
>trying to open another $DSN and running into permission errors when
>writing that data this seems the logical solution.  Below is the script I
>have put together.  Any suggestions would be appreciated.

Hmm... That should be doable... Let's see.

Quote:
>#!/usr/bin/perl

>use Win32::ODBC;

Don't forget your "use strict" -- it'll have fun with your code, but
you'll be safer in the long run.

Quote:
>$data = "C:/Perl/down.txt";
># open reads and closes files
>open (FILE, $data);

open (FILE, $data) or die "Open Failed: $!";

Always check return codes from open().

Read each line into a list. Okay for non-huge files.

Quote:
>close(FILE);

You might want to check the return code from close(), too. I sometimes
do, I sometimes don't. YMMV.

Quote:
># separates each line of the file and breaks each line up into an array

>   $_ = $line;

That line probably isn't necessary.

Quote:

>}

These must be space-delimited records, then...

Quote:
>#open database

>$DSN ="Bob";

Not Microsoft Bob, I hope. :-)

Quote:
>if (!($db = new Win32::ODBC($DSN))){
>   print "Error connecting to $DSN\n";
>   print "Error: " . Win32::ODBC::Error() . "\n";
>   exit;
>}

Looks good.

Wrap this whole thing in something like:


 ...

Quote:
># execute SQL command

>$sql = "INSERT INTO test (1,2,3) VALUES ($ok[0],'$ok[1]','$ok[2]');";
>if ($db->Sql($sql)){
>   print "SQL failed.\n";
>   print "Error: " . $db->Error() . "\n";
>   $db->Close();
>   exit;

Move the exit() outside of the loop, of course.

Quote:
> }

>#  close the connection to the database:
>$db->Close();

Jeremy
--
Jeremy Zawodny
Internet Technology Group
Information Technology Services
Marathon Oil Company, Findlay Ohio

http://www.marathon.com/

Unless explicitly stated, these are my opinions only--not those of my employer.



Mon, 06 Mar 2000 03:00:00 GMT  
 looping through an sql query using win32::odbc


what you want.

# separates each line of the file and breaks each line up into an array

   $_ = $line;

Quote:
}

-mike de beer



Wed, 08 Mar 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. looping through an sql query using win32::odbc

2. SQL Pass-thru query with Win32::ODBC?

3. LONG: Req: Help in retrieving values from SQL Server stored procedure using Win32::ODBC

4. SQL error 170 using Win32::ODBC

5. Win32::ODBC SQL and using date

6. Win32::ODBC, Perl SQL vs. standard SQL

7. DBD:ODBC sql query 'where' problems

8. Strange behavior using Perl's system() function to query SQL Server with osql

9. Testing for an empty result from an Access SQL query using PERL

10. easily generating SQL queries using perl?

11. How do I limit the number of rows in sql query using Win32::ODBC?

12. Win32::ODBC/MSAccess query

 

 
Powered by phpBB® Forum Software