Hello Monks,

I'm new to DBI. I read through the DBI doc on cpan http://search.cpan.org/~timb/DBI/DBI.pm, and was particularly interested in using place holders. However, in practice I'm finding them very limited. I'm hoping it's my inability to create the correct data structures around them. I'm using Sqlite 3.5.9 at the backend.

What I have is a file of objects that are made up of anywhere from 4-8 commands. There is no marker for a definition ending, only a beginning. I slurp the file into an array then cycle through creating a second array of just a single object definition. I then use a case statement to identify the commands and values. After updating or inserting to tables, I grab the next object definition and the process starts again.

        foreach my $line (@jobDef) {
               chomp($line);
               $line =~ s/\s+$//g;
            switch ($line) {
                case (/DOCOMMAND/) {
                    $line =~ s/\s+$//g;
                    #This key word is used for bianry commands; It is 
+mutually exclusive with SCRIPTNAME
                    $command = substr($line, 11);
                    $stmnt{ $command } = "UPDATE twsJobs SET command =
+ ? WHERE jobId = ?"; 
                }
                case (/SCRIPTNAME/) {
                    #This key word is for scripts; It's mutually exclu
+sive with DOCOMMAND
                    $command = substr($line, 11);
                    $stmnt{ $command } = "UPDATE twsJobs SET command =
+ ? WHERE jobId = ?";
                }
...
[download]

As you can see here, I'm creating a separate SQL statement for each command and then creating a hash of definition->SQL. I then cycle through the hash at the end, feeding each key and value to a sub that takes the $dbh,$value,$statement, and $rowId (produced form an earlier SELECT statemetn adn put into @joid) and runs prepared_cached and execute methods from DBI.

               foreach my $jobId (@joid) {
                   #print __LINE__ . " UPDATE twsJobs SET $update WHER
+E jobId = $jobId\n";
                   
                   #Unpack our hash and send the key,value over to upd
+ateTableSingle
                   while (my ($k,$v) = each (%stmnt)) {
                       $dbh = updateTableSingle($dbh,$k,$v,$jobId);
                   }
               }
...
[download]

This is sloooow compared to working with a HOH, which I am currently trying to switch from. A test file with a couple hundred defined objects takes about 7 minutes, whereas with HOH of it took less than a minute. The real data will have tens of thousands of object definitions, and I'm afraid of how long that will take. HOH takes about 3~4 minutes. The answer seems to be in leveraging prepared_cache and place holders. Placeholders don't seem to work if I don't know the column name, the value, and the rowId though.

Is there a way I can leverage placeholders and prepared cache if that many values change on each statement? I tried using simple variables, but single quotes and other random characters break the SQL, and every recommendation is to use placeholders.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.