After complaining about SQL Server 2000 not using the back slash to escape quotes it looks like PostgreSQL is going to do the same thing, eventually. There’s a proposal out describing the potential quotes changes in PostgreSQL. It would make it easier to deal going from one database system to another, but visually I’ve never liked it. At least there will still be a way to use the back slash via E” strings.
We are purchasing some software at work that uses SQL Server 2000 as a back end. I’ve been bringing those systems up and relearning SQL Server since I haven’t look at it in more than five years. We’ll be importing some existing data for this software so I’ve started writing up a few scripts. I’m using PHP with ADOdb for database access. For the most part things have gone well, with some small expections/gotchas. Fortunately none of the problems I noticed were hard to fix, but they did seem a little odd sometimes. Here are some notes on my experiences with this process thus far:
In order to get PHP talk to SQL Server 2000 I’m using FreeTDS on a FreeBSD system. The first thing to note is that SQL Server 2000 uses 8.0 TDS. When building from FreeTDS from the ports collection be sure to override the default, which is 5.0. You’ll also need to enable msdblib in FreeTDS, so make sure that WITH_MSDBLIB is enabled during the build also. Then all that is needed for PHP4 to speak to SQL Server is the php4-mssql port.
In recent versions of FreeTDS they’ve started using a freetds.conf file to configure connections. There is an example in there for connecting to SQL Server 2000, here is what I ended up using:
[SQL2K]
host = xxx.xxx.xxx.xx
port = 1433
tds version = 8.0
; dump file = /var/log/freetds.log
; dump file append = yes
; debug level = 99
I had a few problems connecting initially, so it was helpful to run with debug on until I got things straightened out. In PHP you’d use SQL2K to connect instead of the host name or IP address.
Our user accounts are all managed by Active Directory, besides the default accounts in SQL Server 2000 all of the permissions are handled via Windows domain accounts. This means that all of your usernames have to be prefixed with the Windows domain. Something like EXAMPLE\username. If you run into problems check out the FreeTDS documentation about domain logins.
Note that quotes are escaped with quotes, not back slashes. I personally don’t care for this method, but it seems to be the only one that SQL Server 2000 supports. If you are going to use CSV to import data you need to pay attention to this. Although CSV is supported I ended up going to tab delimited instead. This is the default that SQL server expects and it made some of the quoting issues easier.
For the sake of consistency I’m using ADOdb to deal with database access in PHP. One neat feature I found was the ability to easily export a result set to CSV or tab delimited formats. This was exactly what I needed to do, so this seemed like a great solution. Unfortunately rs2tabout() tries to escape quotes, which SQL Server choked on. The quotes didn’t need to be escaped since tabs were being used as delimiters, so I poked at the code to see what was happening. All of the export functions derive from a base function with several options, so I was able to call that function directly and override the defaults. If you want data tab delimited with quote escaping turned off you can do that with:
print(_adodb_export($rs, "\t", " ", true, false, false));
or
$line = _adodb_export($rs, "\t", " ", false, false, false);
Importing lots of data with bcp is much easier than clicking through the import/export wizard. To make life easier you’ll need a format file for the table that your are importing data into. The easiest way I’ve found to generate the format file is to let bcp do it for you:
bcp testdb.dbo.tablename format tablename.dat -U username -P password -f tablename.fmt -c
Then importing is done like this:
bcp testdb.dbo.tablename in tablename.tsv -U username -P password -f tablename.fmt -e tablename.err
If bcp has any problems importing data will be logged in the tablename.err file. If it runs into enough errors the import will terminate, the default is ten. In the test table that I’ve been using has 98,000+ records in it. The process of retrieving the data and formating into tab separated values (tsv) takes about 110 seconds. The import with bcp takes less than 5 seconds. I like it when things go fast 
That pretty much covers it for now.
JeremY! made the announcement that Yahoo! was the mystery buyer of blo.gs. Jim has also posted his thoughts on the deal. I’m rather surprised by this, but I feel like I shouldn’t be.
As I’ve already hinted at earlier my boss (hi Jim!) has a new blog at http://sonicchicken.net/blog/. Welcome to the mad house
When I put my current layout I forgot to display a link for the TrackBack URL. After some more testing it looks like TrackBack and PingBack are both broken on my site. This came up because my boss is setting up a WordPress blog and wanted to try out PingBack and TrackBack and found that both of our blogs were failing. A little bit of trial and error indicates that WP-Gatekeeper seems to be causing the problem. I’m digging through the code to see how hard this is to fix. So if you been having problems with WordPress receiving TrackBack and PingBacks with an error message of “Sorry, posting has been closed for the time being.” then keep an eye of this spot, hopefully I’ll have a fix soon.
UPDATE 5:15pm 13 Jun 2005: I’ve come up with a simple patch to WP-Gatekeeper to fix this problem. I already emailed Eric Meyer (the author) about this so hopefully an update will be released soon. To fix this just add these lines to the gatekeeper_stand_guard() function just below user login check:
if(preg_match("/^(trackback|pingback)$/i", $commentdata['comment_type'])) {
return($commentdata);
}
While trying to figure out what was going on I came across Ping-o-Mation, which has tools for testing TrackBack and PingBacks. Nifty tool to have around.
UPDATE 1:30pm 14 Jun 2005: Eric has released WP-Gatekeeper 1.5-RC3 which includes this fix. In his announcement for this release Eric makes a a great point, WordPress needs to break out trackbacks and pingbacks from comments so that you can specifically set options for each. It would nice to be able to use WP-Gatekeeper to deal with comment spam and perhaps turn on full moderation for trackback/pingbacks.
Last week California State University, Sacramento (CSUS) sold their bookstore (including the computer store) to Follett. The official announcement uses the term “partner”, but from what I’ve heard the CSUS Foundation has sold it off in its entirety. The employees that I’ve talked to over there don’t seem too thrilled with the whole situation. It still isn’t clear how large of an impact this is going to have on the rest of the campus community.
I seem to remember hearing about the idea of one-time credit card numbers years ago (ah, here’s an article about AmEx doing this five years ago), but haven’t heard a whole lot lately. This seems like something that merits more discussion, because in the end it should be a fairly standard feature for most, if not all, credit cards. I was talking with someone at work about this and we came up with a couple of different ideas and issues that could eventually come out of the one-time credit card number idea.
First on the list is the length of credit card numbers. If this idea is going to really take off, then the size of the number probably needs to increase. This led to the idea of assigning a “person” and credit card number prefix (perhaps the first half of the number?) and then using the rest of the number for the one-timeness functionality. This could lead to using alphanumeric strings for credit cards instead of just numbers. I think you get the idea, if we make the space big enough there are a lot of possibilities.
These brings us to the next thought, why limit this to simply a one-time number (or string)? If I’m going on a three day trip to Disneyland it might be nice to have a single credit card number that I would only use on that trip. So I have a number (or string) generated and would be able to associate that number in Quicken or MS Money for my Disneyland trip. This could be done by requesting a number that would only be valid between a certain date and time range and optionally for a maximum number of transactions. This concept could also apply for online purchases by requesting a number that is good for only one transaction and for the next 15 minutes. If this was the only type of credit card you used it would make the traditional expiration date meaningless.
The Disneyland trip example runs into another snag though, in most cases you’d need to swipe card through a reader for transactions. So now we need a way to easily program these generated numbers in cards. If we wanted start based off of the current limitations, these programmable cards would have to the same form factor as the current cards, with a magnetic strip that is readable by todays systems. This is because not all transactions just swipe the card, think about those gas station systems that pull the whole card in. One way this could be done is to have disposable credit cards that could be easily programmed with your generated number. They would be available like phone cards, at pretty much every AM/PM or truck stop.
There are still other issues that would have to get sorted out, like would an average person be able to easily use the device to program these cards? Do the credit card companies really work fast enough for time based limits like 15 minutes to really work? Would we be able to successfully overhaul the credit card networks to make use of new technologies, such as alphanumeric strings and personal prefixes instead of numbers? Is it likely that we’ll ever be able to widely deploy new types of credit card systems in a decade or are we stuck with what we’ve got?
With confirmation that Apple with switching to Intel based systems from none other than Steve Jobs himself everyone is trying to figure out what this means. This includes Robert X. Cringely, who suggests that what this announcement really means is that Apple and Intel are going to merge. If this turns out to be true then Apple and Intel have way more guts than I gave them credit for.
Of course the whole idea behind a move like this would be to complete with Microsoft and their Windows dominated world. With the resources of Intel behind them Apple would be able to do even more with Mac OS X. It would certainly be easier to get other vendors (could you see Dell selling these things? I know, I laughed too the first time that thought came up) to sell Apple systems will Intel applying a little leverage. I’d love to see the expression on Bill Gate’s face if this turns out to be for real!
Sometimes I forget things, I suppose we all do to some degree. I’ve come up with a few different techniques for reminding myself about things depending on the task/time/info. If someone mentions something at work at the end of the day sometimes I’ll leave a post it note on my monitor so that I’ll see it in the morning. Sarah does something similar at home, she’ll leave herself a note on the bathroom mirror before going to bed so she’ll see it in the morning.
A year or two ago I saw a friend of mine leave a reminder for himself by calling his work number and leaving a voicemail. I thought it was rather odd at the time, but since then I’ve done it a few times myself. What I find is that when I call work number (usually from my cell phone) and leave voicemail explaining what I’m supposed to remember, the mere act of leaving the voicemail tends to keep that info/task in my thoughts. As a result I usually end taking care of what ever it was that I needed to do before I even check my voicemail in the morning. So this idea of leaving reminders via voicemail seems to work, at least so far.
The next step from there is some sort of timed message reminder. I’ve done this before with alphanumeric pagers using QuickPage (qpage), which allows to schedule your alphanumeric page to be sent at a given date and time in the future. Many calendar systems do the same thing with email, reminding you of upcoming events. What I’d like to have schedule voice reminders.
I want to be able to call a number, leave a message and schedule when I want that message to call me (i.e. 4 Aug 2005 @ 4:15pm). I should also be able to tell it which phone number to call (cell, work, home, friend?) and optionally to try them all in a given order if no one answers, or if the voicemail or answering machine picks up instead of a person. On top of all that I should be able to optionally sync calendar items via some sort of text to voice via the same service. Perhaps with some email integration also?
I wonder if the mobile phone providers will jump on this idea. Heck, no reason the land line providers shouldn’t be able to jump on this. Anyone with a large capacity voicemail system should be able to add some extra smarts to implement at least some of these ideas.
I’ve finally done it, I broke down and gave into the temptation to edit a page on Wikipedia. I was looking at their article about CD+G and realized that they didn’t have the Information Society CD on the list. It was a simple enough edit, so I gave into contributed to the vast data repository that is Wikipedia.
I don’t think I’m ready to make this a habit or anything, but it felt like an interesting first step. Perhaps I’ll take the time to update their Information Society page one of these days.
I’m still disappointed that I missed the Behind The Music episode with Information Society on VH1.