Re: RPG SQL insert into

On 05-Oct-2015 09:00 -0600, Hiebert, Chris wrote:

On 30-Sep-2015 15:52 -0600, Booth Martin wrote:

I have two problems:
The first is that the following does not work

exec sql insert into FILEP (FILEKEY, FILESEQ#, FILEDATA)
values('Test 1', 3, :'ddddd');

The second is that I do not know how to find the problem.

Using the STRSQL is good if you are not actually trying to test a
host variable. i.e. The SQL syntax where a colon is followed by an
RPG Variable name ":rpgvarname".

If you are only testing the syntax of the statement, and understand
that all host variables need to be replaced with a static values,
then using STRSQL is great.

There is no need to replace :HV with constants\literals. The Start SQL Interactive Session (STRSQL) feature does enable syntax checking for host language statements. Within an existing session, navigate:
F13=Services -> 1=Change Session Attributes -> Statement Processing=*SYN -> Program Language=_select-an-HLL-name_
Or, for a new SQL session, specify the *SYN special-value on the PROCESS() parameter and specify the appropriate special-value as HLL-name on the PGMLNG() parameter. In either case, be sure to remove the EXEC SQL prefixing the statement and the semicolon as statement terminator, as the syntax-checker is not [at least had never been] designed to deal with those effective /pre-compiler-directives/, just the statement by itself.

The above INSERT statement from the OP, syntax-checked per specifications PGMLNG(*RPG) PROCESS(*SYN) effects the following syntax error, which suggests that an /identifier/ [a name, such as in this case, a variable name] is expected where instead was found the literal character-string data [i.e. the apostrophe as start-of-literal designator was found following a colon, instead of an identifier following a colon]:
SQL0104 "Token 'ddddd' was not valid. Valid tokens: <IDENTIFIER>."

But half the time it's the Host Variables that are mucking up the
statement.
<<SNIP>>

FWiW: Since the addition of CREATE VARIABLE, as I understand, though I have no personal experience, the _Global_ Variable name is referred to without the colon-as-prefix [as with a _Host_ Variable]. Thus statements that would normally be coded to use host variables should be able to be performed instead using a Global Variable in STRSQL, and for both *RUN and *VLD processing, versus only with PROCESS(*SYN) as with Host Variables. For example, the given INSERT, after the variable has been created\assigned, could be coded as shown below:

exec sql create or replace variable GVname1
char(5) ccsid 37 default 'ddddd'

exec sql insert into FILEP (FILEKEY, FILESEQ#, FILEDATA)
values('Test 1', 3, GVname1);