MS_SQL Connectivity

[Vu Pham]

On Fri, 2007-05-18 at 05:06 -0700, Shawn Tayler wrote:
[...]
> Well, my intention is to either run a query every 24 hours that pulls the
> past days data from probably one or 2 tables on each server.  this data is
> dup checked, cleaned up, and inserted into a set of tables in the Postgre
> box. The other option is to have the tables in question, which are present
> in the remote MS-SQL boxes, appear as tables in the PostgreSQL server.
> Either would be helpful to my purpose.

If you feel uncomfortable with Java, then try PHP. Below is an example
of using php to read a table from one server,  convert data to upper
case and then insert them in a second server, then print the data on the
second server. You can run it from the command line. it is just about 50
lines, including comments.

Although each database extension may have a little bit different in
syntax, but they are well documented and there are a lot of examples so
you can do it without any problem.


vu at coh:~> more a.php

//program starts here
<?php

// connection to  database 1
$connection = ocilogon("user1","apassword","//192.168.249.29/xe"); 

// connection to database 2
$connection_remote = ocilogon("user2",
"bpassword","//192.168.249.210/xe");

// query from some table of database1
$query="select * from carrier order by car_name";
$stmt = oci_parse($connection, $query);
oci_execute($stmt);

// command to update to temp table on database 2, using bind variables
$insert = "insert into temp values( :pk, :name, :email )";
$stmt_remote = oci_parse( $connection_remote, $insert );

// now reading row from db1 and insert into db2
while( $carrier = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)){
        echo $carrier['CAR_PK'];
        echo ', ';
        echo $carrier['CAR_NAME'];
        echo ', ';
        echo $carrier['CAR_EMAIL'];
        echo "\n";

        oci_bind_by_name( $stmt_remote, ":pk", $carrier['CAR_PK']);
        oci_bind_by_name( $stmt_remote, ":name",
strtoupper($carrier['CAR_NAME']));
        oci_bind_by_name( $stmt_remote, ":email",
strtoupper($carrier['CAR_EMAIL']));
        oci_execute($stmt_remote);

}

// be nice to the system
oci_free_statement($stmt);
oci_close($connection);
oci_free_statement($stmt_remote);

// print the remote table
print "\n\n\ncontent of the remote table:\n\n";
$query = "select * from temp order by temp_name ";
$stmt_remote = oci_parse( $connection_remote, $query);
oci_execute($stmt_remote);
while( $temp = oci_fetch_array( $stmt_remote, OCI_ASSOC
+OCI_RETURN_NULLS)) {
        echo $temp['TEMP_PK'];
        echo ', ';
        echo $temp['TEMP_NAME'];
        echo ', ';
        echo $temp['TEMP_EMAIL'];
        echo "\n";

}

// nice again
oci_free_statement( $stmt_remote);
oci_close($connection_remote);

?>
//--------- program ends here


Below is the output when you run the program from the command line.
vu at coh:~> php a.php 
2, ATT, mmode.com
1, Cingular, cingularme.com
5, Nextel, messaging.nextel.com
6, Sprint, sprintpcs.com
4, T-Mobile, tmomail.net
3, Verizon, vtext.com



content of the remote table:

2, ATT, MMODE.COM
1, CINGULAR, CINGULARME.COM
5, NEXTEL, MESSAGING.NEXTEL.COM
6, SPRINT, SPRINTPCS.COM
4, T-MOBILE, TMOMAIL.NET
3, VERIZON, VTEXT.COM
vu at coh:~>