Fog Creek Software
Discussion Board




Knowledge Base
Documentation
Terry's Tips
Darren's Tips

DBScript Utility

I wrote a program for adding the results of database queries to .html files. It processes the files before they are published so that the .html is still static.  It's available for free on my site:

http://www.greenwave-solutions.com/HTMLDBScriptForCityDesk.html

It's designed to work with CityDesk's "Before Copy" publishing feature in the Location properties.

If you need to add queries to your pages, but want to process them on the client and publish static pages, this might be for you.

And since the .cty files are Access databases, it's possible to query them and add information to pages as well.  This could be used for linking .cty files or for querying in a way not available in CityScript.

Lou Franco
Thursday, February 19, 2004

Lou, thank you for writing your little utility!

To my shock and amazement, my test worked the first time with no problems.

Using your instructions and copy-pasting your sample code, I got it to work the first time. **Thank you!**

I've been asking for this sort of thing for a while, so it's incumbent upon me to let you know that I appreciate your efforts, and for distributing your utility for free. Very nice!

I strongly encourage my fellow CityDesk brethern to try this program. Then post on this forum nice things about this wonderful little utility, profusely thank the author, and post constructive criticism.

Lou, may I suggest that you create a .cty file that includes your utility, a sample page of your script, a sample .mdb file, and your help instructions. Then upload it to the Fogcreek template collection.


Does your utility work on flat files only? Or can I create a relational db on Access?

Can I SELECT subsets of my db using WHERE?
For example, {* foreach rec in "ContactsDB:select * from Contacts where ContactsDB:category="PERSONAL" *}

What syntax do I use for the WHERE parameter?

Again, THANK YOU :-)

-Bob

Bob Bloom
Friday, February 20, 2004

Thanks for the nice comments.

You may use any query that your database supports -- I just pass it to them through ODBC.  For Access, the syntax is:

select * from contacts where name='nametofind'

Some SQL's are slightly different -- if you need to put a double-quote in the query then download the latest version (at least v1.0 -- none of the beta versions have this feature)

And since I pass it to the DB, you can use full relational syntax (all tables must be in the same DSN) -- like:

select * from contacts, addresses where addresses.cid=contacts.id

When you want to access a field in the loop, I don't handle accessing fields using . syntax yet (just thought of that while reading the comment). There are 2 ways to deal with that:

1) use a view or query in your database to flatten the relation and select from that

2) use the renaming conventions of the SQL of your database to alias the fields.  For instance in Access, you can do this:

SELECT Contacts.id AS id
FROM Contacts WHERE Contacts.id = Children.cid

and then when you access the field, you can use id instead of Contacts.id (which I can't handle yet)

If you want to go across Databases, you can use the features of the database to bring in external queries and then expose a View or query.  In Access you can use the Link Tables feature and then relate and expose views.

I will certainly put up a sample soon. I have one that is part of my test suite.

Lou Franco
Friday, February 20, 2004

Sorry, last SQL should be:

SELECT Contacts.id AS id
FROM Contacts, Children WHERE Contacts.id = Children.cid

Lou Franco
Friday, February 20, 2004

And, also, if you need to use a double quote in a query, then double up the double quote.  e.g.:

"DSN:select * from thetable where field=""value"""

Lou Franco
Friday, February 20, 2004

Lou,

Yup, the select statement works with the ORDER BY parameter no problem.

I won't be doing anything fancy in Access. But you bring up an interesting possibility of using Access to do a lot of the back-end database stuff, and using CityDesk as a front-end html "loader".

I haven't tried to put html in my test mdb. But I will! Then I can have a custom db with the fields I need plus the {body} as well.

My question... I want a recipe db in Access. Each record represents a separate html page, with the actual html in (let me call it) the BODY field.

How do I create my separate CityDesk articles, one for each field?

Again, many thanks!
-Bob

Bob Bloom
Friday, February 20, 2004

Seems to me that CityDesk does this quite well.  I would keep the body fields in CD.  If you have other data that goes with recipe, then use one of the other fields (extra1, for instance) to keep an external key to the database.  Then you can build up the page by using {$.extra1$} in the where clause.

Alternatively, you can use one of the unique fields of an article (like absLink, but that changes if the article moves, so not definitely good).

------

If you want to keep everything in Access, then make a template called recipe that use one of the fields (like extra1) to keep a key to the record.  Then for each recipe, make an article and set the key in extra1.

-----

Another idea (which is a little harder), is to add fields to the appropriate tables in your .cty file and then grab them by using a cityScript variable as a key in the query.  I haven't looked at the cty schema, but there must be a way to get back to the record of the article from one of the variables.

-----

If you want to have the entire site generated from the db and not have to make articles, then this utility won't help -- it embeds queries in articles, it doesn't create articles.  There are tools that do something like that -- another forum topic mentions DBToHTML express, which can be found here.

http://www.xlinesoft.com/

Lou Franco
Friday, February 20, 2004

Lou,

Good suggestions.

I found this post in the forums, "Dynamically Creating Articles" http://discuss.fogcreek.com/CityDesk/default.asp?cmd=show&ixPost=9477&ixReplies=5  It really didn't say how to do it though.

There is no way to create articles dynamically. But what a powerful feature this would be.

I could store my entire site (almost!) in an mdb, with my html in memo fields.

Sometimes, a record represents a row in a table.

Sometimes, a record represents an article.

Currently, most of my articles in CD represent records.

There is probably a way to append records to the current .cty tables, but I'm not going there! Maybe another utility for you :-) 

What I may do is create my custom tables, and just have real long articles created by my ForEach statements. I'll build in bookmark hyperlinks with a table of contents for in-article navigation. This way I can update my records-in-a-table instead of creating article after article. If someone wants to print out that one recipe or event, oh well!

I don't want any pre or post processing in my mdb. I want my mdb to be a passive back-end. I want to keep it simple!

I'll probably end up using categories and sub-categories and using the WHERE clause a lot, to break up my long CD articles.

I'm now designing version 2 of my site, thanks to your little utility. The site itself may not look much different, but the underneath will be more elegant, and much easier to update.

-Bob

Bob Bloom
Friday, February 20, 2004

I think this is some sneaky way of releasing CD3, because this will absolutely push CityDesk to the next level of functionality. I haven't even had a chance to try it yet, but I can tell from this thread it is a great breakthrough for CD users. Thank you Lou!

Ron Lane
Saturday, February 21, 2004

Ron,

I don't think it's a sneaky way to release v3 because there are no dynamically created articles.

It was never that difficult to create a pathway to external db's in CityDesk, considering that Lou did it with a mere 44kb utility.

What is intriguing is Joel spotlights Lou's utility on the CityDesk home page. Obviously, Joel's given HTML DBScript his seal of approval, and is encouraging us to use it.

So... it's time for CityDesk to DYNAMICALLY CREATE ARTICLES from external db's.

Dynamically created articles is the logical progression.

It's a profound feature.

It would be possible for a .cty file to not have any content. Content would feed into the CityDesk templates, variables, and CityScript from somewhere else and out would pop an entire set of html pages for FTP'g.

In my eyes, this is the essence of CityDesk: Content management. Not content creation! The templates, variables, CityScript, and publishing features are compelling and powerful.

The article db in CityDesk is terribly constraining. I use articles to mimic records in a db. Now, I can use a real db and use real records! So now it's time to program dynamically created articles.

It'll take a bit of planning, programming and testing. I'll be happy to beta test.

A special kind of article will be required: an article that doesn't publish. An article that uses CityScript that quarterbacks the dynamic creation of articles from an external db.

And being dynamic, these new html pages need to be deleted! Each dynamically created article s/b created during publishing. Or should only new articles be modified? There will be details to figure out!

With Lou's utility, we've finally come to the essence of CityDesk as a profoundly powerful client-side content management system. That's why it's time for CityDesk to step up to Dynamically Created Articles from external databases.

-Bob

Bob Bloom
Saturday, February 21, 2004

Ok, I see what you mean Bob, I was thinking it was allowing us to put the queries into article templates. It is a step in the right direction and useful, just not what I first thought.

Ron Lane
Saturday, February 21, 2004

*  Recent Topics

*  Fog Creek Home