feature request: open a client session within R

[ajdamico]

just an idea.. currently DBI transmits command to and from an external database through commands like dbGetQuery. i'm curious if it's possible (and hopefully easy) to include a new feature that turns an R session into a "client mode" -- sort of like debugger mode, but one specific DBI connection.

so, for example, a user who types

dbGetQuery( con , "select * from blah" ) dbGetQuery( con , "select * from blah2" ) dbGetQuery( con , "select * from blah3" )

could instead type dbClient or something like that, and the prompt could change

dbClient( con ) entering DBI client mode client[1]> select * from blah; client[1]> select * from blah2; client[1]> select * from blah3; client[1]> Q exiting DBI client mode

and each of those results would print to the screen, the same way they print to the screen from the equivalent dbGetQuery calls above. thank you for DBI!

[hannes]

How about something along the following lines:

dbClient <- function(conn, n=100) {
    res <- NA
    cat("Enter SQL queries below. 'Q' to exit.\n")
    repeat {
        tryCatch({
            repeat {
                query <- readline("sql> ")
                if (tolower(query) == "q") {
                    return(invisible(res))
                }
                if (nchar(query) < 5) {
                    next
                }
                res <- dbGetQuery(conn, query)
                print(head(res, n))
                break
            }}
        , error = function(e) {
            message(e, "\n")
        })
    }
}

[krlmlr]

This looks nice and useful to me. @hadley: Can we add this to DBI?

[hadley]

This feels like a job for another package to me.

[hannes]

MonetDB.R will have it in a generic way in the next release, so you can use that :)

[ajdamico]

thanks! from an organizational perspective, i'm curious: what's the advantage of separating this from DBI? is it comparatively too low-level?

[hadley]

It's too high-level - it just doesn't seem like a good fit to the rest of DBI to me.

[ajdamico]

cool. thanks all

[krlmlr]

@jbryer: Would such a function be a good fit for your sqlutils package?

[jbryer]

Can you provide more details about this feature request? I am not entirely sure what you are asking for.

On Fri, Jan 8, 2016 at 6:17 PM, Kirill Müller notifications@github.com wrote:

@jbryer https://github.com/jbryer: Would such a function be a good fit for your sqlutils package?

— Reply to this email directly or view it on GitHub https://github.com/rstats-db/DBI/issues/29#issuecomment-170154256.

[krlmlr]

The code shown in https://github.com/rstats-db/DBI/issues/29#issuecomment-103991066 is a simple interactive query processing loop. It shows a prompt, waits for the user to input a SQL query, executes it, and prints the result.

Hannes said, this code is now in the MonetDB.R package. I'm wondering if it could (also) live in your sqlutils package. Perhaps you also want to offer the user an option to save a query entered interactively to a file.