Querying a custom ODBC database through PHP/MySQL?

[tfburges]

The company I work for needs to query an outside datasource so it can be compared with the database I've created for them.  The outside source is incredibly large (you have no idea...!) and requires a custom ODBC driver.  I'm able to query it using Microsoft Access but not through PHP or MySQL.  We've contacted the creators of the driver and they were of little to no help.

 

I'm 99% sure I would need to create my own PHP extension to query the database through PHP.  (I was hoping a generic ODBC connection would work as long as the driver was specified, but no.)  I'm not at all familiar with creating PHP extensions and I'm not even sure I would be able to do it properly without the creators giving me the driver's source code.  Nothing helpful shows up when googling the custom driver either.

 

I could either somehow export everything using access or write a script that dumps data from the outside source into a text file (assuming I can use C++ to communicate with the database - don't know about this yet either), which would then be imported into MySQL... but I really don't want to do that, as it takes up way too much space and the information is already there (no point in having it in two places).

 

Any suggestions?

[tfburges]

None?

[fenway]

I assume this isn't a one-off type thing.

[tfburges]

I'm just looking for a quick nudge in the right direction, hoping someone has experience in doing something similar and knows the best way to go about it so that I don't waste hours of my time trying something that either doesn't work or is really inefficient.  That, and I was wondering if I was correct in my assumptions about needing to create a driver and the requirements to do so.

[fenway]

I'm not too familiar with "custom" drivers... it's hard to believe that standard OBDC doesn't work, though.

[tfburges]

That's what I thought too.  I've tried every possible combination of host names and user names and backslashes I could possibly think of.  I must be missing something....

 

This is the odbc connection through PHP that I'm working with:

(Forgive the redundancy of "RBM" lol... I replaced/deleted sensitive information with generic information.)

 $odbc_host = '\\\\xx.xx.xx.xx\\rbm';
$odbc_user = 'xx';
$odbc_pass = 'xx';
$odbc_dbname = 'db.rbm';
//$odbc_dbname = '\\\\xx.xx.xx.xx\\rbm\\RBM\\Cust\\South\\db.rbm'; (tried various combinations)
$odbc_dsn = "DRIVER={CSI RBM 4.02 ODBC Driver};" . 
        "CommLinks=tcpip(Host=$odbc_host);" . 
        "DatabaseName=$odbc_dbname;" .
        "uid=$odbc_user; pwd=$odbc_pass";
$odbc_connect = odbc_pconnect($odbc_dsn, $odbc_user, $odbc_pass);
if ($odbc_connect <= 0) {
  echo "Connection failed.<br>",$odbc_dsn;
  exit;
} else {
  echo  "Connection successful.";
}

 

This is the error message:

PHP Warning:  odbc_pconnect() [<a href='function.odbc-pconnect'>function.odbc-pconnect</a>]: SQL error: [sYWARE, Inc.][Dr. DeeBee ODBC Driver]Unable to connect, SQL state 08001 in SQLConnect in etc.php on line 21, referer: etc.

 

 

Aaaand not that it matters much but...

The data source is located on another machine on the network, but I can definitely access the machine because I have it mapped.

 

 

In the mean time, unless someone knows what's wrong, I'll be working on a workaround (writing a script that pulls data into MS Access since I know that works and then querying Access through PHP... all for comparison with information from another (MySQL) database).  To do this, I need to know how to get a directory/file listing so that I can right a batch file using odbcconf.exe (similar to odbcad32.exe (i.e. ODBC Data Source Administrator) but command line only) that adds the 1500+ rbm files to my data sources.  Whoever designed this whole RBM thing wasn't thinking ahead...

[tfburges]

**Another edit (form wouldn't let me edit my last post again so I need to double post):

Here's the code I'm using for the directory/file listing (I plan on modifying it to insert the directories/files into a MySQL):

<?php
function folderlist(){
$startdir = '\\\\xx.xx.xx.xx\\rbm\\RBM\\Cust';
$ignoredDirectory[] = '.'; 
$ignoredDirectory[] = '..';
  if (is_dir($startdir)){
      if ($dh = opendir($startdir)){
          while (($folder = readdir($dh)) !== false){
              if (!(array_search($folder,$ignoredDirectory) > -1)){
                if (filetype($startdir . $folder) == "dir"){
                      $directorylist[$startdir . $folder]['name'] = $folder;
                      $directorylist[$startdir . $folder]['path'] = $startdir;
                  }
              }
          }
          closedir($dh);
      }
  }
return($directorylist);
}

$folders = folderlist();
foreach ($folders as $folder){
   $path = $folder['path'];
   $name = $folder['name'];

   echo '<a href="'. $path . $name .'">' . $name . '</a><br />';
}
?>

All I really need to know is the syntax for an outside source "$startdir".

 

Oh yeah... and the error message is:

PHP Warning:  Invalid argument supplied for foreach() in D:\\My Documents\\IRLCM_tech_form\\rbm_search.php on line 23

 

I do realize this thread is starting to be in the wrong forum...  I'll post in another if needed.

[xtopolis]

Looking into your problem of accessing a networked drive, I found 2 posts, though old, that might be of use:

 

http://us2.php.net/manual/en/function.opendir.php#79685

http://bugs.php.net/bug.php?id=22153&edit=3 (2nd reply)

 

Paraphrased summary:

1) (just read the post from the link)

2) Drive must be previously authenticated so PHP doesn't have to do it

  (ie, try to browse it like you would in windows via Explorer, and have it prompt for un/pw and auth first)

 

They both reference this syntax for opening drives/ips:

opendir("\\\\my.ip\\my_dir");

but there is a post on the us2.php site saying try reversing the slashes -> //ip.add.ress./folder etc

 

The foreach error is probably just the result of the folder not being able to be opened, surprised you don't have errors for that?  you could/should add

error_reporting(E_ALL);

to the top of the code (after <?php of course)

[Barand]

I notice you have it set as a USER DSN. It needs to be SYSTEM DSN on the server for the server to access it