|BizTalk SQL Adapter and database schemas||Wednesday 22nd August 2012|
When using the SQL Adapter in BizTalk 2006 to Add Generated Items from the adapter; you might come into a problem is you're using different database schemas with your stored procedures.|
The wizard will display all procs irrespective of the database schema they are in. It does not display the schema name (which can lead to confusion). To make matters much worse, when it runs the proc to get the the output schema (XMLDATA); it will fail to get the response due to not finding the proc under the default schema for the configured user.
It's worth noting that you cannot specify the schema on a Send Port either, so even if you tap in all the XSD information by hand, you still wouldn't be able to run the proc from BizTalk successfully.
There are two obvious workarounds:
- Create your procs under your default schema (e.g. "dbo")
- Create a new user logon to be used for these schemas and assign your schema as the default to this user in SQL.
|Serving XHTML5 to Internet Explorer||Wednesday 15th August 2012|
I'm a big fan of XHTML - aka XML Serialised HTML. It's far more strict than HTML, which forces good code. Not only that, it's easy to parse with standard XML parsers and you don't need to handle sloppy HTML.|
HTML5 is the new kid on the block with a host of new features (like um...?). You can also use XHTML5 to keep things tight and proper like.
Assuming you've written your HTML properly and dealt with non-closing tags correctly then there is little difference between HTML5 and XHTML5 - it's just how you serve it up.
HTML5 should have a DocType define like this:
<!doctype html>And XHTML:
<!DOCTYPE html>All nice and simple so far. The other main difference is the Content-Type - this is not something that is done in your mark-up, but by the HTTP server. XHTML5 needs to be served up as application/xhtml+xml, whilst normal HTML comes as text/html.
If you name your document .xhtml some HTTP daemons will automatically do this for you.
There is though, a snag. And this snag is what causes many people to recommend not using XHTML and to instead, using the lapse world of HTML. The snag is Internet Explorer 8 and below bails when it receives a content-type of application/xhtml+xml. Despite IE claiming to handle every content-type with a Accept: */* header, it can't.
Still, with Apache, there is an easy fix for IE with your .htaccess and utilising the BrowserMatch and Header directives:
BrowserMatch "MSIE [1-9]." oldbrowser
Header set Content-Type text/html env=oldbrowser
This simply does a regular expression match against the user-agent, by specifying the . after the major-version number means it won't go wrong with IE 10.0. And if that matches the environment variable "oldbrowser" is created (this is one we've just made up).
The second line sets the Content-Type response header, but only if the environment variable oldbrowser exists. Note, that if you set your Content-Type in PHP or something Apache won't change it.
|SQL select single record from one-to-many sub-query||Tuesday 14th August 2012|
I'm sure the SQL folk have a better names for these things... but anyway. If you have a one-to-many relationship with some tables and you want to select just one record from the many-table then it can be a bit of a fiddle.|
There is an easy way to do it (well, in MS-SQL) using the row_number() function.
Consider the following structure:
CREATE TABLE #customer ( customer_id int PRIMARY KEY,customer_name varchar(32))
CREATE TABLE #orders (
order_id int IDENTITY PRIMARY KEY,
order_customer_id int REFERENCES customer(customer_id),
With some data:
INSERT INTO #customer VALUES (1, 'Bob')
INSERT INTO #orders VALUES (1, 'Widgets', getdate())
INSERT INTO #orders VALUES (1, 'Boxes', getdate()-1)
INSERT INTO #customer VALUES (2, 'Bill')
INSERT INTO #orders VALUES (2, 'Boxes', getdate()-1)
INSERT INTO #orders VALUES (2, 'Widgets', getdate())
And the following objective:
Display the customer record along with their most recent order
You may follow the route of
SELECT * FROM #customer c LEFT JOIN #orders o ON c.customer_id = o.order_customer_id ORDER BY o.order_date DESC
The problem with that route is you get multiple instances of each customer for each order that exists. If you think out to a bigger example you may have hundreds of rows for each customer.
You can though, use the aforementioned ROW_NUMBER() function to produce a sequential number next to each record. This can be ordered and then you merely need to do a where clause against the row_number column to get 1 (or as many as you fancy) records. e.g.
SELECT * FROM #customer c LEFT JOIN (
SELECT row_number() OVER (PARTITION BY order_customer_id ORDER BY order_date DESC) AS [order_rank], *
) AS o ON c.customer_id = o.order_customer_id
WHERE o.order_rank = 1
|Unencrypted password impossibilities||Thursday 9th August 2012|
I'm currently playing around with standard web-site logons, and how to bolster security a bit.|
It is common practice to MD5 hash the user's password and store this in your user database. When they authenticate at a later date, this is done again and you compare the hashes.
Most web developers are aware of individually "salting" each password with a unique code to prevent the use of Rainbow Tables. And most know that you need to use HTTPS to ensure against session/cookie hi-jacking.
And whilst generating SSL certificates is child's play, and the overhead of encryption is minimal in this day of cheap hardware - public certificate authorities still charge a small fortune for certificates.
Generating your certificates yourself is not an option as they won't validate in browsers and the complication of getting users to trust other CAs is never going to happen.
So how can we send at least passwords over the wire encrypted? HTTP has Digest authentication, which hashes the password with a unique one-off ID. Whilst this prevents passwords from being leaked, it means we cannot check that the password is valid against our database unless we store the unencrypted password so we can also hash it and compare the results.
We could use the same nonce each time, but this would undermine the protocol and make it insecure and brings us all the way back to square one of easily crackable passwords.
The cheap solution? Not sure yet...