Sql.Query API and quotes inline SELECT statement

V5 Question
21

Ok keep going you have my attention! Not sure it you will pass the sql.Query parser test? Not unless there is another way to issue a SELECT STATEMENT :confused:

22

Take a look here:

https://forum.sambapos.com/t/request-call-another-handler-function-within-a-script/6907

You can use script.Load to load other scripts.

So basically make you a script that handles the replace for you.

Use a variable for your product
 run the replace script on that variable then insert that variable in the actual SQL.

23

ummm that is not the problem I think as I can do that. The issue is what happens to the Select Statement inside the API helper function.

Not unless you can pass a FUNCTION in the Statement for the Product Name which gets evaluated inside the API helper function. Calling an outside function does not work unless it is inside the same script.

Some like:

var qry = “SELECT [CustomTags] FROM [MenuItems] WHERE [Name]=’”+productfunction()+"’";

So we get a String like:

SELECT [CustomTags] FROM [MenuItems] WHERE [Name]=‘ProductFunction()’
or
SELECT [CustomTags] FROM [MenuItems] WHERE [Name]=ProductFunction()
or
SELECT [CustomTags] FROM [MenuItems] WHERE [Name]={ProductFunction()}

24

Not sure what you mean.

OK I see what your saying. So we need to convert it before its passed.

25

Problem lays within how Emre runs the C++ code to execute the string “Select [Customtags] FROM
” - do not know what the execute command is in that code but probably pseudo wise:

Run SQL Command = "Run " + String -> and String has too many quotes in it
 but just guessing here from my own experiences haha

Can’t convert as tried everything possible. The thing is to pass the T-SQL work around i.e. 2x Single Quotes but it fails as the sql.Query Helper must error on the parser.

If the product name has a QUOTE embedded then it must be passed to search on. Bucks stops inside the API Helper function. It just another one of those “embedded braces things” :smile:

26

Ok so this works:

Pasted image1008×621 4.99 KB

So we have to figure out how to do that


27

Can you show your implementation where your feeding product name into the script?

28

But you are Returning Data which can contain special characters. The Single Quote (special char) must be in the Select String statement.

29

Ok so show your entire setup
 your confusing me because its understood to you. I have yet to see it.

Where are you feeding productname from?

30

OK I will post the functions:

function Order(stringvar,cdescription,cname)
{
	  // Order("9300631742975 Choc Bar,Ord:01/02/2016~9325862000250 Sun Screen,Ord:02/02/2016~9403099004590 M and M,Ord:03/02/2016~","Order Name","Fosters Ltd")
	  var longstr = '' ;
	  
	  // Transaction Document Header	  
      var qry = "INSERT INTO [InventoryTransactionDocuments] (" +
 	  "[Date]" + 
	  ",[Name]" +
	  ",[Description]" + 
	  ",[InventoryDocumentTransactionTypeId]" +
	  ",[TransactionAccountTransactionTypeId]" +
	  ",[AccountTypeId]" +
	  ",[AccountId]" +
	  ") VALUES (" +
 	  "GETDATE()" +
	  ",'" + cname + "'" + 
	  ",'" + cdescription + "'" + 
	  ",(SELECT [Id] FROM [InventoryTransactionDocumentTypes] WHERE [Name]='Purchase Transaction')" + 
	  ",(SELECT [Id] FROM [AccountTransactionTypes] WHERE [Name]='Supplier Purchase Transaction')" + 
	  ",(SELECT [Id] FROM [AccountTypes] WHERE [Name]='Supplier Accounts')" + 
	  ",(SELECT [Id] FROM [Accounts] WHERE [Name]='Payables')" + 
	  ")" ;
	  //sql.ExecSql(qry);

	  // INSERT [InventoryTransactions]
	  // Transactions
	   
	   var lines = stringvar.split('~') ;
  	   for(var i = 0; i < lines.length-1; i++)
       {
         
         var cproduct = lines[i].substring(0,lines[i].indexOf(",")) ;
         var cwarehouse = customtag(cproduct,'Warehouse') ;
         var cquantity = customtag(cproduct,'Quantity') ;
         var ccost = customtag(cproduct,'Cost') ;
         
         longstr += cproduct + "~" + cwarehouse + "~" + cquantity + "~" + ccost + "\r" ;

         //if (i > -1) return longstr + i;

       }
	  
      return longstr ;
}

function customtag(productname, tagname)
{ 
  //var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productname.replace(/'/g, "\\'")+"'";
  
  var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productname+"'";
  var r = sql.Query(qry).First;

  var rtag = JSON.parse(r);
  for(var i = 0; i < rtag.length; i++)
  {
    if (rtag[i].TN == tagname) return rtag[i].TV;
  }
  return 0 ;
}
31

I will explain a few items like longstr is just a test var.

I use this line to test:

// Order("9300631742975 Choc Bar,Ord:01/02/2016~9325862000250 Sun Screen,Ord:02/02/2016~9403099004590 M

Just take out the // and whack it in Test Area.

Ignore the 1st SQL block as this works and you can see I have comment the sql.Query statement.

The line:
var cwarehouse = customtag(cproduct,'Warehouse') ;

will call the bottom function i.e. pass the Product Name and the Tag we are looking for.

The above line should eval to:
var cwarehouse = customtag(9403099004590 M and M,'Warehouse') ;

Interesting - what does cproduct look like literally is it
9403099004590 M and M
or
‘9403099004590 M and M’

32

so what is determining productname?

Nevermind I see it. Hmm

33

So product name is stripperd :open_mouth: (bad typo haha) from the following string passed to the top function.

"9300631742975 Choc Bar,Ord:01/02/2016~9325862000250 Sun Screen,Ord:02/02/2016~9403099004590 M and M,Ord:03/02/2016~"

It is split by “~” and then chopped by comma producing 3 array items:
9300631742975 Choc Bar
9325862000250 Sun Screen
9403099004590 M and M

So if we pass 9403099004590 M and M’s - with the quote - bang!

34

Hmm I keep this in beta. Its not really an issue because the parser is working fine. What we have is something else. I am sure there is a solution I may be the wrong person to help atm though.

I mean its not a broken feature its just a technical situation we understand what it is but we need to figure out a solution for it.

35

Ok I will change the category - as I suggested it a little tough if you have them in Product Names but lets see what @emre has to say.

Thanks for your input.
Changed to beta group.

36

Pasted image694×519 15.2 KB 

function getTags(productName) {

 // replace all instances of single quote ' with 2 single quotes ''
 // we need to use RegEx otherwise it will replace the first instance ONLY
 productName = productName.replace(new RegExp("'", 'g'), "''");

 var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productName+"'";

 var r = sql.Query(qry).First;
 
 return r;
 }
1 Like
37

I knew there had to be a function we could use. Thank you @QMcKay .

38

Umm great Q,
I did try this but it failed? In bed now but will try again in a few hours after some shut eye


The only difference was I built .replace into the string qry = line?

See: post no 20 above.

39

The Test facility shows an error if the query returns no rows, so it is deceiving


Pasted image699×580 18.1 KB

This is probably due to the .First qualifier of the helper, and something to do with the Test facility. @emre should probably trap this error, however, no error is thrown when the script is used in {CALL:X}, so it is somewhat moot


Pasted image1187×591 26.9 KB

In fact, if we use a different qualifier like .All, no error is thrown when using the Test facility, and it returns undefined which is correct, since r[0] has not been “set” 


If we use a different helper like sql.Exec, we also get undefined, which makes sense again 


And of course, if we feed it a valid product, everything works


1 Like
40

Yours:

productname.replace(/'/g, "\\'")+"'";
productname.replace(/'/g, "\''")+"'";

His:

productName.replace(new RegExp("'", 'g'), "''")

@pauln I know what your saying but its not exactly the same. I just tested it and it works fine.

1 Like