Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

ODBC question

by Eugene (Scribe)
on May 10, 2000 at 07:36 UTC ( #10883=perlquestion: print w/ replies, xml ) Need Help??
Eugene has asked for the wisdom of the Perl Monks concerning the following question:

I am using Win32::ODBC and trying to insert into my db like
$val1 = 1; $val2 = "'"; $db->Sql("INSERT INTO Comment ( Field1, Field2 )VALUES ('$val1', '$val +2') ");
That doesn't work because SQL doesn't like ' ' '. Is there a way around it?

Comment on ODBC question
Download Code
DBI answer! (was Re: ODBC question)
by BBQ (Deacon) on May 10, 2000 at 07:51 UTC
    Well, the 1st way around it that I can think of would be to drop Win32::ODBC (unless you REALLY need it) and switch do DBD::ODBC! Not only will you be joining the "standard" in perl database programing, but you'll be able to port your code with minor modifications (not to say none) to other database systems. Here's a sample of what you how you would do it:
    # 1st prepare the SQL with placeholders $sth = $dbh->prepare(qq{ INSERT INTO Comment ( Field1, Field2 ) VALUES ( ?,? ) }) or die("Failed to prepare ".$DBI::errstr); # now execute what you've prepared with the variables $rv = $sth->execute($val1,$val2) or die("Failed to execute ".$DBI::errstr);
    (btrott wrote a complete select example back in March that also addresses placeholders)

    Get the general drift? It gets better... Now you're using ODBC, and I'd guess that you're running Access or MS-SQL, right? If you switch to DBI, you'll be able to connect directly to Oracle, Informix, MySQL and others without the need for ODBC standing as a middle man. Seriously, look into DBI/DBDs, you won't regret it!

    #!/home/bbq/bin/perl
    # Trust no1!
RE: ODBC question
by buzzcutbuddha (Chaplain) on May 10, 2000 at 16:00 UTC
    BBQ gives good guidance on this issue. However, if you really want
    to stick to Win32 Operations, and you think that you will never
    ever move beyond it, you can use ADO, or Active X Data Objects.
    ADO is an easy way to use Microsoft's OLE DB API. If you have
    coded in ASP or VB, then you will find ADO pretty easy.

    An example:
    use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; my $conn = Win32::OLE->new('ADODB.Connection'); $conn->open('FooConn'); $sqlString = "INSERT INTO Foo(Bar, Baz) VALUES (\'$Bar\', \'$Baz\');"; $conn->execute($sqlString); print "\n\n\tPfft: ", Win32::OLE->LastError(), "\n" if (Win32::OLE->La +stError()); exit if (Win32::OLE->LastError());

    The benefit to ADO is that you can connect it to any database
    does support ODBC...but for total portability, I'd suggest you follow
    BBQ's advice.
Re: ODBC question
by Eugene (Scribe) on May 10, 2000 at 19:23 UTC
    That's all cool. But how would I do that using Win32::ODBC?
RE: ODBC question
by mr_ayo (Beadle) on May 10, 2000 at 20:56 UTC
    Yea, there's a way around this.
    sub mySQLQuote { my ($value,$maxlen) = @_; $value =~ s/\'/\'\'/g; $value = substring($value,1,$maxlen); return $value; } val1 = mySQLQuote(1); $val2 = mySQLQuote("'"); $db->Sql("INSERT INTO Comment ( Field1, Field2 )VALUES ('$val1', '$val +2') ");
      Man I always do that! Stupid stupid.

      Your assignments should read:
      $val1 = mySQLQuote(1,1); $val2 = mySQLQuote('1',3);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://10883]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (15)
As of 2014-09-01 15:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (14 votes), past polls