question about ODBC connection and 'file type mixed'

[Bibel, Daniel (POL)]

I have a large SQL Server database which I’d like to access via an ODBC
connection (using spss v.14).

 

Here’s the connect string that I have used to get the data:

 

GET DATA /TYPE=ODBC /CONNECT=

 'DSN=UCRR-New;SERVER=CLM-CISGIS3;UID=ucrr;PWD=)G$~!C._-^;Trusted_Connec
tion=NO;APP=;WSID=;DATA'+

 'BASE=UCRRMA2000;LANGUAGE=;'

 /SQL = 'SELECT  SegmentData FROM  dbo."_txISegments"'

 /ASSUMEDSTRWIDTH=307

 

 

The data I’m accessing is in the format of  ‘file type mixed’.  If I had
dumped the data into a text file, I have syntax which would read it, but
I can’t figure out how to use the odbc to access the data and have it
read using my (old) syntax file.

 

Here’s an excerpt of the syntax file:

 

file type mixed file = 'r:\temp\ucrr\nibrs.dat'

  record = ibr_rec 5 (a) .

Record type '1'.

data list /

      rdw 1-4

   ibr_rec 5 (a)

   action 6 (a)

   tapemo 7-8

   tapeyr 9-12

   city 13-16

…..(etc)

Record type '2'.

data list /

   ori 17-25(a)

   inc_num 26-37(a)

   off_code 38-40 (a)

…..(etc).

 

Any advice would be appreciated.

 

 

 

 

 

 

====================To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 12:19 PM 8/12/2008, Bibel, Daniel wrote:

>I have a large SQL Server database which I'd like to access via an
>ODBC connection (using spss v.14).
>
>Here's the connect string that I have used to get the data:
>
>GET DATA /TYPE=ODBC /CONNECT=
>  'DSN=UCRR-New;SERVER=CLM-CISGIS3;UID=ucrr;PWD=)G$~!C._-^;' +
>  'Trusted_Connection=NO;APP=;WSID=;'                        +
>  'DATABASE=UCRRMA2000;LANGUAGE=;'
>  /SQL = 'SELECT  SegmentData FROM  dbo."_txISegments"'
>  /ASSUMEDSTRWIDTH=307
>
>The data I'm accessing is in the format of  'file type mixed'.

So, you're reading a single long field ("SegmentData") from the
database; and that field is the raw data, with values for many
variables, as you'd ordinarily read with your syntax. It's very odd
to set up a relational database table with unparsed data like that,
but if that's what you have, it's what you have.

>I can't figure out how to use the odbc to access the data and have
>it read using my (old) syntax file:
>
>file type mixed file = 'r:\temp\ucrr\nibrs.dat'
>   record = ibr_rec 5 (a) .
>Record type '1'.
>data list /
>       rdw       1-4
>...
>Record type '2'.
>data list /
>       ori      17-25 (a)
>...

You can't use DATA LIST, or FILE TYPE, against data that's already in
SPSS. Roughly, that leaves two choices:

A. Write to a scratch file, and read back.

If SCRATCH is the name or file handle of a scratch file, then

GET DATA /TYPE=ODBC /CONNECT=
  'DSN=UCRR-New;SERVER=CLM-CISGIS3;UID=ucrr;PWD=)G$~!C._-^;' +
  'Trusted_Connection=NO;APP=;WSID=;'                        +
  'DATABASE=UCRRMA2000;LANGUAGE=;'
  /SQL = 'SELECT  SegmentData FROM  dbo."_txISegments"'
  /ASSUMEDSTRWIDTH=307

WRITE OUTFILE=SCRATCH/SegmentData.
EXECUTE  /* which is needed, here */.

file type mixed file = SCRATCH
   record = ibr_rec 5 (a) .
(etc.)


B. Or, you can use combinations of SUBSTRING and NUMBER functions to
parse the string into its component fields -- essentially,
hand-coding what DATA LIST does.


-Best of luck,
  Richard Ristow

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD