I have been trying with limited success to create a module that will report on orders which are located in an external Oracle database. The module creates a form which enables users to choose the criteria they wish to search on, and then places the resulting data into an html table.

The Oracle driver has been installed, and everything works, provided there is only one table in the query. I know that this query works as it was created using sqldeveloper and was successfully run against the Oracle database. So, if I use SELECT * FROM myTable, everything works. Once I add two or more tables to the query string, although no errors are reported, no table is created.

I have experience doing this sort of thing with ASP and SQL, but Drupal, PHP and Oracle are all new territories for me. Could someone please take a look at the query string below and see if there is something I am missing here? Thanks in advance.

$sqlStmt="SELECT customer_order_header.cust_po_no, customer_order_header.cust_order_date, customer.cs_cust_no, customer.custo_name, customer_order_header.cust_order_id, customer_order_detail.cust_dept_no, customer_order_detail.cust_item_code, customer_order_detail.cust_qty_ord, customer_order_detail.cust_retail_price, customer_order_detail.cust_retail_mult
FROM indord.customer, indord.customer_order_header, indord.customer_order_detail
WHERE customer_order_header.cust_po_no=321 and customer_order_header.cust_order_id= customer_order_detail.cust_order_id AND customer_order_header.cs_cust_no= customer.cs_cust_no";

Comments

siromega’s picture

siromega commented

This is just a guess, but try using ANSI SQL? That means replacing your joins in the WHERE clasue with INNER JOIN in the FROM clause.

Or try creating a view in Oracle and then just do select * from customer_po_vw where customer_po_no = 321 (this may be useful as it encapsulates the logic behind customer orders, so if you implement other apps that need to access the same data, you aren't writing the same SQL statement in another piece of software; I've always been a fan of pushing this type of stuff as far down as possible, which means I use a lot of views to create very simple queries in my code). 

create or replace view customer_po_vw 
SELECT customer_order_header.cust_po_no, customer_order_header.cust_order_date, customer.cs_cust_no, customer.custo_name, customer_order_header.cust_order_id, customer_order_detail.cust_dept_no, customer_order_detail.cust_item_code, customer_order_detail.cust_qty_ord, customer_order_detail.cust_retail_price, customer_order_detail.cust_retail_mult
FROM indord.customer, indord.customer_order_header, indord.customer_order_detail
WHERE customer_order_header.cust_order_id= customer_order_detail.cust_order_id AND customer_order_header.cs_cust_no= customer.cs_cust_no;
nu2drupal-1’s picture

nu2drupal-1 commented

I tried the following sql statement, but still no joy. It would seem that the driver can only support querying one table. It's looking like your idea of using a view is my option. Either that or a stored procedure. Thanks for the suggestions

$sqlStmt='SELECT h.cust_po_no, h.cust_order_date, c.cs_cust_no, c.custo_name, h.cust_order_id, d.cust_dept_no, d.cust_item_code, d.cust_qty_ord, d.cust_retail_price, d.cust_retail_mult FROM indord.customer_order_detail d JOIN indord.customer_order_header h ON d.cust_order_id= h.cust_order_id JOIN indord.customer c ON h.cs_cust_no = c.cs_cust_no WHERE h.cust_po_no=25781';

siromega’s picture

siromega commented

Well, the driver does support multiple table queries (I just combed the search module and it has several INNER JOIN queries that work just fine). If you want more help post error messages and we'll try to figure it out.

The view solution might be optimal from a time standpoint (since it wont much time to get working).

nu2drupal-1’s picture

nu2drupal-1 commented

I tried using a view, same thing. If I select * from the view, it returns everything. When I hard code a parameter in the where clause, all I get is a 304 status code in Fiddler and nothing is returned, including any error codes. If I put in a variable from the form as the parameter value in the select statement, when I try running the query without entering a value in the text box, I do get the following errors:

warning: oci_execute() [function.oci-execute]: ORA-00936: missing expression in /var/www/html/optrhcms01v/sites/all/modules/my_module/my_module.module on line 113.
warning: oci_fetch_all() [function.oci-fetch-all]: ORA-24374: define not done before fetch or execute and fetch in /var/www/html/optrhcms01v/sites/all/modules/my_module/my_module.module on line 115.

This is the rest of the code:

$sqlStmt="sqlStmt="SELECT * FROM indord.customer WHERE indord.customer.cs_cust_no=18500"; //18500 will eventually be a parameter
$connection= oci_connect("UserName","UserPassWord","TNSname") OR die;
$statement = oci_parse($connection, $sqlStmt);
oci_execute($statement);

$nrows = oci_fetch_all($statement, $results);
if ($nrows >= 0) {
echo "

\n";
echo "
\n";
foreach ($results as $key => $val) {
echo "

\n";
}
echo "

\n";

for ($i = 0; $i < $nrows; $i++) {
echo "

\n";
foreach ($results as $data) {
echo "

\n";
}
echo "

\n";
}
echo "

$key
$data[$i]

\n";
} else {
echo "No data found
\n";
}
echo "$nrows Records Selected
\n";
oci_free_statement($statement);
oci_close($connection);
}

This will work as a flat file on the server, so it does work

aaaristo’s picture

aaaristo commented

Why are you using oci instead of db_query function?

somenthing like:

$result = db_query("select * from myview");

while ($row=db_fetch_object($result))
print_r($row);

Have you installed drupal over an Oracle Database ?

The database you are connecting to with
oci_connect("UserName","UserPassWord","TNSname")

is the same of the drupal backend database?

If answered yes on the previous 2 questions use the db_query function
and grant the drupal user the rights to select this view.

This code is misstyped?
$sqlStmt="sqlStmt="SELECT * FROM .. ???????????

Do you mean?
$sqlStmt="SELECT * FROM...

if you are connecting to an external database != from the drupal database use :

function ext_connection ($host,$port,$sid,$user,$pwd)
{
global $extconn;

$extconn= new PDO("oci:dbname=//$host:$port/$sid", $user, $pwd);
$extconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$extconn->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
$extconn->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, FALSE);

}

function ext_query($query,$args=NULL)
{
global $extconn;

$stmt = $extconn->prepare($query);

if ($stmt->execute($args))
return $stmt;
}

function ext_fetch_object($stmt)
{
if ($stmt)
return $stmt->fetchObject();
}

... your code ...

$mypar= 18500;
ext_connection ();
$result= ext_query("SELECT * FROM indord.customer WHERE indord.customer.cs_cust_no= ?",array($mypar));

while ($row=ext_fetch_object($result))
print_r($row);

This code uses PDO_OCI php extension not the oci8 extension only because it was faster to write it..

If you really want to use oci8, this error:
ORA-00936: missing expression

is from the oci_parse...
The statement you are submitting is invalid.

nu2drupal-1’s picture

nu2drupal-1 commented

the SQL statement was mis-pasted - you are right it is $sqlStmt="SELECT * FROM...

Drupal is not installed on Oracle - it is on mySQL. What I need to do is report on orders which are located on an external Oracle database.

I realize that the submitted statement is invalid as I deliberately omitted the value it was expecting in order to get an error. At the time the error was generated, the value of 18500 in the SQL statement was not hard coded, it was expecting the value of a text box on the form ($number = $form_state['values']['number'];). If I had entered the value into the text box then no errors would have been thrown but no data would have been returned either.

I'll give your code a try and let you know if this solves my issue - thanks for your help!

nu2drupal-1’s picture

nu2drupal-1 commented
Status: Active » Fixed

As PDO_OCI was not installed on the server, I was unable to to try your code. However by placing drupal_set_message(t($data)); within the loop that read the returned data I was able to see what if anything was coming in. By naming one column at a time, I was able to figure out the problem; there were a couple of columns in the database that have not yet been populated and when they were explicitly named, the value of the data was returned as 'array'. Once this happened, it seem that the whole query would fail. By eliminating the empty columns, I was able to get the query to work with multiple tables as well as with variables.

Thanks for everyone's help

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

aaaristo’s picture

aaaristo commented

check http://drupal.org/node/622336