You’ll have to upgrade SQL Server. If you’re using Windows 7 your SOL.
Your only other option would be to process everything outside the db.
function posm(tid)
{
var datafields = new Array();
datafields.push("TicketId");
datafields.push("MenuItemId");
datafields.push("MenuItemName");
datafields.push("Price");
datafields.push("Quantity");
var datafieldscount = datafields.length;
var qry = "";
qry += "SELECT";
for (var d=0; d < datafieldscount; d++)
{
qry += (d>0 ? " ," : " ");
qry += "[" + datafields[d] + "]";
}
qry += " FROM [Orders]";
qry += " WHERE 1=1";
qry += " AND [TicketId] = " + tid;
var qryresult = sql.Query(qry).Delimit('~').First;
var qrydata = qryresult.split('~');
var qryfieldcount = qrydata.length;
var data = new Object();
for (var d=0; d < datafieldscount; d++)
{
data[datafields[d]] = qrydata[d];
}
var jsondata = JSON.stringify(data);
return jsondata;
}
@Posflow I have tried that function but itonly returns the first line only yet the are four lines
This would be a good scenario for using JSON PATH.
This will return a JSON array of the orders:
function posm(tid) {
var queryString = "SELECT TicketId, MenuItemId, MenuItemName, Price, Quantity " +
"FROM dbo.Orders WHERE TicketId = " + tid + " FOR JSON PATH;";
return sql.Query(queryString).First;
}
thank you alot
i worked worked
i will post the full script when am done
1 Like
hello
i have tried using the idea in a function , but every time the number of row where am query data from exceed 4 the json comes outer incomplete
function adgoods()
{
// date for request
var dae = DateTime.Now;
var tme = dae.ToString("yyyy-MM-dd HH:mm:ss");
var qyt = script.Load("ode","qrcode");
// query to get goods detail into a json
var querysting = "SELECT '101' as operationType,mp.Name as goodsName,mp.Barcode as goodsCode,'PP' as measureUnit,mz.Price as unitPrice,'101'as currency,'90101501' as commodityCategoryId,'102' as haveExciseTax, '1' as description, '102' as havePieceUnit, '102' as haveOtherUnit FROM SambaPOS5.dbo.MenuItems as mp inner join SambaPOS5.dbo.MenuItemPortions as mip on mp.Id = mip.MenuItemId inner join SambaPOS5.dbo.MenuItemPrices as mz on mip.Id = mz.MenuItemPortionId FOR JSON PATH";
var gdetails = sql.Query(querysting).First;
file.WriteToFile('d:datagoodsdetail1.txt', gdetails);
//return gdetails;
var grthon = JSON.parse(gdetails);
// encodes json into base64 for the content part
var encoded_invoice = JSON.stringify(grthon);
var convert = host.type("System.Convert");
var encoding = host.type("System.Text.Encoding");
var inputBytes = encoding.UTF8.GetBytes(encoded_invoice);
var objJsonB64 = convert.ToBase64String(inputBytes);
var sending_invoice = {"data": {"content": objJsonB64,"signature": "","dataDescription": {"codeType": "0","encryptCode": "1","zipCode": "0"}},"globalInfo": {"appId": "AP01","version": "1.1.20191201","dataExchangeId": "9230489223014123","interfaceCode": "T130","requestCode": "TP","requestTime":tme,"responseCode": "TA","userName": "admin","deviceMAC": "B0-10-41-84-70-ED","deviceNo": "TCS6850d51562704436","tin": "1000067725","brn": "","taxpayerID": "1","longitude": "116.397128","latitude": "39.916527","extendField": {"responseDateFormat": "","responseTimeFormat": "","referenceNo": "21PL010020809"}},"returnStateInfo": {"returnCode": "","returnMessage": ""}};
var efris_sending_invoice = JSON.stringify(sending_invoice);
var resp_efris = web.PostJson('http://127.0.0.1:9880/efristcs/ws/tcsapp/getInformation',efris_sending_invoice);
//return resp_efris;
var pardata = JSON.parse(resp_efris);
// return pardata.data.content;
var checr = pardata.returnStateInfo.returnMessage;
//check for errors in response and decode from base64
if (checr != 'SUCCESS'){
// return '\n' + '<J00>error :'+pardata.returnStateInfo.returnCode+ 'Msg :'+ pardata.returnStateInfo.returnMessage;
dlg.ShowMessage(pardata.returnStateInfo.returnMessage);
}
else
{
var efri2 = pardata.data.content;
// var jsonne =JSON.stringify(efri2);
var convert = host.type("System.Convert");
var encoding = host.type("System.Text.Encoding");
var bytes2 = convert.FromBase64String(efri2);
var json2 = encoding.UTF8.GetString(bytes2);
var obj = JSON.parse(json2);
var jsonne =JSON.stringify(json2);
dlg.ShowMessage('Products added successfully');
}
}
this is what is written to file
[{
"operationType": "102",
"goodsName": "Kacumbali",
"goodsCode": "f00789",
"measureUnit": "PP",
"unitPrice": 8000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "Herbal Tea2x",
"goodsCode": "BEV0062x",
"measureUnit": "PP",
"unitPrice": 8000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "Lemon tea",
"goodsCode": "tea877",
"measureUnit": "PP",
"unitPrice": 8000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "Bogoya test",
"goodsCode": "TEST009",
"measureUnit": "PP",
"unitPrice": 20000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "Croissant test",
"goodsCode": "TEST0076",
"measureUnit": "PP",
"unitPrice": 6000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "Chicken pie test",
"goodsCode": "TEST034",
"measureUnit": "PP",
"unitPrice": 12000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "banana test",
"goodsCode": "TEST089",
"measureUnit": "PP",
"unitPrice": 12000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "meat test",
"goodsCode": "TEST090",
"measureUnit": "PP",
"unitPrice": 12000,
"currency": "101",
"commodityCategoryId": "90101501",
"haveExciseTax": "102",
"description": "1",
"havePieceUnit": "102",
"haveOtherUnit": "102"
}, {
"operationType": "102",
"goodsName": "soda test",
"goodsCode": "TEST091",
"measureUnit": "PP",
"
i don’t know if its a mistake I made in the script but it works fines if the rows are 4 and less, but crashes when the rows are more than four (4)
So it seems that SQL Server returns the JSON in chunks of 2034 characters because why the hell not.
Changing the query to .All returns string[] and needs to be joined.
var lib = host.lib("mscorlib");
// date for request
var tme = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
var qyt = script.Load("ode", "qrcode");
// query to get goods detail into a json
var querysting = "SELECT '101' as operationType, mp.Name as goodsName, mp.Barcode as goodsCode, 'PP' as measureUnit, mz.Price as unitPrice, '101'as currency, '90101501' as commodityCategoryId, '102' as haveExciseTax, '1' as description, '102' as havePieceUnit, '102' as haveOtherUnit FROM dbo.MenuItems as mp inner join dbo.MenuItemPortions as mip on mp.Id = mip.MenuItemId inner join dbo.MenuItemPrices as mz on mip.Id = mz.MenuItemPortionId FOR JSON PATH";
var result = sql.Query(querysting).All;
var gdetails = lib.System.String.Join('', result);
So this modified snippet will return the expected data.
Also, you don’t need to parse from json to object then convert back.
But this brings us to the next issue: SambaPOS freezes when the script calls Encoding.UTF8.GetBytes().
Unfortunately, debugging isn’t giving me any hints as the call stack is a mile long with clr and clearscript stuff.
You may need to build something that sits between SamabPOS and the site to which you send the data that you can trigger with a call from SambaPOS and it will connect to the db, grab the data, format, then send upstream.
I would recommend contacting whatever service you’re using and demand that their API accept JSON like, oh I dunno, most every other endpoint out there.
This Base64 encoding back and forth is just silly. It provides no security (which is why I think they went with it to begin with) and just adds overhead and confusion.
2 Likes
i managed to fix the issue by using two select
var querysting = "SELECT CAST((SELECT '101' as operationType,mp.Name as goodsName,mp.Barcode as goodsCode,'PP' as measureUnit,mz.Price as unitPrice,'101'as currency,'90101501' as commodityCategoryId,'102' as haveExciseTax, '1' as description, '102' as havePieceUnit, '102' as haveOtherUnit FROM SambaPOS5.dbo.MenuItems as mp inner join SambaPOS5.dbo.MenuItemPortions as mip on mp.Id = mip.MenuItemId inner join SambaPOS5.dbo.MenuItemPrices as mz on mip.Id = mz.MenuItemPortionId FOR JSON PATH) AS VARCHAR(MAX)) AS JSONDATA ";
var gdetails = sql.Query(querysting).First;
it works well
but am going to use @Memo solutions
thank you for all help
1 Like
Casting to VARCHAR is a great idea. Depending on the language used, NVARCHAR would be a safer bet.
Does SambaPOS hang when you execute the script using the new query?
1 Like