As400 ISeries数据库中的非常大的字段 [英] very large fields in As400 ISeries database

问题描述

我想将一个较大的XML字符串（可能长于32K或64K）保存到AS400文件字段中。 DDS或SQL文件都可以。下面的SQL文件示例。

 创建表MYLIB / PRODUCT 
（PRODCODE DEC（5）NOT NULL WITH DEFAULT， 
 PRODDESC CHAR（30）缺省值不为空，
 LONGDESC CLOB（70K）ALLOCATE（1000）缺省值不为空）
  

我们将使用RPGLE读取和写入字段。

目标是通过ODBC连接在服务器上提取数据。

AS400字符字段似乎有32K的限制，所以这不是一个好选择。

什么我有什么选择？我一直在阅读CLOB，但是在将大字符串写入CLOBS和远程读取CLOB字段方面似乎存在一些限制。请注意，客户端（仍）在AS400 OS的v5R4上。

谢谢！

---

查尔斯的以下答案显示了如何提取数据。我想插入数据。该代码可以运行，但是会引发 22501 SQL错误。

  D wLongDesc s 65531a改变
 D longdesc s sqltype（CLOB：65531）
 
 / free 
 // eval longdesc = * ALL'123'; 
 eval Wlongdesc = 123; 
 
执行程序SQL 
插入产品（PRODCODE，PRODDESC，LONGDESC）
值（123，产品描述，：LongDesc）； 
 
如果％subst（sqlstt：1：2）<> ‘00’； 
 //发生错误。 
 endif; 
 
 //显式获取长度，变量由预处理器设置
 longdesc_len =％len（％trim（longdesc_data））; 
 
 wLongDesc =％subst（longdesc_data：1：longdesc_len）; 
 
 /无结束
 C Eval * INLR = *在
 C上返回
  

附加问题：这种技术是否适合存储我以后想通过ODBC连接提取的数据？ ODBC是将CLOB读取为指针还是将其提取为文本？

解决方案

在v5r4上，RPGLE实际上支持64K字符变量。 p>

但是，常规char / varchar字段的数据库限制为32K。

您需要使用CLOB来存储大于32K的内容。

如果您可以使用64K （或如此）

 创建表MYLIB /产品
（PRODCODE DEC（5）缺省时不为空，
 PRODDESC CHAR（30）缺省值不为空，
 LONGDESC CLOB（65531）ALLOCATE（1000）缺省值不为空）
  

您可以使用RPGLE SQLTYPE 支持

  D代码S 5s 0 
d wLongDesc s 65531a变化
 D longdesc s sqltype（CLOB：65531）
 
 / free 
 exec SQL 
选择产品代码，从mylib / product 
中将longdesc 
转换成：code，：longdesc 
，其中prodcode =：mykey; 
 
 wLongDesc =％substr（longdesc_data：1：longdesc_len）; 
 DoSomthing（wLongDesc）; 
  

预编译器将用定义如下的DS替换longdesc：

  D longdesc ds 
 D longdesc_len 10u 0 
 D longdesc_data 65531a 
  

您可以直接直接使用它，确保最多使用 longdesc_len 或将其隐藏为VARYING

如果绝对必须处理大于64K的数据...

1. 升级到受支持的操作系统版本（支持16MB变量）


2. 使用文件引用通过IFS文件访问CLOB内容

选项2是我从未见过的一种。...我找不到任何示例。刚刚看到它在这篇旧文章中提到过。.
http://www.ibmsystemsmag.com/ibmi/developer/general/BLOBs,-CLOBs-and-RPG/?page=2

I would like to save a large XML string (possibly longer than 32K or 64K) into an AS400 file field. Either DDS or SQL files would be OK. Example of SQL file below.

CREATE TABLE MYLIB/PRODUCT
(PRODCODE DEC (5 ) NOT NULL WITH DEFAULT,
PRODDESC CHAR (30 ) NOT NULL WITH DEFAULT,
LONGDESC CLOB (70K ) ALLOCATE(1000) NOT NULL WITH DEFAULT)

We would use RPGLE to read and write to fields.

The goal is to then pull out data via ODBC connection on a client side.

AS400 character fields seem to have 32K limit, so this is not great option.

What options do I have? I have been reading up on CLOBs but there appear to be restrictions writing large strings to CLOBS and reading CLOB field remotely. Note that client is (still) on v5R4 of AS400 OS.

thanks!

---

Charles' answer below shows how to extract data. I would like to insert data. This code runs, but throws a '22501' SQL error.

D wLongDesc       s          65531a   varying                      
D longdesc        s                   sqltype(CLOB:65531)          

 /free                                                             
    //eval longdesc = *ALL'123';                                   
    eval Wlongdesc = '123';                                        

    exec SQL                                                       
    INSERT INTO PRODUCT (PRODCODE, PRODDESC, LONGDESC)             
    VALUES (123, 'Product Description', :LongDesc );               

    if %subst(sqlstt:1:2) <> '00';                                 
       // an error occurred.                                       
    endif;                                                         

    // get length explicitly, variables are setup by pre-processor 
    longdesc_len = %len(%trim(longdesc_data));                     

    wLongDesc = %subst(longdesc_data:1:longdesc_len);              

 /end-free                                                         
C                   Eval      *INLR = *on                          
C                   Return                                         

Additional question: Is this technique suitable for storing data which I want to extract via ODBC connection later? Does ODBC read CLOB as pointer or can it pull out text?

解决方案

At v5r4, RPGLE actually supports 64K character variables.

However, the DB is limited to 32K for regular char/varchar fields.

You'd need to use a CLOB for anything bigger than 32K.

If you can live with 64K (or so )

CREATE TABLE MYLIB/PRODUCT
(PRODCODE DEC (5 ) NOT NULL WITH DEFAULT,
PRODDESC CHAR (30 ) NOT NULL WITH DEFAULT,
LONGDESC CLOB (65531) ALLOCATE(1000) NOT NULL WITH DEFAULT)

You can use RPGLE SQLTYPE support

 D code            S              5s 0
 d wLongDesc       s          65531a   varying
 D longdesc        s                   sqltype(CLOB:65531)

  /free
   exec SQL
     select  prodcode, longdesc
      into :code, :longdesc
      from mylib/product
     where prodcode = :mykey;

   wLongDesc = %substr(longdesc_data:1:longdesc_len);
   DoSomthing(wLongDesc);

The pre-compiler will replace longdesc with a DS defined like so:

 D longdesc        ds
 D  longdesc_len                 10u 0
 D  longdesc_data             65531a

You could simply use it directly, making sure to only use up to longdesc_len or covert it to a VARYING as I've done above.

If absolutely must handle larger than 64K...

1. Upgrade to a supported version of the OS (16MB variables supported)
2. Access the CLOB contents via an IFS file using a file reference

Option 2 is one I've never seen used....and I can't find any examples. Just saw it mentioned in this old article.. http://www.ibmsystemsmag.com/ibmi/developer/general/BLOBs,-CLOBs-and-RPG/?page=2

这篇关于As400 ISeries数据库中的非常大的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！