|
Mysql left and right functions |
||||||||
2 name,
3 RIGHT(url, 1) as slash
4 from table
5 where RIGHT(url, 1) != "/"
The RIGHT() function is similar, but it starts from the last character on the right, counting left to encapsulate the last seven characters.
In the SQL statement below, areaCode is reused to order the results set. To reformat the telephone number, it will be necessary to use the SUBSTRING() function.
2 RIGHT(telephone, 7) AS telNo
3 FROM contacts
4 ORDER BY areaCode;
5
6 SELECT CONCAT('(', LEFT(telephone, 3), ') ',
7 SUBSTRING(telephone, 4, 3), '-',
8 MID(telephone, 7)) AS 'Telephone Number'
9 FROM contacts
10 ORDER BY LEFT(telephone, 3);
|
Coldfusion Server connectiong to SQL Server Express 2005 |
||||||||
I've been using SQL Server Express for quite a while noa, and still prefer it to almost every other database application. I was rebuilding a development environment the other day, and discovered that I was using an ODBC "trusted connection" to connect to a SQL Server instance in Coldfusion Server. Why was this I thought?
It turns out that I'd had some problems getting Coldfusion to connect SQL Server datasource connection, so I'd used a custom ODBC connection. Looking into the connection error and having a dig around I found out a few things its worth checking when installing SQL Server.
1. First thing is to enable TCP/IP connectivity, as its not on by default. Go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Under SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS, click on TCP/IP, and click enable. Then restart the service, which you can do from the Configuration Manager under the SQL Server 2005 Services area, or in windows services.
2. In my experience SQL Server always used to use port 1433 by default after installation. For some reason it hadn't used this port, it was using 1523. I was quite puzzled about this as I just used accepted the installation defaults. The port is a crucial setting when you're setting up your datasources for Coldfusion, otherwise CF wont even find the datasource, let alone attempt to connect. To check your port number, go to the SQL Server Configuration Manager, go to Network Configuration/Protocols again, right click on TCP/IP, choose Properties, then click on the IP Addresses tab. Look under IPAll in the TCP Dynamic Ports section. I personally wouldn't change this as I'm not really sure why SQL Server is using a different port now. I just used it in my Coldfusion datasource setup, and it connected fine.
|
Using LIMIT in mySql Update script |
||||||||
I've recently switched to MySql, as my present employer uses it.
Its taken a bit of getting used to, but I'm coming around to it.
One piece of functionality we have is a 'queue-ing' system. There is an image queue that users have to go through to 'vet' photos. In this instance of work I needed to update a collection of records. It didn't matter which ones, just enough to fill the queue.
2SET active = 0
3LIMIT 500;
So rather than using a where clause it seems that you can use a mySql LIMIT value, to just return a set number of records.
Simple but effective.
|
MySQL does not support conditional if/else scripts unless its compiled |
||||||||
I've recently changed companies, and as a consequence changed some toolsets. One of these is a switch from MS SQL to MY SQL. Its an interesting learning curve, and I'm trying to stay open minded, but I really did quite like MS SQL....
I have a series of scripts that I have built over the years, they perform standard functions like creating tables, alters creating stored procs etc... (will post soon!). They are all self checking, and output nice processing messages to tell the user whats going on.
So I embarked on a mission to convert these to MY SQL. My first stumbling block was not referencing the master table form MS SQL, as it doesn't exist in MY SQL, but handily there is something similar.
2FROM information_schema.tables
3WHERE table_schema = DBName
4AND table_name = tableName
The problem then comes with evaluating this result into an IF statement. It turns out that MY SQL cannot process IF-ELSE conditionals unless it is within a compiled script, like a stored proceedure.
Bit of a show stopper there! So now I am trying to find another way to check for the existence of database objects.
