Date:         Tue, 4 Apr 2006 12:35:10 -0700
Reply-To:     Michael Healy <healym@earthlink.net>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Michael Healy <healym@earthlink.net>
Subject:      Re: problem with SAVE TRANSLATE
Comments: To: RD <acatram@yahoo.com>
In-Reply-To:  <20060404184353.81889.qmail@web52514.mail.yahoo.com>
Content-type: text/plain; charset="US-ASCII"

Hi Catalin,
The error message indicates that SPSS is generating incorrect SQL syntax so
you should try the /append solution, even if the table is blank.  My tables
did not contain data either and who knows what goes on inside these black
box procedures.

Another option is to bypass the ODBC connection and save your data to a text
file and read it into MySQL using the LOAD DATA LOCAL INFILE command.
You'll need to convert your numeric variables to text if they have missing
values so that you can assign null values of "\n".

Convert numeric vars to text (suggested by Kevin Gray):
COMPUTE v99 = LTRIM(string(YOUR_NUMBER_VARIABLE_NAME,F11.2)) .
EXECUTE .
* my addition (untested):.
If missing(YOUR_NUMBER_VARIABLE_NAME) v99 = "\n".

QED,
Mike

> From: RD <acatram@yahoo.com>
> Date: Tue, 4 Apr 2006 11:43:53 -0700 (PDT)
> To: Michael Healy <healym@earthlink.net>, SPSSX-L@LISTSERV.UGA.EDU
> Subject: Re: problem with SAVE TRANSLATE
>
> I don't think the problem is with the /replace --> the
> table is blank.
>
> Any other thoughts?
>
> Thank you,
> Catalin V. Acatrinei
>
> --- Michael Healy <healym@earthlink.net> wrote:
>
>> Hi, I've also seen this message using MySQL.  I
>> think it's a bug related to
>> the '/replace' option.  You could try working around
>> this problem by adding
>> /SQL statements in which you drop the table then
>> rebuild the table and then
>> add your data to the new table using the /append
>> option.  For what it's
>> worth though, I found that after fixing this
>> problem, SAVE TRANSLATE /type =
>> ODBC was unusable for large datasets as rows were
>> inserted at a rate of
>> about 100/2 sacs.
>> Mike
>>
>>> From: RD <acatram@yahoo.com>
>>> Reply-To: RD <acatram@yahoo.com>
>>> Newsgroups: bit.listserv.spssx-l
>>> Date: Tue, 4 Apr 2006 03:55:14 -0700
>>> To: SPSSX-L@LISTSERV.UGA.EDU
>>> Subject: problem with SAVE TRANSLATE
>>>
>>> Hi all,
>>>
>>> I want to export from SPSS14 to MySQL; I am using
>> the
>>> MySQL odbc, and works for the first 34 cases;
>>> The command is like:
>>>
>>> SAVE TRANSLATE
>>>       /TYPE=ODBC
>>>
>> /connect='DSN=LocalSource;UID=user;PWD=password'
>>>       /table="table1"
>>>       /unselect=retain
>>>       /replace
>>>       /map.
>>>
>>> However, at case #35, it dies with the following
>>> message:
>>> ---------------------
>>>> Error # 6491.  Text: Case #39 has been dropped
>>>> Insert record failed
>>>> This command not executed.
>>>
>>>> [MySQL][ODBC 3.51 Driver][mysqld-5.0.19-nt]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 '' at line 1
>>> C
>>>
>>>
>>>
>>>
>>>> Error # 6487
>>>> Write request failed - couldn't write any data.
>>>
>>>
>>>> Error # 6452
>>>> An error occurred while the output file was being
>>> written.
>>> ------------------------
>>>
>>> Can anybody point me to the right direction on how
>> to
>>> solve this? Or where/how I can see more detailed
>> log
>>> information?
>>>
>>> If I erase case # 35, then it goes up to #39,
>> where it
>>> dies again. I look at the data and I see no reason
>> why
>>> this is happening.
>>>
>>> Thank you.
>>>
>>> Best regards,
>>> Catalin V. Acatrinei
>>>
>>>
>>>
>>>
>>> __________________________________________________
>>> Do You Yahoo!?
>>> Tired of spam?  Yahoo! Mail has the best spam
>> protection around
>>> http://mail.yahoo.com
>>
>>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com