Eureka!

Hasn’t it always bugged you that you could never do a LIKE clause with multiple criteria? I found this very limiting, especially so when dealing with multi-valued parameters in MS Reporting Services. I believe to have found a way around this! (I haven’t seen this method used anywhere yet, but I’m sure many of you may be familiar with it.)

In order to make this work, we need to use some association. You will have to create a (temporary) table in which to store your criteria, complete with wildcards. Each criterion should be a separate record. From there we JOIN our target table, which we are comparing the criterion with. Furthermore, we will place a limiter on the JOIN to only allow the selected criteria. This last step is only necessary if your criteria are based on a lookup table (presumable the same one you are using to JOIN) and of which only a subset is selected (that is my situation in one of my reports in MS Reporting Services).

Note that the items in [table A] must be stored with their wildcards, i.e. ‘%criterion1%’, ‘%criterion2%’, etc.

Let’s pseudo-code this out:

1. CREATE TABLE [table A] with criteria to be used in LIKE clause.
2. JOIN criterion table to main table [table B].
3. Limit JOIN to LIKE clause using field [field B] from [table B] and field [field A] from [table A].

Now for some code:

SELECT *
FROM [table B] AS b
JOIN [table A] AS a ON a.[field A] LIKE b.[field B]

If you are using [table A] as a lookup table to establish criteria on a report, of which only a subset may be selected, do the following:

SELECT *
FROM [table B] AS b
JOIN [table A] AS a ON a.[field A] LIKE b.[field B] AND a.[field A] IN (@report_parameter)

where @report_parameter is the resultset from a query, or a comma-separated list of items that exist in [table A]. This is analogous to:

SELECT *
FROM [table B] AS b
JOIN [table A] AS a ON a.[field A] LIKE b.[field B] AND a.[field A] IN (’%criterion1%’, ‘%criterion2%’, ‘%criterion3%’)

JavaScript,Extending,Objects

How often have you run into the situation where it would be great if you could chain getElement(s)By method calls? Unfortunately JavaScript does not support this out of the box since the getElement(s)By functions return various collection objects.

Thankfully there is a way you can extend JavaScript functions and objects using prototyping. The stub would look somewhat like this, and you would have to include this in whatever pages use the chained calls:

Object.prototype.getElementByID = function(s_id)
{
alert(s_name);

return null;
}

This is just one example. Using this process you can then run effective chaining that could look like this:

document.getElementsByTagName("rule").getElementsByAttribute("format", document.getElementsByAttribute("data_format");

As soon as I have the extended methods completed, I will make them available here for download. Feel free to contribute, if you have any particular ideas on this!

contribute,adobe,cs3,contribute publishing server

Well, here is the promised follow-up on my evaluation of Contribute and its buddy CPS.

I had some initial trouble getting it configured so that it would tie in to our ActiveDirectory server (that way we don’t need to manage users aside from in ActiveDirectory). The problem was that I couldn’t establish a connection between CPS and AD.

My assumption at this point was that CPS as actually seeing AD, but I was providing incorrect credentials. Well, the CPS documentation states that you need to enter your user DN as follows: uid=username, ou=group, dn=server, dn=com. This never worked for connection to AD using Windows Authentication Method, eventhough I customized it to use our schema (meaning instead of uid I used samAuthentication).

Making a call to Adobe Support also proved fruitless, as they will not support unregistered (speak trial) versions of their software. When I mentioned that I was evaluating the software for a company with the possibility of a future purpose, it didn’t even phase them; *Indian accent* “I’m sorry sir, we can only support registered versions of your software.”

Back to the drawing board. Luckily (after hours of searching) I stumbled accross a post that mentioned that the user was connecting using the folliwng user DN: user@server.com. This worked like a charm!

Now that I was up and running, I ran contribute and began administering the users (I could now search active directory) and was able to establish a writer > publisher > admin workflow process. Exactly what we are looking for.

Contribute really is the optimal solution if you want to give your content writers as little or as much leeway as they need, while not sacrificing versatility (which is often lacking in CRMs with admin modules). Our users can now create whatever content they wish withing the parameters we allow them, without fear of them breaking site funcitonality; all the appropriate checks and balances are in place.

The downside is, that is is divorced from your Microsoft development path that comes with using Visual Studio. In our case that is not a real big deal, as we will simply use Adobe line of software for our public company site, and continue using Microsoft line of software for our intranet. This is a clean division of applications that corresponds to the division of responsibility (meaning that the users creating content for the public site aren’t the users managing the intranet).

What CPS provides is a tie-in to AD (user management), change control (versioning up to 99 changes per page), and mist importantly process workflow.

Where do I start? Well… I guess I could post about the reason behind this post. I am currently evaluating Contribute CS3 as a potential tool for our non-technical staff to assist in maintaining our company we site.

So far I am amazed at the ease of use in Contribute CS3! I played around with it back when Contribute 2 was published (not CS2), and dismissed it as a nifty tool that didn’t have any real value to me at the time. However, it has significantly matured since, and am delighted to find that it recognized my custom install of this WordPress blog on my site with no manual configuratino necessary! (It also recognizes many other blog standards, like Blogger, etc.)

Further testing will determine how viable it is for a medium-sized business that wants to allow content publishing to its web site without necessarily involving the IT department all the time. This means establishing of roles, publishing rights, and chain of events in the publication process. Contribute Publishing Server should allow for all this (and more!).

I’ll break into that next.

[Update: 9/25/2007] At the moment I am still awaiting purchase of Adobe CS3 at my workplace to post more detailed testing results. This post was basically a use-case for connecting to my WordPress blog. Future write-ups will be more detailed.

Over the past few months I have been working on creating an optimum development environment for creating web applications in .NET. The current development environment is for creating web applications in PHP.

While the tools differ (.NET vs. PHP), the goal is still the same: creating web applications. Thus, you should be able to use the same development environment. Now, this will not hold water if you have a PHP environment that is highly specialized toward PHP development; the same goes for strongly .NET dependent environment set-ups.

My contention here is that your development environment should be set up according to the goal, and not the specific flavor of technology being implemented. This will allow for migration between different technologies without creating a huge upset in the way things are done. “What are you specifically getting at here?”, you ask me. Well, lets examine what a development environment entails.

Research
Ideas don’t just make themselves. A lot of thought, trial and error, and playing around goes into perfecting an idea and developing a use case and proof of concept. This includes database designs, code, integration of 3rd-party-tools, plug-ins, and so forth.

Documentation
Once your idea matures and you are able to show proof of concept and derive a use case from your research, it needs to be documented. Often documentation is a burden placed on developers or technical writers to satisfy process, and not necessity. I’m not talking about writing a doctorate on the work that needs to be implemented (code changes, functionality updates, new project design, application design, etc.). What I’m talking about here is the short and sweet documentation that is concise, demonstrative, and clear on how to implement the proposed items outlined in the document. You should be able to hand one portion of the documentation to any developer who should be able to create the desired end product without having to consult with the client. It needs to be that good! And at the same time you should be able to hand the other part of the documentation to the client, so as to give them a clear picture of exactly what the end product will do, what its specific parts are, how they interact, and how the client’s users will interact with them. Yes, it has to be that good. Only then will you achieve a useful product that your clients will be happy with.

Architecture
You will architect your products and applications based on the documentation, as well as your findings from your research. Architecting and documenting a web application design often go hand-in-hand and will require extensive revisioning of the documentation. Your application architecture needs to implement best practice designs wherever possible. If there aren’t any for your given situation, don’t hesitate to create best practices and publish them! Public scrutiny is your best advocate in creating high quality designs.

Development
The development stage of the product should not entail any unforeseen activities. Yes, you have developers, but they should not be trying to figure out problems as they are developing your applications. All problems, hurdles, and glitches should have been thought through in the documentation and architecture phases. At this point the development should follow the guidelines of the documentation, just as a cook follows a recipe.

Testing
While your situation may not allow for test-driven development, it is the best way to achieve your development goals with a minimum of bugs, and it also encourages use of best practices. Test-driven development means that you first develop a test class with various test methods that test each functionality of your application. Since you have thoroughly documented all functionality, you already know what you want to achieve; you write your test methods to test for the desired result by running the methods, classes, or functions of code in your application that correspond to that functionality. If the function fulfills those requirements without failing, you have achieved your goal, and your test is successful; if not, your test fails.
Like I said, not all environments will allow for this, and in the event you can’t or decide not to implement this, you always need to test your code during its development to ensure that you are meeting your goals. This testing should optimally include peers, so as to expose your code to their scrutiny. They will have feedback, certainly if there are errors, and more often than not even if your code runs fine. There is always room for improvement, and it is a great way to advance your programming skills.

Trial Phases
Once portions of the application are preview-ready they should be tried by the client. Never abandon your client during development. Keep them up-to-date at least on a semi-weekly basis, if not daily. Your client will never ever get the design specs exactly how they need to be in practice, so the trial of functionality will reveal any problems that actual implementation may hold. Keep your client in lock-step with your development, show them what’s going on, and even what is not working. Give them the opportunity to adjust the project. This is not necessarily scope creep, but more often corrective measures. We like to label this scope creep so that we can blame the client for missed deadlines. However, not all changes need too be implemented right away. Document any planned changes and add them as enhancements after the project is rolled out, or in another phase of development. Try to avoid adjusting your current documentation to much, as it will cause disruption in the development process, if many developers are working on many different parts, and have to rework a lot of their code. On the other hand, if extensive re-work is required it may be best to halt all development and iron out the details with the client, re-document, and then go from there.

Deployment
Once all trials have been performed satisfactory, the product needs to be deployed into the production environment. The deployment mechanism should have no impact on the development environment, and should not be part of, or depend on, your development environment. If at all possible, deployment should be automated, so as to avoid human error.

Maintenance
We all know that there will be updates and bug-fixes down the road, no matter how good we code an application. Maintaining bugs, and tracking errors should be done in a standardized way, regardless of technology. This means that your applications should inform you that the client is having problems. You should be aware, and maybe already have a patch available when the client comes to you with their issues.

Everything I outlined here is technology independent. It is a collection of procedures that can be adhered to regardless of the technologies being used. This means that you should be able to switch from PHP to .NET development in the same environment with no impact on your procedures, making the life of your management so much easier. Heck, it will make your life so much easier, too.

Now, this isn’t as easy as it sounds. We often have special tools to aid us with each of the above processes, like bug-tracking, project management, deployment, code versioning, and so on. Often these tools integrate with our development environments to make our lives (at the moment) easier.

This is the actual crux of my post: what tools can aid you with your processes, while remaining technology independent. I will post more on this as I work through it myself.

Stefan Esser made a great suggestion: we should have a “Month of PHP Bugs”, highlighting a bug in PHP every day for a month.

I think this is a great idea. If for no other reason than to give novice programmers some good learning material. So far there hasn’t been much “active awareness” pushed by PHP (Zend, et al.) to communicate things that beginning programmers need to be aware of.

The drawback with this is that the programmers need to learn by trial and error (usually at the employer’s expense) and in the mean time make their applications vulnerable.

I would love it if there was a site dedicated to PHP security. Not just vulnerabilities as in bugs, but also in proper ways of coding secure applications, best practices, and such. I’ve always found php.net to be the best resource when looking up functionality and syntax. A lot of the issues and vulnerabilities that programmers need to know about are indeed “hidden” in the user contributions at the bottom of the pages, however, they are cumbersome to read through, and often include a slew of ideas, tips, tricks, warnings, and best practices thrown together.

Of course, the other side of the coin (playing devil’s advocate here) is that developers need to be on top of their game at all times. Meaning they need to educate themselves regularly about their chosen programming languages. This includes reading up on best practices, coding securely, and learning new tricks.

**SQUASH**

Here are some links on the topic to get you started:

• PHP Security Blog
• PHP Security Consortium
• Essential PHP Security
• PHP Advisory
• Hardened-PHP Project
• SecurityFocus

Yahoo! has started a project on web design around the idea that ease of use is a must. While Yahoo! isn’t the first to use these technologies, they bring the concepts together in a targetted manner, almost like a workshop, complete with code examples.
I believe the project is ongoing, but it already has some substantial technologies in its repository that deal with navigation issues, drag-and-drop functionalities, menu systems, and so much more.

While all these technologies are ‘neat’, ‘fun’, and ‘cool’ to develop, and even use, I would argue that improper implimentation will actually be detrimental to the user experience. These technologies are not the one solution that will fix everything, however, with the right planning, the proper foresight, and the correct implimentation, they will take web users into the next generation of non-flash web sites.

Of course these technologies tend to behave a bit different in the different browsers, so when designing the implimentation methods and functional requirements for your site, you need to be keenly aware the specific (dis)abilities that each browser presents, so that you won’t alienate potential customers or visitors.

[Click the title to visit Yahoo!’s site.] Over the next few weeks I will begin to impliment the various technologies and will detail my experiences here.

Cheers!
Mike

Say you are changing information in you app against a dataset, then you can view the changes you have made at any time by utilitising various tracking methods of the dataset. These include .HasChanges(), .GetChanges(), etc.

Transaction processing is an absolutely vital part of developing database applications. It allows for commits and rollbacks. So, in the event that something unforseen should occur, you can roll back all your SQL statements and handle the exception, then make the necessary adjustments, and run the scripts again. Here is a short example:

Dim objConn As New SqlConnectionDim objComm As New SqlCommandDim objTran As SqlTransaction

Try   objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"   objConn.Open()   objTran = objConn.BeginTransaction(IsolationLevel.ReadCommitted)   objComm.Connection = objConn   objComm.CommandType = CommandType.Text   objComm.CommandText = "INSERT INTO blog (entry) VALUES ('" + txtEntry.Text + "')"   objComm.Transaction = objTran   objComm.ExecuteNonQuery()   objTran.Commit()   objConn.Close()   LoadBlog()Catch objError As Exception   objTran.Rollback()   MessageBox.Show(objError.Message)End Try

You will immediately notice that I roll back any SQL queries that happened after the transaction object was set if any error should occur. This means that if anything goes wrong, the database does not get updated. You can see where this is going: for maintenance, batch processes, etc., you should always be using transaction processes, so that if one statement should fail, it wont corrupt other dependant data.

1. When working with datareaders and stored procedures, and your stored procedure returns a paramter value, you must close the datareader before you can access the parameter value. Microsoft says its not a bug, but missing functionality is just as bad as erroneous funcitonality. So: BUG!
2. You can call .ExecuteScalar on queries that return multiple rows (for some reason, eventhough it makes no sense), however, it will only return the first field of the first row.
3. You can bind datareaders to web objects when in ASP.NET, however, you can only bind datareaders to datasets, data tables, and data views when working in VB.NET.
4. You must ‘prime’ a datareader before accessing its content, like so:

   While reader.Read()[...]End While

5. When retrieving data from datareaders using the various get methods, only the SQL data type get methods allow for NULL values. Using the get methods uses less overhead, since the data is returned as that explicit type and not as an object.
6. Multiple selects can be executed at the same time using a single data reader. This can be usefull for minimizing resource usage when initializing data when opening an application or loading a web app. Consider the following (note reader.NextResult(), which moves to the next SQL statement in the datareader results):

   comm.CommandText = "SELECT * FROM customers;SELECT * FROM products"reader = comm.ExecuteReaderWhile reader.Read()   [...]End WhileIf (reader.NextResult()) Then   While reader.Read()       [...]   End WhileEnd If