oracle select and insert syntax

[Delboy14]

I have an oracle database, the format of it is:
table travel_request
(
request_id number(5) not null,
dbdirid number(15) not null,
origin varchar2(128) not null,
destination varchar2(128) not null,
depart_date date not null,
return_date date,
purpose varchar2(255) not null,
justification varchar2(255),
approval_status char(1)
);

I am trying to add a set of data to it from a form, then display the data from the database to verify it has been added correctly. There does not seem to be any error when I add the data to the database, using the insert command. However I cannot display the data.

the date variables are in the format "DD-MM-YYYY", so I may need to use the TO_DATE function to convert to a correct Oracle date format, however I have not go this to work either. Does anyone know the problem with my code? I have included the code below...


<code>
<?php

$dbh = db_connect($database_user,$database_password,$database_name);

$stmt = ociparse($dbh,&quot;insert into TRAVEL_REQUEST (request_id_sequence.nextval,$dbdirid,'$origin',
'$destination' ,$depart_date, '$purpose', '$justification', '$approval_status')&quot;

ociexecute($stmt,OCI_DEFAULT);

$display = ociparse($dbh, &quot;select * from TRAVEL_REQUEST&quot;
ociexecute($display,OCI_DEFAULT);

while(ocifetch($display))
{
echo ociresult($display).&quot;<br>\n&quot;;
}

ocilogoff($dbh);
?>
</code>

 

[sleipnir214]

ociresult() is going to be returning an array in your script. You can't echo an array, you have to use something like print_r() or echo on specific elements of the array.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Delboy14]

I have altered my code to now look like this:
the first piece of code attempts to insert into the database
and the second part to display what has been added,

I cant see where my problem is, but nothing seems to come out of the table when I use my select command.
<code>
<?php
$dbh = db_connect($database_user,$database_password,$database_name);
$stmt = OCIparse($dbh,&quot;insert into TRAVEL_REQUEST (request_id_sequence.nextval,$dbdirid,'$origin', '$destination' ,to_date('depart_date','DD-MM-YYYY'),to_date('return_date','DD-MM-YYYY'), '$purpose', '$justification', '$approval_status')&quot;
OCIexecute($stmt,OCI_DEFAULT);
OCIlogoff($dbh);
?>

<?php
$dbh = db_connect($database_user,$database_password,$database_name);
$display = OCIparse($dbh, &quot;select * from TRAVEL_REQUEST&quot;
OCIexecute($display);

while(OCIFetchInto($display, $values))
{
$current_id=$values[1];
$current_dbdirid=$values[2];
$current_origin=$values[3];
$current_destination=$values[4];
$current_depart_date=$values[5];
$current_return_date=$values[6];
$current_purpose=$values[7];
$current_justification=$values[8];
$current_approval_status=$values[9];

echo &quot;<tr><td>$current_id </td></tr>&quot;;
}

OCIlogoff($dbh);
?>
</code>

 

[sleipnir214]

Use a print_r() inside your while loop to examine the contents of the array $value.

It might be something simple like the ocifetchinto() returns an associative array, not a numerical array.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Delboy14]

I have tried this but nothing is printed from print_r(),it seems the while loop is not entered, and even simple text within the while loop does not appear on screen.

I think my problem is in my insert code or fetch code?

 

[sleipnir214]

It could be......

You have an untrapped error in your code. I see no conditional invocation of the ocierror() function.

Your SELECT query returns no rows.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Delboy14]

thankyou, I realise I should have been adding these error checks, when I add them I find the error appears after the execute statement, does that mean it cannot add this record to the database? if so why?

<code>
<?php
//try first to connect to the database. if we can't then end

$dbh = db_connect($database_user,$database_password,$database_name);
$stmt = OCIparse($dbh,&quot;insert into TRAVEL_REQUEST (request_id_sequence.nextval,$dbdirid,'$origin', '$destination' ,to_date($depart_date,'DD-MM-YYYY'),to_date($return_date,'DD-MM-YYYY'), '$purpose', '$justification', '$approval_status')&quot;
if($stmt==false)
{
echo OCIError($stmt).&quot;<br>&quot;;
echo &quot;error in insert&quot;;
exit;
}

$execute=OCIexecute($stmt,OCI_DEFAULT);
if($execute==false)
{
echo OCIError($execute).&quot;<br>&quot;;
echo &quot;error in execute&quot;;
exit;
}
OCIlogoff($dbh);
?>
</code>

 

[sleipnir214]

As an aside, when posting code, use the TGML [ignore]

...

[/ignore] tags, not <code>...</code>. This will stop the TGML processor from doing odd things to some PHP code.

I have a FAQ in this forum which covers debugging PHP code. It has a section on debugging database code. The specific examples use MySQL, but the advice will also work for Oracle: faq434-2999

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Delboy14]

the code still falls at this stage, and the error happens at the execute stage however no error is displayed from OCI Error(), so I am not sure what to do?

Any Ideas

 

[sleipnir214]

Did you try the database debugging advice I posted in my FAQ?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Delboy14]

sleipnir sorry, up to now their has been an error in my connection to the database, the path was wrong in my index.

I can now see the error when inserting, it says.

insert into TRAVEL_REQUEST values(request_id_sequence.nextval,2115704,'London', 'New York' , to_date(12-03-2004,'DD-MM-YYYY'),to_date(18-03-2004,'DD-MM-YYYY'), 'Visiting Staff', 'fdhdfgh', 'p')Failed to insert 

Reason ORA-01847: day of month must be between 1 and last day of month 1847  
Contact Derek 
   Back To Index

so I think the problem is in the sql?

 

[sleipnir214]

Oracle's complaining about the SQL query you handed it. I'd say the problem was with the query.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Delboy14]

ok it is now sorted, the problem was the sql, I had removed single quotes from the date char form field, when I did not know the database path connection was wrongs,

thanks for your help though