copying sybase content to mysql on the web

[jefargrafx]

hello all, long time no chat.

I got a request to copy part of a sybase database inot a mysql database so that we can write php to display content on a web site.

The copy would need to be done once daily so I'm thinking I'll need a batch process running somehow somewhere.

Anyone do this type of thing before.

thanks for the hlp in advance.

jef

 

[jpadie]

i guess the easiest thing would be:

1. query the tables in sybase
2. for each table, query the results from the last sync iteratively
3. in each iteration, insert the records into a corresponding mysql table.

easily done in php. something like this perhaps (although knowing nothing about sybase, i do not know whether you need to do some specific data transformations between different data types.

//open db connections
$sConn = sybase_connect($s_hostname, $s_username, $s_password);
sybase_select_db($s_db, $sConn);

//open mysql connections
$mConn = mysql_connect($m_hostname, $m_username, $m_password);
mysql_select_db($m_db, $mConn);

//get table schema
$result = sybase_query("SELECT name FROM dbname..sysobjects WHERE type = 'U'");
$tables = array();
while ($row = sybase_fetch_assoc($result)){
  $dataResult = sybase_query("select * from {$row['name']} where timestamp >= $timestamp");
  while ($dataRow = sybase_fetch_assoc($dataResult)){
    $cleansedData = array_map('dbReady', $dataRow);
    mysql_query("Insert into {$row['name']} values ( ". implode(',',$cleansedData) . ")");
  }
}

function dbReady($data){
    //trim the data
    $data = trim($data);
    if (function_exists('mysql_real_escape_string')){
        $result = @mysql_real_escape_string($data);
        if (!$result){
            $result = mysql_escape_string($data);
        }
    } else {
        $result = mysql_escape_string($data);
    }
    return "'$data'";    
}

warning: the above is typed straight into the tek-tips site and is untried and unvalidated. there will probably be syntax errors.

if you do not have table level timestamps then either (i) add them or (ii) iterate the entire table and instead of using inserts into mysql use the replace syntax. you will need to transform the standard value string into a field=value string.

$s = array();
foreach ($cleansedData as $f=>$v){
  $s[] = "$f=$v";
}
$query = "replace into {$row['name']} set " . implode(',', $s);

 

[jefargrafx]

thanks for the quick response. I'm sure this will help, but the sybase version of the database is not avaliable on the internet, so a web server will not be able to connect to the database. I've got to figure out a way to copy the sybase data over to a file (maybe access) and ftp it or copy it to a computer that the web server can see (maybe the web server it's self) all automaticlly by itself.

Once I get the data to the web server I can use your idea to move it into mysql.

it's the copy and move that getting me right now.

let me know

jef

 

[ingresman]

you could write a bit of PHP to operate in command-line mode which will connect to the sybase and to the mysql at the time and simply operate a read-then-insert loop. If you have a massvie result set from sybase that might take a little while. To automate you cold use cron on unix or at on windows.
You can possibly use any MS tools you might have (such as MSDTS) to connect to sybase as sqlserver came from sybase and both use TDS as the underyling protocol. In any case ODBC should help you out.

 

[jefargrafx]

yep!

odbc will get the stuff sorted, got a test version runing on my desktop and can connect via ms access just fine. I'm starting to think that a batch job that could run on the server (where the sybase data lives) that could tp an acccess file over to the webb server nightly might be a the answer. And take mysql out of the picture. all I need are three tables from the sybase database and my own user table to secure the info.

any thoughts

jef

 

[jpadie]

just install php and mysql on the sybase local machine. a LAMP/MAMP/WAMP solution is easiest and will take 3 minutes. then run mysqldump on the mysql db and copy the file across to where ever you want it to end up.

 

[jefargrafx]

now that a great idea, (why didn't I think of that)

only possible issue is security, the stuff the have on this box needs to be safe and they dont want it exposed to the internet. But I'll ask. I'm thinking secure ftp daily might be as much as they will go.

thanks

jef

 

[jpadie]

it does not need to be exposed to the internet. just have LAMP/WAMP installed and make sure that port 80 is only listened to on 127.0.0.1/localhost etc. or as ingresman says - set up a cron job and use a batch file to automate the php script (for the transfer from sybase to mysql), the mysql dump and the ftp output.

you could also set up mysql replication if you were willing to open up the relevant ports. this would avoid the ftp part.

 

[jefargrafx]

thanks guys,

this seems like a real possibility. I'll run it pass the owners of the server and see how they fell about it.

I'd like it a simple as possible and this sounds better than ftp a copy of the sybase to another pc and then insert into mysql

I'll let ya know how it works out

jef