API plugin using SQL LIKE query returns error

[JuiceyBrucey]

Hi,
Using JIWA 7.2.1.0
Doing a request that uses a SQL "LIKE" query to get all inventory items that have similar names.
I have tested the query in MS SQL server and it works fine. It compiles fine in JIWA, but I get an error message:
"HTTP Error 400. The request is badly formed"
Any advice?

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using JiwaFinancials.Jiwa;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Drawing;
using ServiceStack;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using JiwaFinancials.Jiwa.JiwaServiceModel.Tables;
using ServiceStack.Auth;
using System.Linq;
using ServiceStack.OrmLite; // Db

namespace JiwaFinancials.Jiwa.JiwaServiceModel
{
public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
{
public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
{
AppHost.RegisterService<CustomServices>();
AppHost.Routes.Add(typeof(GetInventoryItemsLikeNameRequest), "/Custom/GetInventoryItemsLikeNameRequest/{ItemName}/", "GET", "Retrieves inventory with limited fields, end and start specified.", "");
}
}

#region "Requests"
[ApiResponse(200, "Records retrieved OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "Handler for Request not found")]
public class GetInventoryItemsLikeNameRequest : IReturn<GetInventoryItemsLikeNameResponse>
{
public string ItemName { get; set; }
}
#endregion

#region "Responses"
[Serializable()]
public class GetInventoryItemsLikeNameResponse : List<MyCustomModel>
{
}
#endregion

#region "Models"
public class MyCustomModel
{
public DateTime LastSavedDateTime { get; set; }
public string InventoryID { get; set; }
public string PartNo { get; set; }
public string Description { get; set; }
public int Status { get; set; }
public decimal DefaultPrice { get; set; }
public decimal RRPPrice { get; set; }
public string Aux2 { get; set; }
public bool BackOrderable { get; set; }
public decimal Price1 { get; set; }
public decimal Price2 { get; set; }
public decimal Price3 { get; set; }
public decimal Price4 { get; set; }
public decimal Price9 { get; set; }
}
#endregion

#region "Services"
public class CustomServices : Service
{
[Authenticate]
public GetInventoryItemsLikeNameResponse Get(GetInventoryItemsLikeNameRequest request)
{

string query = @"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
;";
return Db.SqlList<MyCustomModel>(query,
new {ItemName = request.ItemName}).ConvertTo<GetInventoryItemsLikeNameResponse>();
}
}
#endregion
}

[SBarnes]

See this https://stackoverflow.com/questions/421 ... t-wildcard

[JuiceyBrucey]

Thank you for your reply.
This helps with my understanding, but does not quite get me there.
I have tried a couple of things with this, but still no luck. The items name variable needs to be introduced via a request.
I am not sure how I can do this. I have tried below, but getting errors.
Any ideas?

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

[SBarnes]

I believe you have too many @s

Code: Select all

return Db.SqlList<MyCustomModel>(query,
new {ItemName = "%@request.ItemName%"}).ConvertTo<GetInventoryItemsLikeNameResponse>();


should be

Code: Select all

return Db.SqlList<MyCustomModel>(query,
new {ItemName = "%request.ItemName%"}).ConvertTo<GetInventoryItemsLikeNameResponse>();


or

Code: Select all

string sinput = "%" + request.ItemName+ "%";
return Db.SqlList<MyCustomModel>(query,
new {ItemName = sinput }).ConvertTo<GetInventoryItemsLikeNameResponse>();


[JuiceyBrucey]

thank you for your reply.
I have tried a few different methods based on your previous answer, but unfortunately, I am getting:
"HTTP Error 400. The request is badly formed."

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using JiwaFinancials.Jiwa;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Drawing;
using ServiceStack;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using JiwaFinancials.Jiwa.JiwaServiceModel.Tables;
using ServiceStack.Auth;
using System.Linq;
using ServiceStack.OrmLite; // Db

namespace JiwaFinancials.Jiwa.JiwaServiceModel
{
public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
{
public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
{
AppHost.RegisterService<CustomServices>();
AppHost.Routes.Add(typeof(GetInventoryItemsLikeNameRequest), "/Custom/GetInventoryItemsLikeNameRequest/{ItemName}/", "GET", "Retrieves inventory with limited fields, end and start specified.", "");
}
}

#region "Requests"
[ApiResponse(200, "Records retrieved OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "Handler for Request not found")]
public class GetInventoryItemsLikeNameRequest : IReturn<GetInventoryItemsLikeNameResponse>
{
public string ItemName { get; set; }
}
#endregion

#region "Responses"
[Serializable()]
public class GetInventoryItemsLikeNameResponse : List<MyCustomModel>
{
}
#endregion

#region "Models"
public class MyCustomModel
{
public DateTime LastSavedDateTime { get; set; }
public string InventoryID { get; set; }
public string PartNo { get; set; }
public string Description { get; set; }
public int Status { get; set; }
public decimal DefaultPrice { get; set; }
public decimal RRPPrice { get; set; }
public string Aux2 { get; set; }
public bool BackOrderable { get; set; }
public decimal Price1 { get; set; }
public decimal Price2 { get; set; }
public decimal Price3 { get; set; }
public decimal Price4 { get; set; }
public decimal Price9 { get; set; }
}
#endregion

#region "Services"
public class CustomServices : Service
{
[Authenticate]
public GetInventoryItemsLikeNameResponse Get(GetInventoryItemsLikeNameRequest request)
{

string query = @"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
;";
return Db.SqlList<MyCustomModel>(query,
new {ItemName = "'%request.ItemName%'"}).ConvertTo<GetInventoryItemsLikeNameResponse>();}
}
#endregion
}

[SBarnes]

Try changing from a get verb to a post like below and then send the request in the body as Json and the verb as post, it's probably not a good idea to have a route that's going to possibly have space in it anyway and creating a url for this

When the Jiwa api does this it is because it's using things like inventory id which is an id with no spaces in it.

The other thing if you are doing this on a machine with visual studio installed you can always added the code as this to launch visual studios debugger at the top of the service method.

Code: Select all

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


Code: Select all


using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using JiwaFinancials.Jiwa;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Drawing;
using ServiceStack;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using JiwaFinancials.Jiwa.JiwaServiceModel.Tables;
using ServiceStack.Auth;
using System.Linq;
using ServiceStack.OrmLite; // Db

namespace JiwaFinancials.Jiwa.JiwaServiceModel
{
public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
{
public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
{
AppHost.RegisterService<CustomServices>();
AppHost.Routes.Add(typeof(GetInventoryItemsLikeNameRequest), "/Custom/PostInventoryItemsLikeNameRequest/", "POST", "Retrieves inventory with limited fields, end and start specified.", "");
}
}

#region "Requests"
[ApiResponse(200, "Records retrieved OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "Handler for Request not found")]
public class PostInventoryItemsLikeNameRequest : IReturn<PostInventoryItemsLikeNameResponse>
{
public string ItemName { get; set; }
}
#endregion

#region "Responses"
[Serializable()]
public class PostInventoryItemsLikeNameResponse : List<MyCustomModel>
{
}
#endregion

#region "Models"
public class MyCustomModel
{
public DateTime LastSavedDateTime { get; set; }
public string InventoryID { get; set; }
public string PartNo { get; set; }
public string Description { get; set; }
public int Status { get; set; }
public decimal DefaultPrice { get; set; }
public decimal RRPPrice { get; set; }
public string Aux2 { get; set; }
public bool BackOrderable { get; set; }
public decimal Price1 { get; set; }
public decimal Price2 { get; set; }
public decimal Price3 { get; set; }
public decimal Price4 { get; set; }
public decimal Price9 { get; set; }
}
#endregion

#region "Services"
public class CustomServices : Service
{
[Authenticate]
public PostInventoryItemsLikeNameResponse Post(PostInventoryItemsLikeNameRequest request)
{

string query = @"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
;";
return Db.SqlList<MyCustomModel>(query,
new {ItemName = "'%request.ItemName%'"}).ConvertTo<PostInventoryItemsLikeNameResponse>();}
}
#endregion
}


[JuiceyBrucey]

Sorry, did not work.
I have tried all of the above suggestions and no go. I have also tried many variations of them all. No luck.
I keep getting:
"HTTP Error 400. The request is badly formed."
Thank you for your help.
Cheers

[SBarnes]

See the attached this works against Jiwa's Demo database using the Swagger UI.

[JuiceyBrucey]

Thank you.
Just a question, if this is a GET query, where does the item name string get introduced?
I can send it in POST variables, but then it would not be a GET query.
Am I missing something here?
Cheers

[Mike.Sheen]

Thank you.
Just a question, if this is a GET query, where does the item name string get introduced?
I can send it in POST variables, but then it would not be a GET query.
Am I missing something here?
Cheers

You just need your request DTO (class) to contain a property named ItemName. The framework automatically hydrates your DTO from the URL parameters by string matching.