Member Avatar for vspadmanabhan

Hi,
I have to create an executable to put in windows task bar to read foxpro dbf file and update mysql table in the web server by checking new records in the dbf file comparing the unique key in both table whenever the internet connection is available in the desktop with timer. Any one please find a solution and code.

S.Padmanabhan

  • 3 Contributors
  • 4 Replies
  • 335 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

For the scenario described by (desktop app polling a Visual FoxPro DBF and sending new records to a MySQL table), a reliable pattern is: read DBF locally in shared/read-only mode, queue new rows locally, and push batches over HTTPS to a PHP endpoint that enforces idempotency. This builds on ' note that VB can connect to databases and on 's point about VB.NET as a practical client option.

Implementation notes and gotchas:

  • Read DBF with the Visual FoxPro OLE DB provider (or a DBF-reading library) opened for shared access to avoid lock conflicts and to handle .fpt memo files. Convert character encoding to UTF-8 and normalize dates before sending.
  • Track sync state locally (small SQLite DB or a state file) rather than modifying the DBF. Store last-processed unique key or per-row checksum so restarts are safe.
  • Queue records and send in batches. Batching reduces round-trips and allows retry of failed batches without re-scanning entire table.
  • Server-side must enforce uniqueness (MySQL UNIQUE index) and use prepared statements plus an idempotent insert pattern (INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE) so duplicate deliveries are safe.
  • Use HTTPS, an auth token or HMAC signature, and server-side logging. Implement exponential backoff and durable retry for intermittent connectivity. Return per-record status so the client can mark queue entries as done.

Compact examples (adapt and secure for production):

VB.NET (read DBF + POST JSON; requires VFPOLEDB and Json.NET)

' Requires: System.Data.OleDb, System.Net.Http, Newtonsoft.Json
Imports System.Data.OleDb
Imports System.Net.Http
Imports System.Text
Imports Newtonsoft.Json

Async Function PushNewRecordsAsync() As Task
    Dim dbDir = "C:\foxdb"
    Dim connStr = $"Provider=VFPOLEDB.1;Data Source={dbDir};"
    Dim lastKey = LoadLastProcessedKey()
    Dim rows As New List(Of Dictionary(Of String, Object))

    Using cn As New OleDbConnection(connStr)
        cn.Open()
        Dim sql = "SELECT unique_id, name, datefield FROM mytable WHERE unique_id > ?"
        Using cmd As New OleDbCommand(sql, cn)
            cmd.Parameters.AddWithValue("?", lastKey)
            Using rdr = cmd.ExecuteReader()
                While rdr.Read()
                    rows.Add(New Dictionary(Of String, Object) From {
                        {"unique_id", CLng(rdr("unique_id"))},
                        {"name", rdr("name").ToString()},
                        {"date", Convert.ToDateTime(rdr("datefield")).ToString("yyyy-MM-dd")}
                    })
                End While
            End Using
        End Using
    End Using

    If rows.Count = 0 Then Return
    Dim client As New HttpClient()
    client.DefaultRequestHeaders.Authorization = New System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", "REPLACE_TOKEN")
    Dim json = JsonConvert.SerializeObject(rows)
    Dim resp = Await client.PostAsync("https://yourserver.com/sync.php", New StringContent(json, Encoding.UTF8, "application/json"))
    If resp.IsSuccessStatusCode Then SaveLastProcessedKey(rows.Max(Function(r) CLng(r("unique_id"))))
End Function

PHP server endpoint (accept JSON, PDO, idempotent insert)

<?php
$secret = 'REPLACE_TOKEN';
if (strpos($_SERVER['HTTP_AUTHORIZATION'] ?? '', 'Bearer ') !== 0 || substr($_SERVER['HTTP_AUTHORIZATION'],7) !== $secret) {
  http_response_code(401); echo json_encode(['error'=>'unauthorized']); exit;
}
$data = json_decode(file_get_contents('php://input'), true);
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4','dbuser','dbpass',[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$sql = "INSERT INTO mytable (unique_id,name,date) VALUES (:id,:name,:date) ON DUPLICATE KEY UPDATE name=VALUES(name), date=VALUES(date)";
$stmt = $pdo->prepare($sql);
$pdo->beginTransaction();
foreach ($data as $r) $stmt->execute([':id'=>$r['unique_id'],':name'=>$r['name'],':date'=>$r['date']]);
$pdo->commit();
echo json_encode(['ok'=>true]);
?>

Caveat: replace placeholders, enforce TLS, and test with concurrent DBF writes. This pattern provides durability, avoids duplicates, and keeps server-side integrity even with flaky desktop connectivity.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

What language are you going to use ?

Member Avatar for vspadmanabhan

Hi,
I have to create an executable to put in windows task bar to read foxpro dbf file and update mysql table in the web server by checking new records in the dbf file comparing the unique key in both table whenever the internet connection is available in the desktop with timer. Any one please find a solution and code.

S.Padmanabhan

I wish to use vb in the Desktop and PHP in the serverside script.

Edited by vspadmanabhan because: Change the Desktop language
Member Avatar for pritaeas

You can use VB to access mysql directly I think.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.