[VPM] Mysql - Insert question

[Darren Duncan]

P.S.  I actually wrote this around 7pm, but forgot to send it to the list.  Here goes...
--------------------

Scott said:
>so far I can connect to mysql be autheticated and create the table.
>I  have verified that all the varibles contain what there supposed to have
>but the INSERT fails with the message  "DBD::mysql::db do failed: You have
>an error in your SQL syntax near ' 22342132)'  at line 2 at net-space.pl
>line 50, <INFILE1> line 24."
>
><snip>
>$SQL = "INSERT INTO $tablename ( host, share, jobnum, kbused ) VALUES (
>$hostname, $sharename, $jobnum, $kbused)";
>
>$InsertRecord = $dbh->do($SQL);
></snip>

Hello Scott.  I have a large amount of interest in using databases with Perl, and some experience, so I have a couple suggestions or pointers to make.

First of all, when you are using strings as data values in a SQL statement, they need to be quoted and escaped so that the database can parse the SQL statement properly; you don't seem to be doing this, which is why you got the error.  Here is an example that does quoting but not escaping (but to escape, just replace every "'" with a "''" in the $hostname and $sharename).

$SQL = "INSERT INTO $tablename (host, share, jobnum, kbused)
	VALUES ('$hostname', '$sharename', $jobnum, $kbused)";

$rv = $dbh->do($SQL);

Second of all, a better aproach in general that will save you the above worries and more, and still make things work, and make them work faster (especially when inserting multiple rows), is to do your variable binding separately, like this:

$SQL = "INSERT INTO $tablename (host, share, jobnum, kbused)
	VALUES (?, ?, ?, ?)";

$sth = $dbh->prepare($SQL);

$rv = $sth->execute($hostname, $sharename, $jobnum, $kbused);

Personally, I recommend the second approach every time.

That said, there is one catch, which is if you are debugging and want to print out the sql statement that is being run, this statement won't show the 4 values, but the question marks instead.

 -- Darren Duncan