Solved

Oracle Query into an MySQL DB Table using PHP

Posted on 2013-11-25
10
722 Views
Last Modified: 2013-12-19
I need to have a user click a button on a PHP page that then queries an Oracle database and instantly inserts the data into a table in a MySQL db table.  Trying to use this as a "snapshot in time" to compare to physical inventory.

I can connect and display data from both databases, no problem... but I am not sure how to go about inserting the query results in MySQL.

Any help is appreciated!
0
Comment
Question by:hydrazi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39676279
Have you started writing the code yet?  If you can show us what you've written I'm sure we can help you complete it.  In particular if you're looking for some guidance on the MySQL side, this article may be useful.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 

Author Comment

by:hydrazi
ID: 39677575
Thanks!  Yes, I am working on it now.  I have created a PDO object with the Oracle query data inside of it.... then I am going to create a PDO connection to the MySQL database...

And That's where I am stuck... do I do a WHILE statement to build an INSERT statement?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39677694
Yes, I think you're on the right path.  You can use var_dump() to examine the data you get from Oracle.  That should help you see how to set up the query for MySQL.

Interesting app!
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:hydrazi
ID: 39677773
Thanks!  We want to do an "offline" inventory from a snapshot.  Then 2 handheld Windows Mobile bar code scanners.  So, the pages have to be simple.  The scan the items into a table.  Separate tables then a report that compares them and downloads into an Excel spreadsheet.

I got about 30% of it working.  It will be 60% if I can get this part done.
0
 

Author Comment

by:hydrazi
ID: 39678204
OK, here is my code for the query and insert into a table.  I am able to insert any of the fields that contain only NUMBERS (despite all fields in the MYSQL table being VARCHAR and the VAR_DUMP on the Oracle queried array showing as STRINGS.  If I add any of the fields that contain letters.... the insert doesn't work.  And I have no idea why.

<?php

//connect to Oracle
$conn = oci_connect('rrrr','xxxx','//99.99.99.99:1521/mvm.world'); 

//Test Oracle connection and verify
if (!$conn) { 
      $e = oci_error();   // For oci_connect errors pass no handle 
      echo '<b><font color="red">FAILED</font></b> : ' . htmlentities($e[
        'message']); 
} else { 
    // echo '<b><font color="green">Connection to Oracle: OK!</font></b>'; 
     
} 

//create the query to get inventory list from Oracle

$sql = "
SELECT T1.ObjectID,
T1.InvUnitLocation as Loction,
T1.InvUnitLot as Lot,
T1.InvUnitSubLot as Sublot,
T1.InvUnitResource as Resouce,
T1.Classif,
T2.Description,
T1.TotPrimQty as PrimQty,
T1.TotPrimQtyUM as PrimUM,
T2.CatCodesCode1 as Class,
T2.CatCodesCode2 as Subclass
FROM  inInventoryUnit T1, fdBasResc T2, inClassif T3
WHERE T1.InvUnitRescOID = T2.ObjectID
AND  T1.ClassifObjectID  =  T3.ObjectID
AND T2.CatCodesCode1 IN ('WIP','FINISHED GOODS')
AND T1.InvUnitInstType = 0
AND T1.InvUnitSite = 'MPM'
";

//Parse the connection and the query into $stid
$stid = oci_parse($conn, $sql);


//Connect to the MYSQL Database
$dbuser = "xxxx";
$dbpass = "xxxxxxx";
$conn2 = new PDO('mysql:host=localhost;dbname=shipinventory',$dbuser,$dbpass);   //PDO database connection

$check=(oci_fetch_array($stid, OCI_RETURN_NULLS+OCI_ASSOC));

//Loop through the Oracle results and insert each record.
while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS+OCI_ASSOC)) {
    
  $sql2 = "INSERT INTO importlist (objectid,resource,sublot,primqty,description) VALUES ($row[OBJECTID],$row[RESOUCE],$row[SUBLOT],$row[PRIMQTY],$row[DESCRIPTION])";
  $resultx = $conn2->prepare($sql2);
  $resultx->execute();
    
}

oci_close($conn);

Open in new window

0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39678255
I think you might need to both prepare the query and bind the PHP variables into the query.  Please see the code examples in this article.  The important parts that you need will be found if you scan for these key phrases:
PDO - Error Visualization
PDO - Prepare a Query
Escape the Data and Load the Table

You also want to add error_reporting(E_ALL) to the top of the script and correct any things that generate notices.  I think you may find that you're missing some important quote marks.  Explanation here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12241-Quotation-Marks-in-PHP.html
0
 

Author Comment

by:hydrazi
ID: 39690807
Thanks, I got it to work but it worries me a bit.
The reason is that it refuses to insert unless I first assign some of the fields to variables and then insert the variables into MySQL.  I will try the error reporting to see if I can figure it out.

I think it has something to do with conversion.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39691086
first assign some of the fields to variables and then insert the variables into MySQL
That is expected behavior if you're using bound variables in prepared queries.  The variable must exist before it can be bound to the query.
0
 

Author Comment

by:hydrazi
ID: 39730251
Part of my issue seems to have been exactly what you said, Ray.  Thanks again!  Sorry for how long it took.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39730537
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question