Problem with inserting data in Oracle database using PHP

Question

The following error:

"Warning: oci_execute() [function.oci-execute]: ORA-00911: invalid character in F:\wamp\www\SEarch Engine\test1.php on line 69"

is being generated by this code:

<?php
include_once('config.php');
$db = oci_new_connect(ORAUSER,ORAPASS,"localhost/XE");

$url_name = $_POST['textfield'];
$keyword_name = $_POST['textarea'];
$url_type = $_POST['RadioGroup1'];
$anchor_text = $_POST['textfield2'];
$description = $_POST['textarea2'];   

$sql = "select seq_url1.nextval seq_url1 from dual"; 
$result = oci_parse($db,$sql);

oci_execute($result);   
if($result) 
{
    while($row = oci_fetch_array($result))
    {   
        $temp = $row['SEQ_URL1'];
        echo $temp;
    }
}           

$sql_url1 = 'INSERT INTO URL1(Url_ID,Url_Name,url_Type,Anchor_Text,Dscription) '.'VALUES($temp,:url,:type,:anchor,:description)';

$compiled = oci_parse($db, $sql_url1);

oci_bind_by_name($compiled, ':url', $url_name);
oci_bind_by_name($compiled, ':type', $url_Type);
oci_bind_by_name($compiled, ':anchor', $anchor_text);
oci_bind_by_name($compiled, ':description', $description);

oci_execute($compiled);
?>

What could be causing this?

Answer 1

This could be caused by the misspelling of "Description":

$sql_url1='INSERT INTO URL1(Url_ID,Url_Name,url_Type,Anchor_Text,Dscription)...
                                                                  ^ here

Answer 2

My educated guess is that the encoding of your web form, and the encoding of your Oracle database and/or connection don't match. mySQL will gracefully write the broken character into the table - maybe Oracle is more strict.

But first check whether George Johnston's or John Rasch's observations are not already right on the money, and provide us with some debug data to look at.

Answer 3

This:

$sql_url1 = 'INSERT INTO URL1
                (Url_ID,Url_Name,url_Type,Anchor_Text,Dscription)'.'
             VALUES
                ($temp,:url,:type,:anchor,:description)';

...should be:

$sql_url1 = 'INSERT INTO URL1
                (Url_ID,Url_Name,url_Type,Anchor_Text,Dscription)'.'
             VALUES
                (seq_url1.NEXTVAL,:url,:type,:anchor,:description)';

You don't need two queries to perform the operation, just call the sequence.NEXTVAL within the INSERT statement. The only reason to use what you've got, is if you are re-using that sequence value for other records.

Also, it's possible that Dscription is a typo, that doesn't match the actual column name. Confirm by using DESCRIBE URL1. And you could try printing the query prior to it being run:

oci_bind_by_name($compiled, ':url', $url_name);
oci_bind_by_name($compiled, ':type', $url_Type);
oci_bind_by_name($compiled, ':anchor', $anchor_text);
oci_bind_by_name($compiled, ':description', $description);

echo $sql_url1

Answer 4

You've got a poorly used PHP variable in your query.

$sql_url1 = 'INSERT INTO URL1(Url_ID,Url_Name,url_Type,Anchor_Text,Dscription) VALUES($temp,:url,:type,:anchor,:description)';

In here, $temp is intended to just dump the integer, but since the string uses single quotes no substitution takes place and oracle sees "$temp" (which isn't valid in that context). Switch to double quotes:

$sql_url1 = "INSERT INTO URL1(Url_ID,Url_Name,url_Type,Anchor_Text,Dscription) VALUES($temp,:url,:type,:anchor,:description)";

Or take the above suggestion and use seq_url1.nextval. Description is misspelt, but that will throw an unknown column error, not an invalid character error.

Thanks, Joe