PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 04/12/2017 2:39 PM by  TimC
Create export files from SQL code via IPA or crystal and LBI?
 9 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Joan Herzfeldt
System Analyst
Genesis Health System
Veteran Member
(147 points)
Veteran Member
Posts:55


Send Message:

--
07/27/2015 2:44 PM

    I'd like to pick a few brains please.  I'm trying to determine if IPA is a viable option for running SQL code and FTP'g the file or moving it to a directory.  Most of my Lawson exports are created via a SQL View.  So the next step just pulls the entire view (select * from LawsonView). 

    Currently in order to get the file into csv, tab, excel, etc... I have to create a crystal report and schedule it via LBI which exports it to a specific directory.  My network team have to create the FTP connection, make any file name corrections/additions (via scripting) and send the file.

    Another option is to have my network team create a scheduled task to do the above.  I don't like about this option because I as a developer can not see the task to know if it ran or even to check the scheduled run time etc...

    In IPA I can use a SQL query node to get the data and an ftp node to send the data, but I'm not quite sure what to use in the middle.  I tried a data iterator, a msg builder both together a separately.  I've tried just writing the records to a file (file node, append).  None of these seem to do what I'm looking for or at least it doesn't appear to be very efficient.

    Which leads me to my brain picking... In your IPA experience, is this something I should peruse (spend my time on) or is that not really the intent of IPA and I'm better off sticking with the Crystal Report and LBI?
     
    Your input is much appreciated. - Thanks
    Tags: IPA, SQL, Crystal reports, ftp, Scripts, data iterator, message builder
    BarbR
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    07/27/2015 2:49 PM
    We are not IPA yet, still PFI, but we have many flows that have an SQL node to pull the desired data and write it to a file in the desired format (using the File Append node). All of our interfaces are done this way.
    John Henley
    Private
    Private
    Senior Member
    (9641 points)
    Senior Member
    Posts:3231


    Send Message:

    --
    07/28/2015 6:21 AM
    Joan this is certainly a viable approach. I work with a number of clients who use this to extract and interface data. Some caveats apply. For instance, using file node will not scale if IPA not on same server as LSF. You need to use message builder instead. Also beware of what you're querying and how much you're trying to do. You don't want to query for 10 million rows and try to iterate over that in IPA.
    Thanks for using the LawsonGuru.com forums!
    John
    Joan Herzfeldt
    System Analyst
    Genesis Health System
    Veteran Member
    (147 points)
    Veteran Member
    Posts:55


    Send Message:

    --
    07/30/2015 10:02 AM
    The reason for my post is because we well be switching from a Unix platform (and kron jobs) w an Oracle SQL DB to a Microsoft platform w/ MSSQL DB. We have hundreds of scripts (.ksh) that will need to be replaced during our upgrade. So while I'm implementing a couple of new processes I'm trying to determine the best way to handle our file transfers from Lawson to another system. Most of the existing ones have some type of script that runs to manipulate or move the file, so if we can determine our 'best practice' for ftp'g our files and start migrate some of the existing scripts prior to the upgrade that would be a good thing. We have several different applications that could be used to do this and after further discussion with our dba, we've decided that IPA is not the best tool, available to us, to use for this issue.
    Thanks for your input.
    Kwane McNeal
    Private
    Private
    Veteran Member
    (1266 points)
    Veteran Member
    Posts:422


    Send Message:

    --
    07/30/2015 11:18 AM
    Joan, what about using the Cygwin functionality that is installed with Lawson. Granted, you'll need to install far more than what is required to get Lawson itself to work, it should do most anything you need that is inside a kph script.

    Another option would be to rewrite in Powershell or Perl.
    Joan Herzfeldt
    System Analyst
    Genesis Health System
    Veteran Member
    (147 points)
    Veteran Member
    Posts:55


    Send Message:

    --
    07/30/2015 3:27 PM

    Hi Kwane - I know Powershell and Perl have been discussed for the more complicated scripts.  I'll have to ask my sys admin about cygwin since I don't know what that is. We have some planning meetings scheduled in the near future. I'm sure the simple ftp scripts will be handled one way, the scripts that load information into lawson with be replaced with IPA flows and the other more complicated scripts...will depend on what they do.  Needless to say we have some fun ahead of us ;-).  Thanks for your input.

    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    07/30/2015 4:20 PM
    Cyqwin is an open source project that aims to provide a number of standard Unix tools and a mostly POSIX compliant environment to Windows ( https://www.cygwin.com/).

    Properly set up it provides a large portion of the functionality that you would have in a Unix box, on a Windows box. It's usually easier to transition a script from a 'proper' Unix environment to a Cygwin environment than it is to move straight from Unix to a purely Windows based solution such as Powershell or DOS batches, as you are still using the same tools and commands, you just have to tweak things a bit to work around Windows based wonkiness.

    There are some downsides to Cygwin, but most of them are almost entirely outside the scope of what I would perceive the normal Lawson/Infor sort of environment to be worried about.

    Ari
    Senior Programmer/Analyst
    CBJ
    Veteran Member
    (125 points)
    Veteran Member
    Posts:49


    Send Message:

    --
    07/30/2015 7:59 PM
    Since this will be Windows and SQL Server, I would simply convert the extract script to a batch file. Use SQLCMD to select the view and output it to a file. You can then just copy the file to wherever. You can then have the the script write a line to some log so that you can check to make sure it ran or you could also use some freeware program like bmail to email you an alert that the batch file ran. After you have set up the script just have your network people schedule it in the Windows Scheduler.

    We do this with several of our extract scripts. It is simple and easily maintained.
    TBonney
    Private
    Private
    Veteran Member
    (591 points)
    Veteran Member
    Posts:251


    Send Message:

    --
    07/31/2015 7:33 AM
    We are a Windows shop too and what Ari suggested is pretty much what we do as well. We do all of our ftp'ing (not just Lawson but all apps) from a dedicated ftp server. For Lawson, all of the file creation and manipulation that is necessary for any of our Lawson jobs is presently accomplished through a series of batch & vbscript files, which we are in the process of converting over to PowerShell.

    These scripts use the SQLCMD for the Lawson queries where appropriate. Some of these are triggered by a scheduled task. However many others are fired off by the creation of a designated trigger file (like the output file from a standard Lawson job for example). For this process, we have a script that we refer to as our "Folder Monitor" script, which runs on the Lawson server and monitors all identified directories for the presence of these trigger files and calls/runs the designated batch/vbs/PS script each time the trigger file is found to exist. Incorporated into each batch/vbs/PS script that we write that gets run from this process is a notification procedure, which sends the appropriate person(s) notifications of the job having either run or failed.

    Then, there is also a second folder monitor script process that runs on the ftp server, generating additional notifications to relevant parties, as to whether or not the associated/subsequent ftp job ran successfully or failed.
    TimC
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    04/12/2017 2:39 PM
    Well, one thing is, some fields in Lawson are encrypted, such as SSN. So, you would need IPA to fetch the actual data via a LandmarkTransaction or S3Query.
    The MessageBuilder would be used in a loop as long as there is no parsing necessary as you're reading records. If so, this won't work. You'd have to use an Assign node to concatenate to a variable with column and row delimiters. I do this currently in a few flows. If you don't need IPA for the above reasons, then, for instance, I have a custom SQL Database that lives on the same sql instance as landmark and I use views to fetch the data. From there, you could BCP or Bulk executions to output.
    If the former, then IPA has FTP and a SYSCMD node to execute system commands to move files.
    You are not authorized to post a reply.