SPSSX Discussion

Trouble with a macro in a GET DATA/TYPE=OBDC context

Classic

List

Threaded

7 messages Options

Little, Jonathon

Trouble with a macro in a GET DATA/TYPE=OBDC context

Dear LISTERS,

I am having trouble getting a MACRO to work in the GET DATA context
below:

The two macros are:

!userid
!password

DEFINE !userid () '6666666' !ENDDEFINE.
DEFINE !password () '########' !ENDDEFINE.

The marcos need to work in the GET DATA/TYPE=OBDC statement below.

GET DATA /TYPE=ODBC /CONNECT=
'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
/SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE, TRIAGE_DATE,
DOCTOR_SEEN'
/ASSUMEDSTRWIDTH=155.

This may or may not be possible. As always, any help is appreciated.

Kind regards,

Jonathon

Georg Maubach

AW: Trouble with a macro in a GET DATA/TYPE=OBDC context

Hi Jonathon,

I not really sure but it looks to me as if you need to unquote the parameters and if you use the macros !userid and !password within a macro tell it explicitly to expand it. You could try the following:

DEFINE @example ()

GET DATA
/TYPE=ODBC
/CONNECT='DSN=edprod;UID=!UNQUOTE(!EVAL(!userid));PWD=!UNQUOTE(!EVAL(!password));SERVER=edprod;'
/SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE, TRIAGE_DATE, DOCTOR_SEEN'
/ASSUMEDSTRWIDTH=155 .

!ENDDEFINE .

Regards

Georg Maubach
Research Manager

-----Ursprüngliche Nachricht-----
Von: SPSSX(r) Discussion [mailto:[hidden email]] Im Auftrag von Little, Jonathon
Gesendet: Dienstag, 14. August 2007 09:40
An: [hidden email]
Betreff: Trouble with a macro in a GET DATA/TYPE=OBDC context

Dear LISTERS,

I am having trouble getting a MACRO to work in the GET DATA context
below:

The two macros are:

!userid
!password

DEFINE !userid () '6666666' !ENDDEFINE.
DEFINE !password () '########' !ENDDEFINE.

The marcos need to work in the GET DATA/TYPE=OBDC statement below.

GET DATA /TYPE=ODBC /CONNECT=
'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
/SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE, TRIAGE_DATE, DOCTOR_SEEN'
/ASSUMEDSTRWIDTH=155.

This may or may not be possible. As always, any help is appreciated.

Kind regards,

Jonathon

Albert-Jan Roskam

Re: AW: Trouble with a macro in a GET DATA/TYPE=OBDC context

Hi Jonathon,

I agree with Georg that you should use the !EVAL
function. But I am not sure about his suggestion of
UNQUOTE. Here's what I would try:

/CONNECT=!quote(!concat('DSN=edprod;UID=',!EVAL(!userid),';PWD=',!EVAL(!password),';SERVER=edprod;'))

Curious if it works. At least you have two
alternatives to try now.

Cheers!!
Albert-Jan

--- Georg Maubach <[hidden email]> wrote:

> Hi Jonathon,
>
> I not really sure but it looks to me as if you need
> to unquote the parameters and if you use the macros
> !userid and !password within a macro tell it
> explicitly to expand it. You could try the
> following:
>
> DEFINE @example ()
>
> GET DATA
> /TYPE=ODBC
>
>

/CONNECT='DSN=edprod;UID=!UNQUOTE(!EVAL(!userid));PWD=!UNQUOTE(!EVAL(!password));SERVER=edprod;'

> /SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE,
> TRIAGE_DATE, DOCTOR_SEEN'
> /ASSUMEDSTRWIDTH=155 .
>
> !ENDDEFINE .
>
> Regards
>
> Georg Maubach
> Research Manager
>
>
> -----Ursprüngliche Nachricht-----
> Von: SPSSX(r) Discussion
> [mailto:[hidden email]] Im Auftrag von
> Little, Jonathon
> Gesendet: Dienstag, 14. August 2007 09:40
> An: [hidden email]
> Betreff: Trouble with a macro in a GET
> DATA/TYPE=OBDC context
>
> Dear LISTERS,
>
> I am having trouble getting a MACRO to work in the
> GET DATA context
> below:
>
> The two macros are:
>
> !userid
> !password
>
> DEFINE !userid () '6666666' !ENDDEFINE.
> DEFINE !password () '########' !ENDDEFINE.
>
> The marcos need to work in the GET DATA/TYPE=OBDC
> statement below.
>
> GET DATA /TYPE=ODBC /CONNECT=
>
>

'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'

> /SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE,
> TRIAGE_DATE, DOCTOR_SEEN'
> /ASSUMEDSTRWIDTH=155.
>
> This may or may not be possible. As always, any
> help is appreciated.
>
> Kind regards,
>
> Jonathon
>

Cheers!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/

Richard Ristow

Re: Trouble with a macro in a GET DATA/TYPE=OBDC context

In reply to this post by Little, Jonathon

At 03:39 AM 8/14/2007, Little, Jonathon wrote:

>I am having trouble getting a MACRO to work in the GET DATA context
>below:
>
>The two macros are:
>
>!userid
>!password
>
>DEFINE !userid () '6666666' !ENDDEFINE.
>DEFINE !password () '########' !ENDDEFINE.
>
>The marcos need to work in the GET DATA/TYPE=OBDC statement below.
>
>GET DATA /TYPE=ODBC /CONNECT=
> 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
> /SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE, TRIAGE_DATE,
> DOCTOR_SEEN'
> /ASSUMEDSTRWIDTH=155.

To make explicit what other responders have suggested: A quoted string
like

> 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'

is a literal; it isn't scanned for macro expansion. I don't think you
need to be very fancy, though; SPSS string catenation using "+" will do
it. Like this (SPSS 15 draft output). Command "ECHO" displays the
contents of a literal string. The first version, as you have it,
doesn't work; the second, using "+", does:

DEFINE !userid () '6666666' !ENDDEFINE.
DEFINE !password () '########' !ENDDEFINE.

ECHO 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'.
DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;

ECHO 'DSN=edprod;UID=' + !userid + ';PWD=' + !password +
';SERVER=edprod;'.
DSN=edprod;UID=6666666;PWD=########;SERVER=edprod;

Marks, Jim

Re: Trouble with a macro in a GET DATA/TYPE=OBDC context

This works (on my system) :~)

A macro to define the entire connection string:

DEFINE !c_db ()

!QUOTE('DSN=dbasename;UID=username;PWD=encryptedstring;Host=computer;Por
t=####;SID=alias')
!ENDDEFINE.

The macro is stored in a file called connect:

Then INSERT and GET DATA:

INSERT file = 'connect'.

GET DATA /TYPE=ODBC /CONNECT= !c_db /
SQL='SELECT '
...

Notice that the "/" for select is on the same line as the macro call--
the macro will insert a hard return when it expands.

--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Richard Ristow
Sent: Tuesday, August 14, 2007 8:46 AM
To: [hidden email]
Subject: Re: Trouble with a macro in a GET DATA/TYPE=OBDC context

At 03:39 AM 8/14/2007, Little, Jonathon wrote:

To make explicit what other responders have suggested: A quoted string
like

> 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'

is a literal; it isn't scanned for macro expansion. I don't think you
need to be very fancy, though; SPSS string catenation using "+" will do
it. Like this (SPSS 15 draft output). Command "ECHO" displays the
contents of a literal string. The first version, as you have it, doesn't
work; the second, using "+", does:

DEFINE !userid () '6666666' !ENDDEFINE.
DEFINE !password () '########' !ENDDEFINE.

ECHO 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'.
DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;

ECHO 'DSN=edprod;UID=' + !userid + ';PWD=' + !password +
';SERVER=edprod;'.
DSN=edprod;UID=6666666;PWD=########;SERVER=edprod;

Catherine Kubitschek

Re: Trouble with a macro in a GET DATA/TYPE=OBDC context

I've been following this thread and this is the first time my question has
been hinted at.

How do you encrypt the password so you can put it into a macro? I'm
guessing the original intent was to create code that could be run by
multiple people and/or over time (as passwords change).

Alternative question would be - how do you run the database query with an
unencrypted password? It doesn't work for me.

Has anyone gotten further on this than me? Production mode, script, Python
do any of these provide a solution?

What we've done in the past is to create a simple database query and then
cut and paste the id & password to the original before deleting the new
query. It seems like there should be a better way.

Thanks.
Catherine

At 8/14/2007 09:59 AM, Marks, Jim wrote:

>This works (on my system) :~)
>
> A macro to define the entire connection string:
>
>DEFINE !c_db ()
>
>!QUOTE('DSN=dbasename;UID=username;PWD=encryptedstring;Host=computer;Por
>t=####;SID=alias')
>!ENDDEFINE.
>
>The macro is stored in a file called connect:
>
>
>Then INSERT and GET DATA:
>
>INSERT file = 'connect'.
>
>GET DATA /TYPE=ODBC /CONNECT= !c_db /
> SQL='SELECT '
>...
>
>Notice that the "/" for select is on the same line as the macro call--
>the macro will insert a hard return when it expands.
>
>--jim
>
>-----Original Message-----
>From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
>Richard Ristow
>Sent: Tuesday, August 14, 2007 8:46 AM
>To: [hidden email]
>Subject: Re: Trouble with a macro in a GET DATA/TYPE=OBDC context
>
>At 03:39 AM 8/14/2007, Little, Jonathon wrote:
>
> >I am having trouble getting a MACRO to work in the GET DATA context
> >below:
> >
> >The two macros are:
> >
> >!userid
> >!password
> >
> >DEFINE !userid () '6666666' !ENDDEFINE.
> >DEFINE !password () '########' !ENDDEFINE.
> >
> >The marcos need to work in the GET DATA/TYPE=OBDC statement below.
> >
> >GET DATA /TYPE=ODBC /CONNECT=
> > 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
> > /SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE, TRIAGE_DATE,
> >DOCTOR_SEEN'
> > /ASSUMEDSTRWIDTH=155.
>
>To make explicit what other responders have suggested: A quoted string
>like
>
> > 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
>
>is a literal; it isn't scanned for macro expansion. I don't think you
>need to be very fancy, though; SPSS string catenation using "+" will do
>it. Like this (SPSS 15 draft output). Command "ECHO" displays the
>contents of a literal string. The first version, as you have it, doesn't
>work; the second, using "+", does:
>
>DEFINE !userid () '6666666' !ENDDEFINE.
>DEFINE !password () '########' !ENDDEFINE.
>
>ECHO 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'.
>DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;
>
>ECHO 'DSN=edprod;UID=' + !userid + ';PWD=' + !password +
>';SERVER=edprod;'.
>DSN=edprod;UID=6666666;PWD=########;SERVER=edprod;

Marks, Jim

Re: Trouble with a macro in a GET DATA/TYPE=OBDC context

I used the GUI until I got an encryption string without the ' as a
character. Then I copied and pasted

Then I found out about the /UNENCRYPTED subcommand.

--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Catherine Kubitschek
Sent: Tuesday, August 14, 2007 12:28 PM
To: [hidden email]
Subject: Re: Trouble with a macro in a GET DATA/TYPE=OBDC context

I've been following this thread and this is the first time my question
has been hinted at.

How do you encrypt the password so you can put it into a macro? I'm
guessing the original intent was to create code that could be run by
multiple people and/or over time (as passwords change).

Alternative question would be - how do you run the database query with
an unencrypted password? It doesn't work for me.

Has anyone gotten further on this than me? Production mode, script,
Python do any of these provide a solution?

What we've done in the past is to create a simple database query and
then cut and paste the id & password to the original before deleting the
new query. It seems like there should be a better way.

Thanks.
Catherine

At 8/14/2007 09:59 AM, Marks, Jim wrote:

>This works (on my system) :~)
>
> A macro to define the entire connection string:
>
>DEFINE !c_db ()
>
>!QUOTE('DSN=dbasename;UID=username;PWD=encryptedstring;Host=computer;Po
>r
>t=####;SID=alias')
>!ENDDEFINE.
>
>The macro is stored in a file called connect:
>
>
>Then INSERT and GET DATA:
>
>INSERT file = 'connect'.
>
>GET DATA /TYPE=ODBC /CONNECT= !c_db /
> SQL='SELECT '
>...
>
>Notice that the "/" for select is on the same line as the macro call--
>the macro will insert a hard return when it expands.
>
>--jim
>
>-----Original Message-----
>From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf
>Of Richard Ristow
>Sent: Tuesday, August 14, 2007 8:46 AM
>To: [hidden email]
>Subject: Re: Trouble with a macro in a GET DATA/TYPE=OBDC context
>
>At 03:39 AM 8/14/2007, Little, Jonathon wrote:
>
> >I am having trouble getting a MACRO to work in the GET DATA context
> >below:
> >
> >The two macros are:
> >
> >!userid
> >!password
> >
> >DEFINE !userid () '6666666' !ENDDEFINE.
> >DEFINE !password () '########' !ENDDEFINE.
> >
> >The marcos need to work in the GET DATA/TYPE=OBDC statement below.
> >
> >GET DATA /TYPE=ODBC /CONNECT=
> > 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
> > /SQL = 'SELECT EST_CODE, SEQU, ARRIVAL_DATE, TRIAGE_DATE,
> >DOCTOR_SEEN'
> > /ASSUMEDSTRWIDTH=155.
>
>To make explicit what other responders have suggested: A quoted string
>like
>
> > 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'
>
>is a literal; it isn't scanned for macro expansion. I don't think you
>need to be very fancy, though; SPSS string catenation using "+" will do

>it. Like this (SPSS 15 draft output). Command "ECHO" displays the
>contents of a literal string. The first version, as you have it,
>doesn't work; the second, using "+", does:
>
>DEFINE !userid () '6666666' !ENDDEFINE.
>DEFINE !password () '########' !ENDDEFINE.
>
>ECHO 'DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;'.
>DSN=edprod;UID=!userid;PWD=!password;SERVER=edprod;
>
>ECHO 'DSN=edprod;UID=' + !userid + ';PWD=' + !password +
>';SERVER=edprod;'.
>DSN=edprod;UID=6666666;PWD=########;SERVER=edprod;