Tuesday, February 28, 2012

Problem: PSPP on Windows Vista

The purpose of this blog is to record the problem I run into when running PSPP on Windows Vista. For a quickie, PSPP is a GNU SPSS. It was designed for POSIX /Unix  machine and was ported to Windows by several authors. As of today (2012/02/23), there were reported problems for Windows Vista/7.

My use of the PSPP is to handle the files download from the 'DOWNLOAD SURVEY DATA FILES' option of the IPEDS Data Center. The download comes with one .csv data file and one .sps program file. The downloaded .sps file usually need be adjusted a bit for syntax errors. Other than that, those file are in great shape.

I have been using the PSPP v0.7.2 (2009-05-18) from michelboaventura.com on Windows XP machine to do this for quite a while without a problem. Recently, I installed the same version on my Windows Vista machine and I notice that the .csv data file would not be imported into the PSPP worksheet by the .sps 'get data' command. This is eventually solved by adding the 'set workspace=1000000000' command at the beginning of the the .sps file as suggested by awardspace.com. After that everything works fine for the version v0.7.2 from michelboaventura.com.

While I was having trouble with v0.7.2 on Vista, I tried the following newer versions:
v0.7.8 (2011-11-11) from michelboaventura.com and
v0.7.9 (2012-02-16) from awardspace.com.

For v0.7.8, I was able to get the .csv file into the PSPP worksheet window if I add the 'set workspace=1000000000' statement. However, when I try to process the 'variable label' command that is next in the .sps program, the PSPP windows will gray out and doesn't seems to make progresses. When I click the PSPP spreadsheet window, Windows Vista reported that the program has crashed. One extra thing I did with this v0.7.8 is to use the pspp.exe to process the .sps file and it run through the process with no problem - I don't even need the 'set workspace=1000000000' statement.

For the v0.7.9, the installation is successful. But when ever I start the psppire.exe GUI, Windows Vista reported the program has crashed. I did not pursue more on this one.

Monday, February 6, 2012

RODBC sqlSave() autonumber auto-increase problem

This is a quick note on R.

The RODBC package for R-project is a popular add-on to access ODBC database. The sqlSave() function is a common method used to save a data.frame to an ODBC table. For an ODBC table with autonumber or auto-increase fields, this function reports errors. Turning on the verbose option of that function, it is observed that sqlSave() constructed a SQL statement that ALWAYS intended to insert values to all table fields. With this SQL statement, user are forced to assign a value for the auto-increase field, which, by its own definition, should not be assigned a value from external source.

For some odd database implementation, you might be able to assign a null value to the auto-increase field and got it to work. But, as far as I know, the MS ACCESS would not take that.

I was trying to find the source code for the RODBC package but was unsuccessful. I do found that if I wrote a function so that it write the data.frame to database using the sqlQuery() function, this can be overcome. However, this may take longer to process the data. The idea solution is to modify the sqlSave() function so that it is more flexible.

I also run into the problem with sqlSave() when dealing with two MS ACCESS memo fields. Again, this can be solved with the sqlQuery() function.

MySql MyOdbc MS ACCESS server has gone away error

While using MS ACCESS to connect to MySQL database through MyOdbc, I constantly got the following error: 'server has gone away error #2006'.

After searching the MySql site with no success, I finally find the solution at: SiteGround Forum posted by hwj.

Basically, when create DSN in Windows using ODBC configuration tool, you need to enable the option auto-reconnect immediately before you click OK to exit. According to hwj, if you did not do it while you creating the DSN, you will have to start over.

This works for me!

Hopefully, with this link, it will promote that article in Google search.