Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.
SQL null values in RPG
Hi Experts,
I have some SQLRPGLE problems with reading a tabel (with null capable fields)
The program reads a record in a DS , change 2 fields and write it to a remote (identical) table.
But I have to add a null "check field" per field
exec sql fetch next from cIMPH INTO
:DS_REFERENCE :ISNUL1,
:DS_CONTEXT :ISNUL2,
:DS_OBJECT_TYPE :ISNUL3;
In this example it's not a problem, but I need to do this for a file with round 100 fields.
So I prefer to use the SQL
exec sql fetch next from cIMPH INTO :DS;
On top of that, if I use the null-check field, the original field contains a space or a zero.
the remote table expect null values.
How can I solve this?
I have some SQLRPGLE problems with reading a tabel (with null capable fields)
The program reads a record in a DS , change 2 fields and write it to a remote (identical) table.
But I have to add a null "check field" per field
exec sql fetch next from cIMPH INTO
:DS_REFERENCE :ISNUL1,
:DS_CONTEXT :ISNUL2,
:DS_OBJECT_TYPE :ISNUL3;
In this example it's not a problem, but I need to do this for a file with round 100 fields.
So I prefer to use the SQL
exec sql fetch next from cIMPH INTO :DS;
On top of that, if I use the null-check field, the original field contains a space or a zero.
the remote table expect null values.
How can I solve this?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Create an array for the null indicators:
dcl-s NullInds int(5) dim(100);
then do the fetch like this:
fetch cIMPH into :bigDS :NullInds;
Then on the INSERT:
INSERT INTO remote-table-name
(column-name1, column-name-2, ....)
VALUES (:host-var1:nullInds(1), :host-var2:nullInds(2), ...)
dcl-s NullInds int(5) dim(100);
then do the fetch like this:
fetch cIMPH into :bigDS :NullInds;
Then on the INSERT:
INSERT INTO remote-table-name
(column-name1, column-name-2, ....)
VALUES (:host-var1:nullInds(1), :host-var2:nullInds(2), ...)
Hi Gary,
I figured that out allready, but then I need to make an insert statment with 100 fields and 100 values (+ nullind),
that will become unreadable and unmaintainable
What if I read the file using RPG with ALWNULL(*USRCTL)
(until now I avoid using null capability, because of this problems)
I figured that out allready, but then I need to make an insert statment with 100 fields and 100 values (+ nullind),
that will become unreadable and unmaintainable
What if I read the file using RPG with ALWNULL(*USRCTL)
(until now I avoid using null capability, because of this problems)
Hi Gary,
After some trial & error, its working (until now)
VALUES (:host-var1:nullInds(1), :host-var2:nullInds(2), ...) is not working, compile error on use of array index :-(
But VALUES (:dataDS:nullInds) is functioning as long as dataDS is not qualified, so writeout per field is not required :-)
After some trial & error, its working (until now)
VALUES (:host-var1:nullInds(1), :host-var2:nullInds(2), ...) is not working, compile error on use of array index :-(
But VALUES (:dataDS:nullInds) is functioning as long as dataDS is not qualified, so writeout per field is not required :-)
Good solution. You can also get around the array index issue by overlaying the NullInds array with a DS of named elements, then using the meaningful names in your SQL statement. This is particularly useful if the SELECT DS and INSERT format aren't identical, and you are only dealing with a subset of columns.
dcl-ds ids;
meaningfulName1Null int(5);
meaningfulName2Null int(5);
meaningfulName3Null int(5);
meaningfulName4Null int(5);
meaningfulName5Null int(5);
NullInds int(5) dim(5) pos(1);
End-Ds;
Then in SQL:
VALUES (:meaningfulName1:meaningf
And as for writing maintainable SQL, use this format, with one variable/null indicator pair per source line:
VALUES (
dcl-ds ids;
meaningfulName1Null int(5);
meaningfulName2Null int(5);
meaningfulName3Null int(5);
meaningfulName4Null int(5);
meaningfulName5Null int(5);
NullInds int(5) dim(5) pos(1);
End-Ds;
Then in SQL:
VALUES (:meaningfulName1:meaningf
And as for writing maintainable SQL, use this format, with one variable/null indicator pair per source line:
VALUES (
:meaningfulName1:meaningfu
:meaningfulName2:meaningfu
...
)
:meaningfulName2:meaningfu
...
Hi Gary,
Now the next problem:
Nullind in SQL is working fine.
The nullind in RPG is working also (I assume)
SQL expect an array of integer(5) fields with indication 0 or -1 as nullind.
I have to read the record from the local file (with RPG) and have to write it to a remote DB with SQL but the nullind fields are not compatible.
(not investigated yet) but I don't think that I can read with SQL from the local DB and write to an remote server in the same program?
Now the next problem:
Nullind in SQL is working fine.
The nullind in RPG is working also (I assume)
FMYIMPH IF E K DISK ALIAS
DIMPH DS LIKEREC(IMPH_00001)
D NULLIND(IMPHNULL)
DIMPHNULL DS LIKEREC(IMPH_00001:*NULL)
SQL expect an array of integer(5) fields with indication 0 or -1 as nullind.
I have to read the record from the local file (with RPG) and have to write it to a remote DB with SQL but the nullind fields are not compatible.
(not investigated yet) but I don't think that I can read with SQL from the local DB and write to an remote server in the same program?
In the past, I've always segregated operations like this in separate programs. If both systems are IBM I db2, you can access both the local and remote db in the same program. Pre v7r1 you have to explicitly CONNECT to each system each time you switch target DBs, but you dont lose existing connections when you do that. In v7r1, you can use 3 part naming and skip the CONNECT TO step. Havent tested this with Ardgate running to a non IBM I remote db, though.
I assume you are constructing SQL dynamically. If so, you could just use a set of IF blocks to test each null indicator and append the special value NULL to your SQL INSERT string in place of a value when the null indicator is on. This enables you to avoid the whole null indicator issue on the INSERT completely.
Hi Gary,
"I assume you are constructing SQL dynamically."
Wrong assumption :-)
I will write the complete records 1:1 from the local table to the remote-DB.
Otherwise I have to dynamically generate the field names (up to 100 names) and the values.
"I assume you are constructing SQL dynamically."
Wrong assumption :-)
I will write the complete records 1:1 from the local table to the remote-DB.
Otherwise I have to dynamically generate the field names (up to 100 names) and the values.
Found a workaround :-)
It's not very nice, but it will work.
DIMPH DS LIKEREC(IMPH_00001)
D NULLIND(iNull)
DiNull Ds LIKEREC(IMPH_00001:*Null)
DoNull S 5I 0 dim(100)
clear oNull;
For idx = 1 to %len(iNull);
If %subst(iNull:idx:1) = '1';
oNull(idx) = -1;
endif;
endfor;
It's not very nice, but it will work.
If you dont like the NULL VALUES, you coould actually try modifying your SQL select statement to include COALESCE. For example
Select Cast(COALESCE(b.Account_Na
Select Cast(COALESCE(b.Account_Na
@P_S_PRICE: Unfortunately, that's not even remotely related to this problem.
If you'll review the entire thread, you'll see that the poster is reading from a null-capable table on one system, and writing to a null-capable table on another system, and needs to set the null indicator correctly (and if I recall correctly from another thread, the remote DB may not be IBM i DB2, so null indicators may require a different format).
If you'll review the entire thread, you'll see that the poster is reading from a null-capable table on one system, and writing to a null-capable table on another system, and needs to set the null indicator correctly (and if I recall correctly from another thread, the remote DB may not be IBM i DB2, so null indicators may require a different format).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial