I have a desktop application build in Visual Fox Pro, and another Web application build with html,css,javascript and php.
These applications have distinct databases, desktop(interbase) and web(MySQL). I need to show the data from the desktop's database in the Web application and to do so, I use a program to convert the desktop database to the format of the Web database, and then I upload the data manually.
Is there any way to do this process automatically?
I mean, everytime that new data is input in the desktop Application, this data is converted and uploaded to the Web database automatically?
Thanks for the help.
(The Easy Method) I use csv format to migrate data from vfp DB to mysql. First I created a simple application in vfp that use table and convert to csv file. Then I upload that file on server through PHP and use its function to extract data from csv and insert into mysql database.
(Some more Code required) As you guys have discussed I directly communicate with mysql from vfp through mysql odbc connector it is not so difficult if you have use sql server with vfp just some date format have to change.
My codes are below (Create database)
LPARAMETERS par_table, par_extra_field, par_after
IF VARTYPE(par_table) <> "C" OR LEN(ALLTRIM(par_table)) = 0
MESSAGEBOX("Table Not Found to Export", 16, "Table")
RETURN
ENDIF
IF VARTYPE(par_extra_field) <> "C"
par_extra_field = ""
ENDIF
IF VARTYPE(par_after) <> "C"
par_after = ""
ENDIF
DO mysql_connect && this file connect to mysql database and return hconnAZ as connection
sele (par_table)
ncount = AFIELDS(arr_fields)
SQL_NEW_TABLE = [CREATE TABLE IF NOT EXISTS ] + par_table + [(] + par_extra_field
FOR i = 1 TO FCOUNT()
DO CASE
CASE arr_fields(i, 2) = "L"
var_type = " tinyint "
CASE arr_fields(i, 2) = "C"
var_type = " char(" + ALLTRIM(STR( arr_fields(i, 3) )) + ") "
CASE arr_fields(i, 2) = "N"
IF arr_fields(i, 3) = 1 AND arr_fields(i, 4) = 0
var_type = " boolean "
ELSE
var_type = " decimal(" + ALLTRIM(STR( arr_fields(i, 3) )) + ;
IIF(arr_fields(i, 4) > 0, ", " + ALLTRIM(STR( arr_fields(i, 4) )), ",0") + ") "
ENDIF
CASE arr_fields(i, 2) = "D"
var_type = " date "
CASE arr_fields(i, 2) = "T"
var_type = " datetime "
CASE arr_fields(i, 2) = "I"
var_type = " integer "
ENDCASE
SQL_NEW_TABLE = SQL_NEW_TABLE + [`] + arr_fields(i, 1) + [`] + var_type + [ NOT NULL ] + IIF(i = FCOUNT(), [)], [,] )
ENDFOR
SQL_NEW_TABLE = SQL_NEW_TABLE + par_after
_cliptext = SQL_NEW_TABLE
MESSAGEBOX( SQLEXEC(hconnAZ, SQL_NEW_TABLE ) )
SQLDISCONNECT(hconnAZ)
(Data Upload to mysql database)
LPARAMETERS par_table, par_extra_field, par_extra_value
IF VARTYPE(par_table) <> "C" OR LEN(ALLTRIM(par_table)) = 0
MESSAGEBOX("Table Not Found to Export", 16, "Table")
RETURN
ENDIF
sele (par_table)
SET DELETED ON
ncount = AFIELDS(arr_fields)
SQL_INSERT = ["INSERT INTO ] + par_table + [(]
len_insert = LEN(SQL_INSERT)
FOR i = 1 TO FCOUNT()
SQL_INSERT = SQL_INSERT + [`] + arr_fields(i, 1) +[`] + IIF(i <> FCOUNT(), [,], [])
len_insert = len_insert + LEN([`] + arr_fields(i, 1) +[`] + IIF(i <> FCOUNT(), [,], []))
IF len_insert > 200
len_insert = 0
SQL_INSERT = SQL_INSERT + [" + "]
ENDIF
ENDFOR
IF VARTYPE(par_extra_field) = "C" AND NOT EMPTY(par_extra_field)
SQL_INSERT = SQL_INSERT + [, `] + par_extra_field + [`]
ENDIF
SQL_INSERT = SQL_INSERT + [) VALUES (" + ]
st = ["'"+]
en = [+"'"]
FOR i = 1 TO FCOUNT()
var_f_data = st + par_table + [.] + arr_fields(i, 1) + en
DO CASE
CASE arr_fields(i, 2) = "C"
var_f_data = st + [ ALLTRIM(] + par_table + [.] + arr_fields(i, 1) + [)]+ en
CASE arr_fields(i, 2) = "L"
var_f_data = [ IIF(] + par_table + [.] + arr_fields(i, 1) + [, '1', '0') ]
CASE arr_fields(i, 2) = "N" OR arr_fields(i, 2) = "I"
var_f_data = [ ALLTRIM(STR(] + par_table + [.] + arr_fields(i, 1) + [)) ]
CASE arr_fields(i, 2) = "D"
var_f_data = st + [ LEFT(TTOC(] + par_table + [.] + arr_fields(i, 1) + [, 3), 10) ] + en
CASE arr_fields(i, 2) = "T"
var_f_data = st + [ LEFT(TTOC(]+ par_table + [.] + arr_fields(i, 1) + [, 3), 10)] + " + SPACE(1) + " + ;
[RIGHT(TTOC(] + par_table + [.] + arr_fields(i, 1) + [, 3), 8) ] + en
ENDCASE
SQL_INSERT = SQL_INSERT + var_f_data + IIF(i = FCOUNT(), [], [+","+] )
ENDFOR
IF VARTYPE(par_extra_value) = "C" AND NOT EMPTY(par_extra_value)
SQL_INSERT = SQL_INSERT + [+","+"'] + ALLTRIM(par_extra_value) + ['"]
ENDIF
SQL_INSERT = SQL_INSERT + [+")"]
DO mysql_connect
sele (par_table)
total_rec = ALLTRIM(STR(RECCOUNT()))
SCAN
SQL_INSERT_Final = &SQL_INSERT
_cliptext=&SQL_INSERT
t = SQLEXEC(hconnAZ, SQL_INSERT_Final)
IF t <> 1
SCATTER MEMVAR
_cliptext = SQL_INSERT_Final
file_unposted = par_table+"_unposted"+".dbf"
IF NOT FILE(file_unposted)
COPY STRUCTURE TO &file_unposted
ENDIF
IF NOT USED( JUSTSTEM(file_unposted))
USE &file_unposted IN 0
ENDIF
SELECT (file_unposted)
APPEND BLANK
GATHER MEMVAR FIELDS EXCEPT ID_NO
ENDIF
sele (par_table)
t = ALLTRIM(STR(RECNO())) + " / " + total_rec
WAIT t WINDOW AT 0, 0 NOWAIT NOCLEAR
ENDSCAN
SQLDISCONNECT(hconnAZ)
(Mysql_Connect)
PUBLIC hconnAZ
sql_driver = "MySQL ODBC 5.3 ANSI Driver"
*sql_driver = "MySQL ODBC 3.51 Driver" &&older version
data_source = "localhost"
database = "az"
userid = "root"
password = ""
CStringAZ="DRIVER={"+sql_driver+"};SERVER="+data_source+";PORT=3306;DATABASE="+database+"; USER="+userid+";PASSWORD="+password+";OPTION=3;"
hconnAZ = SQLSTRINGCONNECT(CStringAZ)
IF hconnAZ <= 0
= MESSAGEBOX('Cannot make connection with Database.', 16, 'Database Connection Error')
ENDIF
EDIT
create CSV from vfp
CLOSE ALL
var_file = GETFILE("dbf", "File to Export", "Export")
var_dir = GETDIR() + "\"+ JUSTSTEM(var_file)+".csv"
USE &var_file IN 0
SELECT JUSTFNAME(var_file)
SET SAFETY OFF
DELETE FILE &var_dir
SET SAFETY ON
COPY TO &var_dir TYPE DELIMITED
Upload file and insert in mysql code
<?php
include("db_connect.inc"); // connect to mysql
function import_from_csv()
{
echo "
<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html xmlns=\"http://www.w3.org/1999/xhtml\" xml:lang=\"en\" lang=\"en\">
";
echo "
<HTML>
<HEAD>
<TITLE>Import Data from CSV File</TITLE>
</HEAD>
<BODY>
<form id=\"form1\" name=\"form1\" enctype=\"multipart/form-data\" method=\"post\" action=\"".$_SERVER['PHP_SELF']."?type=post\">
<table width=\"90%\" border=\"2\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" >
<tr valign = \"centre\">
<td height = \"50\" colspan=\"4\" bgcolor=\"#E6E6E6\" align =\"center\" >
<h3></br>Import Data from CSV File</h3>
</td>
</tr>
</table>
<table width=\"85%\" align=\"center\" >
<tr>
<td align=\"center\"><BR>
<!-- MAX_FILE_SIZE must precede the file input field -->
<input type=\"hidden\" name=\"MAX_FILE_SIZE\" value=\"20000000\" />
<!-- Name of input element determines name in $_FILES array -->
<input type=\"file\" name=\"CSVFile\" value=\"\" size=\"60\"/><BR>
<input type=\"submit\" name=\"Submit\" value=\"Import Data\" />
</td>
</tr>
</table>
</form>
</body>
</html>";
}
if (isset($_POST['Submit']) )
{
$uploaddir = '../';
$uploadfile = $uploaddir . basename($_FILES['CSVFile']['name']);
if (move_uploaded_file($_FILES['CSVFile']['tmp_name'], $uploadfile)) {
// echo "File is valid, and was successfully uploaded.\n";
} else {
echo '<pre>';
echo "File Not Uploaded Successfully";
print_r($_FILES);
exit();
print "</pre>";
}
$table_CHART_CREATE = query_sql($conn, "
CREATE TABLE IF NOT EXISTS `chartofacc` (
`CO_CODE` varchar(4) NOT NULL DEFAULT ''
PRIMARY KEY (`CO_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
");
$sql = 'REPLACE INTO chartofacc (ACC_CODE) VALUES ';
$row = 0;
$handle = fopen($uploadfile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql .= (($row == 0) ? '' : ', ')."('".$data[1]."')";
$row++;
}
query_sql($conn, $sql); // my own defined function (just mysqli code to execute query)
}
else import_from_csv() ;
?>
EDIT 2
You can simply update data locally and then using mysql odbc connector https://dev.mysql.com/downloads/connector/odbc/ , update data in mysql. This method will only work if your mysql can listen from other ports than 127.0.0.1(localhost).