http://blog.gmane.org/gmane.comp.lang.r.db hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.lang.r.db/730 <pre>I would 't be optimistic about the &lt; at &gt;variable persisting from one dbSendQuery call to the next. But maybe it will. However, I think it likely you can achieve the same result on the R side, i.e., by defining an R variable to take the place of the &lt; at &gt;variable, and then using paste() to construct the subsequent queries. Other than that, the rest of your steps look just like what I have done from time to time. My guess would be like Sean's, a permissions issue. I use dbExistsTable() and dbDropTable() instead of dbSendQuery() when I need to drop a table if it exists. -Don -- Don MacQueen Lawrence Livermore National Laboratory 7000 East Ave., L-627 Livermore, CA 94550 925-423-1062 From: Mark Moon &lt;mmoon-UCSEKdMUFqx8VGe09Uz+lhL4W9x8LtSr&lt; at &gt;public.gmane.org&lt;mailto:mmoon-UCSEKdMUFqx8VGe09Uz+lhL4W9x8LtSr&lt; at &gt;public.gmane.org&gt;&gt; To: Donald MacQueen &lt;macqueen1-i2BcT+NCU+M&lt; at &gt;public.gmane.org&lt;mailto:macqueen1-i2BcT+NCU+M&lt; at &gt;public.gmane.org&gt;&gt; Cc: "r-sig-db-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org&lt;mailto:r-sig-db-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org&gt;" &lt;r-sig-db-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org&lt;mailto:r-sig-db-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org&gt;&gt; Subject: Re: [R-sig-DB] RMySQL frustrations Thanks, all. I am trying to do the following: - set an &lt; at &gt;variable, - drop a table if exists - create a temp table from a select - update the temp table - do a select on the updated, temp table I'm now trying to run each as separate calls to dbSendQuery(). But, I'm getting allkinds of errors, now of the flavor, Error in mysqlExecStatement(conn, statement, ...): RS-DBI driver (could not run statement .... DROP command denied to user .. Access denied for user .. UPDATE command denied to user .. Not sure why I don't have access to DROP, UPDATE, nor can create a temporary table. Any help will be GREATLY appreciated! Mark On 05/17/2012 11:23 AM, MacQueen, Don wrote: I'm no expert, but I believe they are designed to pass one SQL expression at a time to the database. (this is not the same as "simple, one line queries"; I frequently use multi-line moderately complex queries with sub-selects and such). If you are talking about multiple queries of the form mydat &lt;- dbGetQuery(con, "select ... ; select ... ; select ...") then I would argue this makes no sense to even try. Any R function returns a only single object, but this would be asking it to return several objects. Sure, we could ask the people who wrote the package to recognize such a case, run each query separately, and return a list object containing their individual results -- but I think that would be asking too much since it is simple to split such a set of queries into multiple calls. If you are talking about multiple "queries" of the form dbGetQuery(con, "create table as ... ; create table as ... ; select ...") that might be a little more reasonable to expect, but even so, it's easy enough to break this into a sequence of uses of dbSendQuery() followed by a final dbGetQuery(). I have done this kind of thing. [or dbSendUpdate() in the case of JDBC] I'm trying to imagine a use of multiple statements separated by ";" where it's truly essential that they all be done in a single R command. -Don -- -- Mark Moon Managing Director and Principal Ross Institutional Investors 33 Whitney Avenue New Haven, CT 06510 cell (805) 657-9504 home (805) 491-2826 [[alternative HTML version deleted]] _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> MacQueen, Don 2012-05-18T15:59:55 http://permalink.gmane.org/gmane.comp.lang.r.db/729 <pre>You need to read some basic R documentation. 'R' is not a 'bash script'. not even an sh script. But the front-end script allows e.g. R -d gdb to run the R executable under gdb. </pre> Prof Brian Ripley 2012-05-18T05:50:04 http://permalink.gmane.org/gmane.comp.lang.r.db/728 <pre>We are trying to attach R to our ODBC driver via RODBC. Unfortunately we're getting Segmentation faults in odbcConnect (error detailed below). We would like to attach a debugger to whatever process is communicating with UnixODBC/our driver, but are not sure where to start. R itself seems to be a large bash script, ideally we could inject "gdb --args" before the execution of some process in that script to get the desired results. Any guidance is much appreciated. *** caught segfault *** address 0x100000abb, cause 'memory not mapped' Traceback: 1: .Call(C_RODBCDriverConnect, as.character(connection), id, as.integer(believeNRows)) 2: odbcDriverConnect(st, ...) 3: odbcConnect("sql1010odbc") </pre> Christopher A. Simon 2012-05-17T20:06:22 http://permalink.gmane.org/gmane.comp.lang.r.db/727 <pre> Hi, Mark. Access to those features is a function of the mysql server. You will need to explicitly GRANT those privileges to your user (or have your DBA/admin do it) before you can perform those functions. This isn't an RMySQL issue, I don't think. Sean [[alternative HTML version deleted]] _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Sean Davis 2012-05-17T19:25:00 http://permalink.gmane.org/gmane.comp.lang.r.db/726 <pre>Thanks, all. I am trying to do the following: - set an &lt; at &gt;variable, - drop a table if exists - create a temp table from a select - update the temp table - do a select on the updated, temp table I'm now trying to run each as separate calls to dbSendQuery(). But, I'm getting allkinds of errors, now of the flavor, Error in mysqlExecStatement(conn, statement, ...): RS-DBI driver (could not run statement .... DROP command denied to user .. Access denied for user .. UPDATE command denied to user .. Not sure why I don't have access to DROP, UPDATE, nor can create a temporary table. Any help will be GREATLY appreciated! Mark On 05/17/2012 11:23 AM, MacQueen, Don wrote: </pre> Mark Moon 2012-05-17T18:43:20 http://permalink.gmane.org/gmane.comp.lang.r.db/725 <pre>I'm no expert, but I believe they are designed to pass one SQL expression at a time to the database. (this is not the same as "simple, one line queries"; I frequently use multi-line moderately complex queries with sub-selects and such). If you are talking about multiple queries of the form mydat &lt;- dbGetQuery(con, "select ... ; select ... ; select ...") then I would argue this makes no sense to even try. Any R function returns a only single object, but this would be asking it to return several objects. Sure, we could ask the people who wrote the package to recognize such a case, run each query separately, and return a list object containing their individual results -- but I think that would be asking too much since it is simple to split such a set of queries into multiple calls. If you are talking about multiple "queries" of the form dbGetQuery(con, "create table as ... ; create table as ... ; select ...") that might be a little more reasonable to expect, but even so, it's easy enough to break this into a sequence of uses of dbSendQuery() followed by a final dbGetQuery(). I have done this kind of thing. [or dbSendUpdate() in the case of JDBC] I'm trying to imagine a use of multiple statements separated by ";" where it's truly essential that they all be done in a single R command. -Don </pre> MacQueen, Don 2012-05-17T18:23:41 http://permalink.gmane.org/gmane.comp.lang.r.db/724 <pre> What kinds of queries are you trying to do that require multiple statements separated by ";"? It seems these could be simply separated into individual queries without loss of generality? Sean [[alternative HTML version deleted]] _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Sean Davis 2012-05-17T15:06:02 http://permalink.gmane.org/gmane.comp.lang.r.db/723 <pre>Getting frustrated using R with MySQL (on Debian Squeeze). I can connect to mySQL database and I can execute simple queries. But, having various difficulties executing queries with multiple statements separated by ";". Set the client.flag to CLIENT_MULTI_STATEMENTS but still having difficulties. Questions: are the RMySQL and RODBC packages really meant just for simple, one line queries? Can anyone point me to where I might learn how to execute more complicated queries with in R? Thanks! Mark _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Mark Moon 2012-05-17T14:59:38 http://permalink.gmane.org/gmane.comp.lang.r.db/722 <pre>Hi, Did you check the dependency of the error on 32 bit version and 64 bit version of R? I assume you used a 32 bit version on Windows XP and now perhaps using the 64 bit version. If so, you might try if it works on the 32 bit version of R (which usually should run on windows 7 as well). On 2012/05/07, at 9:24, Jadhav, Alok wrote: _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> NISHIYAMA Tomoaki 2012-05-07T14:20:30 http://permalink.gmane.org/gmane.comp.lang.r.db/721 <pre>Hello, when I query a MySQL database using the commands con &lt;- dbConnect(MySQL(), user=dbuser, password=dbpasswd, dbname=dbname, host=dbhost) df &lt;- dbGetQuery(con, sql) I get the following message when a table contains the timestamp type: RS-DBI driver warning: (unrecognized MySQL field type 7 in column 1 imported as character) I googled and found a post on this mailing list from 2006: https://stat.ethz.ch/pipermail/r-sig-db/2006q3/000221.html Obviously this is still not fixed. Are there any plans to do so? Thanks, Andreas R version 2.15.0 (2012-03-30) Platform: i686-pc-linux-gnu (32-bit) locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C [3] LC_TIME=de_CH.UTF-8 LC_COLLATE=en_US.UTF-8 [5] LC_MONETARY=de_CH.UTF-8 LC_MESSAGES=en_US.UTF-8 [7] LC_PAPER=C LC_NAME=C [9] LC_ADDRESS=C LC_TELEPHONE=C [11] LC_MEASUREMENT=de_CH.UTF-8 LC_IDENTIFICATION=C attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] RMySQL_0.8-0 DBI_0.2-5 _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Andreas Voellenklee 2012-05-09T14:05:20 http://permalink.gmane.org/gmane.comp.lang.r.db/720 <pre> On May 8, 2012, at 5:48 AM, Jadhav, Alok wrote: Hi Alok, Glad that you got it resolved. If you have not yet, you should send in a bug report to Sybase. Regards, Marc _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Marc Schwartz 2012-05-08T12:08:58 http://permalink.gmane.org/gmane.comp.lang.r.db/719 <pre>Hi Marc, After investigation, I found out the issue. The issue is with the precision of numeric column which is not handled properly by ASE driver on windows 7. So one of the columns in the query was the culprit. I found the correct query to get the data. Issue was plainly with sybase driver only. Regards, Alok Please follow the attached hyperlink to an important disclaimer http://www.credit-suisse.com/asiapac/legal/securities/ -----Original Message----- From: Marc Schwartz [mailto:marc_schwartz-BUHhN+a2lJ4&lt; at &gt;public.gmane.org] Sent: Monday, May 07, 2012 10:56 PM To: NISHIYAMA Tomoaki Cc: Jadhav, Alok; r-sig-db-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org Subject: Re: [R-sig-DB] [R] Why does RODBC driver returns garbage from Sybase server on new windows 7 machine? Hi, I had thought of that as well, however, at least based upon prior posts, if there is an architecture mis-match between R/RODBC/ODBC (not all components are either 32 OR 64 bit), you will not be able to successfully connect to the DB server, much less run a query. In other words, there would be an error issued from the call to odbc[Driver]Connect() before even getting to sqlQuery(). On some Linuxen (eg RHEL), a mis-match has caused segfaults upon trying to connect. Regards, Marc On May 7, 2012, at 9:20 AM, NISHIYAMA Tomoaki wrote: windows 7 box. =============================================================================== Please access the attached hyperlink for an important el...{{dropped:4}} _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Jadhav, Alok 2012-05-08T10:48:13 http://permalink.gmane.org/gmane.comp.lang.r.db/718 <pre> Marc, I am sure that my R version is 32 bit. Though I am not sure which driver is picked up, 32bit or 64 bit. My box has both the drivers installed. I will look into it further. I have tried a simple query which returns single row and it is working fine. This issue happens only in case when the result set is larger than 100 rows. I have a new observation. I was wrong when I said that this query was working fine on old machine (windows xp) with same driver. Looks like windows xp driver is different. I was using Merant driver on xp and using sybase native driver ASE on windows 7. Looks like this is actually a driver issue because I tried to use the same driver from a python script and I am getting exactly same truncated result from my python script. This clearly shows that RODBC doesn't has any problem. I will test this out with a different driver and let you know. Thanks for your time. Regards, Alok Please follow the attached hyperlink to an important disclaimer http://www.credit-suisse.com/asiapac/legal/securities/ -----Original Message----- From: Marc Schwartz [mailto:marc_schwartz-BUHhN+a2lJ4&lt; at &gt;public.gmane.org] Sent: Monday, May 07, 2012 10:14 PM To: Jadhav, Alok Cc: r-sig-db-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org Subject: Re: [R-sig-DB] [R] Why does RODBC driver returns garbage from Sybase server on new windows 7 machine? Alok, One additional thought as I was driving to work this morning. Try running your RODBC connect/query code outside of the function and use a simple query like "select * from TABLENAME". Also be sure to use a clean working environment. Just to eliminate the possibility that there is something in your workspace, function and/or a more complex query, if you are using one, that is not obvious. Marc On May 7, 2012, at 8:03 AM, Marc Schwartz wrote: cannot attempt to replicate the problem you have or implement a working scenario. the SQL query to be sure that there are no strange characters and/or things like line breaks, etc. that might cause issues. insight into where the issue might be here. windows 7 box. =============================================================================== Please access the attached hyperlink for an important el...{{dropped:4}} _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Jadhav, Alok 2012-05-08T02:03:33 http://permalink.gmane.org/gmane.comp.lang.r.db/717 <pre>Hi, I had thought of that as well, however, at least based upon prior posts, if there is an architecture mis-match between R/RODBC/ODBC (not all components are either 32 OR 64 bit), you will not be able to successfully connect to the DB server, much less run a query. In other words, there would be an error issued from the call to odbc[Driver]Connect() before even getting to sqlQuery(). On some Linuxen (eg RHEL), a mis-match has caused segfaults upon trying to connect. Regards, Marc On May 7, 2012, at 9:20 AM, NISHIYAMA Tomoaki wrote: _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Marc Schwartz 2012-05-07T14:55:58 http://permalink.gmane.org/gmane.comp.lang.r.db/716 <pre>Alok, One additional thought as I was driving to work this morning. Try running your RODBC connect/query code outside of the function and use a simple query like "select * from TABLENAME". Also be sure to use a clean working environment. Just to eliminate the possibility that there is something in your workspace, function and/or a more complex query, if you are using one, that is not obvious. Marc On May 7, 2012, at 8:03 AM, Marc Schwartz wrote: _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Marc Schwartz 2012-05-07T14:13:30 http://permalink.gmane.org/gmane.comp.lang.r.db/715 <pre>Hi Alok, Unfortunately, I don't have access to either Windows or Sybase, so I cannot attempt to replicate the problem you have or implement a working scenario. You might just re-check the character vector that you are passing for the SQL query to be sure that there are no strange characters and/or things like line breaks, etc. that might cause issues. I will need to defer to others to see if anyone might have some insight into where the issue might be here. Regards, Marc On May 6, 2012, at 7:24 PM, Jadhav, Alok wrote: _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Marc Schwartz 2012-05-07T13:03:59 http://permalink.gmane.org/gmane.comp.lang.r.db/714 <pre> [Moving this mail from R-Help to R-SIG-DB ] Marc, The below mentioned code is inside a function and the value of as.is is passed to the function, default value being FALSE. I tried your suggestion of passing rows_at_time=1 and believeNRows=FALSE. This did not solve the problem. It removed the garbage data in the result set but the data is truncated. In my query, I am expecting a result of 200 rows. Earlier I was getting around 17 rows with correct data and 3 rows with Nas. After above change now I am getting total of 17 rows with no NAs in the output. But data is not complete. I am tempted to try a different driver but still think driver is not a problem as the same driver in other app works without any issues. Seems like an issue with RODBC. Same functions work well on my old machine which is a windows XP. I am trying this first time on my windows 7 box. Regards, Alok Please follow the attached hyperlink to an important disclaimer http://www.credit-suisse.com/asiapac/legal/securities/ -----Original Message----- From: Marc Schwartz [mailto:marc_schwartz-BUHhN+a2lJ4&lt; at &gt;public.gmane.org] Sent: Friday, May 04, 2012 10:54 PM To: Jadhav, Alok Cc: r-help-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org Subject: Re: [R] Why does RODBC driver returns garbage from Sybase server on new windows 7 machine? On May 4, 2012, at 2:38 AM, Alok Jadhav wrote: driver or issue with RODBC library? resolve it? 1. This query is better posted to r-sig-db, which is dedicated to R and DB connectivity issues. More info at: https://stat.ethz.ch/mailman/listinfo/r-sig-db 2. It is not clear what 'as.is = as.is' in the call to sqlQuery is. Is the value of 'as.is' defined before the call someplace else that you have not shown here, along with the 'sql' query itself? 3. Try using 'rows_at_time = 1' as well as 'believeNRows = FALSE' in the calls as below. That may help with corrupted data coming back. I have to use the former with Oracle. That is also referenced in ?odbcConnect with a comment specific to Sybase. chan &lt;- odbcDriverConnect(conn, rows_at_time = 1, believeNRows = FALSE) x &lt;- sqlQuery(chan, sql, as.is = as.is, rows_at_time = 1, believeNRows = FALSE) Regards, Marc Schwartz =============================================================================== Please access the attached hyperlink for an important el...{{dropped:4}} _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Jadhav, Alok 2012-05-07T00:24:35 http://permalink.gmane.org/gmane.comp.lang.r.db/713 <pre>Unfortunately, I'm not really making any progresses, despite a lot of effort. I've compiled R on Mac OS X for myself using MacPorts and the error is now "state 28000, code 201" which is failed password authentification. _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> julia.jacobson-KvP5wT2u2U0< at >public.gmane.org 2012-04-29T14:09:00 http://permalink.gmane.org/gmane.comp.lang.r.db/712 <pre>Thanks a lot for your answer. Macports compiles by default to the architecture of the machine on which it is running. In my case this is 64 bit and should work fine together with R, because .Machine$sizeof.pointer gives me 8, proving it to be compiled for 64 bit architectures. ----- Original Nachricht ---- Von: Marc Schwartz &lt;marc_schwartz-BUHhN+a2lJ4&lt; at &gt;public.gmane.org&gt; An: julia.jacobson-KvP5wT2u2U0&lt; at &gt;public.gmane.org Datum: 25.04.2012 18:37 Betreff: Re: [R] RODBC Error Code 202 on Mac OS X 10.6 _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> julia.jacobson-KvP5wT2u2U0< at >public.gmane.org 2012-04-25T18:02:57 http://permalink.gmane.org/gmane.comp.lang.r.db/711 <pre>Greetings, I'm not sure if this is the right list for this subject so if not please point me there. rpart() and some other R libraries now have model scoring output in PMML through the pmml library. I do some consulting in predictive modeling and often the companies require SQL case statement code or C# or some other language. I know there are things such as the Oracle solution for in database scoring but this is somewhat different in that typically the IT shops will want the scoring code as the deliverable and then run it natively in the data warehouse they have. Most (all?) commercial data mining and modeling packages have SQL as one of the options for scoring code. SAS eMiner for instance can deliver code in several languages. So can Angoss, KXEN, etc. Does anyone know of an equivalent to the pmml library in SQL? Thomas Brandenburger Assistant Professor Department of Mathematics and Statistics South Dakota State University Cell: 605.480.0951 Fax: 605.688.5880 thomas.brandenburger-B3sqeG8WtU+Vc3sceRu5cw&lt; at &gt;public.gmane.org&lt;mailto:thomas.brandenburger-B3sqeG8WtU+Vc3sceRu5cw&lt; at &gt;public.gmane.org&gt; [[alternative HTML version deleted]] _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB-0bNBQ1PAWB4BXFe83j6qeQ&lt; at &gt;public.gmane.org https://stat.ethz.ch/mailman/listinfo/r-sig-db </pre> Brandenburger, Thomas 2012-04-19T15:11:11 http://permalink.gmane.org/gmane.comp.lang.r.db/710 <pre></pre> siddharth arun 2012-04-14T14:58:27 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.lang.r.db