How to Pass a Table Name variable within a ODBC Connection?

Al_22614

I have several COM automation jobs where Monarch Classic connects to a data source on an AS400.  Usually the connection is hard coded with the Monarch Project.  I have a requirement to pass the data source name (e.g. VENAFILE.TABLENAME) where TABLENAME contains the month year (e.g., 0224 within VENAFILE.DSA0224 table name).  Is it possible use a dynamically passed the full table name within the COM automation session with an AS400?

The Monarch programmer's guide mentions ODBC connection but does not provide examples.  If anyone has experience using user defined variables (specifically data source name) within COM Automation/AS400 sessions, please provide sample VBA or VBScript that I can edit for my purpose.

Many thanks in advance.

Al 

Mahmoud

Hi Al,

You can use the following code to pull data from a database table.  I tested this code with Monarch Classic v2023.1.  The code export data from table dbo.ClassicJan to an an Excel file (export2.xlsx).

 The odbc17-demodb in the connection string is an ODBC that i created on my server, which connects to a database named Demo.  The user id is demo.  

The dbo.Classjan is the table name within the database Demo.

You need to provide the password in the second argument where i put "your password here".

You also need to create a model in Monarch classic that basically pulls data from database table and displays it in Monarch Classic table view.  You can change the table name in the connection string as long as table columns matched in the model.

The ODBC that I created which connects to a Database required a password.  If you connect to a database using Windows authentication then you don't need the password.

You can click on the following link and review the Monarch Com Auto documentation:

https://help.altair.com/monarch/programmers_guide/altair_monarch_programmers_guide.pdf

 

Sub testDB()
        Dim monarchobj As Object

        Try
            monarchobj = CreateObject("monarch32")
            If monarchobj Is Nothing Then
                monarchobj = CreateObject("Monarch32")
            End If
            monarchobj.OPENDATABASE("Provider=MSDASQL.1;Persist Security Info=True;User ID=demo;Data Source=odbc17-demodb;Initial Catalog=Demo", "your password here", "dbo.Classjan", "C:\Datawatch\ComAuto\Demo_DB_with_odbc.dmod")
            monarchobj.ExportTable("c:\mo\export2.xlsx")

        Catch ex As Exception
            Console.WriteLine("Unexpected error: " & ex.Message)
        Finally
            monarchobj.CloseAllDocuments
            monarchobj.Exit
            monarchobj = Nothing
        End Try
    End Sub

Mahmoud

Hi Al,

You can use the following code to pull data from a database table.  I tested this code with Monarch Classic v2023.1.  The code export data from table dbo.ClassicJan to an an Excel file (export2.xlsx).

 The odbc17-demodb in the connection string is an ODBC that i created on my server, which connects to a database named Demo.  The user id is demo.  

The dbo.Classjan is the table name within the database Demo.

You need to provide the password in the second argument where i put "your password here".

You also need to create a model in Monarch classic that basically pulls data from database table and displays it in Monarch Classic table view.  You can change the table name in the connection string as long as table columns matched in the model.

The ODBC that I created which connects to a Database required a password.  If you connect to a database using Windows authentication then you don't need the password.

You can click on the following link and review the Monarch Com Auto documentation:

https://help.altair.com/monarch/programmers_guide/altair_monarch_programmers_guide.pdf

 

Sub testDB()
        Dim monarchobj As Object

        Try
            monarchobj = CreateObject("monarch32")
            If monarchobj Is Nothing Then
                monarchobj = CreateObject("Monarch32")
            End If
            monarchobj.OPENDATABASE("Provider=MSDASQL.1;Persist Security Info=True;User ID=demo;Data Source=odbc17-demodb;Initial Catalog=Demo", "your password here", "dbo.Classjan", "C:\Datawatch\ComAuto\Demo_DB_with_odbc.dmod")
            monarchobj.ExportTable("c:\mo\export2.xlsx")

        Catch ex As Exception
            Console.WriteLine("Unexpected error: " & ex.Message)
        Finally
            monarchobj.CloseAllDocuments
            monarchobj.Exit
            monarchobj = Nothing
        End Try
    End Sub

Al_22614

Hi Mahmoud:

Many thanks... This almost got me there.  I cannot seem to get the Filter to work with the Monarch Model that is called.  See my VbScript, below.  Should the "CurrentFilter = NV_RECS" be inside the "OpenFile" string?

Sub testDB()

        Dim monarchobj As Object

' Try

            Set monarchobj = CreateObject("monarch32")

            If monarchobj Is Nothing Then

                monarchobj = CreateObject("Monarch32")

            End If

         monarchobj.CurrentFilter = NV_RECS       ‘FILTER NOT WORKING???

openfile = monarchobj.OpenDatabase("Provider=MSDASQL.1;Persist Security Info=True;User ID=financntr1;Data Source=BOOKPM;Initial Catalog= BOOKPM", "1financntr49", "DSAT0227PM", "C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\Models\Masters_8X\90_NV_Inv_Eval_Test.dmod")

            monarchobj.ExportTable ("C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\New_Master_8X\NV_ODBC_TEST.xlsx")

'Catch ex As Exception

'            Console.WriteLine ("Unexpected error: " & ex.Message)

'Finally

        monarchobj.CloseAllDocuments

        monarchobj.Exit

Set monarchobj = Nothing

'End Try

End Sub

Your help is very much appreciated.  You guys are the greatest!!

Thanks

Al

Mahmoud

Al_22614 said:

Hi Mahmoud:

Many thanks... This almost got me there.  I cannot seem to get the Filter to work with the Monarch Model that is called.  See my VbScript, below.  Should the "CurrentFilter = NV_RECS" be inside the "OpenFile" string?

Sub testDB()

        Dim monarchobj As Object

' Try

            Set monarchobj = CreateObject("monarch32")

            If monarchobj Is Nothing Then

                monarchobj = CreateObject("Monarch32")

            End If

         monarchobj.CurrentFilter = NV_RECS       ‘FILTER NOT WORKING???

openfile = monarchobj.OpenDatabase("Provider=MSDASQL.1;Persist Security Info=True;User ID=financntr1;Data Source=BOOKPM;Initial Catalog= BOOKPM", "1financntr49", "DSAT0227PM", "C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\Models\Masters_8X\90_NV_Inv_Eval_Test.dmod")

            monarchobj.ExportTable ("C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\New_Master_8X\NV_ODBC_TEST.xlsx")

'Catch ex As Exception

'            Console.WriteLine ("Unexpected error: " & ex.Message)

'Finally

        monarchobj.CloseAllDocuments

        monarchobj.Exit

Set monarchobj = Nothing

'End Try

End Sub

Your help is very much appreciated.  You guys are the greatest!!

Thanks

Al

Good Morning Al,

Please make sure you add the filter in the model that you are using in the script, and then add the Currentfilter method right before the ExportTable method.

Sub testDB()

        Dim monarchobj As Object

' Try

            Set monarchobj = CreateObject("monarch32")

            If monarchobj Is Nothing Then

                monarchobj = CreateObject("Monarch32")

            End If

         

openfile = monarchobj.OpenDatabase("Provider=MSDASQL.1;Persist Security Info=True;User ID=financntr1;Data Source=BOOKPM;Initial Catalog= BOOKPM", "1financntr49", "DSAT0227PM", "C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\Models\Masters_8X\90_NV_Inv_Eval_Test.dmod")

             monarchobj.CurrentFilter = "NV_RECS"

            monarchobj.ExportTable ("C:\Users\rhoagland\Pitco Foods Dropbox\Rod Hoagland\New_Vena_Automation\New_Master_8X\NV_ODBC_TEST.xlsx")

'Catch ex As Exception

'            Console.WriteLine ("Unexpected error: " & ex.Message)

'Finally

        monarchobj.CloseAllDocuments

        monarchobj.Exit

Set monarchobj = Nothing

'End Try

End Sub

I tested my code and it worked:

If you have multiple filters you can add it before the exportTable method like the following:

            monarchobj.CurrentFilter = "CD"
            monarchobj.ExportTable("C:\mo\cd.xls")
            monarchobj.CurrentFilter = "dvd"
            monarchobj.ExportTable(C:\mo\dvdxls")

Regards

Mo

Al_22614

Brilliant!!!  Works like a charm.  

Many Thanks Mahmoud.

This matter is resolved.

Al