App Development question with middle tier
-
- Posts: 5
- Joined: Fri Feb 09, 2018 8:43 am
App Development question with middle tier
Hi, I'm new here.
I'm trying to extend a home-grown app to support the middle tier option. Previously, the app just connected directly to the local mysql database and ran queries to produce reports.
A new client was using a middle tier architecture and so I don't have direct database access, but instead go through the soap/rest endpoint. I'm not well-versed in C#, so go easy on me.
My question is this: How can I run raw SQL queries through the middle tier layer? Some code samples in C# would be much appreciated!
ArizonaWebGuy
I'm trying to extend a home-grown app to support the middle tier option. Previously, the app just connected directly to the local mysql database and ran queries to produce reports.
A new client was using a middle tier architecture and so I don't have direct database access, but instead go through the soap/rest endpoint. I'm not well-versed in C#, so go easy on me.
My question is this: How can I run raw SQL queries through the middle tier layer? Some code samples in C# would be much appreciated!
ArizonaWebGuy
Re: App Development question with middle tier
You need to comprise an XML version of our OpenDentBusiness.DataTransferObject.cs as a payload that you will send to the WebService's ServiceMain.ProcessRequest(string dtoString). Your DataTransferObject payload needs to be structured in a way that tells the middle tier that you want to execute the method OpenDentBusiness.Reports.GetTable(string command). The web service will then execute that method on your behalf and will return a custom-serialized DataTable with your query results. You then have to deserialize this DataTable yourself or use our custom deserializer which can be found in OpenDentBusiness.XmlConverter.XmlToTable(string xmlData).
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 5
- Joined: Fri Feb 09, 2018 8:43 am
Re: App Development question with middle tier
Ok, so if I understand correctly, I can't tell it directly to run SQL queries, but instead I have to go through it's data query methods?
Can you give a simple example to get me started?
Can you give a simple example to get me started?
Re: App Development question with middle tier
You can absolutely tell the middle tier service to run SQL queries, that is the only job of the OpenDentBusiness.Reports.GetTable() method. You just have to ask the middle tier service to run that query in a way that the middle tier understands. Also, I forgot to mention that you have to scrub the return value for any escaped charaters due to SOAP
E.g. payload:
Which the Middle Tier will return me:
Our de-scrubbing logic is kind of tricky so you'll have to reverse engineer our OpenDentBusiness.XmlConverter.XmlToTable() method which usesOpenDentBusiness.XmlConverter.XmlStringUnescape() heavily.
Good luck, what you're doing is not going to be easy. It's easy if you have access to OpenDentBusiness in a plugin sort of way because then you'd just invoke Reports.GetTable() directly and it would do all of the above for you.
E.g. payload:
Code: Select all
<?xml version="1.0" encoding="utf-16"?>
<DtoGetTableLow xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Credentials>
<Username>Jason</Username>
<Password>Password1</Password>
</Credentials>
<MethodName />
<Params>
<DtoObject>
<TypeName>System.String</TypeName>
<Obj>
<string>SHOW CREATE TABLE account</string>
</Obj>
<IsNull>False</IsNull>
</DtoObject>
</Params>
</DtoGetTableLow>
Code: Select all
<DataTable>
<Name/>
<Cols>
<Col>Table</Col>
<Col>Create Table</Col>
</Cols>
<Cells>
<y>account|CREATE TABLE `account` ( `AccountNum` bigint(20) NOT NULL AUTO_INCREMENT, `Description` varchar(255) DEFAULT '', `AcctType` tinyint(3) unsigned NOT NULL DEFAULT '0', `BankNumber` varchar(255) DEFAULT '', `Inactive` tinyint(3) unsigned NOT NULL DEFAULT '0', `AccountColor` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`AccountNum`) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8</y>
</Cells>
</DataTable>
Good luck, what you're doing is not going to be easy. It's easy if you have access to OpenDentBusiness in a plugin sort of way because then you'd just invoke Reports.GetTable() directly and it would do all of the above for you.
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 5
- Joined: Fri Feb 09, 2018 8:43 am
Re: App Development question with middle tier
This is great to get me started!
I'll definitely consider the plugin option.
Also, if I'm querying for updates on a schedule (say every 5 mins), could there be performance impact to the end-users using the Open Dental software in the office? Is there a low-priority option?
I'll definitely consider the plugin option.
Also, if I'm querying for updates on a schedule (say every 5 mins), could there be performance impact to the end-users using the Open Dental software in the office? Is there a low-priority option?
Re: App Development question with middle tier
That all depends on your query sadly.arizonawebguy wrote:Also, if I'm querying for updates on a schedule (say every 5 mins), could there be performance impact to the end-users using the Open Dental software in the office?Is there a low-priority option?
There is not a low-priority option.arizonawebguy wrote:Is there a low-priority option?
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 5
- Joined: Fri Feb 09, 2018 8:43 am
Re: App Development question with middle tier
Ok, I'll keep that in mind.
Also, I'm having trouble with the XML example. The response it returns is:
System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (dtoString="<?xml version="1.0" ...").
I've tried it both in the Test box on http://localhost/OpenDentalServer/Servi ... essRequest as well as in an application called POSTMAN to send a POST to http://localhost/OpenDentalServer/Servi ... essRequest.
Any ideas? You mentioned SOAP. Is that what your example was for? Can you give me a REST-based example as that's what I'm much more familiar with.
Thanks!
Also, I'm having trouble with the XML example. The response it returns is:
System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (dtoString="<?xml version="1.0" ...").
I've tried it both in the Test box on http://localhost/OpenDentalServer/Servi ... essRequest as well as in an application called POSTMAN to send a POST to http://localhost/OpenDentalServer/Servi ... essRequest.
Any ideas? You mentioned SOAP. Is that what your example was for? Can you give me a REST-based example as that's what I'm much more familiar with.
Thanks!
Re: App Development question with middle tier
Sounds like you might want to use https instead of http?
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 5
- Joined: Fri Feb 09, 2018 8:43 am
Re: App Development question with middle tier
Ok, I've enabled https and switched to posting to that instead. Same error.
System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (dtoString=""<?xml version=\"1.0\...").
at System.Web.HttpRequest.ValidateString(String value, String collectionKey, RequestValidationSource requestCollection)
at System.Web.HttpRequest.ValidateHttpValueCollection(HttpValueCollection collection, RequestValidationSource requestCollection)
at System.Web.HttpRequest.get_Form()
at System.Web.Services.Protocols.HtmlFormParameterReader.Read(HttpRequest request)
at System.Web.Services.Protocols.HttpServerProtocol.ReadParameters()
at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()
System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (dtoString=""<?xml version=\"1.0\...").
at System.Web.HttpRequest.ValidateString(String value, String collectionKey, RequestValidationSource requestCollection)
at System.Web.HttpRequest.ValidateHttpValueCollection(HttpValueCollection collection, RequestValidationSource requestCollection)
at System.Web.HttpRequest.get_Form()
at System.Web.Services.Protocols.HtmlFormParameterReader.Read(HttpRequest request)
at System.Web.Services.Protocols.HttpServerProtocol.ReadParameters()
at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()
Re: App Development question with middle tier
Negative, I'm only familiar enough with SOAP and it seems to work for me. It might even be an IIS problem with the web service (but I doubt that). Someone else might be able to help you with other examples.arizonawebguy wrote:Any ideas? You mentioned SOAP. Is that what your example was for? Can you give me a REST-based example as that's what I'm much more familiar with.
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com