FW: Message ("There is no LISTSERV list on this server. Try...")

[Cathy Underhill]

I apologize in advance for posting this to the list -- I am having
trouble changing my list options to digest, and am getting an error
message.

Can someone help with this?


Thanks (I am going on holidays for 3 weeks and do not want to get
individual messages during that time period, but digests instead; also
want to change my email address)

-----Original Message-----
From: L-Soft list server at UGA (1.8d)
[mailto:[hidden email]]
Sent: August 4, 2006 10:04 AM
To: Cathy Underhill
Subject: Message ("There is no LISTSERV list on this server. Try...")


There is no  LISTSERV list on this  server. Try sending a  "LIST"
command to get a description of all the lists available from
[hidden email].

[Tesiny, Ed]

Send a msg to [hidden email]
No Subject
In the body of the msg: INFO REFCARD
This will give you a list of Listserv commands

Ed Tesiny
[hidden email]

> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]]
> On Behalf Of Cathy Underhill
> Sent: Friday, August 04, 2006 10:17 AM
> To: [hidden email]
> Subject: FW: Message ("There is no LISTSERV list on this
> server. Try...")
>
> I apologize in advance for posting this to the list -- I am having
> trouble changing my list options to digest, and am getting an error
> message.
>
> Can someone help with this?
>
>
> Thanks (I am going on holidays for 3 weeks and do not want to get
> individual messages during that time period, but digests instead; also
> want to change my email address)
>
> -----Original Message-----
> From: L-Soft list server at UGA (1.8d)
> [mailto:[hidden email]]
> Sent: August 4, 2006 10:04 AM
> To: Cathy Underhill
> Subject: Message ("There is no LISTSERV list on this server. Try...")
>
>
> There is no  LISTSERV list on this  server. Try sending a  "LIST"
> command to get a description of all the lists available from
> [hidden email].
>

[Jeff-125]

Accessing databases via ODBC is completely new to me, and our computer IT
person (who knows more about databases) knows nothing about SPSS.
Together  I think that we figured most of this out, but we still have one
question/problem.

I have a database stored on a MySQL software/database/what-ever-it's-called
that is used to store/collect data entered from another source - our IT guy
is handling that, so I may not be using the correct terminology.

Using SPSS 14, I can go to the menu, select opendatabase, and follow the
rest of the instructions to get access to the required database. ...very
cool, so far so good.

What neither of us knows is whether I can use SPSS to modify those data
tables within the database - and save back the modified-data to those
tables. The standard save commands only give me the option to save to
standard .sav files (already very familiar with that).

Could someone point me in the right direction about whether SPSS can
directly manipulate/store back to an ODBC data source, or whether SPSS can
only retrieve it and then store to a regular .sav or other file afterward?

Thanks in advance



Jeff

[Oliver, Richard]

The answer is a qualified yes, you can -- but not through the GUI. You need to use command syntax, specifically SAVE TRANSLATE /TYPE=ODBC, using the SQL subcommand to specify the necessary SQL statements for writing back to the database. Typically, you will need to write the modified data to a new table, and then use SQL statements to get the data from the new table back into the original table. There are a few examples in the Command Syntax Reference. In SPSS 15 you will be able to do this all through an Export to Database Wizard, without needing to know any SQL.
 
Now for the qualification part: I don't know if MySQL is supported.

________________________________

From: SPSSX(r) Discussion on behalf of Jeff
Sent: Fri 8/4/2006 6:52 PM
To: [hidden email]
Subject: ODBC Data Access Question



Accessing databases via ODBC is completely new to me, and our computer IT
person (who knows more about databases) knows nothing about SPSS.
Together  I think that we figured most of this out, but we still have one
question/problem.

I have a database stored on a MySQL software/database/what-ever-it's-called
that is used to store/collect data entered from another source - our IT guy
is handling that, so I may not be using the correct terminology.

Using SPSS 14, I can go to the menu, select opendatabase, and follow the
rest of the instructions to get access to the required database. ...very
cool, so far so good.

What neither of us knows is whether I can use SPSS to modify those data
tables within the database - and save back the modified-data to those
tables. The standard save commands only give me the option to save to
standard .sav files (already very familiar with that).

Could someone point me in the right direction about whether SPSS can
directly manipulate/store back to an ODBC data source, or whether SPSS can
only retrieve it and then store to a regular .sav or other file afterward?

Thanks in advance



Jeff

[Jeff-125]

At 05:11 PM 8/4/2006, you wrote:
>In SPSS 15 you will be able to do this all through an Export to Database
>Wizard, without needing to know any SQL.
>
>Now for the qualification part: I don't know if MySQL is supported.


Okay, I have most of this. I copied over the /Connect=  connection string
provided by pasting the syntax generated by the opendatabase wizard (which
did give me access to the database and did properly bring in the data to
spss 14) into the syntax below. (the syntax below was altered to take out
my real username, password, and serverID, and database name.)

When I did so, I received the error in the output window listed below. Can
this be due to the possibility that MySQL is not supported? ...it can read
from MySQL but possibly not write to it? I tried with both the encrypted
password given by the paste command and with a plain text one, but got the
same error. I also got the same error when I altered the port to 3306 from
the pasted 0.

If this is the likely source of the error, what type of database engines
(or whatever they are called) are supported? How about Microsoft's Express
SQL? (I'm not speaking about excel, access, etc., but rather databases that
can be used with custom visual basic code)



SAVE TRANSLATE
/CONNECT="DATABASE=MyDataBase;DESCRIPTION=Connection to the Server's"+
  " MySQL;DSN=Jeff'sServerMySQL;OPTION=0;PWD=MyEncrypted-password"
   "PORT=3306;SERVER=MyServerIP;UID=MyUserID"
/TABLE="survey_info'"
/TYPE=ODBC.


 >Error # 6492
 >The ODBC subsystem has issued an error which prevents the processing of
 >SAVE TRANSLATE ODBC request.
 >This command not executed.

 >[MySQL][ODBC 3.51 Driver]Access denied for user 'Jeff'@'XXXX.edu'
 >(using password: YES)

 >Error # 6452
 >An error occurred while the output file was being written.





Jeff

[Oliver, Richard]

If the table specified on the TABLE subcommand already exists, you cannot overwrite it unless you also include the REPLACE subcommand (which completely replaces the table with data from SPSS), which might possibly be the source of the "access denied" message. Alternatively, if you don't have write-access to the database, then even with the REPLACE subcommand you'll probably get some kind of "access denied" error.
 
As for supported databases, any of those for which the SPSS Data Access Pack installs ODBC drivers should be supported; for others it may be a matter of trying them and seeing what happens (provided you have an ODBC driver for that database). But since you're getting a fairly specific "access denied" message, I'm guessing this may be a permissions issue, which means the first step is to find out if you have write-access permission to the database.

________________________________

From: Jeff [mailto:[hidden email]]
Sent: Fri 8/4/2006 7:37 PM
To: [hidden email]
Cc: Oliver, Richard
Subject: RE: ODBC Data Access Question



At 05:11 PM 8/4/2006, you wrote:
>In SPSS 15 you will be able to do this all through an Export to Database
>Wizard, without needing to know any SQL.
>
>Now for the qualification part: I don't know if MySQL is supported.


Okay, I have most of this. I copied over the /Connect=  connection string
provided by pasting the syntax generated by the opendatabase wizard (which
did give me access to the database and did properly bring in the data to
spss 14) into the syntax below. (the syntax below was altered to take out
my real username, password, and serverID, and database name.)

When I did so, I received the error in the output window listed below. Can
this be due to the possibility that MySQL is not supported? ...it can read
from MySQL but possibly not write to it? I tried with both the encrypted
password given by the paste command and with a plain text one, but got the
same error. I also got the same error when I altered the port to 3306 from
the pasted 0.

If this is the likely source of the error, what type of database engines
(or whatever they are called) are supported? How about Microsoft's Express
SQL? (I'm not speaking about excel, access, etc., but rather databases that
can be used with custom visual basic code)



SAVE TRANSLATE
/CONNECT="DATABASE=MyDataBase;DESCRIPTION=Connection to the Server's"+
  " MySQL;DSN=Jeff'sServerMySQL;OPTION=0;PWD=MyEncrypted-password"
   "PORT=3306;SERVER=MyServerIP;UID=MyUserID"
/TABLE="survey_info'"
/TYPE=ODBC.


 >Error # 6492
 >The ODBC subsystem has issued an error which prevents the processing of
 >SAVE TRANSLATE ODBC request.
 >This command not executed.

 >[MySQL][ODBC 3.51 Driver]Access denied for user 'Jeff'@'XXXX.edu'
 >(using password: YES)

 >Error # 6452
 >An error occurred while the output file was being written.





Jeff

[Jeff-125]

At 06:09 PM 8/4/2006, Richard O. wrote:
>As for supported databases, any of those for which the SPSS Data Access
>Pack installs ODBC drivers should be supported; for others it may be a
>matter of trying them and seeing what happens (provided you have an ODBC
>driver for that database). But since you're getting a fairly specific
>"access denied" message, I'm guessing this may be a permissions issue,
>which means the first step is to find out if you have write-access
>permission to the database.



...got it - well almost (and it only took 3-4 hours :( ). I messed around
with the syntax and took out a good bit of the options that the
wizard/menu/paste-syntax put in (for opendatabase). The Save Translate
wouldn't work with the encrypted password that the wizard/menu/paste wrote.
After all of this and putting in the password in plain text, it worked in
terms of being able to save a new table or replace an existing one. I
couldn't get the menu/wizard/opendatabase to work with SPSS 13 on two
different machines, one running W2000 and the other XP. Each time it would
crash. When I uninstalled v13 and installed v14, it worked. I eventually
tried the syntax below on the machine that still had v13 and that syntax
worked, it's just the menu that crashes v13.

However, while spss 13 and 14 will read from the database tables just fine,
writing is problematic, at least if I wish to keep the exact same table
structure. SPSS doesn't save information about "primary keys" when reading,
which are irrelevant to spss, but critical to some relational database
tables. ...and apparently if I read an integer from the table, it works
just find in spss, but if I write it back to MySQL, it messes up unless the
integer is specified as length 11 with no decimal places in spss - it just
writes all zeros. It also does something to the string variables so that
other software doesn't recognize them in the same way as they were before.

What I'm doing is attempting to read data from a relational MySQL database
that  is collecting data from some custom survey software we wrote, in
order to do the statistical analyses on the collected data in spss (since
that data does not need written back to MySQL, this part works great). I
was hoping, however, to also use spss to modify some basic existing
relational tables that handle the ordering of the questions used by the
survey software, so those modification need to be written back. All of the
necessary tasks are simple in SPSS, but I can't properly write back the
modified table without completely messing up the primary key designation on
the MySQL table.

...anyone know if there is some code or other method that would solve the
problem?

** to get **.
Get Data
/Type=ODBC
/Connect='DSN=ServerMySQL;PWD=MyPassWord;UID=MyUserID'
/Sql = 'Select  * From  testtable3'  .


** To save **.
*** but this will erase the primary key from the original table and will
mess up the MySQL table's structure unless the numeric variables are
specified perfectly **.
Save Translate
/Connect='DSN=ServerMySQL;PWD=MyPassWord;UID=MyUserID'
/Table = "testtable3"
/Replace
/Type=ODBC.

[Oliver, Richard]

In SPSS 14 (but not 13) you can use SQL statements in SAVE TRANSLATE to define the table structure, including key variables and variable type and width. A simple example that just replaces an existing table:
 
SAVE TRANSLATE /TYPE=ODBC
 /CONNECT='your connection string'
 /SQL='DROP TABLE Table1'
 /SQL='CREATE TABLE Table1(keyvar double, numvar double, stringvar varchar(50), ' +
  'primary key(keyvar))'
 /REPLACE
 /TABLE='temp'
 /KEEP=keyvar, numvar, stringvar
 /SQL='INSERT INTO Table1(keyvar, numvar, stringvar) SELECT * FROM temp'
 /SQL='DROP TABLE temp'.
 
It's also possible to replace values to existing fields in a table or add new fields to an existing table.

________________________________

From: Jeff [mailto:[hidden email]]
Sent: Sat 8/5/2006 11:24 AM
To: [hidden email]
Cc: Oliver, Richard
Subject: Re: ODBC Data Access Question



At 06:09 PM 8/4/2006, Richard O. wrote:
>As for supported databases, any of those for which the SPSS Data Access
>Pack installs ODBC drivers should be supported; for others it may be a
>matter of trying them and seeing what happens (provided you have an ODBC
>driver for that database). But since you're getting a fairly specific
>"access denied" message, I'm guessing this may be a permissions issue,
>which means the first step is to find out if you have write-access
>permission to the database.



...got it - well almost (and it only took 3-4 hours :( ). I messed around
with the syntax and took out a good bit of the options that the
wizard/menu/paste-syntax put in (for opendatabase). The Save Translate
wouldn't work with the encrypted password that the wizard/menu/paste wrote.
After all of this and putting in the password in plain text, it worked in
terms of being able to save a new table or replace an existing one. I
couldn't get the menu/wizard/opendatabase to work with SPSS 13 on two
different machines, one running W2000 and the other XP. Each time it would
crash. When I uninstalled v13 and installed v14, it worked. I eventually
tried the syntax below on the machine that still had v13 and that syntax
worked, it's just the menu that crashes v13.

However, while spss 13 and 14 will read from the database tables just fine,
writing is problematic, at least if I wish to keep the exact same table
structure. SPSS doesn't save information about "primary keys" when reading,
which are irrelevant to spss, but critical to some relational database
tables. ...and apparently if I read an integer from the table, it works
just find in spss, but if I write it back to MySQL, it messes up unless the
integer is specified as length 11 with no decimal places in spss - it just
writes all zeros. It also does something to the string variables so that
other software doesn't recognize them in the same way as they were before.

What I'm doing is attempting to read data from a relational MySQL database
that  is collecting data from some custom survey software we wrote, in
order to do the statistical analyses on the collected data in spss (since
that data does not need written back to MySQL, this part works great). I
was hoping, however, to also use spss to modify some basic existing
relational tables that handle the ordering of the questions used by the
survey software, so those modification need to be written back. All of the
necessary tasks are simple in SPSS, but I can't properly write back the
modified table without completely messing up the primary key designation on
the MySQL table.

...anyone know if there is some code or other method that would solve the
problem?

** to get **.
Get Data
/Type=ODBC
/Connect='DSN=ServerMySQL;PWD=MyPassWord;UID=MyUserID'
/Sql = 'Select  * From  testtable3'  .


** To save **.
*** but this will erase the primary key from the original table and will
mess up the MySQL table's structure unless the numeric variables are
specified perfectly **.
Save Translate
/Connect='DSN=ServerMySQL;PWD=MyPassWord;UID=MyUserID'
/Table = "testtable3"
/Replace
/Type=ODBC.