On Thu, Mar 20, 2008 at 1:41 PM, Velen <[EMAIL PROTECTED]> wrote:
>
> Actually I am updating TableA in DatabaseA with values from TableB in
> DatabaseB. Database B is on a stand alone PC. I'm using VB6 to create a
> .txt file containing data from TableB then using VB6 once more to
> recronstruct the table in DatabaseA then remove all data which are already in
> TableA and insert the remaining.
[snip!]
> How can I optimise this process? and What are the alternatives available ?
If you don't absolutely need to use VB6, why not use something
with native support like PHP?
<?
function dba_query($sql) { // Simply return the connection resource ID
// Select the primary database....
$dba_conn =
mysql_connect('hostname_a','username_a','password_a') or
die(mysql_error());
$dba_db = mysql_select_db('database_a',$dba_conn);
$r = mysql_query($sql,$dba_conn) or die(mysql_error());
return $r;
}
function dbb_query($sql) { // Simply return the connection resource ID
// Select the secondary database....
$dbb_conn =
mysql_connect('hostname_b','username_b','password_b') or
die(mysql_error());
$dbb_db = mysql_select_db('database_b',$dbb_conn);
$r = mysql_query($sql,$dbb_conn) or die(mysql_error());
return $r;
}
$sql = "SELECT field1,field2,field3,field4 FROM table_a";
$result = dba_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ssql = "INSERT INTO table_b(field1,field2,field3,field4)
VALUES(
'".$row['field1']."',
'".$row['field2']."',
'".$row['field3']."',
'".$row['field4']."'
}";
dbb_query($ssql) or die(mysql_error());
}
?>
If you decide to go that route, I recommend subscribing to the
PHP-DB list at http://php.net/mailinglists (referred to there as
"Databases and PHP"). You should see a significant gain in
performance using a native client as opposed to what you're now using
(probably an ODBC DSN, MyODBC, or a JDBC hack).
--
</Daniel P. Brown>
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
