ACU4GL - SQL Cobol Coding

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

We do this by using a system call.

Here is an example:

     
           MOVE SPACE TO OSQL-COMMAND-LINE.
           STRING 'osql -U ' DELIMITED BY SIZE
               A_MSSQL_LOGIN DELIMITED BY ' '
               ' -P ' DELIMITED BY SIZE
               A_MSSQL_PASSWD DELIMITED BY ' '
               ' -S ' DELIMITED BY SIZE
               A_MSSQL_DEFAULT_CONNECTION DELIMITED BY ' '
               ' -d ' DELIMITED BY SIZE
               A_MSSQL_DATABASE DELIMITED BY ' '
               ' -i "' DELIMITED BY SIZE
               PCSETUP-INSTALL-PATH DELIMITED BY NULL
               '\\scripts\\createidsrchvw.sql"',
               ' -o createidsrchvw.lis' DELIMITED BY SIZE
                   INTO OSQL-COMMAND-LINE.
           INSPECT OSQL-COMMAND-LINE
               REPLACING TRAILING SPACES BY LOW-VALUES.
           CALL 'C$SYSTEM' USING OSQL-COMMAND-LINE, 0,
               GIVING CALL-EXIT-STATUS.

The createidsrchvw.sql file contains our sql code for creating the view.

Hope that helps,

Paul

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

I forgot to mention that you need to have osql available on the workstation where this is going to be running.

Paul

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

I forgot to mention that you need to have osql available on the workstation where this is going to be running.

Paul

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

Hi Patty,

We've already spoken about this but I wanted to update this thread so the information is there for all Community users.

Acu4GL includes a utility program sql.acu that may be called from your COBOL program. Simply call sql.acu passing it a SQL command which then is executed against the database. You may execute any SQL that doesn't return a result set. So in this case you can call sql.acu to create your view, then drop it with another call to sql.acu. You must make sure that the database account you are using has sufficient privilege to execute the SQL.

The sql.acu program is located in the \\AcuGT\\sample\\acu4gl directory, along with some very helpful sample programs. The documentation is in Section 2.4 of the Acu4GL User's Guide.

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

We use SQL.ACU as well. Very useful. As mentioned, SQL.ACU does not return a resultset. This limitation can be worked around by placing your sql results in a table, and then opening that table with Acu4GL just like you would your other data files. Just create an XFD for it. For example, we use SQL.ACU to call a stored procedure, which runs some queries and T-SQL code and then stores it's results in a table. We then open that table as a file in our acucobol code. BTW, the table that gets created and opened can be a sql view if you like. :)

If you want to go a bit further down the rabbit hole, and you're comfortable with AciveX/COM coding in AcuCOBOL, then you can use the built-in Windows ADO(ActiveX Data Object). The old acucorp forum had code samples on it. I'm a bit annoyed that they are ALL gone now.

Anyway, using ADO will open up full access to SQL Server, including running queries on tables/views, creating tables/views, performing stored procedures and returning results in record sets. It allows you to go beyond the limitations of Acu4GL and SQL.ACU when you want/need more access to your SQL data. We use all three options: Acu4GL, SQL.ACU, and ADO.

If this is of interest to anyone, I'll post some acucobol code samples for ADO.

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

danno,
i would love see the sample ADO code here.
or email it to: cuyu@udsnet.com
Thanks

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

Having SYNTAX ISSUES. I have the CREATE VIEW command. Only problem is I have to login to database, connect to the right database server before I send the CREATE VIEW command. The osql solution will not work as the client will not have it installed on all the user's workstations. If I get it figured out, I will post the results. Thanks for the ideas.

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

Having SYNTAX ISSUES. I have the CREATE VIEW command. Only problem is I have to login to database, connect to the right database server before I send the CREATE VIEW command. The osql solution will not work as the client will not have it installed on all the user's workstations. If I get it figured out, I will post the results. Thanks for the ideas.

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

Solution: I created a User-Defined Function in the database that allows me to pass a parameter. Inside my program in the select statement I use the parameter in the FROM statement portion of the SELECT statement. This was much easier. The function has the main select statement I needed. I just pass the As Of Date parameter to filter the data I need to get.

Here is info:
Inline User-Defined Functions
SQL Server 2000 New Information - SQL Server 2000 SP3.

Inline user-defined functions are a subset of user-defined functions that return a table. Inline functions can be used to achieve the functionality of parameterized views.

Consider this view:

CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'
You can create a more generalized version, vw_CustomerNamesInRegion, by replacing the WHERE Region = 'WA' with a WHERE Region = @RegionParameter and letting users specify the region they are interested in viewing. Views, however, do not support parameters in the search conditions specified in the WHERE clause.


Security Note Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.

Inline user-defined functions can be used to support parameters in the search conditions specified in the WHERE clause. This is an example of a function that allows users to specify the region in their select:

CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
GO
-- Example of calling the function for a specific region
SELECT *
FROM fn_CustomerNamesInRegion(N'WA')
GO
Inline user-defined functions follow these rules:

•The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable because it is set by the format of the result set of the SELECT statement in the RETURN clause.


•There is no function_body delimited by BEGIN and END.


•The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.


•The table-valued function accepts only constants or @local_variable arguments
Inline functions can also be used to increase the power of indexed views. The indexed view itself cannot use parameters in its WHERE clause search conditions to tailor the stored result set to specific users. You can, however, define an indexed view that stores the complete set of data that matches the view, and then define an inline function over the indexed view that contains parameterized search conditions that allow users to tailor their results. If the view definition is complex, most of the work performed to build a result set involves operations such as building aggregates or joining several tables when the clustered index is created on the view. If you then create an inline function that references the view, the function can apply the user's parameterized filters to pull specific rows from the result set that was built by the CREATE INDEX statement. The complex aggregations and joins are done once, at CREATE INDEX time, and all subsequent queries referencing the inline function filter rows from the simplified, stored result set. For example:

1.You define a view vw_QuarterlySales that aggregates all sales data into a result set that reports summarized sales data by quarter for all stores.


2.You create a clustered index on vw_QuarterlySales to materialize a result set containing the summarized data.


3.You create an inline function to filter the summarized data:
CREATE FUNCTION fn_QuarterlySalesByStore
(
@StoreID int
)
RETURNS table
AS
RETURN (
SELECT *
FROM SalesDB.dbo.vw_QuarterlySales
WHERE StoreID = @StoreID
)
4.Users can then get the data for their specific store by selecting from the inline function:
SELECT *
FROM fn_QuarterlySalesByStore( 14432 )
Most of the work needed to satisfy the queries issued at Step 4 is to aggregate the sales data by quarter. This work is done once at Step 2. Each individual SELECT statement in Step 4 uses the function fn_QuarterlySalesByStore to filter out the aggregated data specific to their store

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

pattystepp - Awesome information! Thanks for the details!

danno - I would love some ADO source samples please email to jwhalen@pass-port.com

Thanks!!

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

ADO sample and discussion located in the archived COBOL extend forum here: http://community.microfocus.com/Forums/25_COBOL__extend/1579_archive_Adodb?Page=1

[Migrated content. Thread originally posted on 12 July 2011]

In my programs I am unable to create more than 1 cursor to be used at a time.
Is there a way to create a SQL View from within the program? Use the view in a SELECT statment then delete the view from the database when I am finished with it?

The view is needed to speed up processing. It will include a SUM(invoice_amt) as SUM_AMT GROUP BY INVOICE_NO. Then I would query the view to get all WHERE SUM_AMT <> 0.

Ideas anyone?

ADO sample and discussion located in the archived COBOL extend forum here: http://community.microfocus.com/Forums/25_COBOL__extend/1579_archive_Adodb?Page=1