Where stored the data returned by the function wSDBFetchRecord ?

SOLVED


I'm trying to extract data from tables in the database Sage 50 CA 2016, with a Visual Basic application that uses the SDK Sage 50 CA (Simply.ConnectionManagerService.dll). 

 I'm using the function "wSDBSelectRecords" to extract the data And then I 'm using the function "wSDBFetchRecord" to read data, but I do not know how I can access the readed data, or if stored in a temporary variable. If anyone knows how to read the data correctly and effectively with the SDK in Visual basic .NET way , I appreciate your help .

I attached the code I am using for reading the data:

 Public Declare Function wSDBSelectRecords Lib "Sage_SA_dblyr.dll" (ByVal wDBLinkNo As Short, ByVal wTBLinkNo As Short, ByVal pszFilter As String, ByVal wKey As Short, ByVal wFlags As Boolean) As Short


Public Declare Function wSDBFetchRecord Lib "Sage_SA_dblyr.dll" (ByVal wDBLinkNo As Short, ByVal wTBLinkNo As Short, ByVal pRecord As Byte(), ByVal wFetch As Short, ByVal pFetched As Byte()) As Short

Dim pCount As Short

Dim pRecord2(1999) As Byte
Dim pFetched(1999) As Byte

Call wSDBSelectRecords(iDBLink, iTBLink, "", 0, True)
Call wSDBFetchRecord(iDBLink, iTBLink, pRecord2, pCount, pFetched)

 

  • 0
    verified answer

    Luis Marroquin said:

    I'm using the function "wSDBSelectRecords" to extract the data And then I 'm using the function "wSDBFetchRecord" to read data, but I do not know how I can access the readed data, or if stored in a temporary variable. If anyone knows how to read the data correctly and effectively with the SDK in Visual basic .NET way , I appreciate your help .

    I attached the code I am using for reading the data:

     Public Declare Function wSDBSelectRecords Lib "Sage_SA_dblyr.dll" (ByVal wDBLinkNo As Short, ByVal wTBLinkNo As Short, ByVal pszFilter As String, ByVal wKey As Short, ByVal wFlags As Boolean) As Short


    Public Declare Function wSDBFetchRecord Lib "Sage_SA_dblyr.dll" (ByVal wDBLinkNo As Short, ByVal wTBLinkNo As Short, ByVal pRecord As Byte(), ByVal wFetch As Short, ByVal pFetched As Byte()) As Short

    Dim pCount As Short

    Dim pRecord2(1999) As Byte
    Dim pFetched(1999) As Byte

    Call wSDBSelectRecords(iDBLink, iTBLink, "", 0, True)
    Call wSDBFetchRecord(iDBLink, iTBLink, pRecord2, pCount, pFetched)

    What data are you trying to extract?

    The pSzFilter parameter above is "", I think it's supposed to be an integer ( long pointer to a string), rather than an empty string, which might point to the termination character value of 255.   And if the filter is an empty string, the result should be an empty set.

    I think that some of those wSDB API calls are leftovers from when Sage 50 was based on an Access database, or even before that when the .REC files were raw, proprietary binary files.   Working with record pointers is an old way of doing things that pre-dates high level SQL access to an RDBMS.

    It may be easier to go after the data you need using some of the defined, higher level methods in the sample file for c#.

    If those don't provide the exactness that you need, in sample.CS in the folder for the C# language there are also some examples using SDKDatabaseUtility - Get a string, integer, short, date-time, double, dataset, a 'scalerquery' and a 'non-query', mostly using SQL.

    I hope that helps, please post back.

  • 0 in reply to RandyW
    verified answer
    Thank you very much friend, It is certainly easier to use the API for C#. Now I Could access data and extract them to manipulate the side of C#, I did as follows:

    //The references

    using Simply.Domain.Utility;
    using SimplySDK;
    using SimplySDK.Support;
    using SimplySDK.GeneralModule;
    using SimplySDK.PayableModule;
    using SimplySDK.ReceivableModule;
    using SimplySDK.PayrollModule;
    using SimplySDK.InventoryModule;
    using SimplySDK.ProjectModule;

    //The Code

    OpenDataBase = SDKInstanceManager.Instance.OpenDatabase(pSaiFile, pUsername, pPassword, false, "Sage 50 SDK Sample Program", "SASDK", 1);

    if (OpenDataBase == true)
    {
    try
    {
    DKDatabaseUtility _Query = new SDKDatabaseUtility();

    //Execute the query
    IDataReader _reader = _Query.ExecuteReader("SELECT lId, sName FROM tCustomr");

    DataSet ds = new DataSet();

    // The table is filled With the table returned for BulldCustomerTable Method
    ds.Tables.Add(BulldCustomerTable());

    //Read the data
    while (_reader.Read())
    {

    DataRow row = ds.Tables["Customers"].NewRow();

    row["Id"] = Convert.ToInt32(_reader["lId"]);
    row["companyName"] = (string)_reader["sName"];

    //I store the record in my table
    ds.Tables["Customers"].Rows.Add(row);
    }
    }
    finally
    {
    SDKInstanceManager.Instance.CloseDatabase();
    }
    }


    To call this code from a Visual Basic application , it should only be do the reference to the dll of this class and use the methods that are defined.

    I hope this helps someone else.
  • 0 in reply to Luis Marroquin
    In Vb.net, I'm getting an error that the database is not open. I know it is. How does return value of sdkdatautility()
    know which database it's referencing? Trying to solve this issue for countless hours.