Visual LANSA Forum

Posted: **Fri Mar 16, 2018 7:14 am**

With Independent Windows Workstation and VLF-ONE development we have the problem of there being no data available in the local database for the developer to test with. The specific situation encountered here is VLF-ONE development. To allow VLF-ONE server modules to connect to an iSeries database when run on a windows machine (localhost) we are sending a value to the server module that initiates the define_server/define_db_server, connect_server, connect file BIF's before selecting data. This works OK for the SELECT command but has issues with the SELECT_SQL command

When using the define_db_server BIF this works fine for the following command;
Select Fields(#column1) From_File(DEBUG) Io_Error(*NEXT)
Endselect

Works without error for the following command but seems to ignore the where clause;
Select_Sql Fields(#column1) From_Files((debug)) Where(#SQl_string) Io_Error(*NEXT)
Endselect

Errors on the following command;
Select_Sql Fields(#column1) Io_Error(*NEXT) Using(#SQl_string)
Endselect

Of course the method we are using the most is the last example. Is there any way to manipulate the connection to support the use of the SELECT_SQL commands, particularly in the last example, or is there another method of implementing this? Any other solutions that do not involve replicating/creating data on all developer systems?

Posted: **Fri Mar 16, 2018 8:25 am**

You could check your RDMLX code into, and compile it on, your IBM i server.

Then run your application testing from the IBM i server instead of localhost.

There’s an option on the VLF-ONE developer’s launch page to use a local or remote web server so it’s fairly easy to switch between them.

The remote one server option has an upload script (FTP based) option to upload your changed framework definition, if required.

Personally, I tend to work this way when doing any work that is centered on IBM I data. For example, the shipped IBM I spool files examples will only run in this mode.

Posted: **Fri Mar 23, 2018 4:31 am**

The method you described is what we are doing right now but there are some issues when using independent workstations and a git repository.

If two developers work on the same object they will overlay each others changes when delivering to the iSeries.
If the iSeries repository is updated from the git repository and a developer then delivers new changes to the iSeries the "latest" version of the object is lost. This then impacts the deployments with the wrong version being deployed.

We are currently only 2 developers and have experienced these issues. We are planning to add more developers and need a method to avoid these issues.

Posted: **Fri Mar 23, 2018 10:24 am**

Nic,

one way around this is to use a partition for each developer (on the iSeries). This should allow each developer to 'deliverTo' their own partition for testing.

Of course, this may slightly complicate your use of Git, but this is one way around your problem.

the other way that i can see, is to use seperate installations of LANSA for each developer.

Posted: **Fri Mar 23, 2018 10:47 am**

OK, I'll ask a very dumb question. Why would 2 developers be working on the same object at the same time?

For small development shops, "Hey Joe, I need to change reusable part ABC", "Hey, Charlie, can you wait till I've finished with it?", "Sure thing".

For larger development shops there is task tracking. I am not sure whether this applies to your mode of working. Alternatively the manager allocates work "appropriately".

Posted: **Sat Mar 24, 2018 12:32 am**

Regarding the the two developers working on the same file; its very common in other non IBM / LANSA shops for two people to work on different parts of the same code file. For example I may be adding a new feature to Web API Controller and a teammate may be fixing a bug in another method. VCS systems like Git make the merging of those changes super easy. If said controller was "locked" by me and my changes are not in a deployable state then that bug could not be fixed until I unlock it and that length of time may not be acceptable.

Regarding the data issue, if you are using "Deliver To" then you can still do a "Load Other File" from the IBM i and after the File is compiled locally, you can pull data from i itself. If there is a ton of data, then you will likely blow the limits out of SQL Express, I done it myself. The best option we came up with is to create a Linked Server is SQL Server Management Studio and then you can "import" data into SQL Server with a minimal amount of fuss. This has the benefit of by creating a SQL script it is very easy to refresh your local data.

For example, here is our import script for a file that contains millions of records, and I am only pulling in a couple hundred thousand.
I've change the library and table names.

Code: Select all

USE LANSA;
GO

TRUNCATE TABLE MYLIB.MYTABLE;

INSERT INTO MYLIB.MYTABLE(CWOFID, CWOFD, CWOCN, CWOD, CWDWP, CWJTYP, CWELVL, CWDLE, CWDLN, CWDXMT, CWHRW, CWHRB, CWSALS, CWBNT, CWSLST, CWBPT, CWBRH, CWBRS, CWCLBC, CWCLBP, CWPG, CWPNT, CWPPT, CWEC, CWEWP, CWPRH, CWPRS, CWBRDN, CWGMA, CWWCA, CWBNET, CWNMA, CWULU, CWTLU, CWSLID, CWTERR, [@@UPID])
SELECT CWOFID, CWOFD, CWOCN, CWOD, CWDWP, CWJTYP, CWELVL, CWDLE, CWDLN, CWDXMT, CWHRW, CWHRB, CWSALS, CWBNT, CWSLST, CWBPT, CWBRH, CWBRS, CWCLBC, CWCLBP, CWPG, CWPNT, CWPPT, CWEC, CWEWP, CWPRH, CWPRS, CWBRDN, CWGMA, CWWCA, CWBNET, CWNMA, CWULU, CWTLU, CWSLID, CWTERR, 1
	FROM OPENQUERY(DEV, 'select * from MYLIB.MYTABLE where CWOFID IN (''1002'', ''1003'', ''1926'', ''2714'') and CWDWP >= 20170101 and CWELVL = 0')

The OPENQUERY is what allows us to connect to the other server (DEV is our testing / development iSeries) and execute queries on that server.

Over time, I have created an import scripts for almost every File that we have locally and it makes it very easy to wipe out our local data and pull in fresh data periodically.

I will say that creating that script can be tedious with all the columns, so I have another small script that gets the column names from a File

Code: Select all

USE LANSA;
GO

SELECT c.Name 
FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.object_id = OBJECT_ID('MYLIB.MYFILE')

Then you can copy those results into Notepad++ and to a find and replace to find newlines ([\r\n]+) and replace them with commas(, ). Then its easy to copy those columns into your SQL Script.

: capture 1.PNG (46.09 KiB) Viewed 12273 times

: capture 2.PNG (10.68 KiB) Viewed 12273 times

Hope this helps,
Joe

Visual LANSA Forum

SELECT_SQL - Connect to server

SELECT_SQL - Connect to server

Re: SELECT_SQL - Connect to server

Re: SELECT_SQL - Connect to server

Re: SELECT_SQL - Connect to server

Re: SELECT_SQL - Connect to server

Re: SELECT_SQL - Connect to server