odbc连接字符串格式，找不到文件
这是一个可能有单一答案的“双重”问题。
我正在使用带有 AS/400 的 Odbc 连接，连接字符串如下：
driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; DefaultLibraries=*USRLIBL;
我能够正常连接到系统。
*USRLIBL 包含用户所需的所有库（属于“仅 API”类型，可以访问所有用户库）。
但是，当我尝试访问某些 ERP 库时，它说找不到它们，而其他库则可以。
因此，作为一个非常基本的演练：
1. Open Connection - Query File 1 from Library A: OK! - Close Connection
2. Open Connection - Query File 2 from Library A: OK! - Close Connection
3. Open Connection - Query File 1 from Library B: Exception SQL0204 - in UserName type *FILE not found
好的，所以我在 ERP 文件所在的特定库中添加了连接字符串，如下所示，只是为了测试程序：
driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; DefaultLibraries=*USRLIBL, LibraryB;
但是然后我开始遇到不同的问题（另一个非常基本的演练） ）
1. Open Connection - Query File 1 from Library A: OK! - Close Connection
2. Open Connection - Query File 2 from Library A: OK! - Close Connection
3. Open Connection - Query File 1 from Library B: OK! - Close Connection
4. Open Connection - Query File 1 from Library A again: Exception SQL0202 - in LibraryB type *FILE not found.
所以我的问题是：
为什么 odbc 连接字符串 DefaultLibraries=*USRLIBL 不返回正确的库？ （注意：我还使用 iDB2Connection 对此进行了测试，实际上它工作得很好...但是，iDB2Connection 无法部署，因为它实际上使服务器崩溃）
为什么第二个演练会抛出异常，它似乎只是“跳过” *USRLIBL 从 LibraryB 读取一次后。
有什么想法吗？
开始编辑：
实际上有两个用户，DEV和PROD
*USRLIBL从环境本身获取所有必需的库，因此如果在打开连接时，它检测到本地主机环境或任何不安全的东西（加上一些其他注意事项），它默认在创建连接之前使用 DEV 登录凭据。这就是为什么系统、uid 和 pwd 被指定为连接中的参数（而不仅仅是 stackoverflow 我不想给出数据占位符），
然后 *USRLIBL 从 API 用户处提取必要的库。
澄清一下，它的设置方式确实可以使用 iDB2 连接器工作，但由于我们的 ERP 系统的限制（我们认为），将其与 IIS 7 服务器一起使用会导致灾难性的失败，因此我们正在使用 ODBC 连接器。
结束编辑：
This is kind of a 'double' question that might have a single answer.
I'm working with an Odbc Connection with an AS/400, with my connection string as follows:
driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; DefaultLibraries=*USRLIBL;
I'm able to connect to the system fine.
*USRLIBL contains all the necessary libraries from the user (which is of the type 'API only' which has access to all user libraries).
However, when I try to access certain ERP libraries, it says they can't be found, while other ones can.
So as an extremely basic walkthrough:
1. Open Connection - Query File 1 from Library A: OK! - Close Connection
2. Open Connection - Query File 2 from Library A: OK! - Close Connection
3. Open Connection - Query File 1 from Library B: Exception SQL0204 - in UserName type *FILE not found
Ok, so I added in the specific library that the ERP files would be in, making the connection string as follows, just to test the program:
driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; DefaultLibraries=*USRLIBL, LibraryB;
But then I start getting a different problem (another extremely basic walkthrough)
1. Open Connection - Query File 1 from Library A: OK! - Close Connection
2. Open Connection - Query File 2 from Library A: OK! - Close Connection
3. Open Connection - Query File 1 from Library B: OK! - Close Connection
4. Open Connection - Query File 1 from Library A again: Exception SQL0202 - in LibraryB type *FILE not found.
So my question(s) are:
Why doesn't the odbc connectionstring DefaultLibraries=*USRLIBL not return the correct libraries? (Note: I also tested this using an iDB2Connection which in fact works fine... however, the iDB2Connection can not be deployed as it literally crashes the server)
Why does the second walkthrough throw an exception, it just seems to 'skip past' *USRLIBL after reading from LibraryB even once.
Any thoughts?
Begin Edit:
There are actually two users, DEV and PROD
The *USRLIBL gets all the necessary Libraries from the Environment itself, so if when opening the connection, it detects a localhost environment, or anything that's unsecure (plus a few other caveats), it defaults to DEV log in credentials before creating the connection. This is why the system, uid, and pwd are designated as parameters in the connection (and not just stackoverflow I-dont-want-to-give-out-data placeholders)
The *USRLIBL then pulls the necessary libraries from the API user.
To Clarify, the way it's set up does work using the iDB2 Connector, but because of the limitations of our ERP system (we think), using it with an IIS 7 server causes a catastrophic failure, so we're working with the ODBC connector.
End Edit:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以将表名限定为library.filename，而不必处理任何库列表问题。
有关更多信息：
客户端访问 ODBC：默认库设置
<一href="http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzaik/connectkeywords.htm" rel="nofollow">ODBC 连接字符串关键字
相关部分摘录如下：
使用 SQL 命名约定，操作系统不会执行库列表搜索来查找不合格的对象。如果定义了默认集合，则使用默认集合来解析不合格的 SQL 语句。
...
使用 SYS 命名约定，不合格的 SQL 语句将转到默认集合。如果没有默认集合，则使用当前库。如果未指定当前库，则使用库列表。
...
默认集合
由 ODBC 设置的作业属性，用于确定处理包含不合格的 SQL 语句时使用的库SQL 名称。设置默认集合时，除了过程、函数和类型之外的所有非限定对象都必须驻留在默认集合中，无论命名约定如何。
...
如何让 ODBC 搜索库列表？ strong>
如上所述，编辑 ODBC 数据源并将系统命名设置为 SYS。默认库必须为空，或者在早于 R510 的版本上，默认库设置必须以逗号开头，以便不定义默认集合（例如，“,MYLIB1, MYLIB2”）。
尝试此连接字符串启用系统命名且不设置默认库：
driver={iSeries Access ODBC Driver}；系统={0}； uid={1}；密码={2}；命名=1； DefaultLibraries=,*USRLIBL,LibraryB;You can qualify your table names as library.filename and not have to deal with any library list issues.
For more information:
Client Access ODBC: Default Libraries Setting
ODBC connection string keywords
Excerpts of the relevant parts are:
With SQL naming convention, the operating system does not perform a library list search to locate an unqualified object. If a default collection is defined, the default collection is used to resolve unqualified SQL statements.
...
With the SYS naming convention, the unqualified SQL statements go to the default collection. If there is no default collection, the current library is used. If no current library is specified, the library list is used.
...
Default Collection
A job attribute set by ODBC that determines the library used when processing SQL statements that contain unqualified SQL names. When a default collection is set all unqualified objects except procedures, functions and types must reside in the default collection, regardless of naming convention.
...
How can I get ODBC to search the library list?
As explained above, edit the ODBC data source and set system naming to SYS. The default library must be empty, or on versions older than R510, the default libraries setting must start with a comma so that no default collection is defined (for example, ",MYLIB1, MYLIB2").
Try this connection string to enable system naming and to not set a default library:
driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; naming=1; DefaultLibraries=,*USRLIBL,LibraryB;如果有人遇到这篇文章并且像我一样使用 IBM.Data.DB2.iSeries .NET 数据提供程序，那么上面的关键点是使用
naming=1并且没有指定“默认”收藏”。在连接字符串中使用以下部分时，我终于成功了If anyone runs into this post and is using the IBM.Data.DB2.iSeries .NET data provider as I am, the key point taken from above was using the
naming=1and not specifying a "Default Collection". I was finally successful when using the following portion in my connection string另一种方法是为每个环境设置单独的用户配置文件。由于 *USRLIBL 是由职位描述设置的，因此还需要设置单独的职位描述。例如：
user: WEB job desc: WEB 库列表: CUSTPROD, ITEMPROD, UTILITY
user: WEBTEST job desc: WEBTEST 库列表: CUSTTEST, ITEMTEST, UTILITY
除了使用测试或生产用户 ID 进行身份验证外，C# 代码不会更改。
An alternative is to set up a separate user profile for each environment. Since the *USRLIBL is set by the job description, this would entail setting up a separate job description as well. For example:
user: WEB job desc: WEB library list: CUSTPROD, ITEMPROD, UTILITY
user: WEBTEST job desc: WEBTEST library list: CUSTTEST, ITEMTEST, UTILITY
The C# code does not change except for using the test or production user ID to authenticate.