[R] mysqlWriteTable . error in your SQL syntax?

[Vladimir Morozov]

 I have partialy figured out the problem "condition" might be some intrenal MySQL function/variable and  can't be use as column name directly.

-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Vladimir Morozov
Sent: Thursday, March 04, 2010 10:25 AM
To: 'r-help at r-project.org'
Subject: [R] mysqlWriteTable . error in your SQL syntax?

Hi,

Can somebody advice on weird mysqlWriteTable bug.

> mysqlWriteTable(conn, 'comparison',design2, row.names = F,
> overwrite=T)

Error in mysqlExecStatement(conn, statement, ...) :

RS-DBI driver: (could not run statement: 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 '"condition" text,

treatment text,

condition2 text,

conditionNum double,

impo' at line 12)

[1] FALSE

Warning message:

In mysqlWriteTable(conn, "comparison", design2, row.names = F, overwrite = T) :

could not create table: aborting mysqlWriteTable



The problem seems to be in double qoutes:



syntax to use near '"condition" text,



I have tracked down it to the problem with specific column in my frame

If  "condition" clumn is exluded, it works fine

> mysqlWriteTable(conn, 'comparison',design2[,-10], row.names = F,
> overwrite=T)

[1] TRUE



> class(design2[,10])

[1] "character"

> packageDescription('RMySQL')

Package: RMySQL

Version: 0.7-4

Date: 2009-04-07

Title: R interface to the MySQL database

Author: David A. James and Saikat DebRoy

Maintainer: Jeffrey Horner <jeff.horner at vanderbilt.edu>

Description: Database interface and MySQL driver for R. This version

complies with the database interface definition as implemented

in the package DBI 0.2-2.

LazyLoad: true

Depends: R (>= 2.8.0), methods, DBI (>= 0.2-2), utils

License: GPL-2

URL: http://biostat.mc.vanderbilt.edu/RMySQL

Collate: S4R.R zzz.R MySQLSupport.R dbObjectId.R MySQL.R

Packaged: Tue Apr 7 15:19:44 2009; hornerj

Repository: CRAN

Date/Publication: 2009-04-14 17:26:56

Built: R 2.10.1; x86_64-unknown-linux-gnu; 2010-01-16 19:42:24 UTC;

unix







Vladimir Morozov
Sr. Computational Biologist
ALS Therapy Development Institute
215 First Street, Cambridge MA, 02142
Phone: 617-441-7242
www.als.net<http://www.als.net/>
Want to help stop ALS? Become an ALS Ambassador and take action. Learn more online at www.als.net/ambassador<http://www.als.net/ambassador>



***************************************************************************************
The information contained in this electronic message is ...{{dropped:30}}