LANSA command parametrization?

[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

[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

[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

[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)

https://docs.lansa.com/15/en/lansa093/i ... update.htm

[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*.

[Dino]

Excellent! That will do it!

[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

[atostaine]

I am going to suggest something that you should NEVER do...

Very cool.

[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))

[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

[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