Date: Fri, 11 Nov 2005 07:55:11 -0600
Reply-To: "Peck, Jon" <firstname.lastname@example.org>
Sender: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From: "Peck, Jon" <email@example.com>
Subject: Re: GET DATA SQL and MS Access
Content-Type: text/plain; charset="utf-8"
Only ancient versions of the SPSS Database Wizard generate GET CAPTURE ODBC syntax. That was superseded by GET DATA /TYPE=ODBC.
GET DATA extended the usual SPSS literal concatenation rules and accepts a sequence of '...' '...' because of the length of typical generated SQL syntax. (In SPSS 14, we fixed a glitch with SAVE TRANSLATE /TYPE=ODBC. That requires a /CONNECT string, and users typically copy the connect string generated by the Database Wizard, but the wizard took advantage of the relaxed literals handling and did not paste '+', but SAVE TRANSLATE did not understand this. Now it does.)
By the ODBC standard, we ask the target database a few questions about quoting and act accordingly. The actual character used to do the quoting is what the database requires and can vary from one to another. Secondly, each database has a different list of reserved words, e.g., FROM, that must be quoted if they are used as field names. We used to just check a standard list, but now we query the database for its list and quote whatever it requires. You would be amazed at how many reserved words there can be.
We reduced the amount of quoting to somewhere near a safe minimum in order to make the generated SQL more readable. On top of the SQL quoting, though, we have to ensure that the SPSS quoting that goes around the SQL does not conflict. So if, say, the database uses the single quote, and SPSS uses the single quote around the whole expression, we have to double the inner quotes, which are then removed in the parsed expression that we pass to ODBC.
This can get very confusing when those quotes also appear inside string literals inside SQL syntax.
These enhancements do not apply to the old GET CAPTURE command, which is only preserved for backwards compatibility.
From: Richard Ristow [mailto:firstname.lastname@example.org]
Sent: Thursday, November 10, 2005 6:41 PM
To: Peck, Jon; SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: GET DATA SQL and MS Access
At 09:03 AM 11/10/2005, Peck, Jon wrote:
>When you use ODBC in SPSS (or other apps), the SQL you or the database
>wizard writes is validated by the data source, which is Access in your
[Long, useful substance, which I'm not quoting]
>The best way to develop SQL that will work with Access if you are
>going beyond the Database Wizard is to use Access itself [...]You can
>test the query within Access using the "!" button to see that it does
>what you want.
>Now, in particular, I think you would want the Cdbl function to
>convert to a number.
THANK you, Jon. Really good stuff. I floundered when I was doing
Access->SPSS. I did succeed in modifying the Database Wizard SQL code a
very little bit, and getting the modification to work, but having this
information available would have helped a lot.
I greatly appreciate it, and it's enough, but there's nothing like
answering one question to get another thrown at you. The following
command, which works,
GET CAPTURE ODBC /CONNECT='DSN=MS Access 97 Database;' +
'DBQ=C:\B_Client\Martha Lang\Home Visits\HV_Dict.mdb;' +
'DefaultDir=C:\B_Client\Martha Lang\Testing;' +
/SQL = 'SELECT `ObjDef`.`ParentID` AS `ParentID`, '
' `ObjDef`.`ID` AS `ID`, '
' `ObjDef`.`Name` AS `Name`, '
' `ObjDef`.`Object Type` AS `Obj_Typ`, '
' `ObjDef`.`Extra1` AS `Extra1`, '
' `ObjDef`.`Extra2` AS `Extra2`, '
' `ObjDef`.`Extra3` AS `Extra3` '
' `C:\B_Client\Martha Lang\Home Visits\HV_Dict`.`Object
has two subcommands: "ODBC" and "SQL". The long string parameter to
"ODBC" is straight from the Database Capture wizard, though I've
reformatted it to put the line breaks at logical places; and I don't
understand it at all. "DBQ" names the Access database I'm reading from.
(Of course; "DBQ" is the obvious name for that.) "DSN" and "FIL" seem
to have something to do with specifying that it IS an Access database.
But I sure don't understand it well enough to change anything. Can you
say anything, or more likely point to documentation, about what they
mean and what you might do with them?
"SQL" makes more sense; that's SQL syntax, all right. Again, I've
reformatted it to read more easily. I noticed, though, that
* There are no '+' signs at the end of the lines, so it's a series of
string parameters, not a single string. Either the GET CAPTURE command
itself, or Access, must join them to a single string to be executed
* The "`" are, of course, quotes to define literals within the literals
delimited by "'". When I was working with this syntax, I tried some of
the other standard ways to write quotes within quotes, like "'" in
literals delimited by double quotes, but none of them worked. I don't
know whether it's because Access SQL only accepts '`' as a literal
delimiter (though that seems odd), or some subtler reason.