如何在Informix中打开和读取文件 [英] How to open and read a file in Informix
问题描述
我需要编写一个存储过程，该存储过程从包含员工记录(多行)的文本文件中读取输入，并将这些值与employee表中的值进行比较，并在有任何修改的情况下编辑employee表中的行.我正在使用连接类型为Informix的 DB Visualizer 工具.由于我是Informix的新手，所以我不知道如何开始.
I need to write a stored procedure which reads input from a text file which contains employee records (multiple rows) and compares those values to that in the employee table and edits the row in the employee table if there are any modifications. I am using the DB Visualizer tool with connection type as Informix. As I am new to Informix, I don't know how to start.
场景是:
我有一个Employee表，其中包含emp_no，dept_no，fname，lname，crp_id，sal，hours等字段. 我在'C:\ sample.txt'中有一个包含数据的文本文件
I have an Employee table which has fields emp_no, dept_no, fname, lname, crp_id, sal, hours etc. I have a text file in 'C:\sample.txt' with data
111 222 fname lname 3456
112 223 fname2 lname2 3457..
我需要读取文本文件的第一行，获取emp_no(111)并从emp表中获取特定记录.现在，检查其他字段并根据需要进行修改.对于Ex，如果emp表中emp 111的名字是'fname1'而不是'fname'，则我需要对其进行更新.顺便说一句，它是从文本文件读取的，而不是从外部表读取的.
I need to read the first line of the text file, get the emp_no(111) and get the particular record from the emp table. Now, check for the other fields and modify if necessary. For Ex, if the first name for emp 111 in emp table is 'fname1' instead of 'fname', I need to update it. And BTW, it is from a text file that I am reading not from an external table.
推荐答案
假设您使用的是Informix的最新版本(12.10或11.70)，则可能应该将文件映射到外部表，并且然后使用MERGE命令将更新合并，并从外部表中插入(可能删除)到新表中.从理论上讲，大约有5条SQL命令:
Assuming you are using a recent enough version of Informix (12.10, or 11.70), then you should probably be looking to map the file to an external table, and then using the MERGE command to merge the updates and inserts (and possibly deletes) from the external table into the new table. In theory, that's about 5 SQL commands:
BEGIN WORK;
CREATE EXTERNAL TABLE mergeable_data (...) ...;
MERGE ... YourMainTable ... FROM mergeable_data
ON MATCH UPDATE ...
ON NO MATCH INSERT ...; -- Check the syntax; I'm inventing on the fly!
COMMIT WORK;
DROP TABLE mergeable_data;
如果您有未记录的数据库，则不需要BEGIN和COMMIT(但是为什么?).总体而言，应该记录您的数据库.您可以将COMMIT放在DROP之后；如果您有MODE ANSI数据库，则在DROP之后可能需要另一个COMMIT.
The BEGIN and COMMIT aren't necessary if you have a non-logged database (but why?). On the whole though, your database should be logged. You could place the COMMIT after the DROP; you could need another COMMIT after the DROP if you have a MODE ANSI database.
- IDS 12.10 Information Centre
- CREATE EXTERNAL TABLE
- MERGE
考虑到问题中的其他信息，我比以往任何时候都更加确信使用CREATE EXTERNAL TABLE来指定磁盘上的文件作为数据源，而MERGE语句正是您所需要的. CREATE EXTERNAL TABLE语句允许IDS将文件视为表一样对待. MERGE语句自动执行(无需进行任何编程)会执行诸如由于名字不同而更新名字"之类的事情.
Given the extra information now in the question, I am more convinced than ever that using CREATE EXTERNAL TABLE that specifies the file you've got on disk as the data source and the MERGE statement is exactly what you need. The CREATE EXTERNAL TABLE statement allows IDS to treat the file as if it was a table. The MERGE statement automatically (with no programming on your part) does things like 'update the first name because it is different'.
如果愿意，您可以用另一种方式来做-成为我的客人.您在使用Informix 4GL(I4GL)还是其他某种语言? I4GL是必须购买的单独产品，它会创建针对数据库运行的已编译程序.我不知道DB Visualizer如何适合该系统.如果使用的是I4GL，则可以创建临时表，然后(可能)将数据加载到该临时表中，然后进行匹配工作.或者，您可以在SQL ... END SQL块中使用CREATE EXTERNAL TABLE和MERGE.
You can do it in another way if you wish — be my guest. Are you using Informix 4GL (I4GL) or some other language? I4GL is a separate product that must be purchased, and creates compiled programs that run against the database. I've no idea how DB Visualizer would fit into that system. If you are using I4GL, you can create your temporary table and then (probably) load the data into it, and then do your matching work. Or you could use CREATE EXTERNAL TABLE and MERGE in SQL ... END SQL blocks.
我敢肯定，这也会使您感到困惑；您对Informix并不十分熟悉，也不太确定自己所拥有的和正在使用的. I4GL完全不使用JDBC.它建立在Informix ESQL/C之上.使用普通的ESQL/C是另一种选择，但仅适用于像我这样的受虐狂(已将其用于æons).即便如此，我仍然希望使用CREATE EXTERNAL TABLE和MERGE.
I'm sure it is confusing for you, too; you're not terribly familiar with Informix and you're not quite sure what you've got and what you're using. I4GL does not use JDBC at all; it is built atop Informix ESQL/C. Using plain ESQL/C is another option, but only for masochists like myself (who've been using it for æons). And even so, I'd still be looking to use CREATE EXTERNAL TABLE and MERGE.
这篇关于如何在Informix中打开和读取文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
