I get an exception when executing a large SQL script.
In the SQL, if the entry parameters are not passed into the script, i have given them all valid default values for testing purposes. This way i know the script runs without errors in SQL Server Management Studio (where it was originally written and tested.)
But I’ve tried using both sql.ExecSQL(x) and sql.Query(x) with various ways of passing in the SQL script including reading the sql from a setting, and it always throws an exception.
A very simplified test script works fine using sql.Query(x)
Is there any length limit on the sql script or any other special requirements which need to be met?
My script length is around 7K including various comments, but it isn’t really doing that much.
It reads the passed in parameters, and if those are NULL, default values are assigned.
It does a few SELECTS to retrieve the foreign key Id of some related records before doing an INSERT INTO InventoryTransactionDocuments, gets the IDENTITY of the that new record, and then does an INSERT INTO InventoryTransactions.
Actually these details should not be important as it runs fine in SSMS no problem.
Is there any compatibility issue using T-SQL in SambaPOS?
You might want to give more info on the script.
7k characters?
Not sure on any limits, SQL is pretty powerful.
Not familiar with T-SQL, if its a framework type thing you might need to load it - i have loaded mySQL framwork for selecting info from PBX CDR but in JScript not SQL script.
Showing the exception might help us understand what is happening. Also explaining what you are trying to do might help us recommend solutions to help you accomplish your goals. For example if we knew why you are inserting directly to the database in InventoryTransactionDocuments we might be able to provide alternative methods without a 7k long SQL script.
Sorry, I meant Transact-SQL which is the name of the SQL language for Microsoft SQL Server.
It seems the exception data gives no useful information regarding errors in script.
Using the Test function in the script editor, it always shows the generic “Exception has been thrown by the target of an invocation.”
I also had exceptions with some simple scripts when executed with sql.ExecSQL(x) but the same worked fine using sql.Query(x).
I am guessing that the main difference between the 2 types of SQL calls is the return value. The ExecSQL method appears to return a void, but Query returns an array of rows (perhaps empty).
I think it would be useful to have a new sql helper function which returns an object having various properties, example: rowdata, exception, return value. or another idea is to append error data to a script error log file. Somehow we need to see a hint as to what caused the error.
Remember though, i can open the script file in SSMS and run it. It works fine there, so without any detailed error data in SambaPOS i am really stuck on what is causing this exception
@Jesse Thanks for your eagerness to find alternative solutions. It’s probably worthy of a new discussion thread if we go deeper into it. But briefly …
We need to track stock levels of pre-prepared food items (Prep items) separately from inventory which is consumed by sales. It’s quite a complex operation where we make a lot of stuff in-house.
To avoid specifics, it is easiest to think of making meat balls and storing them in the freezer. I want to know if someone stole meat, or frozen meatballs, so the logic of consuming stock via sales rippling through a bunch of nested recipes doesn’t provide the reporting we need:
How many meatballs should there be in the freezer today? (assuming nobody stole any)
At the current rate of consumption, how many days stock of meatballs do we have left?
When should we buy more meat? and how much?
The script that’s crashing is responsible for recording production of … actually 22 different items but let’s just stay with meatballs …
The inventory items of meat etc. need to be deducted from stock at the time of production, not when sold. When the menu item is sold, the quantity of frozen meatball stock should be reduced, not the meat!
This is a case where zero priced tickets are really useful btw.
@Jesse Special characters … interesting call!
But i think any valid SQL characters should be accepted.
There could be a problem if the sql helper function did try to escape special characters.
Maybe i could revert to a batch file using sqlexec calls but i would eventually need to set up a special user in the database with permissions to limit the rather problematic security issues this route would expose. It could be done but was hoping to simply store my SQL in a (doubleAt)Script and use that.
There is another route. You can create database task files and use the execute database task action. Look at the database tasks folder in your documents/sambapos5/database tasks folder for an example. The clear database task is there as reference.