PHP + MSSSQL issues

PHP + MSSQL unicode issues...

Last night, I was tinkering with using MSSQL with PHP.
All was going great.. created my database tables, created the stored procedures that were required for the project etc.

Now, the time to test out these stored procs was here. Slap a little php code together to connect to the database, and execute the stored procedure, when I was greeted with the following error message:

 Warning: mssql_execute(): message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16)  

Well.. this wasnt what I expected to see!

A bit of googling later, and the overwhelming suggestion was to cast my NTEXT fields as TEXT.
There's just one problem with this. None of my fields were NTEXT. (They were all NVARCHAR)

I gave it a whirl, modified my stored procs to cast to text, and retried. And the same error.Back to the drawing board. (in this case, more Googling).

A few more posts read later, FreeTDS 4.2 doesnt support unicode data. V8.0 does. So, lets change the version. Edit your freetds.conf file.. mine was located in

 /etc/freetds.conf  

I SFTP'ed the file down to my machine, and opened in text editor.

Mine looked a little like this:

 #  $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $  
 #  
 # This file is installed by FreeTDS if no file by the same   
 # name is found in the installation directory.   
 #  
 # For information about the layout of this file and its settings,   
 # see the freetds.conf manpage "man freetds.conf".   
 # Global settings are overridden by those in a database  
 # server specific section  
 [global]  
     # TDS protocol version  
 ;     tds version = 4.2  

Ah-ha! 4.2! - lets change that bad boy to 8.0.

 ;     tds version = 8.0  

Re-upload, and refresh my page...
..
..
And, still the same.

Thats when I noticed the semi-colon at the start. Now, me not being particularly up on linux, or tinkering with linux config files, I wasnt sure what the semi-colon meant.
As things turn out, its a comment.. so all text after it is ignored.  I knew that hashes (#) were comments, but didnt know about semi-colons. So what's the difference? Comments starting with # are a proper comment, and those starting with a ; character, indicate that this is a default value for an attribute.

Ah-ha! so, this is saying that the default value for the 'tds version' is 4.2.. I don't want to set a comment to show its default value, I want to set it.. simply remove the semi-colon, save, and re-upload.

 #  $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $  
 #  
 # This file is installed by FreeTDS if no file by the same   
 # name is found in the installation directory.   
 #  
 # For information about the layout of this file and its settings,   
 # see the freetds.conf manpage "man freetds.conf".   
 # Global settings are overridden by those in a database  
 # server specific section  
 [global]  
     # TDS protocol version  
      tds version = 8.0  

Refresh your page, and BINGO! proper text coming through from your stored procs :)

A few Google Maps tools / handy websites

I've been working with Google Maps a fair bit recently, and during my researching / development, I've come across a few handy websites.

http://www.birdtheme.org/useful/v3tool.html

Useful site for plotting points on a map. I've used this for generating "custom-routes", that do not stick to streets/paths

http://gmaps-samples-v3.googlecode.com/svn/trunk/styledmaps/wizard/index.html

This is very useful site for styling the actual maps. from Google, this allows you to hide/show elements, and change colours of just about everything. Once you are done, you can click the 'show JSON' button to get a json object of your styles that you can import into your project.

http://www.cycloloco.com/shadowmaker/shadowmaker.htm

Handy site for generating shadows for custom placemarker images. Upload your image, and it will generate a shadow image. The second handy part, is that it will give you the code to place into your page for your marker, shadow et al.

Let me know if you discover any more!

Extending Google Maps' InfoWindow

Google Maps' InfoWindow...

... is good, in that you don't really need to do any extra work to get a popup to appear on a map.

But, What happens when you want to customize it? - sure, you can put whatever content you like inside the infowindow, but your stuck with the standard white box with trail arrow.. you know the one..



yeah.. that one!

I came across a very handy plugin the other day, the InfoBox plugin.

This plugin replaces the default InfoWindow object in google maps, but crucially, allows you to specify the complete HTML that makes up your popup window.

To use the infobox plug, download a copy of the source, and reference in your page:

 <script src="/path/to/infobox.js" type="text/javascript"></script>  

Then, in your code, create an instance of this object:

 var ib = new InfoBox();  

You can then call that in place of the regular infoWindow.open()

 ib.open(theMap, marker);  

however, the power of this control comes with the options. Have a check over the API Reference for the full details.

with the InfoBox options, you can

Here's a snapshot of the example from the InfoBox site:

     var myOptions = {  
          content: boxText 
         ,boxClass : 'infoBoxClass' 
         ,disableAutoPan: false  
         ,maxWidth: 0  
         ,pixelOffset: new google.maps.Size(-140, 0)  
         ,zIndex: null  
         ,boxStyle: {   
          background: "url('tipbox.gif') no-repeat"  
          ,opacity: 0.75  
          ,width: "280px"  
          }  
         ,closeBoxMargin: "10px 2px 2px 2px"  
         ,closeBoxURL: "http://www.google.com/intl/en_us/mapfiles/close.gif"  
         ,infoBoxClearance: new google.maps.Size(1, 1)  
         ,isHidden: false  
         ,pane: "floatPane"  
         ,enableEventPropagation: false  
     };  

The main ones you want to look at are the 'content', and infoBoxClearence.

the "content" option specifies the actual HTML content that will be displayed in the popup.
Define your HTML markup inside your script, and then point it to the "content" element.

Here, I've taken the example from the API examples page:

     var marker = new google.maps.Marker({  
      map: theMap,  
      draggable: true,  
      position: new google.maps.LatLng(49.47216, -123.76307),  
      visible: true  
     });  
     var boxText = document.createElement("div");  
     boxText.style.cssText = "border: 1px solid black; margin-top: 8px; background: yellow; padding: 5px;";  
     boxText.innerHTML = "City Hall, Sechelt<br>British Columbia<br>Canada";  
     var myOptions = {  
          content: boxText  
         ,disableAutoPan: false  
         ,maxWidth: 0  
         ,pixelOffset: new google.maps.Size(-140, 0)  
         ,zIndex: null  
         ,boxStyle: {   
          background: "url('tipbox.gif') no-repeat"  
          ,opacity: 0.75  
          ,width: "280px"  
          }  
         ,closeBoxMargin: "10px 2px 2px 2px"  
         ,closeBoxURL: "http://www.google.com/intl/en_us/mapfiles/close.gif"  
         ,infoBoxClearance: new google.maps.Size(1, 1)  
         ,isHidden: false  
         ,pane: "floatPane"  
         ,enableEventPropagation: false  
     };  
     var ib = new InfoBox(myOptions);  
     ib.open(theMap, marker);  

 You can see that 'boxText' variable contains HTML elements. This is what is output to your infobox.
One thing to bear in mind, is that the HTML you provide here, will be wrapped inside of a <div> element.
If you do not specify a 'boxClass' option, then the default will be ".infoBox". Of course, specify your own if you like.

This means that you can style both the outter element, and the inner content as you please.

So, with some custom CSS, you can make rather fetching (or at least, different to the standard infowindow)

Footnote: 

Writing out your markup directly in javascript like above, can be tedious. Especially if you want to put a large amount of content into said infobox.

A better alternative to this is to use Templating. One plugin we've used recently is Handlebars. Go check it out. It makes working with HTML inside of javascript/jquery much much easier