在Glassfish服务器上从FoxPro数据更新sql数据库 [英] Update sql database from FoxPro data on Glassfish server

问题描述

我正在构建一个包含JavaFX客户端的系统，该客户端通过glassfish应用服务器上的servlet从MS SQL服务器提取数据。我还从Visual FoxPro数据库中消耗一些来自遗留系统的数据，这些遗留系统无法修改。我使用JDataConnect库在glassfish服务器上创建一个JDBC连接，我可以通过这个连接进行常规的SQL查询以获取我需要的数据。我的javafx客户端包括许多表视图绑定到从数据库检索到的数据的ObservableLists。有一张表是由MS SQL数据和FoxPro数据组成的数据。 FoxPro数据从不同的系统更新。由于客户可以通过多种方式切片并将切片呈现给用户，所以尝试保持这些复合数据同步（并非不可能，但很困难）是令人望而却步的。如果更改FoxPro数据库中的必要值，MS SQL数据库中的相关表中的列将更容易（并且更好地用于客户端的设计）。由于许多客户端将访问数据，因此客户端尝试更新数据库并不明智。

那么，我可以编写一个应用程序，它将运行在glassfish服务器将监视foxpro数据库并将必要的更改/数据写入SQL服务器数据库？我只是不知道从哪里开始开发这种类型的解决方案，如果的确如此，这是专家们的建议。我甚至不知道要搜索什么。

感谢您的任何帮助或想法。

解决方案

您可能无法修改旧的FoxPro系统，但是如果这些表存储在VFP数据库容器（即DBC文件）中，则可以编写存储过程触发器（插入，更新和/或删除）在VFP数据库容器中，并让它们直接写入SQL服务器数据库。
您需要：

1. 访问VFP数据库容器


2. 连接字符串到SQL服务器数据库
   
3. 为要推送到SQL Server表的VFP数据编写VFP存储过程触发器。

完成连接之后，您可以使用SQLEXEC（）命令编写VFP存储过程，该命令将处理语句的数据源的SQL语句（来自VFP帮助） 。例如

  m.lnFileHandle = SQLSTRINGCONNECT（m.lcConnectString）
 m.lcSQLCommand =insert into elctablebk.dbo。 t_bkcust（custidnum）values（'EdTest'）
 m.lnRetVal = SQLEXEC（m.lnFileHandle，m.lcSQLCommand）
  

上面的代码从连接到服务器获取文件句柄＃，使用SQL INSERT命令创建变量，并使用SQLEXEC（）命令在服务器上执行SQL语句。如果成功，则在服务器上的示例t_bkcust文件中插入一条记录。

I'm building a system which includes JavaFX clients pulling data from a MS SQL server via servlets on a glassfish app server. I also consume some data from a Visual FoxPro database that comes from a legacy system which can't be modified. I'm using the JDataConnect library to create a JDBC connection on the glassfish server against which I can make regular SQL queries to get the data I need.

My javafx clients include many tableviews which are bound to ObservableLists of data retrieved from the databases. There is one table which is a composite of data from the MS SQL data and the FoxPro data. The FoxPro data is updated from a different system. Since there are many ways for the client to slice and dice the data they present to the user, it is prohibitive to try to keep this composite data in sync (not impossible, but difficult). It would be easier (and better for the design of the clients) to update columns in relevant tables in the MS SQL db with the necessary values from the FoxPro database as they change. And since many clients will be accessing the data, it's not wise to have the clients trying to update the database.

So, can I write an application which will run on the glassfish server that will monitor the foxpro database and write the necessary changes/data to the the SQL server database? I just don't know where to start to develop this type of solution, if indeed, this is what the experts would suggest. I'm not even sure what to search for. Totally lost sums it up best.

Thanks for any help or ideas.

解决方案

You may not be able to modify the legacy FoxPro system, but if the tables are stored in a VFP Database container (i.e. DBC file), you may be able to write stored procedure triggers (insert, update and/or delete) within the VFP database container and have them write directly to the SQL server db. You'd need:

1. Access to the VFP database container
2. Connection string to the SQL server database
3. To write VFP stored procedure triggers for the VFP data you want pushed up to the SQL Server tables.

Once you have the connection sorted out, you can write VFP stored procedures using SQLEXEC() command which sends a "SQL statement to the data source, where the statement is processed" (from VFP help). E.g.

m.lnFileHandle = SQLSTRINGCONNECT(m.lcConnectString)
m.lcSQLCommand = "insert into elctablebk.dbo.t_bkcust (custidnum) values ('EdTest')"
m.lnRetVal = SQLEXEC(m.lnFileHandle, m.lcSQLCommand)

The above code gets a filehandle # from connecting to the server, a variable is created with an SQL INSERT command, and the SQL statement is executed on the server using the SQLEXEC() command. If successful, a record is inserted in the example t_bkcust file on the server.

这篇关于在Glassfish服务器上从FoxPro数据更新sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！