Integration of MySQL Procedure

[SD]

Hi,

I'm trying to integrate some MySQL-Code into SPSS.
In general, it works. But I have problems with integrating the following SQL-Code:

DELIMITER $$
CREATE Procedure `test`()
BEGIN
  create table abc(
  a int
  );
  SET @i=0;
  while(@i<=100)
  do
    insert abc(a) values (@i);
    Set @i=@i+1;
  end while;
END $$

(I know I don't need a procedure for the code above. I have simplified the original code.)

If I try to integrate it with

GET DATA
  /TYPE=ODBC
  /CONNECT=' DSN=myDB;'
  /SQL=' DELIMITER $$ '+
' CREATE Procedure test() '+
' BEGIN '+
' create table abc( '+
' a int '+
'   ); '+
'   SET @i=0; '+
'   while(@i<=100) '+
'   do '+
'     insert abc(a) values (@i); '+
'     Set @i=@i+1; '+
'   end while; '+
' END $$ '
 /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.

SPSS says, that there is some Syntax-Error in my SQL-Code, but running the code (not with SPSS) works.

Thank you in advance.
SD

edit: deleted a "+" at the end which was wrong (but didn't solve the problem)

[drfg2008]

SPSS seems to have problems with MySQL PROCEDURE . Isn't it possible to run MySQL procedures in SPSS?


this is the error message:

>Warning.  Command name: GET DATA
>SQLExecDirect failed :[MySQL][ODBC 5.1 Driver][mysqld-5.5.8-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$  CREATE Procedure timebounds1022sd()  BEGIN  create table intervall' at line 1


On MySQL the syntax works perfectly.
Frank

[Jon K Peck]

A SqlExecDirect failure is an issue between the ODBC driver and the database.  It is not under the control of SPSS.  The SQL text as run in Statistics has to be quoted on each line.  Before it is sent to the driver, all the quoted literals are joined without any end of line character, so if a line does not end with a blank inside the literal, the resulting joined strings might have a syntax error.

There is, by the way, a custom dialog that will take an arbitrary of SQL syntax and write the quoted lines correctly, including escaping interior quotes.  This can be useful if you have a large block of SQL created outside the Database Wizard.  It is called quotetext and can be found in the Utilities Collection on the SPSS Community website.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        drfg2008 <[hidden email]>
To:        [hidden email]
Date:        04/04/2012 01:50 AM
Subject:        Re: [SPSSX-L] Integration of MySQL Procedure
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

@David Marso
@Jon

SPSS seems to have problems with MySQL PROCEDURE . Isn't it possible to run
MySQL procedures in SPSS?


this is the error message:

/>Warning.  Command name: GET DATA
>SQLExecDirect failed :[MySQL][ODBC 5.1 Driver][mysqld-5.5.8-log]You have an
error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'DELIMITER $$  CREATE
Procedure timebounds1022sd()  BEGIN  create table intervall' at line 1/


On MySQL the syntax works perfectly.
Frank

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

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Integration-of-MySQL-Procedure-tp5614496p5617116.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

[Tierney, Mary Lou]

I am PTOB today Wednesday 4 April returning to the office on Friday 6 April by 1 p.m. 

 

For assistance with the MITRE Innovation Program please contact the [hidden email]

 

For assistance with Project/Portfolio Pages or Discover, please contact the [hidden email]

 

For assistance with other Innovation Zone sites, such as CI&T InZone, please contact [hidden email].

 

 

Regards,

Mary Lou

[David Marso]

CREATE Procedure `test`() != ' CREATE Procedure test() '+,...
Just a guess.

SD wrote

Hi,

I'm trying to integrate some MySQL-Code into SPSS.
In general, it works. But I have problems with integrating the following SQL-Code:

DELIMITER $$
CREATE Procedure `test`()
BEGIN
  create table abc(
  a int
  );
  SET @i=0;
  while(@i<=100)
  do
    insert abc(a) values (@i);
    Set @i=@i+1;
  end while;
END $$

(I know I don't need a procedure for the code above. I have simplified the original code.)

If I try to integrate it with

GET DATA
  /TYPE=ODBC
  /CONNECT=' DSN=myDB;'
  /SQL=' DELIMITER $$ '+
' CREATE Procedure test() '+
' BEGIN '+
' create table abc( '+
' a int '+
'   ); '+
'   SET @i=0; '+
'   while(@i<=100) '+
'   do '+
'     insert abc(a) values (@i); '+
'     Set @i=@i+1; '+
'   end while; '+
' END $$ '
 /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.

SPSS says, that there is some Syntax-Error in my SQL-Code, but running the code (not with SPSS) works.

Thank you in advance.
SD

edit: deleted a "+" at the end which was wrong (but didn't solve the problem)

[SD]

Thanks for your answers.

The solution a colleague figured out: the Delimiter had to be deleted.
SQL needs them, SPSS seems not to be able to work with them.