So, after two months of trying I have finally got it to work. I now have an SQL server and a web server. The web server runs PHP5 and most importantly, can connect to the SQL server, run SQl and return the results. This is a major breakthrough. It is what will actually allow me to work on the code of my project when the time comes. You see, although I have put a lot of time into getting this setup to work and talked about at great length how and what I am doing, it isn’t really part of my project any further than I need this to work to progress. My project is all about the system I build on top of this connection, not the connection itself.
The final piece of the puzzle was to get a working user account on the database server with the correct permissions. For this I did enlist the help of Nick (one of my housemates). I had never set-up an SQL account before, clearly i was just missing a step as he did almost what i did, just added a few extra bits. With a user account created all my research before in getting these two technologies to talk finally paid off, from there my code and server set-up just worked. No more fiddling about, just simple SQL that can now be run.
Although not really listed anywhere but here, this is a major milestone of the project for me. For now this means I can get back to concentrating on the paperwork before the big set of application builds.
OK, so last you saw Slackware was driving me insane with installing but not quite. Having tried to re-install several times the solution ended up being fairly simple. Not to install Slackware. Instead, I went and downloaded Ubuntu Server.
Fortunately for me this installed first time without any trouble. Unfortunately for me, I didn’t realise this version came without X (which is what allows me to use a mouse and point and click instead of typing commands). Thankfully for me one of my housemates isn’t too bad with such things and between us we managed to get Apache installed with PHP (that actually works!) and share the web directory through Samba so I can just upload code to it from my machine.
So, looks like we are getting there. The Hardware and software side of my system are complete, now all i require is an application to run it on. As my project assumes I can just get MSSQL and PHP to just talk I am working on this bit now. I have a test database setup and some code which should work. But of course it doesn’t, but to be fair I haven’t really looked into that yet.
For the meantime I have been more interested in the other side of my project which I’m less keen on. This is the bits of paper side. What I haven’t mentioned so far here is that throughout all of this part I have been working on paperwork, research and meetings with my tutor and second reader.
I handed in a big document which was the beginning of my research into database system and application languages. I then had a meeting with my tutor and second reader to confirm my project is not mad and that i can continue.
Next I am working on research into lots of different current CMS’s and hopefully writing a report on all of that to show my tutor on Tuesday morning. This should then give me a basic set of features i should be putting into my CMS so it actually constitutes a CMS.
So, all fun and games, certainly enough work to be getting on with.
So, server update. I was given another machine and as of last time my plan was to split my application and my database setup to a much more realistic setup.
Under the new setup the old server is just for SQL and as such I have removed anything which would get in the way of this. Now this leave me the new machine to setup as a web-server running PHP. Given in my aim I said I am looking at smaller businesses which need to do the most with the least I planned to make this server Linux based and use the Apache web-server. All of this is free and in theory should be pretty simple to setup.
So first I took the machine apart and stripped out all the hardware it didn’t need. and give me a fairly simple shell. After discovering one of the hard-disk was dead and removing it i got the setup running. Having run all the congif and started the install i started to get packages failing to install. Not pointless ones either, things like glib and glibc. At current it looks like the current install is beyond repair so will require another re-install. Getting closer , it is just taking more time than a) it should and ) I wanted it to.
OK, so it is official, PHP hates me.
I have MS Server 2003, Apache and MS SQL Server all working just fine. The missing link in this is PHP. Try as I might, I can not get this to work with Apache at current. This appears to be down to be doing the fairly non standard setup of Apache and PHP on an Microsoft server. I have had PHP on IIS working before but this is also a fairly common task.
Thanks to a friend of mine I may however have a solution that isn’t really a solution but yet makes my system better.
I have been offered a spare machine. I fairly old and poor machine, but a machine that works non the less. My new plan is to install a Linux server OS on this machine with Apache and PHP on it. Then get that to connect to my SQL server on my own network through a 100Mbit switch. This way I am actually creating a much more realistic setup for my project. I can have my SQL server separate to my web server and then a separate client attached as well.
With just these 3 machines attached i still keep my test clear of any network traffic which would conflict with the results and at the same time give myself a much closer to production testing environment.
So, I will call PHP hating me a blessing in disguise for now. That is until i can’t get my application server and my database server to talk again, at that point i may cry…
OK, so it has been a long time since I have updated this. This doesn’t mean that I haven’t been doing anything, it just means I haven’t had the time to update or haven’t felt what I was doing worth an update.
So last time I left this blog I was struggling with getting PHP to be able to run queries on MSSQL at the university. This turned out to be considerably more complicated than I first though.
The first issue I came across was that the University was running PHP on an IIS web-server. I have never really used PHP on IIS before and there are a couple of small but important differences. Thankfully these didn’t hold me up for too long. When my first few tests of connection strings didn’t work I began to research this on the web to see what methods worked for others. Having tried many permutations and combinations of methods I found online I eventually found some articles suggesting that this was due to a driver issue.
http://coffeeonthekeyboard.com/connecting-php-iis-6-and-sql-server-2005-129/
Unfortunately the technicians for the CMIS computer suit were not too interested in installing these as they had a known setup.
With a little more research I then found an article suggesting that the reason the connection wasn’t working was because of a change to default network settings for SQL Server 2005 over 2000.
http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadID=227248&messageID=2266046
Clearly, again the computing staff were not keen on changing their setup, but did agree to help me debug this. I wrote a test script which would confirm the connection when run and the technicians build two virtual servers for testing. After making the suggested changes still it didn’t work.
I left this with the technician for a week or so to see if he could come up with a solution. Unfortunately at current there still isn’t one.
Thankfully this doesn’t spell the end for my research, like any good student I had a backup plan. This involves taking an old dev machine of my own and installing Server 2003, a web server and SQL server 2005 all from the universities MSDN subscription. As of current I have the Server 2003 installed, Apache installed, PHP installed but not yet fully working and SQL server installed and working.
Once I get PHP up and running i hope to be able to go back to connection strings. As they are now on the same server and I can tweak the setup all I like without the university’s restrictions this should progress quickly and I hope to have a working connection by the end of the week.
Of course, connection issues are not the old thing this solution solves. You see, my project relies heavily on me being able to accurately benchmark my system to ensure I am getting the performance gains I am after. If I was to be running this on the university setup I would have an unknown load on the server at any one time. This is because the university web server is connected to the Internet. As such I would never be able to have the controlled environment I require for benchmarking.
So, first week of Uni over and as such, first week of my project over. It has been a fairly successful week in some ways, although mildly frustrating in others.
On Monday morning (AKA, first day back) I went and spoke to Brian Spencer to make my pitch to him. He was fairly impressed and most importantly, interested in my idea. He has said he will take it on and I just need to send him a project summary and he will confirm. I have written most of the summary now, just playing with some of the details.
So, I would like to try and explain what my project actually is so that later comments should mean more to people.
CMS’s (Content Management Systems) are some form of web based application which allows pages to be generated from database content. So normally you see something like index.php?id=42 in a URL. This means the variable “42″ is being passed into the script. Some checks are performed on it to make sure it is a valid number etc and then it collects the content (title, page details, place in the menu etc) from the database, and using that constructs the page using some form of template and gives it back to you.
So this means the page you are viewing doesn’t actually exist as a file, but it is generated from database content. The real beauty of this system is that means you can write an admin interface which can edit this content in the database and as such, non technical people can update pages.
This is all very well and normally such a system does all the logic (so, checking if the ID is a number, not text, dealing with errors like that ID that was requested not being in the database). This means you have a database that is just used for storing data and an application in something like C# or PHP that does all the “thought”.
Now modern database systems have basically become programming languages in their own right. They have a log of powerful functionality in them.
My project aims to research moving this balance of power and functionality from the application to the database and getting the database system itself to do some of the “thought” and not just mindlessly passing data back.
This is a research project mainly because I have some idea of how much this can be done and how to do it, but really, I’m not aware of this being done before and everyone I have talked to about it who has any idea what I’m talking about agrees this is an interesting concept and I would be nice to see how far it can be pushed, but really, we have no idea.
For now thought I just need to start with the basics. I need to have PHP (my chosen language) connecting and talking to MSSQL (my chosen database system) before I can go much further. This is proving more challenging than first thought. Watch this space….
This is the new section of my site dedicated to my University work. It is actually mainly for the purpose of a log for my final year project, but it may also serve as an out for other University work related thoughts. As a bit of a sum up, my project will be an investigation into moving the functionality of a CMS from within the front end scripting language (such as PHP or C#) into the database engine itself taking advantage of the new levels of power and control in modern query processing engines and T-SQL.
This is an almost unheard of subject as far as I know and as such I hope that at the very least it will provide some original work and an insight into the hidden power of T-SQL and database servers in general that is often kept locked away.
I plan to be focusing on MSSQL as it is the system I am most familiar with of the 3 major database engines (DB2 and Oracle being the other two).