Syntax error with insert

[wsexton]

I keep getting a syntax error message when I try to execute this sql statement. I know it's a long one. Can anyone spot the problem?

$sqlstmt = $dbh->prepare("INSERT INTO request_data (requestnumber, date, requestor, duedate, reoccuring, frequency, purposeandscope, question, utilization, datashown, published, linechart, barchart, columnchart, piechart, pivottable, crosstabulation, spreadsheet, writtenanalysis, fullreport) VALUES ('$requestnumber','$date','$requestor','$duedate','$reoccuring','$frequency','$purposeandscope', '$question','$utilization','$datashown','$published','$barchart','$columnchart','$piechart','$pivottable','$crosstabulation','$spreadsheet','$writtenanalysis','$fullreport')");


Thanks.

 

[duncdude]

You have 20 fields in the 1st part and only 19 in the second

You are missing $linechart in the 2nd maybe?

Duncan


Kind Regards
Duncan

 

[duncdude]

... also - i guess you are using Perl!? won't the single ticks cause the variables to be unable to expand? double speech marks???

Duncan


Kind Regards
Duncan

 

[wsexton]

Thanks for spotting the missing variable. I corrected that, and I've tried using \' for the single quotes and I still get a syntax error. I just can't see what's causing the problem.

 

[duncdude]

try this:-

$sqlstmt = $dbh->prepare ("

INSERT INTO

request_data ( requestnumber, 
               date,
               requestor,
               duedate,
               reoccuring,
               frequency,
               purposeandscope,
               question,
               utilization,
               datashown,
               published,
               linechart,
               barchart,
               columnchart,
               piechart,
               pivottable,
               crosstabulation,
               spreadsheet,
               writtenanalysis,
               fullreport )
               
VALUES ( \"$requestnumber\",
         \"$date\",
         \"$requestor\",
         \"$duedate\",
         \"$reoccuring\",
         \"$frequency\",
         \"$purposeandscope\",
         \"$question\",
         \"$utilization\",
         \"$datashown\",
         \"$published\",
         \"$linechart\",
         \"$barchart\",
         \"$columnchart\",
         \"$piechart\",
         \"$pivottable\",
         \"$crosstabulation\",
         \"$spreadsheet\",
         \"$writtenanalysis\",
         \"$fullreport\" )
         
         ");

but i'm not very good with Perl/SQL


Kind Regards
Duncan

 

[duncdude]

... i have added the missing linechart


Kind Regards
Duncan

 

[wsexton]

I certainly appreciate your help.

I'm still getting an error message: DBD::ODBC::db prepare failed: [Microsoft][ODBC Microsoft Access Driver] Syntax e
rror in INSERT INTO statement. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) at
datarequest.pl line 60.

I don't have much experience with Perl/SQL either, and I just can't spot the problem.

 

[duncdude]

no problem... this methodology works for me:-

use DBI;

         $db = '?????';
       $host = '?????.?????????.co.uk';
    $db_user = '?????';
$db_password = '?????';
  
$dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password")
          || die print ("Cannot connect to the database");

while (<DATA>) {
  
  print "<h1>$_</h1>\n";
  
  $query = "INSERT INTO advertisers
            (company)
            VALUES
            (\"$_\")";
            
  $rows = $dbh->do($query);
  
}

$dbh->disconnect;

i don't seem to use 'prepare' ?


Kind Regards
Duncan

 

[stevexff]

Using prepare with placeholders is a great way to do what you want, especially (but not only) if you expect to insert a large number of similar rows.

my $sql = "INSERT INTO mytab (col1, col2) VALUES([red]?, ?[/red])";

my $sth = $dbh->prepare($sql);

while (<DATA>) {
   my @coldata = split(",", $_);
   $sth->execute(@coldata);
}
__DATA__
col1 a,col2 a
col1 b,col2 b
col1 c,col2 c

The question marks are used as placeholders in the prepare, which effectively binds a set of parameters to the query. Then in the execute step, you just pass in a different set of values each time. As well as saving cpu cycles (the statement is only prepared once), and DBI takes care of the quoting for you so you don't have to mess about with escapes...

BTW, welcome back, duncdude.

 

[duncdude]

Thank you stevexff!


Kind Regards
Duncan