SQL Queries

Submitted by kidder on Fri, 07/01/2016 - 15:15
Hi guys

interesting one for you on a friday...

We have a project that is on our Dev, UAT and Live servers. One of the SQL queries that it runs returns a recordset like this on our Dev and UAT servers
-<record>
<Name>Client1_TVLetter2</Name>
<FieldID>483</FieldID>
<XML_Element">ThirdPartyInfo1Title</XML_Element>
<FieldDesc>Enter the Third Party Contact Person Title, If Given:- </FieldDesc>
<Field_Data_Type>S</Field_Data_Type>
<Field_Mandatory>0</Field_Mandatory>
<updated/>
</record>

However on our Live system it returns this recordset...slightly different for no reason we can determine.

-<record>
<Name>Client1_TVLetter2</Name>
<FieldID>483</FieldID>
<value column_name="XML_Element">ThirdPartyInfo1Title</value>
<FieldDesc>Enter the Third Party Contact Person Title, If Given:- </FieldDesc>
<Field_Data_Type>S</Field_Data_Type>
<Field_Mandatory>0</Field_Mandatory>
<updated/>
</record>

Our first thoughts were that it must be data, but we can't see any significant differences between the environments. The projects are the same (I've WinDiff'd them and also pointed Dev at Live and reproduced the problem that way). The fields do hold some characters that may be an issue - but they work ok on dev/uat. Any ideas?
[hr][/hr]
On another note, I've just been promoted to Guru but all i do is ask silly questions!!
[hr][/hr]

Hi guys

interesting one for you on a friday...

We have a project that is on our Dev, UAT and Live servers. One of the SQL queries that it runs returns a recordset like this on our Dev and UAT servers
-<record>
<Name>Client1_TVLetter2</Name>
<FieldID>483</FieldID>
<XML_Element">ThirdPartyInfo1Title</XML_Element>
<FieldDesc>Enter the Third Party Contact Person Title, If Given:- </FieldDesc>
<Field_Data_Type>S</Field_Data_Type>
<Field_Mandatory>0</Field_Mandatory>
<updated/>
</record>

However on our Live system it returns this recordset...slightly different for no reason we can determine.

-<record>
<Name>Client1_TVLetter2</Name>
<FieldID>483</FieldID>
<value column_name="XML_Element">ThirdPartyInfo1Title</value>
<FieldDesc>Enter the Third Party Contact Person Title, If Given:- </FieldDesc>
<Field_Data_Type>S</Field_Data_Type>
<Field_Mandatory>0</Field_Mandatory>
<updated/>
</record>

Our first thoughts were that it must be data, but we can't see any significant differences between the environments. The projects are the same (I've WinDiff'd them and also pointed Dev at Live and reproduced the problem that way). The fields do hold some characters that may be an issue - but they work ok on dev/uat. Any ideas?
[hr][/hr]
On another note, I've just been promoted to Guru but all i do is ask silly questions!!


oh, and running the queries directly on SQL Server returns the data as you would expect.

Hi kidder,

I think you've been using WebMaker for long enough now to warrant the Guru label! :)

With regards to the question, my initial guess would be that the Live system is running a different WebMaker version than the others.

In version 7 we tightened up the conversion from database column names to XML elements to better handle names that are actually invalid XML names.

The spec states that names starting with 'xml' are reserved (see https://www.w3.org/TR/REC-xml/#dt-name) so while the string 'XML_Element' is a valid name otherwise it shouldn't really be used for an XML element. This is why this is getting converted to a 'value' element with a 'column_name' attribute.

Before version 7 we didn't do this checking which could explain why you are getting the first output on your other servers.

Do you think it is possible that your Dev and UAT servers could be using older versions of the WebMaker jars?

Regards,
Gerard

Hi Gerard

As we use BizFlow, and all our environments are at the same BF level (12.3), shouldn't this mean that the versions of WM are the same too?

I've windiff'd the webapps\bizflowwebmaker\WEB-INF\lib folders on our dev and live environments. Everything is identical (well there are a couple of files suffixed .old/.bak), except that our Live server has a commons-httpclient-3.1.jar, a commons-codec-1.3.jar and a ojdbc5.jar that are on live but not on dev (also a soap.jar and a xmlspyinterface.jar which relate to SoapUI and XMLspy i think).

thanks for your help

Hi kidder,

Yes I would expect all the environments to have the same WebMaker version if they are the same BizFlow version, but it was one answer that would explain the behaviour you are getting.

BizFlow 12.3 includes WebMaker 7 so I would have expected all the environments to have the 'value' element in the SQL response structure.

Do you know if the windiff check compared the contents of the JAR files? One way to confirm would be to look at the meta-inf/manifest.mf file within xplatform.jar in each environment.
I believe this should specify an 'Implementation-Version' of 'build.1624 r18897 October 24 2014' for the WM7 BizFlow release.

One other thing that you could check is whether there are any WebMaker jars elsewhere on the system that could be being picked up. eg under tomcat/lib.

Other than this, I am unsure why you would be getting the different behaviour. Are the database versions the same on all environments?
I guess you could use a 'SELECT abc AS xyz' type of statement in the SQL to rename the 'XML_Element' column in the result set, but this would be working round the problem rather than solving it!

Regards,
Gerard

I've looked at the manifest.mf files within xplatform.jar on our live web01 server and our dev server and they are identical. I can't see any WebMaker files under tomcat/lib - are there any i should lookout for in particular?

Database versions are all consistent at SQL Server 10.50.6529.

I think i'll have to go for the workaround for now. We're a little concerned by the implications off this issue. We have WM projects still active on v3.1.2 and v5. We thought that they were all self-contained - but could changes to the WM jar files cause problems with older versions? Do we need to upgrade all WM projects to the latest version when we upgrade BizFlow?

Hi kidder,

This is very strange. I can't see why you are getting the different behaviour at the moment.
Can you clarify the type of SQL Statement being used? Is this just a simple SELECT or are you calling a stored procedure for example?

Also, going back to your original post, can you clarify what you mean by 'pointed Dev at Live and reproduced the problem'?

We will continue to look into this, but if required, would you possibly be able to test a JAR with some additional debugging code on your dev environment?


Each published project contains the complete specification, but the WebMaker JARs provide the platform on which this specification runs.
Any new WebMaker version will run older projects without them needing to be upgraded within the studio, but the new JARs could include bug fixes that may affect older projects, in addition to any new functionality that wont apply to them.
This is why we would always recommend retesting your projects after upgrading the runtime environment, even if they haven't been upgraded in the studio.
What is strange in your case is that your dev and UAT environments aren't behaving in the same way as the live one.

The change I mentioned above is one of these bug fixes, which was put in place to resolve issues where a column with an invalid name was found. Previously this was stopping any data from being returned from the SQL call.
Your case is a bit of an 'outlier' in that the name being used is officially invalid, but was previously working ok.

Regards,
Gerard

Hi Gerard
the SQL is fairly standard:

select TE.Name Name, TF.FieldID FieldID, TF.XML_Element ElementName,
TF.FieldDesc FieldDesc, TF.Field_Data_Type Field_Data_Type,
TF.Field_Mandatory Field_Mandatory, '' updated, TF.Field_RowID Field_RowID
from Template_Env TE
inner join bizflow_bespoke..schemetype st on st.typid = te.busareaid
inner join Template_Fields TF on TF.TemplateID = TE.TemplateID
where TE.Name = $Template
order by TF.Field_RowID;

As you can see I've adapted it now so that it aliases the column name to a name that shouldn't cause WM issues.

Re: recreating the issues in Dev by pointing it at live, i was referring to using the Morphyc file from our live server on Dev. After trying this again now I can't recreate the issue...

I'd be happy to put the dubugging on in Dev - not sure whether I'd have to set the project back though?

Thanks for all your assistance on this.

Thanks kidder.

I've attached a temporary version of the v7 xplatform.jar that it would be great if you could test on your dev environment. This needs to be placed in the bizflowwebmaker/WEB-INF/lib directory after taking a copy of the existing one so you can put it back afterwards.

Can you roll back your SQL change and then re run it with this jar? This should output a few debug comments to standard output to hopefully give us more info on how the column names are being checked.

It would be great if you could let me know what output you get in your environment.

Many thanks,
Gerard
attachment
xplatform.jar

Hi Gerard

I've put this jar file in, and rolled back the workaround. Where should i be looking for the debug output?

Hi kidder,

The new debug statements are just being sent to standard out. If you have started tomcat manually you should see them in the tomcat window. If tomcat is running as a service, then I think they appear in a stdout or catalina.out log file.

Regards,
Gerard

Hi Gerard

We're under a bit of pressure to fix this so i've gone with the workaround for now to try to fix the issue.

I intend to come back to this in our Dev environment, and will let you know how i get on.

Thanks

We use cookies and analytics on our website to support technical features that enhance your user experience.

-