abhishes has asked for the wisdom of the Perl Monks concerning the following question:

Hello Perl Monks,

When doing Win32::ODBC programming can I use one db connection to do 2 queries on the db.

like 

$db = new Win32::ODBC("MyDSN");
$db->Sql("select * from table");
while($db->FetchData()) {
   my $var = $db->Data("column");
   $db->Sql("update table2 set col2=\'$var\'");
}

[download]

I did not try this code because I am working on quite senstive DB and do not have permissions to create test tables. If one of the update goes wrong then I would be in trouble.

regards,
Abhishek

Replies are listed 'Best First'.
Re: Query twice
by Tomte (Priest) on Mar 05, 2003 at 21:18 UTC
    It is to late for me to contribute code, but consider this:

    Even if merlyn presented me the finest collection of code he's able to produce, I would want to test it before I throw it in a production environment, and I guess he expects me to do so.

    If you're working on sensitive data and are not permitted to test your code in a development-environment:Quit if you can! You will have more trouble than any job can be worth, if you are not allowed to work like a thinking human being.

    regards,
    tomte

    Update: The second paragraph may sound a bit blunt, but I had my fair share of 'do it know, ask questions later' situations, and somehow I always lost, one way or the other

[reply]

      I am already working on a dev environment. However, this DB is shared between many members of my team. If I run it I risk ruining their test data. One approach could have been that I ruin some records and then send an email to everyone saying following records have become unusable because of my script.

      Upon looking up the the FAQ of DB programming and not finding this particular question, I decided to ask it.

[reply]

        Ah, I see
        I guess 'sensible data' and 'trouble' just rang a bell. I didn't meant to insult or offend you.

        regards,
        tomte

[reply]
Re: Query twice
by dws (Chancellor) on Mar 05, 2003 at 21:42 UTC
    When doing Win32::ODBC programming can I use one db connection to do 2 queries on the db.

    When you execute the second query, you implicitly "finish" the prior result set. This isn't what you want to do.

    If you must, use two connections.

    Switching to DBI might give you more flexibility.

[reply]
Re: Query twice
by JamesNC (Chaplain) on Mar 06, 2003 at 04:21 UTC
    and as Charleton Heston might say:
    For the love of God, man... always use a WHERE clause
    Really... get in the habit of using WHERE in ALL your SELECT INSERT UPDATE DELETE's... or you will be get burned sooner or later, it is easier to fix one record than the whole DB :-)
    Load the results of you query into a perl variable, massage them and THEN open a new connection... and you will do yourself a big favor by just doing some simple error checking:
    my $db= new Win32::ODBC("MyDSN") or die "DSN failure, is MyDSN defined
+? Error: ?!";
#Get me everything in the world
$db->Sql("SELECT * from table") or die "SQL Error: $!";
...

    [download]
    I wonder why you would be given a task that requires you to update if you can't test it on something however... this is really the odd part... Good luck :)
[reply]
[d/l]
Re: Query twice
by Cabrion (Friar) on Mar 06, 2003 at 01:16 UTC
    That's not going to work as expected. Specifically "update table2 set col2=\'$var\'" will set col2 in EVERY row in table2 to be whatever is in $var. Even more specifcally, it will set every row to be equal to the last "column" returned form table1. Quite arbitrary.

    In addition to using 2 db connections, you need to qualify your second SQL statement with a where statement. Assuming I understand what you are trying to accomplish

[reply]
Re: Query twice
by CountZero (Bishop) on Mar 05, 2003 at 21:45 UTC

    I'm not familiar with Win32::ODBC, but what I see from your code would not allow you to re-use your connection simultaneously: the second call to SQL would seem to clobber the first one.

    Is there no specific DBD module for your database? If not, DBD::ODBC could be used and DBI/DBD allows re-using one connection for multiple simultaneous queries as you can spawn many query-objects out of the same connection.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

Back to Seekers of Perl Wisdom