Create/Drop tables

Is there a way to use the SDK to create or drop a table? I'd like to create a temporary table, then drop it directly in the Sage database.

  • Also, I was able to create the table manually while logging in as sysadmin. The problem is that no one has privileges to that table. Is there a way of granting all privileges to the new table?

  • There is an example of a SQL query in the SDK sample:

    // Example of getting a double

    // Get all prices that are greater than 50 from the the inventory price table

    count = util.RunSelectQuery("SELECT dPrice FROM tInvPrc WHERE dPrice > 50");

    double price;

    for (int index = 0; index < count; index++)

    {

    price = util.GetDoubleFromLastSelectQuery(index, 0);

    Console.WriteLine("RunSelectQuery(\"SELECT dPrice FROM tInvPrc WHERE dPrice > 50\"): " + price.ToString());

    }

    Console.WriteLine();

    And there are a .RunNonQuery methods but they're only other DML queries, Insert, Update or Delete

    There's no DDL in the .net reference.

    As far as I know, there's no guidance in the SDK on working with additional tables, other than this remark in dev-progref.pdf:

    "The following limitations apply to any application you develop for Sage 50 using the database services API:

    • Tables cannot be created or destroyed while a transaction is being processed."

    And there are some return codes: 

    3 DBS_CREATE_ERROR

    4 DBS_DROP_ERROR

    So... should be possible. 

    But, tables that you create may be inaccessible.  It was possible to access the security tables with ODBC in the older versions of Simply Accounting, no longer.  My recollection is that it changed before the move from MySQL 3.5x to 5.x