Feed: Ben Forta's Blog - SQL - AggScore: 66.9

I just received three new translations of my books, two into Simplified Chinese and one into Korean:

• Sams Teach Yourself Microsoft SQL Server T-SQL In 10 Minutes is now available in Simplified Chinese
• MySQL Crash Course is now available in Simplified Chinese
• Sams Teach Yourself SQL In 10 Minutes is now available in Korean

Links to the new translated versions, as well as all other translations, are on the specific book pages. I am particularity excited about the Korean translation, as this is my first book in Korean, bringing the number of languages that my books have been translated into to 16. Here is a full list of all the translations (that I am aware of).

I've discussed SQL injection attacks several times on this blog. But, judging by how many sites fall victim to these attacks, it's one of those topics that we can never discuss enough. Which is why I want to point out a new Adobe Developer Center article by Adobe Enterprise Developer Support Consultant Ryan Wagener entitled Secure your ColdFusion application against SQL injection attacks.

My Sams Teach Yourself SQL In 10 Minutes and Sams Teach Yourself Regular Expressions In 10 Minutes have both been published in Chinese by Turing Book. Links are on the books pages. And here is a full list of all book translations.

It's been two and a half years since I published my MySQL Crash Course (a MySQL specific adaptation of my Sams Teach Yourself SQL in 10 Minutes), and during this time the book has proven to be incredibly popular, especially with MySQL beginners. And today I was informed that MySQL Crash Course is now in its seventh printing! Seven printings in two and a half years, I think that's my new personal record. (BTW, it's the same edition, so no need to buy another copy if you have one already).

George Poulose's Query Tools have long been a core part of my SQL tools and utilities collection. And George just let me know that he has released new Windows 64bit versions of his ODBC and ODO tools.

SQLite is a vital core component of newly released AIR, providing a local SQL data store for your desktop AIR applications. Dave McAllister has announced that Adobe has joined the SQLite Consortium, supporting the continued growth and improvements in SQLite.

Amazon.com has created a feature page highlighting the 3 new ColdFusion Web Application Construction Kit books (as well as SQL books that may be of interest to ColdFusion developers).

The Microsoft bookstore (on the Microsoft campus in Redmond, WA) has only sold Microsoft Press books to date. But, starting next week they will be selling select books published by other publishers. And apparently my Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes is one that they have selected to sell. So, any of you working for (or visiting) Microsoft, feel free to pick up a copy! :-)

MySQL has released a beta of a new version of their Java driver. As per this post, MySQL Connector/J 5.1.2 is a Type-IV pure-Java JDBC driver that is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon alpha release. And this new driver contains code that automatically detects ColdFusion and then adjusts settings to optimize performance for ColdFusion use. Thank to Tom Jordahl for bringing this one to my attention.

Pinalkumar Dave is a DBA with extensive SQL Server (and ColdFusion) experience. I just stumbled upon his blog SQL Authority (via a link in a comment on my own blog) and am more than impressed by some of his SQL Server related posts. If you use SQL Server, then this is one blog you should add to your regular reading list.

After lots of rather painful delays, my new book Sams Teach Yourself SQL Server T-SQL in 10 Minutes is ready to ship. This book (which is the SQL Server version of my MySQL Crash Course) is based on my best-selling Sams Teach Yourself SQL in 10 Minutes and goes in to far more detail than the generic SQL book, and even includes coverage of new SQL Server 2005 functionality. Details, and a chapter listing, can be found on the book page.

Like many DBMSs, Apache Derby (included with ColdFusion 8) supports identity fields - fields that auto-increment each time a row is add. These are commonly used for primary key values, as the DBMS itself ensures that these values are unique and never reused. One problem with identity fields is that sometimes you may need to insert a row providing an explicit value to be used, essentially overriding identity functionality. Some DBMSs (like SQL Server) allow you to turn off identity processing with an explicit directive while a row is inserted and then turn it on again, and numbering automatically continues from the new highest value. Other DBMSs (like MySQL) allow you to simply insert values with specific values and, if present, these are used instead of auto generated values. Derby also supports identity fields. These can be defined as ALWAYS in which case Derby always generates the value (which can never be manually specified), or BY DEFAULT in which case identity fields are generated only if an explicit value is not provided. It's a rather nice implementation. But, it does not work as you'd expect. You can indeed specify an explicit value if BY DEFAULT is used, and your value will be used. But Derby does not seem to pay attention to explicitly provided values and does not update the internal counters accordingly, so when you next insert a row without an explicit value it may generate the exact same value as the one you specified. And if that column is a primary key, well, obviously the second INSERT is going to fail. There is a workaround. When the table is created you may specify an optional START WITH value. So, if you need to load the table with 25 rows you can set START WITH to 26, and that will be the starting point for generated identity values. I guess you could also START WITH some really high number, and reserve the lower values for when you needed to explicitly provide a value. Still, this is a hack, and it makes BY DEFAULT rather useless, which is a shame.

When performing full-text searches you usually want not just results, but a ranking indicating how close a match is to what you are looking for. In SQL Server 2005, ranks are accessed via ranking functions - FULLTEXT searches are ranked using function FULLTEXTTABLE() and CONTAINS searches are ranked using function CONTAINSTABLE(). Both of these functions are used the same way, and both accept search patterns, the same search patterns supported by the FULLTEXT and CONTAINS predicates themselves. Here is an example: SELECT f.rank, note_id, note_text FROM productnotes, FREETEXTTABLE(productnotes, note_text, 'rabbit food') f WHERE productnotes.note_id=f.[key] ORDER BY rank DESC; This example performs a FREETEXT type search. Instead of filtering using the WHERE clause, the FREETEXTTABLE() function is used and given a search pattern instructing the full-text engine to match any rows that contain words meaning rabbit and food. FREETEXTTABLE() returns a table which is given an alias of "f" (so as to be able to refer to it in column selection and the join), this table contains a column named "key" which will contain the primary key value of the table that was indexed (productnotes in this example), and "rank" which is the rank value assigned. And finally, results here are sorted by rank descending, as the higher the rank the greater the match. It is also possible to assign weight values to search patterns and words. The rankings assigned in the example used here assumed that all words were equally important and relevant. If this is not the case, and some words are more important than others, then the ISABOUT() function can be used to assign relative weights, and the full-text search engine will then use these values when determining rankings.

SQL Server 2005 supports two forms of full-text search, FREETEXT and CONTAINS. CONTAINS is used to search for rows that contain words, phrases, partial phrases, words with the same stem, proximity searches, synonyms (using a thesaurus lookup), and more. Here is a simple example: SELECT note_id, note_text FROM productnotes WHERE CONTAINS(note_text, 'handsaw'); WHERE CONTAINS(note_text, 'handsaw') means find the word handsaw in column note_text. CONTAINS also supports the use of wildcards: SELECT note_id, note_text FROM productnotes WHERE CONTAINS(note_text, '"anvil*"'); '"anvil*"' means match any word that starts with anvil. Note that unlike LIKE, full-text searching uses * as the wildcard character (instead of %). Wildcards may be used at the beginning or end of a string. Also, when passing simple text to CONTAINS then that text is enclosed within single quotes. When passing wildcards each search phrase must be enclosed within double quotes inside those outer single quotes. Failing to do this will likely cause your searches to return no matches. CONTAINS also supports Boolean operators AND, OR, and NOT. Here are a couple of examples: SELECT note_id, note_text FROM productnotes WHERE CONTAINS(note_text, 'safe AND handsaw'); SELECT note_id, note_text FROM productnotes WHERE CONTAINS(note_text, 'rabbit AND NOT food'); When searching through extremely long text there is a greater likelihood of matches being found if search terms are near each other in the saved data. A simple AND search matches terms anywhere in the text, but NEAR can be used to instruct the full-text search engine to only match terms when they are close together. Here is an example: SELECT note_id, note_text FROM productnotes WHERE CONTAINS(note_text, 'detonate NEAR quickly'); 'detonate NEAR quickly' means match only rows that contain the words detonate and quickly near each other. Sometimes you may want to match a word that is part of the same family (based on the same stem). For example, if you were searching for "life" you'd also want to match "lives". Obviously, a wildcard of life* could not help here, and using li* would likely match too many false positives. This is where inflectional matching helps. Here is an example: SELECT note_id, note_text FROM productnotes WHERE CONTAINS(note_text, 'FORMSOF(INFLECTIONAL, life)'); 'FORMSOF(INFLECTIONAL, life)' instructs the full-text engine to look for any words that share the same stem as the specified word, in this case "life". FORMSOF() also supports THESAURUS searches, where words can match synonyms. To use this functionality you must first populate an XML thesaurus file with words and their synonyms.

FREETEXT provides a simple mechanism by which to perform SQL Server 2005 full-text searches, matching by meaning as opposed to exact text match. Here is a simple example: SELECT * FROM my_table WHERE FREETEXT(column1, 'rabbit food'); FREETEXT(column1, 'rabbit food') means perform a FREETEXT lookup on column column1 looking for anything that could mean rabbit food (but not necessarily those two exact words, and not necessarily as a phrase). You can also search across all columns indexed for full-text search by using FREETEXT(*, 'search text'). If double quotes surround a search term then that exact phrase is matched, not the meaning.