How to use ODBC to access database with different user passwords

[Frank Milthorpe]

Hi

Following on from Ben Cohen <[hidden email]> and "Marks, Jim"
<[hidden email]> postings.

The trick is have the username and password in a file in the same place
for each user. I suggest the personal area on the network drive. The
alternative is the C:\ drive, but this is less secure.

Here is an example to connect to Oracle database. The same principal
can be adapted for other databases.

===  H: drive is home network drive  ===

* Spss Syntax file - "h:\spss\oracle_odbc.sps" .
PRESERVE.
SET PRINTBACK = OFF.

* Create macro variables to connect to Oracle databases.
DEFINE !o_odbc  ( ) 'DSN=ORACLE - SPSS;HOST=DATA_WHAREHOUSE;'
!ENDDEFINE.
DEFINE !o_user  ( ) 'UID=username;PWD=xxxpasswordxxx'
!ENDDEFINE.

SET PRINTBACK = ON.
SET PRINTBACK = ON.
RESTORE.


---
  Note the use of SET PRINTBACK to hide the echoing of the password.





===  Now for the analysis  ===


INCLUDE 'h:\spss\oracle_odbc.sps' .

GET CAPTURE ODBC /
    CONNECT= !o_odbc+!o_user / UNENCRYPTED /
  SELECT
    tour.*
  FROM
    database.table  tour
  WHERE
    (tour.tour_no  < 10)
  ORDER BY
    person_no, tour_no.

DESCRIPTIVES VARIABLES = all /   STATISTICS = MIN MAX SUM MEAN.

*and etc.


Essentially this syntax can be shared between users without disclosing
usernames and passwords. You can even the change the name of the Oracle
and not have to change every syntax file.


Cheers

Frank

PS
  I give SPSS permission to include this solution in the tech support
database.




--------------------------------------------------------------------------------
Frank Milthorpe
Senior Manager, Transport Model Development
Transport and Population Data Centre
Department of Planning
Direct:   [phone]
Phone:  [phone]
Fax:      [phone]
Street:    Level 7, Tower 2, 1 Lawson Square, Redfern NSW 2016
Mail:       GPO Box 39, Sydney NSW 2001
Email:     [hidden email]
Web:      www.tpdc.nsw.gov.au



------------------------------------------------------------------------------------------------------------------------------------------------
This message is intended for the addressee named and may contain
confidential/privileged information. If you are not the intended
recipient, please delete it and notify the sender.
Views expressed in this message are those of the individual sender, and
are not necessarily the views of the Department.
You should scan any attached files for viruses.
------------------------------------------------------------------------------------------------------------------------------------------------