SQL

  • 24 Sep

    How to exit rule when SQL record count is 0

    How to exit rule when SQL record count is 0

    Q: We have an SQL as source job that emails the results of the SQL statement as a file. The problem is that we do not want the results/email sent when there are no records. I see that the variable %QSC contains the record count. How can I use that variable to exit the rule and not email the empty results file?

    A: So I guess you have SMTP as Destination?
    Please add the following SQL ‘On Destination’ script and try once more:

    limagito file mover paascal script option

    Begin
      psExitCode:= 1;
      // ... add your code here
      If psQSC = 0 Then
        psExitCode:= 0;
    End.

    limagito file mover pascal script setup

    • Feedback customer: Yes, it is a SMTP destination. Based off what you sent below I added the Pascal script with the addition of some logging:

    limagito file mover pascal script setup

    This works as expected and what we want, but it also creates the temp file. Is there a way the temp file can be suppressed? If not, I can add a rule to delete them.

    8/9/2023 5:07:43 PM RecordCount: 0

    8/9/2023 5:07:43 PM FieldDefs: “contributor_system”,”logged_datetime”,”mrn”,”patient_name”,”fin”,”error_status”,”error_text”,”message_text”

    8/9/2023 5:07:43 PM Copied Cerner_ESI_log_other_2023-08-09.csv to C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv;110

    8/9/2023 5:07:43 PM Backup Successful C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv to C:\Data\SourceFileBackups\Prod\2023\08\09\Cerner_ESI_log_other_2023-08-09.csv

    8/9/2023 5:07:43 PM Compiling..

    8/9/2023 5:07:43 PM Compiled Succesfully

    8/9/2023 5:07:43 PM psQSC (SQL record count) = 0. Exiting rule.

    8/9/2023 5:07:43 PM Total Files Redo, Count: 1 & Size: 110

    • Reply to customer:

    Please try to add

    DeleteFile(psFilePath + psFileName);

    to the script.

    Begin
      psExitCode:= 1;
      // ... add your code here
      If psQSC = 0 Then
      Begin
        DeleteFile(psFilePath + psFileName);
        psExitCode:= 0;
      End;
    End.

    It seems you are using our Backup option. In your case, we would add the ‘backup’ as a second WIN destination because otherwise you still get the temporary file in your backup folder (instead of using our BACKUP option).

    • Feedback customer: That got it! Thanks you for your help. We are all good now and the rule is working as we want it to.

    If you need any info about this ‘exit rule when SQL record count is 0’ question, please let us know.

    #sql #filetransfer #filemanagement

    Best regards,

    Limagito Team

    By Limagito-Team SQL ,
  • 04 Dec

    Export Chinese Text Data from SQL server database to CSV file

    Q: I want to export Chinese Text data from an SQL server database to a CSV file. Setting up the Limagito rule was simple: SQL input and an output directory. I am actually using an SQL Select because the data that I want to export results from a join. Unfortunately the exported file is in ANSI encoding. Since my SQL Server source has nvarchar fields, some of them with Chinese text in them, I cannot use the CSV. Is there any way to tell Limagito to output CSV from an SQL Server source as UTF-8?

    A: We had to add some new options in version v2020.12.4 to achieve this.

    We added a basic example to get you started:

    Added SQL as Source

    Limagito File Mover SQL as Source

    We’ll be using a simple QRY to get all the date from a database table:

    Limagito File Mover SQL as Source Select Statement

    We are using a MS SQL Database, connection Setup:

    Limagito File Mover SQL as Source database setup

    Some info about the Database table (Demo_10

    ) we’ll be using:

    Add the query, simple seclect all statement:

    Limagito File Mover SQL as Source Query

    Important, Add the encoding you need. In this example we added UTF-8.

    Limagito File Mover SQL as Source Common options

    Destination Setup, WIN as Destination:

    Limagito File Mover WIN as Destination

    Destination File Result:

    Limagito File Mover SQL to Csv File

    RunTime Log Result:

    Limagito File Mover SQL to Csv RunTime Log

    If you need any help with this ‘Export Chinese Text Data’ question, please let us know.

    Best Regards,

    Limagito Team

    By Limagito-Team SQL , ,
  • 26 Apr

    How-To use ODBC DSN with SQL as Source or Destination

    On request we’ve added ODBC as connection option for SQL as Source or Destination in version v2020.4.25.0. Our ODBC bridge driver supports Level 2, 3 drivers. It requires the x86 or x64 ODBC driver to be installed according to ODBC specification.

    First you’ll need to setup a System DSN (Data Source Name) that will be used in the Database Connection Setup of the SQL Source or Destination Setup of our File Mover.

    Configure DSN using the ODBC Administrator control panel. Important:

    1. When you are using the 64-bit version of LimagitoX you’ll need the ODBC Data Sources (64-bit) App.
    2. When you are using the 32-bit version of LimagitoX you’ll need the ODBC Data Sources (32-bit) App.

    We’ll be using the 64-bit version of the ODBC Data Source Administrator . Select the System DSN Tab and click < Add >.

    In this example we’re going to connect to a MS SQL Server Express (v2014). We’ll be using the (already installed) ODBC Driver 11 for SQL Server. Select and click < Finish >.

    ( https://web.synametrics.com/odbcdrivervendors.htm )

    Please choose the Name carefully. This DSN Name will be used later in the SQL Database Connection Setup of our file Mover. The Server is the ‘full name’ of the SQL Server we are going to connect to (Hostname \ SQL Instance). Click < Next >.

    Hint: If you don’t know the Server name, please start the ‘Microsoft SQL Server Management Studio’.

    We’ll be using SQL Server authentication. Please enter Login ID (Username) and Password. We’ll need this during the DSN setup so we can select the default database and test the connection. The Password will not be stored in the DSN. You ‘ll need to add this in the SQL Database Connection Setup of our File Mover later.

    We changed the default database to the one we’ll be using in our File Mover. Click < Next >.

    Click < Finish >.

    Click < Test Data Source >

    Result should show: TESTS COMPLETED SUCCESSFULLY!.

    Click < OK >.

    Click < OK >.

    The new System DSN is ready to be used within LimagitoX File Mover. Click < OK >.

    In the SQL as Source or Destination Setup of LimagitoX File Mover you’ll find ODBC as Database Vendor option (Database Tab).

    Set the following fields:

    • DSN (Name of the existing system DSN to use for connecting)
    • Database
    • Username
    • Password

    Click < Connect > to test the ODBC connection.

    LimagitoX-SQL-ODBC

    Check the Log window for the connection result. Log shows the different table names of the database so the connection is OK.

    LimagitoX-SQL-ODBC-Test

    If you need help, please let us know.

    Best Regards,

    Limagito Team

    By Limagito-Team SQL , ,
1 2
SEARCH