Re: Avoiding CPF2973 and truncation

On Thu, Feb 10, 2022 at 8:44 AM frank boke <fboxxx1958@xxxxxxxxx> wrote:

Thanks for the info folks and I want to copy from pf to pf where the input
is 200 recl and the output is 100 recl like Rob noted. We want to
automatically have all the data be put into the 100 recl file without
having to change the 100 recl file in any way...I didn't think that was
possible so I figured I'd ask you folks for options/possibilities...are
there any? The output file is a "flat" file. What's the syntax of the sql
insert...I'll try anything at this point.

Here's a demo.

1. I create two tables, one with two 100-varchar fields, another with
one.
2. I fill the first table with 26 rows with the whole row filled with
that letter of the alphabet (1-26).
3. I fill the second table by splitting the each row of the first table
and making two rows in the second.

In iACS SQL:
create schema jaxtest;
create table jaxtest.a (field1 varchar(100), field2 varchar(100));
create table jaxtest.b (field1 varchar(100));

In Python:
import pyodbc

cn = pyodbc.connect("DSN=*LOCAL")
c200 = cn.cursor()
c100 = cn.cursor()

q2 = "select field1, field2 from jaxtest.a"
q2i = "insert into jaxtest.a (field1, field2) values(?, ?)"
q1i = "insert into jaxtest.b (field1) values(?)"

try:
s = ""
sl = list(" " * 100)
for i in range(26):
c = chr(ord("a") + i)
for x in range(100):
sl[x] = c
c200.execute(q2i, s.join(sl), s.join(sl))
c200.commit()
rows = c200.execute(q2).fetchall()
for row in rows:
c100.execute(q1i, row[0])
c100.execute(q1i, row[1])
c100.commit()
finally:
c200.close()
c100.close()
cn.close()