Page 1 of 1
LANSA command parametrization?
Posted: Mon Apr 11, 2022 7:53 pm
by sotis
Hello,
I need to use the command Delete From_File(MYFILE) WITH_KEY and possibly others
where MYFILE is a variable determined at run time and prone to change.
For example say that there is a file named ANOTHER_FILE with a specific string field (MY_FILENAME_FIELD) that denotes a file name
Is somehow the following
pseudocode possible?
Code: Select all
Select Fields(#MY_FILENAME_FIELD) from File(ANOTHER_FILE)
MYFILE = #MY_FILENAME_FIELD *?????
Delete From_File(MYFILE) WITH_KEY...
EnsSelect
PS. I know that the above is possible with executing as400 a concatenated command within a LANSA program
e.g. Exec_OS400 Command(#MY_CONCAT_COMMAND_FIELD) but is it possible to implement a similar
thing with only LANSA code?
best,
Sotiris
Re: LANSA command parametrization?
Posted: Tue Apr 12, 2022 12:35 am
by MARCOREMMEDATA
Is not possible.
For similar case i use BIF SYSTEM_COMMAND to run a .bat built at runtime with the dos command to delete the database table
Re: LANSA command parametrization?
Posted: Tue Apr 12, 2022 3:28 am
by Dino
While it's possible to have a variable name for the select, in a read/fetch situation, delete it's a different problem.
So something like this:
Code: Select all
#COM_OWNER.readWhatever fieldname('EMPNO') tablename('PSLMST')
#COM_OWNER.readWhatever fieldname('DEPTMENT') tablename('DEPTAB')
could reuse a routine like this:
Code: Select all
Mthroutine Name(readWhatever)
Define_Map For(*Input) Class(#STD_STRNG) Name(#fieldname)
Define_Map For(*Input) Class(#STD_STRNG) Name(#tablename)
Define Field(#select_using) Reffld(#STD_STRNG)
#select_using := 'SELECT "T1"."' + #fieldname + '" FROM "X4RHOLIB"."' + #tablename + '" "T1"'
Clr_List Named(#ListView1)
Select_Sql Fields(#STD_TEXTL) Using(#select_using)
Add_Entry To_List(#ListView1)
Endselect
Endroutine
to read a fields, table, even using a condition, all variable.
But to delete... its a different problem, you cannot use a DELETE command in a SELECT_SQL USING, or to use a DELETE command inside a SELECT/SELECT_SQL without indicating the specific file/table you want to delete the records from
Re: LANSA command parametrization?
Posted: Tue Apr 12, 2022 3:33 am
by Dino
now, if you have LANSA Integrator....you could use the SQLSERVICE to do that job for you from your LANSA code populating the sql string in whatever way you want:
Code: Select all
SET PARAMETER(*SQL) #WRKLST(TXT)
EXECUTE UPDATE(*SQLPARAMETER)
EXECUTE UPDATE(DELETE FROM EMPLOYEE WHERE AGE=25)
Re: LANSA command parametrization?
Posted: Tue Apr 12, 2022 8:19 am
by BrendanB
I am going to suggest something that you should NEVER do...
Code: Select all
define_com class(#prim_alph) name(#SqlStatement)
#SqlStatement := 'DELETE FROM ' + #schemaName + '.' + #tablename + " where keyfield = '" + #keyfield + "' ;SELECT @@ROWCOUNT;"
Select_sql Fields(#std_count) Using(#SqlStatement)
Endselect
Message Msgtxt(("Deleted " + #std_count + " Records from table " + #tablename))
Note: the restriction on using SELECT_SQL is that it *MUST* return a value... so 'adding' the SELECT @@ROWCOUNT to the end of the command (seperated by a semi-colon) will return the number of rows deleted.
You *SHOULD NOT* do this because it *BYPASSES* the OAM, and therefore it *ALSO* Bypasses any Trigger Functions/Validation Logic.
That said, if you feel it is OK, then it should work.
Syntax can be checked using STRSQL or SqlServiceManager but the important bit is that it returns *something*.
Re: LANSA command parametrization?
Posted: Tue Apr 12, 2022 8:51 am
by Dino
Excellent! That will do it!
Re: LANSA command parametrization?
Posted: Wed Apr 13, 2022 5:19 pm
by sotis
Hello,
thank you for your replies.
I tried BrendanB's solution
Code: Select all
#KEY_FIELD := #ACTIONID
#SqlStatement := 'DELETE FROM ' + #FILELIB + '.' + #FILENAM + " where ACTIONID = '" + #KEY_FIELD + "' ;SELECT @@ROWCOUNT;"
Message Msgtxt(#SqlStatement)
Select_Sql Fields(#std_count) Using(#SqlStatement)
Endselect
but I keep getting the following errors during runtime
* SQL error for table N/AVAIL when using DBM function X_DBM_Open_Cursor .
* SQL TESTFILE in LANSAMOD not valid for operation.
The sql from Message is the following:
DELETE FROM LANSAMOD.TESTFILE where ACTIONID = 'C' ;SELECT @@ROWCOUNT;
Note: The TESTFILE is not ENABLED for RDMLX (only the function)
best,
Sotiris
Re: LANSA command parametrization?
Posted: Thu Apr 14, 2022 9:28 am
by atostaine
BrendanB wrote: Tue Apr 12, 2022 8:19 am
I am going to suggest something that you should NEVER do...
Very cool.
Re: LANSA command parametrization?
Posted: Thu Apr 14, 2022 10:32 pm
by Dino
After checking with Brendan, a commit is needed at the end, final example here using DEPTAB, which is a non rdmlx table as well, the program deletes one record after I added a department with XXX as key
Code: Select all
Define_Com Class(#prim_alph) Name(#SqlStatement)
Define_Com Class(#prim_alph) Name(#schemaName)
Define_Com Class(#prim_alph) Name(#tablename)
Define_Com Class(#prim_alph) Name(#keyfield)
#schemaName := "X4RHOLIB"
#tablename := "DEPTAB"
#keyfield := "XXX"
#SqlStatement := 'DELETE FROM ' + #schemaName + '.' + #tablename + " where DEPTMENT = '" + #keyfield + "' ;SELECT @@ROWCOUNT;COMMIT"
* #SqlStatement := "DELETE FROM [X4RHOLIB].[DEPTAB] WHERE DEPTMENT = 'XXX';SELECT @@ROWCOUNT;COMMIT;"
Select_Sql Fields(#std_count) Using(#SqlStatement)
Endselect
Close File(DEPTAB)
Message Msgtxt(("Deleted " + #std_count.AsString + " Records from table " + #tablename))
Re: LANSA command parametrization?
Posted: Fri Apr 15, 2022 9:11 pm
by sotis
After trying also with COMMIT the above error unfortunately persists:
========= Messages issued by the task you are currently performing =========
DELETE FROM LANSAMOD.TESTFILE where ACTIONID = 'C ' ;SELECT @@ROWCOUNT;COMMIT
SQL error for table N/AVAIL when using DBM function X_DBM_Open_Cursor . SQL
TESTFILE in LANSAMOD not valid for operation.
Function TESTFU2 failed - see second level text for instructions +
I know that the SQL syntax is correct because it does the job when it is executed from SQL IBM i Access Client Solutions.
also when I tried with a simpler SQL:
#SqlStatement := 'Select * from LANSAMOD.TESTFILE'
the program run without errors, so I guess the problem is with the DELETE, also I cannot use the file DEPTAB for testing since the installed LANSA Version is 13 and the file is missing or deleted after installation.
best,
Sotiris
Re: LANSA command parametrization?
Posted: Sat Jun 18, 2022 5:52 am
by Theo de Bruin
Perhaps this will work better :
Function Options(*DIRECT)
Define_Com Class(#prim_alph) Name(#Database)
Define_Com Class(#prim_alph) Name(#SqlCmd)
Define_Com Class(#prim_alph) Name(#schema)
Define_Com Class(#prim_alph) Name(#tablename)
Define_Com Class(#prim_alph) Name(#keyfield)
Define_Com Class(#prim_alph) Name(#keyval)
#Database := "V15PGMLIB"
#schemaName := "XDEMOLIB"
#tablename := "DEPTAB"
#keyfield := "DEPTMENT"
#keyval := "ADM"
#SqlCmd := "USE " + #database + ";DELETE FROM " + #schema + "." + #tablename + " WHERE " + #Keyfield + " like '" + #keyval + "';SELECT @@rowcount;COMMIT"
Select_Sql Fields(#STD_NUM) Using(#SqlCmd)
Endselect
Close File(DEPTAB)
Message Msgtxt(("Deleted " + #std_num.AsString + " Records from table " + #tablename))
Return