Multiple GET DATA (from Oracle) problem

[Catherine Kubitschek]

Hello SPSS people. I'm hoping someone can point me in the right direction to solve my problem.

(Big picture: I'm running SPSS 22.0.2 (64-bit) on Win7. The Oracle client and database are both 11g; the client is from Oracle. The database is on RAC. I have a System DSN created & named "query_odsprod" that works when tested. I'm trying to setup a system where different people can run the same SPSS code at different time points to get data from an Oracle database and process the data. Each person will have a different Oracle login and password. These passwords aren't shared and will be changed periodically.)

Little picture: I'm trying to extract data from an Oracle database using GET DATA. I've got my code working well enough on the first GET DATA and processing but after the second GET DATA the processing bombs.

Here's the slimmed down code (with the password X'd out).

*** Begin SPSS code *** .

DEFINE !o_odbc  ( ) 'DSN=query_odsprod;'      !ENDDEFINE. 

DEFINE !o_user  ( ) 'UID=ckubitsc;PWD=XXXXXXXX;' !ENDDEFINE.

 *** Query 1 - should return a single row, 1 column .

GET DATA /

    TYPE=ODBC / 

    CONNECT= !o_odbc+!o_user+'Pooling=true;' / UNENCRYPTED /

    SQL="SELECT * FROM dual" . 

/* CACHE.

/* EXECUTE.

/* DATASET NAME dual WINDOW=FRONT.

list var = all .

save outfile = t1 .

get file = t1 .

list var = all .

 *** Query 2 - should return 425 rows, 2 columns .

GET DATA /

    TYPE=ODBC / 

    CONNECT= !o_odbc+!o_user+'Pooling=true;' / UNENCRYPTED /

    SQL="SELECT * FROM instres.accessMember" . 

/* CACHE.

/* EXECUTE.

/* DATASET NAME accessMember WINDOW=FRONT.

list var = all .

save outfile = t2 .

*** End SPSS code *** .

Either query works if it is the first query. Either query has problems if it is second. I don't understand the "Pooling=true" so that may be a problem. The rest of the CONNECT string seems to work without any problems.

The queries (whether run first or second) will pull back some legitimate data with the GET DATA that can be viewed in the data editor. The problem comes when I try to make a pass through the data. At least that's what it looks like to me.

EXECUTE, LIST, or SAVE either 1) result in a pop-up saying "Unrecoverable application error in the Statistics processor"; 2) eventually result in a pop-up saying "An unknown error has terminated communication with the processor. The SPSS Statistics Processor is unavailable" or 3) result in eventual termination with Task Manager. Either 1) or 2) result in "IBM SPSS Statistics Processor is unavailable" at the bottom of any open SPSS windows.

CACHE and DATASET may affect which error I see (or how long I wait) but don't result in a second working GET DATA processing.

I first tried using the wizard to produce the CODE. I found some help in the archives (http://spssx-discussion.1045642.n5.nabble.com/How-to-use-ODBC-to-access-database-with-different-user-passwords-td1071645.html and others) which caused me to use a couple of macros to configure the CONNECT subcommand and to add the "Pooling=true". The wizard didn't provide better results and it was harder to maintain.

Does anyone have any suggestions?

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