API plugin using SQL LIKE query returns error

[Mike.Sheen]

Not sure where to see the logs, where is that?Cheers

In the requestlogs folder on the filesystem relative to where your JiwaAPISelfHostedService.exe is deployed.

[SBarnes]

The following works fine in chrome and postman to search for the phrase network server, just replace the value I have for the apikey based on generating one for Admin and it should work

Code: Select all

http://localhost:81/Custom/GetInventoryItemsLikeNameRequest?itemname=network%20server&apikey=APIKEyValueGoesHere


In Postman

[JuiceyBrucey]

using process of elimination.

So when I use this SQL query (I have hard coded the search term to that I can eliminate possibilities one by one):
WHERE
main.Description='GREY SUNFLOWER 20KG'
ORDER BY main.Description;

With this URL (after logging in to get key):
http://localhost/Custom/GetInventoryIte ... mName=GREY
Or this:
http://localhost/Custom/GetInventoryIte ... mName=GREY SUNFLOWER 20KG

it works and I get a result and one row of data.

When I have the SQL like this:
WHERE
main.Description=@ItemName
ORDER BY main.Description
;";
return Db.SqlList<MyCustomModel>(query,
new {ItemName = request.ItemName}).ConvertTo<GetInventoryItemsLikeNameResponse>();}

Using this URL:
http://localhost/Custom/GetInventoryIte ... mName=GREY SUNFLOWER 20KG

It works and I get one row of data.

When I do this:
main.Description=@ItemName
ORDER BY main.Description
;";
string sinput = request.ItemName;
return Db.SqlList<MyCustomModel>(query,
new {ItemName = sinput}).ConvertTo<GetInventoryItemsLikeNameResponse>();}

with this URL:
http://localhost/Custom/GetInventoryIte ... LOWER+20KG
It works and I get a row of data.

When I do the SQL like this (deliberately incorrect)
WHERE
main.Description LIKE @ItemName
ORDER BY main.Description
;";
string sinput = request.ItemName;
return Db.SqlList<MyCustomModel>(query,
new {ItemName = sinput}).ConvertTo<GetInventoryItemsLikeNameResponse>();}

I get:
Handler for Request not found (404):

when I do this:
main.Description LIKE '%@ItemName%'
ORDER BY main.Description
;";
string sinput = request.ItemName;
return Db.SqlList<MyCustomModel>(query,
new {ItemName = sinput}).ConvertTo<GetInventoryItemsLikeNameResponse>();}
I get no error and no data.

Is it possible that the request.ItemName is already an object that has been passed and that I am just trying to put wildcards (%) around an object that is already prepped to go into the SQL as it is. This would mean that no matter what I put around request.ItemName it is as if my query string, GREY SUNFLOWER 20KG has already been enclosed in quotes like this: 'GREY+SUNFLOWER+20KG' so that adding wild cards, is the equivalent of doing this: %'GREY SUNFLOWER 20KG'% and that adding single quotes around the wild card is the equivalent of this:
'%'GREY SUNFLOWER 20KG'%'
Any ideas on this?

[SBarnes]

My advice is run the code on a machine with visual studio installed and at the top of the routine put the code

Code: Select all

System.Diagnostics.Debugger.Launch();
System.Diagnostics.Debugger.Break();


and let the visual studio debugger launch so you can see what is going on, if surround all the code in a try catch block you should be able to get information regarding the exception and see what request is being passed in.

Anything else would be us just guessing at what is going on.

[Mike.Sheen]

I have just verified the plugin Stuart provided in this post works.

I've run Bruce's version which doesn't return any rows:

Code: Select all

main.Description LIKE '%@ItemName%'
ORDER BY main.Description
;";
string sinput = request.ItemName;
return Db.SqlList<MyCustomModel>(query,
new {ItemName = sinput}).ConvertTo<GetInventoryItemsLikeNameResponse>();}

I captured the SQL query using SQL profiler and it looks like this:

Code: Select all

exec sp_executesql N'SELECT
         main.LastSavedDateTime,
         main.InventoryID,
         main.PartNo,
         main.Description,
         main.Status,
         main.DefaultPrice,
         main.RRPPrice,
         main.Aux2,
         main.BackOrderable,

         price.InventoryID,
         price.Price1,
         price.Price2,
         price.Price3,
         price.Price4,
         price.Price9

         FROM IN_Main main
         LEFT JOIN
         IN_SellingPrices price
         ON
         price.InventoryID=main.InventoryID
         WHERE
         main.Description LIKE ''%@ItemName%''
         ORDER BY main.Description
         ;',N'@ItemName varchar(14)',@ItemName='Network Server'

Running that in query analyser and you get no rows - that's a big clue as to what the problem is. Compare that to the SQL that Stuarts version produces:

Code: Select all

exec sp_executesql N'SELECT
         main.LastSavedDateTime,
         main.InventoryID,
         main.PartNo,
         main.Description,
         main.Status,
         main.DefaultPrice,
         main.RRPPrice,
         main.Aux2,
         main.BackOrderable,

         price.InventoryID,
         price.Price1,
         price.Price2,
         price.Price3,
         price.Price4,
         price.Price9

         FROM IN_Main main
         LEFT JOIN
         IN_SellingPrices price
         ON
         price.InventoryID=main.InventoryID
         WHERE
         main.Description LIKE @ItemName
         ORDER BY main.Description
         ;',N'@ItemName varchar(16)',@ItemName='%Network Server%'

So, it's the SQL stored proc sp_executesql which doesn't seem to want to parse the % wildcards outside of the variable name - which makes sense as the wildcard is part of the data of the expression, not an operator - so they belong inside the variable and not the query.

TLDR; Stuarts plugin works and why didn't you profile trace like I asked.

[JuiceyBrucey]

Thank you for your help. I am now using Postman and SQL profiler and find them very helpful.

This is what is working:
main.Description LIKE @ItemName
ORDER BY main.Description ;";
string sinput = "%" + request.ItemName + "%";
return Db.SqlList<MyCustomModel>(query,
new {ItemName = sinput}).ConvertTo<GetInventoryItemsLikeNameResponse>();}

I dont know why this did not work before. I have tried every possible combination including this one, and it did not work before.
Must have missed something.
Cheers