SPSSX Discussion

SPSS native ODBC driver

Classic

List

Threaded

5 messages Options

Roberts, Michael

SPSS native ODBC driver

Good Afternoon all,

I am hoping for some technical knowhow on choosing an ODBC driver to run
queries from within SPSS against an Oracle database. Currently I have
configured and am using the SPSS ODBC32V5.0 driver for Oracle.

Specifically, I want to be able to create and run queries against the
database without having to repeatedly type in my login and password -
somewhat like with SQL server.

I had been able to run queries with SPSS against an Informix database
without having to type in my password after an initial entry, but our
organization has since moved to Oracle 10.x and now each time I create a
query with SPSS, I have to do some copying and pasting of password
gobledygook characters into my syntax, which is ridiculous.

Ps. I will be unable to get our database support staff to make any
changes to accommodate this need, so if there is no other way to do
this, that info would be helpful too!

TIA

Mike

====================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

Marks, Jim

Re: SPSS native ODBC driver

Michael:

Copy the gobbledegook and create a macro.

Here is a sample using a pseudo connection string (without the '
character):

DEFINE !c_str ()

!QUOTE('DSN=yourdb;UID=youruname;PWD=yourgoobledegook;Host=yourhost;Port
=####;SID=yours')
!ENDDEFINE.

Save the macro into a syntax file. (C:\myconnect.sps)

Include the macro at the start of your syntax.

INCLUDE FILE = ' C:\myconnect.sps'.

Write your GET DATA command to use the macro:

GET DATA /TYPE = ODBC /CONNECT = !c_str /
SQL = 'SELECT * FROM yourdb.yourtable '
/ASSUMEDSTRWIDTH = 10.

Notice that the '/' for the SQL subcommand comes on the line before the
SQL statement-- macros automatically add a CR at the end.

You also can use the 'UNENCRYPTED' subcommand for the pwd (though I
haven't tried it).
--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Roberts, Michael
Sent: Wednesday, January 21, 2009 2:22 PM
To: [hidden email]
Subject: SPSS native ODBC driver

Good Afternoon all,

I am hoping for some technical knowhow on choosing an ODBC driver to run
queries from within SPSS against an Oracle database. Currently I have
configured and am using the SPSS ODBC32V5.0 driver for Oracle.

Specifically, I want to be able to create and run queries against the
database without having to repeatedly type in my login and password -
somewhat like with SQL server.

I had been able to run queries with SPSS against an Informix database
without having to type in my password after an initial entry, but our
organization has since moved to Oracle 10.x and now each time I create a
query with SPSS, I have to do some copying and pasting of password
gobledygook characters into my syntax, which is ridiculous.

Ps. I will be unable to get our database support staff to make any
changes to accommodate this need, so if there is no other way to do
this, that info would be helpful too!

TIA

Mike

=======
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

=====================
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

Roberts, Michael

Re: SPSS native ODBC driver

Hi All,

Many thanks to Jim Marks for his macro code and suggestion...it works
wonderfully and saves me much angst by not having to unnecessarily input
login and pwords!

Hurrah for you, Jim.

Mike

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Marks, Jim
Sent: Wednesday, January 21, 2009 4:02 PM
To: [hidden email]
Subject: Re: SPSS native ODBC driver

Michael:

Copy the gobbledegook and create a macro.

Here is a sample using a pseudo connection string (without the '
character):

DEFINE !c_str ()

!QUOTE('DSN=yourdb;UID=youruname;PWD=yourgoobledegook;Host=yourhost;Port
=####;SID=yours')
!ENDDEFINE.

Save the macro into a syntax file. (C:\myconnect.sps)

Include the macro at the start of your syntax.

INCLUDE FILE = ' C:\myconnect.sps'.

Write your GET DATA command to use the macro:

GET DATA /TYPE = ODBC /CONNECT = !c_str /
SQL = 'SELECT * FROM yourdb.yourtable '
/ASSUMEDSTRWIDTH = 10.

Notice that the '/' for the SQL subcommand comes on the line before the
SQL statement-- macros automatically add a CR at the end.

You also can use the 'UNENCRYPTED' subcommand for the pwd (though I
haven't tried it).
--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Roberts, Michael
Sent: Wednesday, January 21, 2009 2:22 PM
To: [hidden email]
Subject: SPSS native ODBC driver

Good Afternoon all,

I am hoping for some technical knowhow on choosing an ODBC driver to run
queries from within SPSS against an Oracle database. Currently I have
configured and am using the SPSS ODBC32V5.0 driver for Oracle.

Specifically, I want to be able to create and run queries against the
database without having to repeatedly type in my login and password -
somewhat like with SQL server.

I had been able to run queries with SPSS against an Informix database
without having to type in my password after an initial entry, but our
organization has since moved to Oracle 10.x and now each time I create a
query with SPSS, I have to do some copying and pasting of password
gobledygook characters into my syntax, which is ridiculous.

Ps. I will be unable to get our database support staff to make any
changes to accommodate this need, so if there is no other way to do
this, that info would be helpful too!

TIA

Mike

=======
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

=====================
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

=====================
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

Frank Milthorpe-2

Re: SPSS native ODBC driver

In reply to this post by Roberts, Michael

Jim & Michael

If you want to keep your IT Department and not have your password printed out, do the following.

Within the 'C:\myconnect.sps' file make the following additions.

SET PRINTBACK = OFF.

* Create macro variables to connect to Oracle databases.
DEFINE !c_str ()

!QUOTE('DSN=yourdb;UID=youruname;PWD=yourgoobledegook;Host=yourhost;Port
=####;SID=yours')
!ENDDEFINE.

SET PRINTBACK = ON.
SET PRINTBACK = ON.

Cheers

Frank

PS
I actually use the following in my setups:

=== IN 'C:\myconnect.sps' ====

* Create macro variables to connect to Oracle databases.
DEFINE !o_odbc ( ) 'DSN=Oracle - SPSS;HOST=production;' !ENDDEFINE.
DEFINE !o_user ( ) 'UID=frank;PWD=xxxxx' !ENDDEFINE.

=== IN syntax analysis file ====

GET CAPTURE ODBC /
CONNECT= !o_odbc+!o_user / UNENCRYPTED /
SELECT
*
FROM
censusdata
ORDER BY
var1
WHERE
(var20 LT 100) .

>>>

Michael:

Copy the gobbledegook and create a macro.

Here is a sample using a pseudo connection string (without the '
character):

DEFINE !c_str ()

!QUOTE('DSN=yourdb;UID=youruname;PWD=yourgoobledegook;Host=yourhost;Port
=####;SID=yours')
!ENDDEFINE.

Save the macro into a syntax file. (C:\myconnect.sps)

Include the macro at the start of your syntax.

INCLUDE FILE = ' C:\myconnect.sps'.

Write your GET DATA command to use the macro:

GET DATA /TYPE = ODBC /CONNECT = !c_str /
SQL = 'SELECT * FROM yourdb.yourtable '
/ASSUMEDSTRWIDTH = 10.

Notice that the '/' for the SQL subcommand comes on the line before the
SQL statement-- macros automatically add a CR at the end.

You also can use the 'UNENCRYPTED' subcommand for the pwd (though I
haven't tried it).
--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Roberts, Michael
Sent: Wednesday, January 21, 2009 2:22 PM
To: [hidden email]
Subject: SPSS native ODBC driver

Good Afternoon all,

I am hoping for some technical knowhow on choosing an ODBC driver to run
queries from within SPSS against an Oracle database. Currently I have
configured and am using the SPSS ODBC32V5.0 driver for Oracle.

Specifically, I want to be able to create and run queries against the
database without having to repeatedly type in my login and password -
somewhat like with SQL server.

I had been able to run queries with SPSS against an Informix database
without having to type in my password after an initial entry, but our
organization has since moved to Oracle 10.x and now each time I create a
query with SPSS, I have to do some copying and pasting of password
gobledygook characters into my syntax, which is ridiculous.

Ps. I will be unable to get our database support staff to make any
changes to accommodate this need, so if there is no other way to do
this, that info would be helpful too!

TIA

Mike

=======
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

-----
Frank Milthorpe
Senior Manager, Transport Model Development
Transport Data Centre
NSW Ministry of Transport

Phone: (02) 9268 2211
Direct: (02) 9268 2945
Fax: (02) 9268 2839

Street: Level 21, 227 Elizabeth Street, Sydney NSW 2000
Postal: GPO Box 1620, Sydney NSW 2001
Email: [hidden email]
Web: www.transport.nsw.gov.au/tdc

_________________________________________________________
This message (including any attachments) is intended solely
for the addressee named and may contain confidential and or
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 Ministry of Transport (MoT). Whole or parts of
this e-mail may be subject to copyright of the Ministry or third
parties. You should only re-transmit, distribute or use the material
for commercial purposes if you are authorised to do so.

Please visit us http://www.transport.nsw.gov.au or
http://www.131500.info

=====================
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

Roberts, Michael

Re: SPSS native ODBC driver

Hi Frank,

Thank you for the tip. Looks like your implementation of two macros is
a neat way to do this.

Sincerely

Mike

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Frank Milthorpe
Sent: Thursday, January 22, 2009 12:39 AM
To: [hidden email]
Subject: Re: SPSS native ODBC driver

Jim & Michael

If you want to keep your IT Department and not have your password
printed out, do the following.

Within the 'C:\myconnect.sps' file make the following additions.

SET PRINTBACK = OFF.

* Create macro variables to connect to Oracle databases.
DEFINE !c_str ()

!QUOTE('DSN=yourdb;UID=youruname;PWD=yourgoobledegook;Host=yourhost;Port
=####;SID=yours')
!ENDDEFINE.

SET PRINTBACK = ON.
SET PRINTBACK = ON.

Cheers

Frank

PS
I actually use the following in my setups:

=== IN 'C:\myconnect.sps' ====

* Create macro variables to connect to Oracle databases.
DEFINE !o_odbc ( ) 'DSN=Oracle - SPSS;HOST=production;' !ENDDEFINE.
DEFINE !o_user ( ) 'UID=frank;PWD=xxxxx' !ENDDEFINE.

=== IN syntax analysis file ====

GET CAPTURE ODBC /
CONNECT= !o_odbc+!o_user / UNENCRYPTED /
SELECT
*
FROM
censusdata
ORDER BY
var1
WHERE
(var20 LT 100) .

>>>

Michael:

Copy the gobbledegook and create a macro.

Here is a sample using a pseudo connection string (without the '
character):

DEFINE !c_str ()

!QUOTE('DSN=yourdb;UID=youruname;PWD=yourgoobledegook;Host=yourhost;Port
=####;SID=yours')
!ENDDEFINE.

Save the macro into a syntax file. (C:\myconnect.sps)

Include the macro at the start of your syntax.

INCLUDE FILE = ' C:\myconnect.sps'.

Write your GET DATA command to use the macro:

GET DATA /TYPE = ODBC /CONNECT = !c_str /
SQL = 'SELECT * FROM yourdb.yourtable '
/ASSUMEDSTRWIDTH = 10.

Notice that the '/' for the SQL subcommand comes on the line before the
SQL statement-- macros automatically add a CR at the end.

You also can use the 'UNENCRYPTED' subcommand for the pwd (though I
haven't tried it).
--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Roberts, Michael
Sent: Wednesday, January 21, 2009 2:22 PM
To: [hidden email]
Subject: SPSS native ODBC driver

Good Afternoon all,

I am hoping for some technical knowhow on choosing an ODBC driver to run
queries from within SPSS against an Oracle database. Currently I have
configured and am using the SPSS ODBC32V5.0 driver for Oracle.

Specifically, I want to be able to create and run queries against the
database without having to repeatedly type in my login and password -
somewhat like with SQL server.

I had been able to run queries with SPSS against an Informix database
without having to type in my password after an initial entry, but our
organization has since moved to Oracle 10.x and now each time I create a
query with SPSS, I have to do some copying and pasting of password
gobledygook characters into my syntax, which is ridiculous.

Ps. I will be unable to get our database support staff to make any
changes to accommodate this need, so if there is no other way to do
this, that info would be helpful too!

TIA

Mike

=======
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

-----
Frank Milthorpe
Senior Manager, Transport Model Development
Transport Data Centre
NSW Ministry of Transport

Phone: (02) 9268 2211
Direct: (02) 9268 2945
Fax: (02) 9268 2839

Street: Level 21, 227 Elizabeth Street, Sydney NSW 2000
Postal: GPO Box 1620, Sydney NSW 2001
Email: [hidden email]
Web: www.transport.nsw.gov.au/tdc

_________________________________________________________
This message (including any attachments) is intended solely
for the addressee named and may contain confidential and or
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 Ministry of Transport (MoT). Whole or parts of
this e-mail may be subject to copyright of the Ministry or third
parties. You should only re-transmit, distribute or use the material
for commercial purposes if you are authorised to do so.

Please visit us http://www.transport.nsw.gov.au or
http://www.131500.info

=====================
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

=====================
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