App Development question with middle tier

[arizonawebguy]

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

[jsalmon]

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

[arizonawebguy]

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?

[jsalmon]

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.

[arizonawebguy]

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?

[jsalmon]

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.

Is there a low-priority option?

There is not a low-priority option.

[arizonawebguy]

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!

[jsalmon]

Sounds like you might want to use https instead of http?

[arizonawebguy]

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

[jsalmon]

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.