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.
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
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
*Community Hub is the new name for Sage City