search for: sqlgetresults

...RODBC is mostly working very well on Windows XP talking to MS SQL Server. However, when trying to retrieve a result set in repeated batches the first batch returns results ok, but then subsequent calls return no data (see code below). I tried setting believeNRows=FALSE both in odbcConnect() and in sqlGetResults() but this doesn't appear to make any difference. Also odbcFetchRows() suffers the same problem. The documentation states "sqlGetResults is a mid-level function. It should be called after a call to odbcQuery and used to retrieve waiting results into a data frame. Its main use is with max...

...annel,"select sysdate from dual") Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, : negative length vectors are not allowed > close(channel) > q() I know I can replace the call to sqlQuery with calls to its component functions odbcQuery and sqlGetResults to get the same result (and the same error) > library(RODBC) > channel <- odbcConnect("OraLSH", <user>, <password>) > odbcQuery (channel, "select sysdate from dual") > sqlGetResults(channel, as.is=FALSE, errors=FALSE, max=0, buffsize=1, nullstring=NA,...

...annel,"select sysdate from dual") Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, : negative length vectors are not allowed > close(channel) > q() I know I can replace the call to sqlQuery with calls to its component functions odbcQuery and sqlGetResults to get the same result (and the same error) > library(RODBC) > channel <- odbcConnect("OraLSH", <user>, <password>) > odbcQuery (channel, "select sysdate from dual") > sqlGetResults(channel, as.is=FALSE, errors=FALSE, max=0, buffsize=1, nullstring=NA,...

...er, when I use the exact same thing from sqlQuery() function, I get the following error: Error in "[.data.frame"(data, , ) : not all specified columns exist Traceback() gives this: 5: stop("not all specified columns exist") 4: "[.data.frame"(data, , ) 3: data[, ] 2: sqlGetResults(channel, errors = errors, ...) 1: sqlQuery(con.object, " ... long query here ... ") The puzzling thing is that the error does not seem to be like the usual mispecified-SQL type that I normally get when using sqlQuery(). I am not sure how to debug this problem, since I can't really s...

...n't that read if (!any(sqlTables(channel)[4] == sqtable)) ? The above mentioned one-line-only problem with sqlTables() of course breaks this code (6) Some more comments (perhaps due to my ignorance about ODBC, can ODBC write data without sending sql statements?) (6.1) looping sqlwrite() sqlgetresults() uses for-loops for writing/reading data rowwise. I expect this to be rather slow. Is it possible to have this done in C instead of R? If in R, might it be that the sequence of multiple rbind() leads to memory segmentation (growing lists)?? May be one should ask on R-Help, if it is better to firs...

Un texte encapsul? et encod? dans un jeu de caract?res inconnu a ?t? nettoy?... Nom : non disponible Url : https://stat.ethz.ch/pipermail/r-help/attachments/20060719/a0844373/attachment.pl

...tion in a 8-element character vector (based on an idea of Matthew Dowle). * The C code calls SQLExecuteDirect rather than SQLExecute and does not call SQLCloseCursor, based on a problem report from Matthew Dowle using MS SQLServer. Repeated calls to sqlGetResults now work. * New function sqlFetchMore. * Table names in Access with embedded spaces are mapped to the [name space] form which Access requires. * Table creation no longer removes _ from column names. * New functions get/setSqlTypeInfo and the typeInfo ar...

I'm tryuing to get the RODBC package to work with a MS Sql database (Wonderware's InSQL). I'm able to connect to the database using the following: version _ platform i386-pc-mingw32 arch x86 os Win32 system x86, Win32 status Under development (unstable)

...ot;) > channel channel [1] 0 And an abbreviated table list is: ... 25 Runtime dbo AnalogHistory TABLE ... A abbreviated Column List is as follows: > odbcColumns(channel,"AnalogHistory") odbcColumns(channel,"AnalogHistory") [1] 1 > sqlGetResults(channel) sqlGetResults(channel) TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME 1 Runtime dbo AnalogHistory DateTime 11 datetime 2 Runtime dbo AnalogHistory TagName 12 varchar 3 Runtime dbo AnalogHistory Valu...

...tion in a 8-element character vector (based on an idea of Matthew Dowle). * The C code calls SQLExecuteDirect rather than SQLExecute and does not call SQLCloseCursor, based on a problem report from Matthew Dowle using MS SQLServer. Repeated calls to sqlGetResults now work. * New function sqlFetchMore. * Table names in Access with embedded spaces are mapped to the [name space] form which Access requires. * Table creation no longer removes _ from column names. * New functions get/setSqlTypeInfo and the typeInfo ar...

...example, Select Formula from OGA_DEV.dbo.Equation where EquationId = 3 I tried to work within RGui using RODBC: > library(RODBC) > conn = odbcConnect("SQL Server") > odbcQuery(conn, "Select Formula from OGA_DEV.dbo.Equation where EquationId = 3") [1] 1 > results = sqlGetResults(conn) > close(conn) > results$Formula [1] ((-0.19*({EthanePropaneMolFracTotal}*100)^2)+(12.3*({EthanePropaneMolFracTotal}*100))-13)*0.8 Levels: ((-0.19*({EthanePropaneMolFracTotal}*100)^2)+(12.3*({EthanePropaneMolFracTotal}*100))-13)*0.8 > typeof(results$Formula) [1] "integer" &g...

...n Excel spread sheet. I have looked at the RODBC help pages and am having trouble setting up code that will work. My code and the results are pasted below: > jo<-odbcConnectExcel("i:\\all\\sorkinjohn\\stats\\silvermannatalie\\NEMOcomb ined06-24-09.xls",readOnly = TRUE) > mo<-sqlGetResults(jo) > mo [1] -1 I have tried to set up the connection string using odbcConnectExcel and then have tried to read the file using sqlGetResults. Can someone tell me what I am doing wrong? Thanks, John John David Sorkin M.D., Ph.D. Chief, Biostatistics and Informatics University of Maryland Schoo...

Version 1.2.6 of RODBC is now on CRAN. This has a number of bug fixes and many workarounds for ODBC driver quirks--I've set up further testbeds for SQL Server 2008, Oracle and DB2. More visibly, the documentation has been expanded in several ways, in particular in collecting together advice on using 'schemas' and 'catalogs' in the ?RODBC overview. There is also a test

Version 1.2.6 of RODBC is now on CRAN. This has a number of bug fixes and many workarounds for ODBC driver quirks--I've set up further testbeds for SQL Server 2008, Oracle and DB2. More visibly, the documentation has been expanded in several ways, in particular in collecting together advice on using 'schemas' and 'catalogs' in the ?RODBC overview. There is also a test

Dear list, I have a rather large dataset in SAS which I export to a SQLite database for subsequent use in R. One of the columns is cowidp 1881501224.2 1881501224.2 and the column is stored as a character in SAS. It becomes a varchar in the database (it should be - it is an identifier; not a number). Reading this into R gives cowidp 1 1881501224 2 1881501224 .... - i.e. the

...e kind of row limit or buffer limit thing going on. I searched the archives and Brian Ripley suggested playing with believeNRows parameter (setting it to FALSE) as Oracle is not well behaved. I've tried various combinations of this and delving into the lower level functions like odbcQuery and sqlGetResults but I'm still getting the same thing: 0 rows back unless I cut the query down to a more return a more reasonable range. Obviously I can't reproduce the problem here because of the database, but does anyone have any suggestions/tips/pointers to get me going toward a solution? Maybe it'...

...ries, I simply recieve a "No Data" message. Debugging a query such as "select * from sometable", on the R side of things I receive the column names OK but when it calls down to RODBCFetchRows, the following object gets returned into erg, which in turn gets assigned to dbdata in sqlGetResults. Browse[1]> dbdata $data $data[[1]] NULL $stat [1] -1 I have executed the same query via unixODBC's isql using the same DSN and I receive a full result set with several rows. Any suggestions please? Thanks very much, Phil -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-....

...ing is that Excel's ODBC driver seems to have a limit on the number of fields/columns (output below). I haven't found any documentation on what this limit L might be, but I know that 128 <= L < 256. Does anyone know of a way to avoid "L" via - the arguments to sqlQuery() or sqlGetResults()? - an SQL select statement that can subset the columns/fields and index a range of columns? - otherwise? [system info: win98 with 128Mb RAM, R v1.6.1, RODBC v. 1.0-1, excel 2000, odbc driver v. 03.51; my knowledge of sql = $\epsilon$.] Gratefully, alejandro > #Sheet1 has 92 rows and 256 co...

...e. The problem I've found is that numeric data from my sql queries is returned with only two decimal digits. After searching the docs, I think the problem comes from my sql numeric data having "float" type instead of "double", as I found the following: "Where possible sqlGetResults transfers data directly: this happens for double, real, integer and smallint columns in the table. All other SQL data types are converted to character strings (of length up to 256), and then converted by type.convert as controlled by the as.is argument." What I did not found was a way to get...

I run R 2.0.1 on Debian and connect to Informix database via RODBC. In the table below the column "month" is of type char(1). RODBC seems to be converting this column to boolean if the value is F or T. This is the data in my table: id month year 25 F 2005 26 Z 2005 When I select * for id 25 I get 25 FALSE 2005 When I select * for id 16 I get 26 Z