Perl Guru Forums: Perl Programming Help: DBI: DBI do Command Used Recursively...not updating with new data in DB

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
DBI do Command Used Recursively...not updating with new data in DB

cms370
New User

Nov 18, 2010, 8:30 AM

Post #1 of 4 (10441 views)

DBI do Command Used Recursively...not updating with new data in DB	Can't Post

All,

I am using a bit of DBI code to recursively place data into an SQL Database. The issue I am running into is that the data will not update even though a variable has changed in the SQL statement.

For instance (im going to use pseudo code here to get the point across...):

$SQL = "INSERT INTO Test_Table (ColA, ColB) VALUES (varA,varB)";

#Use this print to check that the SQL string has updated to the correct values and has changed

print $SQL;

#Place data into database

$sth->do($SQL);

This chunk of code will execute and the database will be updated with the appropriate values. The problem comes in when i wrap a loop around this and start changing the varA and varB values. The SQL print statement will update as it should with the correct values but upon inspecting the DB itself, it will show that only the first "set" of values that varA and varB take on will be in the database?? Is the DBI somehow holding onto the old values when it is being executed with the "do" command (I know that the SQL string has updated correctly because of the screen print).

Thanks for your help.

Zhris
Enthusiast

Nov 19, 2010, 5:15 AM

Post #2 of 4 (10426 views)

Re: [cms370] DBI do Command Used Recursively...not updating with new data in DB [In reply to]	Can't Post

If you check the documentation for DBI, it suggests that you use placeholders whilst looping/changing values. Without seeing your actual code its difficult to suggest whats wrong, but it sounds like you should try using placeholders.

cms370
New User

Nov 19, 2010, 8:12 AM

Post #3 of 4 (10411 views)

Re: [cms370] DBI do Command Used Recursively...not updating with new data in DB [In reply to]	Can't Post

Ok...so I found my problem last night. Essentially it has nothing to do with DBI and from what I can tell, everything to do with the fact that my simple program was assigning the same keyed index to the database for sequential loops.

For instance, on each loop the DBI do command was executed twice. The index that was assigned to the database remained the same for each one of the do command calls, but the second variable was updated. Not sure what happened exactly, but the database must have been saying that it already has a record keyed at index "1" and was basically just making a copy of that data. After making sure that for each entry the index was updated sequentially for each do call, everything works perfectly and the data is going into the DB.

FishMonger
Veteran / Moderator

Nov 19, 2010, 4:34 PM

Post #4 of 4 (10394 views)

Re: [cms370] DBI do Command Used Recursively...not updating with new data in DB [In reply to]	Can't Post

You should not use $sth->do() in a loop. The do method will needlessly do a prepare for each iteration, which is wasteful especially as the number of iterations increases.

You should use placeholders and call a single prepare statement, then put the execute statement in the loop passing it the needed vars.

Search for (options)

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