Sql-Mode - getting query results from MS SQL Server 2000

Sql-Mode - getting query results from MS SQL Server 2000

[Brett Kelly]

[-- Attachment #1.1: Type: text/plain, Size: 1116 bytes --]

Hello All,

Let me preface by saying that I'm pretty new to emacs, so I apologize in
advance if I've missed something obvious. I'm using emacs 21.3.1 on windows
xp professional.

I've read the SqlMode page on the wiki as well as the sql-help from within
emacs, but I still can't figure out how to do this.

I'd like to be able to type in a sql query and have it return a resultset in a
different buffer (a la Query Analyzer).  Here's what I'm doing now:
- Create a new buffer, do M-x sql-ms RET, then enter the relevant connection
  information (user, password, server, dbname). This gives me no errors.
- Type in my SQL statement (I've tried simple "select" statements, as well as
  more complicated "create proc" type stuff)
- Highlight the relevant code with the mouse, and choose Send Region from the
  SQL menu.

Now, if I'm understanding this correctly, I should see the results in a new
buffer, but I'm not.

Can anybody tell me what I'm doing wrong?

Thanks!

-- 
Brett Kelly
inkedmn@inkedmn.com
http://inkedmn.com:8000
GPG Public Key: http://inkedmn.com:8000/stuff/inkedmn.asc

[-- Attachment #1.2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]

[-- Attachment #2: Type: text/plain, Size: 152 bytes --]

_______________________________________________
Help-gnu-emacs mailing list
Help-gnu-emacs@gnu.org
http://lists.gnu.org/mailman/listinfo/help-gnu-emacs

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Kevin Rodgers]

Brett Kelly wrote:
 > I'd like to be able to type in a sql query and have it return a 
resultset in a
 > different buffer (a la Query Analyzer).  Here's what I'm doing now:
 > - Create a new buffer, do M-x sql-ms RET, then enter the relevant 
connection
 >   information (user, password, server, dbname). This gives me no errors.
 > - Type in my SQL statement (I've tried simple "select" statements, as 
well as
 >   more complicated "create proc" type stuff)
 > - Highlight the relevant code with the mouse, and choose Send Region 
from the
 >   SQL menu.
 >
 > Now, if I'm understanding this correctly, I should see the results in 
a new
 > buffer, but I'm not.

 From the commentary at the top of sql.el excerpted below, I guess that
you are typing your SQL statement directly into the sql-interactive-mode
*SQL* buffer.  If you enter them in some other sql-mode buffer, Send
Region should cause the *SQL* buffer with the results to be displayed
(see sql-pop-to-buffer-after-send-region).

;; This file provides a sql-mode and a sql-interactive-mode.  The
;; interactive mode had to provide a command-line history; the other
;; mode had to provide "send region/buffer to SQL interpreter"
;; functions.

;; sql-interactive-mode is used to interact with a SQL interpreter
;; process in a SQLi buffer (usually called `*SQL*').  The SQLi buffer
;; is created by calling a SQL interpreter-specific entry function.  Do
;; *not* call sql-interactive-mode by itself.

;; sql-mode can be used to keep editing SQL statements.  The SQL
;; statements can be sent to the SQL process in the SQLi buffer.

-- 
Kevin Rodgers

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Brett Kelly]

[-- Attachment #1.1: Type: text/plain, Size: 2737 bytes --]

Sometime around Thu, Jun 16, 2005 at 12:15:57PM -0600, Kevin Rodgers said:
> Brett Kelly wrote:
> > I'd like to be able to type in a sql query and have it return a 
> resultset in a
> > different buffer (a la Query Analyzer).  Here's what I'm doing now:
> > - Create a new buffer, do M-x sql-ms RET, then enter the relevant 
> connection
> >   information (user, password, server, dbname). This gives me no errors.
> > - Type in my SQL statement (I've tried simple "select" statements, as 
> well as
> >   more complicated "create proc" type stuff)
> > - Highlight the relevant code with the mouse, and choose Send Region 
> from the
> >   SQL menu.
> >
> > Now, if I'm understanding this correctly, I should see the results in 
> a new
> > buffer, but I'm not.
> 
> From the commentary at the top of sql.el excerpted below, I guess that
> you are typing your SQL statement directly into the sql-interactive-mode
> *SQL* buffer.  If you enter them in some other sql-mode buffer, Send
> Region should cause the *SQL* buffer with the results to be displayed
> (see sql-pop-to-buffer-after-send-region).
> 
> ;; This file provides a sql-mode and a sql-interactive-mode.  The
> ;; interactive mode had to provide a command-line history; the other
> ;; mode had to provide "send region/buffer to SQL interpreter"
> ;; functions.
> 
> ;; sql-interactive-mode is used to interact with a SQL interpreter
> ;; process in a SQLi buffer (usually called `*SQL*').  The SQLi buffer
> ;; is created by calling a SQL interpreter-specific entry function.  Do
> ;; *not* call sql-interactive-mode by itself.
> 
> ;; sql-mode can be used to keep editing SQL statements.  The SQL
> ;; statements can be sent to the SQL process in the SQLi buffer.
> 
> -- 
> Kevin Rodgers
> 
> 
> 
> _______________________________________________
> Help-gnu-emacs mailing list
> Help-gnu-emacs@gnu.org
> http://lists.gnu.org/mailman/listinfo/help-gnu-emacs
> 

Kevin  - thanks for the reply.  Unfortunately, this still doesn't seem to be
working.

Went through the followig steps:

- Opened up a .sql file in a new buffer, called TestSQL.sql
- Checked "Pop to SQLi Buffer after Send" in the SQL menu
- ran M-x sql-ms RET which created a new SQLi buffer called *SQL*
- In the TestSQL.sql buffer, i chose Set SQLi buffer from the
  SQL menu, and set it to *SQL*
- I wrote a simple select statement in TestSQL.sql, highlighted it, and chose
  "send region" from the sql menu.
- In the minibuffer, it says "string sent to buffer *SQL*"

And that's it, I get no other output beside that.

-- 
Brett Kelly
inkedmn@inkedmn.com
http://inkedmn.com:8000
GPG Public Key: http://inkedmn.com:8000/stuff/inkedmn.asc

[-- Attachment #1.2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]

[-- Attachment #2: Type: text/plain, Size: 152 bytes --]

_______________________________________________
Help-gnu-emacs mailing list
Help-gnu-emacs@gnu.org
http://lists.gnu.org/mailman/listinfo/help-gnu-emacs

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Michael Mauger]

Brett Kelly <inkedmn <at> inkedmn.com> writes:
> 
> I've read the SqlMode page on the wiki as well as the sql-help from within
> emacs, but I still can't figure out how to do this.
> 
> I'd like to be able to type in a sql query and have it return a resultset in a
> different buffer (a la Query Analyzer).  Here's what I'm doing now:
> - Create a new buffer, do M-x sql-ms RET, then enter the relevant connection
>   information (user, password, server, dbname). This gives me no errors.
> - Type in my SQL statement (I've tried simple "select" statements, as well as
>   more complicated "create proc" type stuff)
> - Highlight the relevant code with the mouse, and choose Send Region from the
>   SQL menu.
> 
> Now, if I'm understanding this correctly, I should see the results in a new
> buffer, but I'm not.

The `sql-ms' command should have opened up a new *SQL* buffer to hold the 
interaction with osql.  The results from a sql command you send to the command 
interpreter from a sql file should appear at the end of the *SQL* buffer.

I think you will find the Query Analyzer interaction model doesn't work well in 
the Emacs environment.  In the *SQL* buffer, you have all of Emacs' power to 
recall and edit sql statements.  You can then easily copy tested sql statements 
from your sql-ms buffer into a sql file script.  I also frequently copy the 
result rows, clean them up some in a scratch buffer and then paste them into 
other applications.

I have been playing around with a feature that will submit a single statement 
to the SQL command interpreter and displays the results in its own buffer.  But 
it is mostly useful for things like data dictionary listings.

If you give me more specific examples of what you want to be able to do and how 
you want to be able to interact with it, I may be able to offer more assistance.

-- Michael Mauger
sql.el maintainer

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Brett Kelly]

[-- Attachment #1.1: Type: text/plain, Size: 2389 bytes --]

Sometime around Thu, Jun 16, 2005 at 12:15:57PM -0600, Kevin Rodgers said:
> Brett Kelly wrote:
> > I'd like to be able to type in a sql query and have it return a 
> resultset in a
> > different buffer (a la Query Analyzer).  Here's what I'm doing now:
> > - Create a new buffer, do M-x sql-ms RET, then enter the relevant 
> connection
> >   information (user, password, server, dbname). This gives me no errors.
> > - Type in my SQL statement (I've tried simple "select" statements, as 
> well as
> >   more complicated "create proc" type stuff)
> > - Highlight the relevant code with the mouse, and choose Send Region 
> from the
> >   SQL menu.
> >
> > Now, if I'm understanding this correctly, I should see the results in 
> a new
> > buffer, but I'm not.
> 
> From the commentary at the top of sql.el excerpted below, I guess that
> you are typing your SQL statement directly into the sql-interactive-mode
> *SQL* buffer.  If you enter them in some other sql-mode buffer, Send
> Region should cause the *SQL* buffer with the results to be displayed
> (see sql-pop-to-buffer-after-send-region).
> 
> ;; This file provides a sql-mode and a sql-interactive-mode.  The
> ;; interactive mode had to provide a command-line history; the other
> ;; mode had to provide "send region/buffer to SQL interpreter"
> ;; functions.
> 
> ;; sql-interactive-mode is used to interact with a SQL interpreter
> ;; process in a SQLi buffer (usually called `*SQL*').  The SQLi buffer
> ;; is created by calling a SQL interpreter-specific entry function.  Do
> ;; *not* call sql-interactive-mode by itself.
> 
> ;; sql-mode can be used to keep editing SQL statements.  The SQL
> ;; statements can be sent to the SQL process in the SQLi buffer.
> 
> -- 
> Kevin Rodgers
> 
> 
> 
> _______________________________________________
> Help-gnu-emacs mailing list
> Help-gnu-emacs@gnu.org
> http://lists.gnu.org/mailman/listinfo/help-gnu-emacs
> 

Well, I guess that was my core "goal" - to get emacs to behave as much as
possible like Query Analyzer...

I spend probably half of every day writing sql code, just thought it'd be nice
to be able to write/run sql stuff in something other than query analyzer.

Thanks for the help!

-- 
Brett Kelly
inkedmn@inkedmn.com
http://inkedmn.com:8000
GPG Public Key: http://inkedmn.com:8000/stuff/inkedmn.asc

[-- Attachment #1.2: Digital signature --]
[-- Type: application/pgp-signature, Size: 189 bytes --]

[-- Attachment #2: Type: text/plain, Size: 152 bytes --]

_______________________________________________
Help-gnu-emacs mailing list
Help-gnu-emacs@gnu.org
http://lists.gnu.org/mailman/listinfo/help-gnu-emacs

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Ryan Bowman]

--- Brett Kelly <inkedmn@inkedmn.com> wrote:

> 
> Well, I guess that was my core "goal" - to get emacs to behave as much as
> possible like Query Analyzer...
> 
> I spend probably half of every day writing sql code, just thought it'd be
> nice
> to be able to write/run sql stuff in something other than query analyzer.
> 

I sometimes use query analyzer, and while it's not quite the same xkeymacs
(http://www.cam.hi-ho.ne.jp/oishi/indexen.html) allows me to use most of the
usual emacs commands for editing text and moving the cursor around.  It helps a little.

----
Ryan Bowman

There is no vi there is only Emacs
----

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Tim X]

Brett Kelly <inkedmn@inkedmn.com> writes:

> Sometime around Thu, Jun 16, 2005 at 12:15:57PM -0600, Kevin Rodgers said:
> > Brett Kelly wrote:
> > > I'd like to be able to type in a sql query and have it return a 
> > resultset in a
> > > different buffer (a la Query Analyzer).  Here's what I'm doing now:
> > > - Create a new buffer, do M-x sql-ms RET, then enter the relevant 
> > connection
> > >   information (user, password, server, dbname). This gives me no errors.
> > > - Type in my SQL statement (I've tried simple "select" statements, as 
> > well as
> > >   more complicated "create proc" type stuff)
> > > - Highlight the relevant code with the mouse, and choose Send Region 
> > from the
> > >   SQL menu.
> > >
> > > Now, if I'm understanding this correctly, I should see the results in 
> > a new
> > > buffer, but I'm not.
> > 
> > From the commentary at the top of sql.el excerpted below, I guess that
> > you are typing your SQL statement directly into the sql-interactive-mode
> > *SQL* buffer.  If you enter them in some other sql-mode buffer, Send
> > Region should cause the *SQL* buffer with the results to be displayed
> > (see sql-pop-to-buffer-after-send-region).
> > 
> > ;; This file provides a sql-mode and a sql-interactive-mode.  The
> > ;; interactive mode had to provide a command-line history; the other
> > ;; mode had to provide "send region/buffer to SQL interpreter"
> > ;; functions.
> > 
> > ;; sql-interactive-mode is used to interact with a SQL interpreter
> > ;; process in a SQLi buffer (usually called `*SQL*').  The SQLi buffer
> > ;; is created by calling a SQL interpreter-specific entry function.  Do
> > ;; *not* call sql-interactive-mode by itself.
> > 
> > ;; sql-mode can be used to keep editing SQL statements.  The SQL
> > ;; statements can be sent to the SQL process in the SQLi buffer.
> > 
> > -- 
> > Kevin Rodgers
> > 
> > 
> > 
> > _______________________________________________
> > Help-gnu-emacs mailing list
> > Help-gnu-emacs@gnu.org
> > http://lists.gnu.org/mailman/listinfo/help-gnu-emacs
> > 
> 
> Kevin  - thanks for the reply.  Unfortunately, this still doesn't seem to be
> working.
> 
> Went through the followig steps:
> 
> - Opened up a .sql file in a new buffer, called TestSQL.sql
> - Checked "Pop to SQLi Buffer after Send" in the SQL menu
> - ran M-x sql-ms RET which created a new SQLi buffer called *SQL*
> - In the TestSQL.sql buffer, i chose Set SQLi buffer from the
>   SQL menu, and set it to *SQL*
> - I wrote a simple select statement in TestSQL.sql, highlighted it, and chose
>   "send region" from the sql menu.
> - In the minibuffer, it says "string sent to buffer *SQL*"
> 
> And that's it, I get no other output beside that.
> 
> -- 
> Brett Kelly
> inkedmn@inkedmn.com
> http://inkedmn.com:8000
> GPG Public Key: http://inkedmn.com:8000/stuff/inkedmn.asc

Brett,

Are you sure emacs is communicating with the SQL process? Keep in mind
that emacs cannot/does not communicate directly with the database - it
communicates with some program which then communicates with the
database. I don't know anything about MS SQL Server, but with Oracle
(as an example), Emacs SQL mode communicates with sqlplus, which is
Oracle's interface program for entering sql commands. I imagine SQL
Server has something similar. You need to ensure that emacs is
communicating with this program and the program is in turn
communicating with the SQL Server.

Tim


-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Re: Sql-Mode - getting query results from MS SQL Server 2000

[Michael Mauger]

Brett Kelly <inkedmn <at> inkedmn.com> writes:

> 
> Sometime around Thu, Jun 16, 2005 at 12:15:57PM -0600, Kevin Rodgers said:
> > Brett Kelly wrote:
> > > I'd like to be able to type in a sql query and have it return a 
> > resultset in a
> > > different buffer (a la Query Analyzer).  Here's what I'm doing now:
> > > - Create a new buffer, do M-x sql-ms RET, then enter the relevant 
> > connection
> > >   information (user, password, server, dbname). This gives me no errors.
> > > - Type in my SQL statement (I've tried simple "select" statements, as 
> > well as
> > >   more complicated "create proc" type stuff)
> > > - Highlight the relevant code with the mouse, and choose Send Region 
> > from the
> > >   SQL menu.
> > >
> > > Now, if I'm understanding this correctly, I should see the results in 
> > a new
> > > buffer, but I'm not.
> > 
> > From the commentary at the top of sql.el excerpted below, I guess that
> > you are typing your SQL statement directly into the sql-interactive-mode
> > *SQL* buffer.  If you enter them in some other sql-mode buffer, Send
> > Region should cause the *SQL* buffer with the results to be displayed
> > (see sql-pop-to-buffer-after-send-region).
> > 
> > ;; This file provides a sql-mode and a sql-interactive-mode.  The
> > ;; interactive mode had to provide a command-line history; the other
> > ;; mode had to provide "send region/buffer to SQL interpreter"
> > ;; functions.
> > 
> > ;; sql-interactive-mode is used to interact with a SQL interpreter
> > ;; process in a SQLi buffer (usually called `*SQL*').  The SQLi buffer
> > ;; is created by calling a SQL interpreter-specific entry function.  Do
> > ;; *not* call sql-interactive-mode by itself.
> > 
> > ;; sql-mode can be used to keep editing SQL statements.  The SQL
> > ;; statements can be sent to the SQL process in the SQLi buffer.
> > 
> > -- 
> > Kevin Rodgers
> 
> Well, I guess that was my core "goal" - to get emacs to behave as much as
> possible like Query Analyzer...
> 
> I spend probably half of every day writing sql code, just thought it'd be nice
> to be able to write/run sql stuff in something other than query analyzer.
> 
> Thanks for the help!
> 

A couple of issues with MS under Emacs -- you must follow the command with 
the "go" to get them to execute, and you may not get a prompt.

Are you able to type sql commands directly in the *SQL* buffer and have them 
return results?  
Are you including the trailing "go" in the region sent?

If you are getting the "Sent string to buffer *SQL*" message, then Emacs thinks 
the interpreter is live and has sent the string.  The next step in the code is 
to either pop or display the buffer.  Do you have any settings in your .emacs 
that might impact where a buffer is displayed (pop-up-frames, display-buffer-
reuse-frames, special-display-buffer-names, special-display-regexps, same-
window-buffer-names, or same-window-regexps)?

I can speak to the fact that Emacs can be used very effectively as a 
replacement for Query Analyzer.  I used Emacs as a front-end for MS and Oracle 
in parallel for over a year.  I'd usually have interactive sessions on both 
databases active at the same time.

More questions: 
what version of Emacs and sql-mode are you using? 
What's the value of sql-ms-program?