Stephen Smith's Blog

Musings on Machine Learning…

12 Responses

Subscribe to comments with RSS.

  1. […] Introduction Generally in these blog posts I talk about how to access the Sage 300 ERP data through our various APIs that go through our Business Logic Views. This is the only way to update the dat…  […]

  2. Reblogged this on Dinesh Ram Kali..

    dineshramitc

    February 10, 2015 at 4:44 am

  3. […] Last week, we looked a bit at using the Sage 300 ERP .Net API to do a general SQL Query which could be used to optimize calculating a KPI. In this case you could construct a SQL statement to do exactly what you need and optimize it nicely in SQL Management Studio. In some cases this will be much faster than the Sage 300 Views, in some cases it won’t be if the business logic already does this. […]

  4. […] on, I did blog on some of the technical aspects that went into how these were implemented using CSQRY and then on caching and calculating these. For this on premise release, we aren’t implementing […]

  5. Hi Mr, Smith,

    Can you advise whether it is possible to get the my user lists with corresponding email?.

    Please advise.

    Thanks,

    Lyn

    Lyn

    September 10, 2015 at 10:50 am

    • Not using CSQRY, since the User records aren’t stored in the database (because they are needed to sign on to the database).

      smist08

      September 10, 2015 at 1:35 pm

    • You could use the AS0003 view to get this information via a macro, export the Users or use the Users report.

      smist08

      September 11, 2015 at 5:12 am

  6. […] Introduction Generally in these blog posts I talk about how to access the Sage 300 ERP data through our various APIs that go through our Business Logic Views. This is the only way to update the data in a supported manner, but often for reporting needs something more is hoped for. We store our data…  […]

  7. […] could do a CSQRY call and select based on the audit stamps that are newer than our last clock tick (sync time). […]

  8. Hi Stephen, I am in a bit of a jam trying to use the CS0120 superview to execute a SQL transaction

    If I use a transaction that has two statements in it, only the first statement executes
    In the example below only the UPDATE statement executes and I am not able to retrieve the result

    csUpdateW.Browse “BEGIN TRANSACTION; ” & _
    ” UPDATE OWID SET LASTVALUE = LASTVALUE + 1; ” & _
    ” SELECT LASTVALUE FROM OWID; ” & _
    “COMMIT TRANSACTION;”, True
    csUpdateW.InternalSet 256

    Do While csUpdateW.Fetch
    resultval = csUpdateW.Fields(0).Value
    Loop
    csUpdateW.Close

    I am trying to use the superview instead of an ADO connection for various reasons.

    Any suggestions are appreciated, thank you very much.

    Ilona

    April 11, 2018 at 8:56 pm

    • Generally Sage doesn’t like you going behind the regular Views to do updates since this bypasses the regular validation and integrity checks. The intent of this View was to provide a way to do sophisticated data queries. Not sure how its handling your case. You might want to try running SQLTrace to see what is really going to SQL Server.

      smist08

      April 11, 2018 at 11:23 pm

  9. Thanks Stephen.
    After additional testing it turns out that both select statement and update statement execute but the Fetch reads the results of only the first statement. So if I put the select statement first and update second I get what need.
    I usually use the view for complicated queries used in reporting but in this case I was asked by a third party to use the particular transaction to update a field and since the rest of the projects uses the view I wanted to use the view for the update as well. Thanks again.

    Ilona

    April 13, 2018 at 1:52 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: