Archive for the 'dbms' Category

SQL – XML in SQL Server 2000

September 13, 2006

I managed to try out the sp_makewebtask T-SQL stored procedure for exporting the result of an SQL query to XML file that I mentioned in an earlier post. I’m happy to say that it works a treat! I’m quite excited about it and would love to know if there is something similar for mysql now that stored procedures are supported (mysql 5).

The conversion is as simple as creating a .tpl file with the following (root can either be removed or changed to represent a doc type definition, much more useful then just root!):

<root>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</root>

And then calling the stored procedurce in the query analyser, or however way you wish to call it.

sp_makewebtask @outputfile = ‘c:\temp\myxmlfile.xml’,
@query = ‘select * from sysobjects for xml auto’,
@templatefile = ‘c:\temp\template.tpl’

The result is an XML file created in c:\temp called myxmlfile.xml. I tried a few different queries such as: select * from customer where custID=1… which created a node called customer with the customer details as attributes.

<root>

<Customers custID=”1″ custFirstName=”John” custLastName=”Smith” custAddress=”123 Queen St”/>

</root>

I see this as being useful for sites such as del.icio.us where a user may want to export their url list to a file (del.icio.us actually offer this service, just using it as an example).

Advertisements

DBMS for basic application data storage

August 19, 2006

An application I am making requires a basic DBMS and it would be ideal if I could have this as a standalone file without having to install mySQL or another service based DBMS on a users computer when it is not necessary. Microsoft Access files definately do the job but I’d like an alternative. All the file needs to do is store the SQL data and provide a DBMS engine. I don’t need any of the visual functions (form creation, etc).

I’ll proberly end up going with OO.o Base 2 due to the simplicity. The other alternatives include: looking for or implementing an XML based storage solution, using an object database to directly save objects, using an API based database engine or as mentioned installing mySQL on the client.

For quick and easy implementation it is much easier to use a datafile, ODBC, and SQL. I’ll install OO.o Base 2 and see what I come up with, still have assignments that I need to get done at the moment though!

WordPress install

July 9, 2006

Well I’m definately on my way to a new theme, I have installed all the development tools and have them working together (apache, mysql, php and wordpress). My install went something like this:

Apache 2.2.x didnt like php4 and was not loading the module dll, I understand this is a common problem and came across some solutions based on php5. I wanted to run php4 so I installed Apache 2.0.x.

I did come across XAMPP, a cross platform easy to install Apache distribution containing MySQL, PHP and Perl. Looked like the perfect solution but I’d like to learn how to do it manually.

I Changed the default folder public_html by editing DocumentRoot pram and edited the various other settings to work in with the directory structure I created (%roo%/www/…)

I also installed mysql from a zip archive and changed the ini file to point to my non default install location and set my windows classpath. After setup I gave the root user a password, created a user account and removed the anonymous accounts.

I extracted the wordpress install to a folder named blog in my public_html directory

I came accross a small error in setting up a user for the wp-config file. MySQL 5 uses a new password encryption format that is not supported currently by wordpress this is fixed by starting up the mysql server with the –old-passwords pram:

mysqld –console –old-passwords (I don’t have mysql installed as a service)

Or by creating a new user in the following way:

GRANT ALL ON *.* to ‘user’@’localhost’;
SET PASSWORD FOR ‘user’@’localhost’=OLD_PASSWORD(‘password’);

Note the OLD_PASSWORD function.

From the website:

[UPDATE: MySQL 4.1.7 uses a new password encryption system that is incompatible with prior methods. If you want to get WordPress working with a 4.1.7 database, you need to make sure your user password is set as an old-style password (password-old instead of password, if you’re using mysqladmin). — Nabil] Note : The above are actual user comments, and may be subjective in their content. It is hoped that it will be of help to some users who face the specific problems that are addressed by this question..

Once wordpress could work with my passwords the installation was extremely straight forward and my blog was up in no time! Now time to find/create a theme!

blog

Link dump and SPAM

June 21, 2006

I'm still finishing assignments and have been really flat out so this post  is just a link dump. I had a rather large collection of bookmarks in FireFox which was getting very hard to manage as well as many links going un-noticed (I'd forgotten about them). I decided to organise them by deleting them from FireFox and uploading them to my del.icio.us account. The result is much better managed bookmarks that I can access from anywhere as well as share with anyone interested. I am a huge supporter of social networking. (excluding myspace :P)

Links of interest:

XSPF: XML Shareable Playlist Format: Home

Nvu – The Complete Web Authoring System for Linux, Macintosh and Windows

APPLE VS MICROSOFT (WINDOWS VS MACINTOSH)

Dia – Wikipedia, the free encyclopedia

These are a select few of the links on my del.icio.us account. If you are interested in software development I suggest having a look as there are lots of good resources not in this list. Alternatively you can keep visiting this blog as I will be doing more link dumps as welll as a few write ups.

Lately I have been getting a large amount of spam comments which is very annoying, I'm going to have to start validating comments I think. 

McKoi SQL Database for Java

June 13, 2006

Java programmers may be interested in this, from the website

Mckoi SQL Database is an SQL (Structured Query Language) Database management system written for the JavaTM platform. Mckoi SQL Database is optimized to run as a client/server database server for multiple clients, however it can also be embedded in an application as a stand-alone database. It is highly multi-threaded and features an extendable object-oriented engine. 

McKoi is released under the GNU General Public License and supports the SQL standard to a comparable level of other SQL database management systems available (see McKoi SQL Syntax). McKoi supports similar data-types to mySQL but with the addition of JAVA_OBJECT , allowing a serializable objects in Java to be stored.

I couldn't find any mention on if McKoi supports referential integrity which the default database engine in mSQL does not. Not really that big a deal if you design your appication to implement these features itself.

This will be the first thing I will be playing with in the holidays as it looks very useful!