Problem with importing data from MySQL

[drfg2008]

If we import data from a MySQL-DB all variables are imported properly, except variables with BIGINT (big integer format). The result is somewhat strange. For example, if the BIGINT value is 1234, SPSS would read in 542313546579 (something like that). Even negative values suddenly appear in SPSS.

Is there anything one can do about it?


Frank

[Jon K Peck]

It looks like the number format is not being converted into the normal SPSS float representation.  If the database supports the SQL CONVERT function, try this to see if it solves the problem.
Paste your DB Wizard code and modify the SQL portion something like this, where x is the name of a BIGINT variable
SELECT a, b, c, CONVERT(x, FLOAT) AS xx ...
That would cause the conversion to happen on the database end.
Not all databases support CONVERT.  And sometimes FLOAT is called REAL, so some experimentation may be required.

HTH,

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




From:        drfg2008 <[hidden email]>
To:        [hidden email]
Date:        11/03/2011 07:23 AM
Subject:        [SPSSX-L] Problem with importing data from MySQL
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

If we import data from a MySQL-DB all variables are imported properly, except
variables with BIGINT (big integer format). The result is somewhat strange.
For example, if the BIGINT value is 1234, SPSS would read in 542313546579
(something like that). Even negative values suddenly appear in SPSS.

Is there anything one can do about it?


Frank

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

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Problem-with-importing-data-from-MySQL-tp4961015p4961015.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

[Albert-Jan Roskam]

I was thinking about using CAST, but perhaps that is the same as CONVERT, but for a different SQL dialect.

 

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: Jon K Peck <[hidden email]>
To: [hidden email]
Sent: Thursday, November 3, 2011 3:44 PM
Subject: Re: [SPSSX-L] Problem with importing data from MySQL

It looks like the number format is not being converted into the normal SPSS float representation.  If the database supports the SQL CONVERT function, try this to see if it solves the problem.
Paste your DB Wizard code and modify the SQL portion something like this, where x is the name of a BIGINT variable
SELECT a, b, c, CONVERT(x, FLOAT) AS xx ...
That would cause the conversion to happen on the database end.
Not all databases support CONVERT.  And sometimes FLOAT is called REAL, so some experimentation may be required.

HTH,

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




From:        drfg2008 <[hidden email]>
To:        [hidden email]
Date:        11/03/2011 07:23 AM
Subject:        [SPSSX-L] Problem with importing data from MySQL
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

If we import data from a MySQL-DB all variables are imported properly, except
variables with BIGINT (big integer format). The result is somewhat strange.
For example, if the BIGINT value is 1234, SPSS would read in 542313546579
(something like that). Even negative values suddenly appear in SPSS.

Is there anything one can do about it?


Frank

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

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Problem-with-importing-data-from-MySQL-tp4961015p4961015.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, we are going to check it.


This is by the way the error message, SPSS20 produces:

>Warning.  Command name: EXECUTE
>SQLFetch (getting tables) failed :[MySQL][ODBC 5.1 Driver][mysqld-5.5.8-log]

However, it produces the table (only BIGINT numbers are wrong).

Frank

[drfg2008]

we found the solution at last:


 /SQL='SELECT convert(id, decimal) AS ...



with a "convert"-statement it works.


Frank