App Development question with middle tier

This forum is for programmers who have questions about the source code.
Post Reply
arizonawebguy
Posts: 5
Joined: Fri Feb 09, 2018 8:43 am

App Development question with middle tier

Post by arizonawebguy » Fri Feb 09, 2018 9:04 am

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

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: App Development question with middle tier

Post by jsalmon » Fri Feb 09, 2018 9:53 am

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

arizonawebguy
Posts: 5
Joined: Fri Feb 09, 2018 8:43 am

Re: App Development question with middle tier

Post by arizonawebguy » Fri Feb 09, 2018 11:06 am

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?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: App Development question with middle tier

Post by jsalmon » Fri Feb 09, 2018 11:41 am

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:

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>
Which the Middle Tier will return me:

Code: Select all

<DataTable>
	<Name/>
	<Cols>
		<Col>Table</Col>
		<Col>Create Table</Col>
	</Cols>
	<Cells>
		<y>account|CREATE TABLE `account` (&#10;  `AccountNum` bigint(20) NOT NULL AUTO_INCREMENT,&#10;  `Description` varchar(255) DEFAULT '',&#10;  `AcctType` tinyint(3) unsigned NOT NULL DEFAULT '0',&#10;  `BankNumber` varchar(255) DEFAULT '',&#10;  `Inactive` tinyint(3) unsigned NOT NULL DEFAULT '0',&#10;  `AccountColor` int(11) NOT NULL DEFAULT '0',&#10;  PRIMARY KEY (`AccountNum`)&#10;) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8</y>
	</Cells>
</DataTable>
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.
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

arizonawebguy
Posts: 5
Joined: Fri Feb 09, 2018 8:43 am

Re: App Development question with middle tier

Post by arizonawebguy » Fri Feb 09, 2018 12:18 pm

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?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: App Development question with middle tier

Post by jsalmon » Fri Feb 09, 2018 12:34 pm

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?
That all depends on your query sadly.
arizonawebguy wrote:Is there a low-priority option?
There is not 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

arizonawebguy
Posts: 5
Joined: Fri Feb 09, 2018 8:43 am

Re: App Development question with middle tier

Post by arizonawebguy » Fri Feb 09, 2018 12:39 pm

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!

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: App Development question with middle tier

Post by jsalmon » Fri Feb 09, 2018 1:07 pm

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

arizonawebguy
Posts: 5
Joined: Fri Feb 09, 2018 8:43 am

Re: App Development question with middle tier

Post by arizonawebguy » Fri Feb 09, 2018 1:40 pm

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()

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: App Development question with middle tier

Post by jsalmon » Fri Feb 09, 2018 2:45 pm

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.
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.
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

Post Reply