building sql statement

[mwill007]

I have to update database manually alot, so I wrote an app that reads the first line of a csv file and creates the headers/tables for an insert or update statement. The next lines are the data in the file.

I build the statement like so

 if ($checksite eq $none)
  {
    $insertsqlstatement = 'INSERT INTO database.database(';

   $x=0;
   foreach (@dbcolumn)
    {
     $insertsqlstatement = $insertsqlstatement . "@dbcolumn[$x]";
#
     if ($x != $numelements )
      {
       $insertsqlstatement =  $insertsqlstatement . ",";
      }
#
     else
      {
       $insertsqlstatement = $insertsqlstatement . ') VALUES (';
      }
#
   $x=$x+1;
  }
#
   $x=0;
   foreach (@dbcolumn)
    {
     $insertsqlstatement = $insertsqlstatement . '\'' . "@splitarray[$
+x]" . '\'';
     $splitstatement = $splitstatement . '$' .@dbcolumn[$x] ;
#
     if ($x != $numelements)
      {
      $insertsqlstatement = $insertsqlstatement . ',';
      $splitstatement = $splitstatement . ',';
      }
#
     else
      {
       $insertsqlstatement = $insertsqlstatement .')';
      }
    $x=$x+1;
   }
  }

when I print the value of $insertsqlstatement is equal to INSERT INTO database.database(site_id, site_store_type, project, site_city, site_state, site_country, project_begin ) VALUES ('9999','SC OSR','Prebuild','Penfield','NY','US','8/8/2016 ')

I can copy it an it runs just fine in sql

Buy when i write from my sql statement it will not write sqlexec($insertsqlstatement);

But if i manualy set the value of $insertsqlstatement = "INSERT INTO database.database(site_id, site_store_type, project, site_city, site_state, site_country, project_begin ) VALUES ('9999','SC OSR','Prebuild','Penfield','NY','US','8/8/2016 ')";

The sqlexec($insertsqlstatement); executes and updates the database

[haukex]

Hi mwill007,

Please use <code> tags so your code will display properly. See How do I post a question effectively?

In your problem description you say that when you print the string, you get the expected value, and it doesn't work - please tell us the exact error message. Second, you say that when you manually set the SQL to the same value, it works - this leads me to believe that the two strings are actually not the same. Please try using Data::Dumper to output them both, and use the option $Data::Dumper::Useqq=1; to make nonprintable characters easier to see. See the Basic debugging checklist.

Lastly, there are modules to help you to read CSV - Text::CSV - and modules that help building SQL, like SQL::Abstract:

use warnings;
use strict;

# example data
my @dbcolumn = qw/ site_id site_store_type project site_city site_stat
+e site_country project_begin /;
my @splitarray = ('9999','SC OSR','Prebuild','Penfield','NY','US','8/8
+/2016 ');

# make a hash out of two arrays of the same length
my %data = map { $dbcolumn[$_] => $splitarray[$_] } 0..$#dbcolumn;

# build the SQL statement
use SQL::Abstract;
my $sql = SQL::Abstract->new;
my ($stmt, @bind) = $sql->insert('database.database', \%data);

# debug: display the Perl variables holding the SQL
use Data::Dumper;
print Dumper($stmt, \@bind);

__END__

$VAR1 = 'INSERT INTO database.database ( project, project_begin, site_
+city, site_country, site_id, site_state, site_store_type) VALUES ( ?,
+ ?, ?, ?, ?, ?, ? )';
$VAR2 = [
          'Prebuild',
          '8/8/2016 ',
          'Penfield',
          'US',
          '9999',
          'NY',
          'SC OSR'
        ];

Hope this helps,
-- Hauke D

Update: Added link to SQL::Abstract, thanks to Your Mother for the inspiration :-)

[Your Mother]

For mwill007: this response from haukex is exactly what I would have suggested (SQL::Abstract ).

[mwill007]

Thanks, I though there was some hidden characters. That option for data dumper is what I was needing the "\n\n". I will keep that in my do not forget pile. First program I have written with alot of string manipulation. I got tired of modifying my database update program, since the data never comes to me the same way. Now I add two lines two the excel file I receive, first line is which database,second line is the database fields with drop downs, the other lines is the data. This way I never have to manipulate the program again.

[GotToBTru]

With complicated if-then logic like this, indentation can be a great way of detecting structural problems. Edit your post to use code tags, please, so we can see any indentation in your source. That will also help with the $x versus [$x] problems too.

References to single array elements should have the $ sigil instead of @:

$dbcolumn[$x] instead of @dbcolumn[$x]

Your loop contains that variable $x but it really isn't necessary. Using the foreach loop with an array is one way of avoiding array indexes. The following are equivalent:

for ($x = 0; $x <= $#array; $x++) {
  ... $array[$x] ...
}

versus

foreach $element (@array) {
  ... $element ...
}

[KeighleHawk]

It's been a long time sine I've done serious database work, but have you looked at the DBI/DBD modules for your database? You may just be doing this the hard way.