SPSS + MySQL (more than one statement)

[drfg2008]

we are trying to figure out how to do multiple SQL-statements (MySQL). The DB is set on "allow multiple statements", so the problem obviously lies in our lack of programming ability:

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=Stephanies Datenbank;'
  /SQL=' CREATE TABLE T2 AS SELECT * FROM ebet.program_Event limit 0, 100;'
  /SQL='SELECT * FROM T2;'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.


The error message:
>Error.  Command name: GET DATA
>Unrecognized keyword is found : "SQL".
>Execution of this command stops.
 
>Error # 105.  Command name: EXECUTE
>This command is not valid before a working file has been defined.
>Execution of this command stops.


each statement itself works:

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=Stephanies Datenbank;'
  /SQL=' CREATE TABLE T4 AS SELECT * FROM ebet.program_Event limit 0, 199'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.



GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=Stephanies Datenbank;'
  /SQL=' SELECT * FROM T4;'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME DataSet1 WINDOW=FRONT.





need some help, thanks

[Albert-Jan Roskam]

I think the second sql statement needs to start with a space, as the two queries are merged together.

 

Cheers!!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: drfg2008 <[hidden email]>
To: [hidden email]
Sent: Wednesday, November 9, 2011 2:01 PM
Subject: [SPSSX-L] SPSS + MySQL (more than one statement)

we are trying to figure out how to do multiple SQL-statements (MySQL). The DB
is set on "allow multiple statements", so the problem obviously lies in our
lack of programming ability:

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=Stephanies Datenbank;'
  /SQL=' CREATE TABLE T2 AS SELECT * FROM ebet.program_Event limit 0, 100;'
  /SQL='SELECT * FROM T2;'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.


The error message:
>Error.  Command name: GET DATA
>Unrecognized keyword is found : "SQL".
>Execution of this command stops.

>Error # 105.  Command name: EXECUTE
>This command is not valid before a working file has been defined.
>Execution of this command stops.


each statement itself works:

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=Stephanies Datenbank;'
  /SQL=' CREATE TABLE T4 AS SELECT * FROM ebet.program_Event limit 0, 199'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.



GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=Stephanies Datenbank;'
  /SQL=' SELECT * FROM T4;'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME DataSet1 WINDOW=FRONT.





need some help, thanks


-----
Dr. Frank Gaeth
FU-Berlin

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/SPSS-MySQL-more-than-one-statement-tp4977610p4977610.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

[drfg2008]

we tried to find out, what you mean by space.

[Albert-Jan Roskam]

I meant instead of:

  /SQL='SELECT * FROM T2;'

You let the statement precede with one space:

  /SQL=' SELECT * FROM T2;'

 
I used multiple SQL statements in GET DATA the other day. It should work.

Cheers!!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: drfg2008 <[hidden email]>
To: [hidden email]
Sent: Wednesday, November 9, 2011 5:07 PM
Subject: Re: [SPSSX-L] SPSS + MySQL (more than one statement)

we tried to find out, what you mean by space.



-----
Dr. Frank Gaeth
FU-Berlin

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/SPSS-MySQL-more-than-one-statement-tp4977610p4978161.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

[drfg2008]

Thanks Albert-Jan,

however it will not work.


Frank