sponsor Vim development Vim logo Vim Book Ad

dbext.vim : Provides database access to 10 databases (Oracle, Sybase, SQL Server, MySQL,...)

 script karma  Rating 422/126, Downloaded by 7796

created by
Peter Bagyinszki
 
script type
utility
 
description
This plugin contains functions/mappings/commands to enable Vim to access several databases. Currently Mysql, PostgreSQL, Ingres, Oracle, Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, Microsoft SQL Server, DB2, Interbase and SQLite are supported.

NOTE: As of version 4.0 this plugin requires Vim 7.

dbext provides a common interface between your editor and a database.  If your company/project moves onto a new database platform, there is no need to learn the new databases tools.  Without leaving Vim while editing your SQL you can excute database commands, run queries, display results, and view database objects.  dbext understands various programming languages, and can parse and prompt the user for [host] variables and execute the resulting statement.  See below for more details.

Adds a menu for the common commands for gvim users.

Some of the features that are supported:

Tutorial
-----------
A tutorial has been added to help you become familiar with the features of the plugin, :h dbext-tutorial.

Connection Profiles
-----------------------------
You can create as many profiles as you like in your vimrc.  Each profile specifies various connection information.  Each buffer can be connected to a different database.   The plugin will automatically prompt the user for connection information.  If you have defined profiles in your vimrc, for ease of use,  you can choose from a numbered list.

SQL History
-----------
As of version 3.0, dbext maintains a history file which is shared between multiple instances of Vim.  A statement added in one instance of Vim will be immediately available in a different instance of Vim on the same computer.  To re-run a statement you can either press <enter> on the line, or if you prefer the mouse you can double click on the statement.

Modeline Support
---------------------------
Similar to Vim modelines, you can specify connection information as comments within your buffers.  To prevent sensitive information (ie passwords) from being visible, you can specify a connection profile as part of your modeline.  

Object Completion
----------------------------
dbext ties into Vim dictionary feature.  You can complete table names, procedure names and view names using the i_CTRL-X_CTRL-K feature.

Viewing Lists of Objects
------------------------------------
You can browse through the various objects in the database you are connected to and specify wildcard information.  For example you can say, "Show me all tables beginning with 'ml_' ".  These objects are currently supported: Tables, Procedures, Views,  Columns (for a table).

Result Buffer
-------------------
The output from any of the commands is placed into a new buffer called Result.  In the event of an error, both the error and the command line is included for inspection.

Mappings
----------------
There are many maps created for convenience.  They exist for most modes (command, visual and insert).

Place the cursor on a word, and invoke the default mapping (or menu) and a Result buffer will open with the contents of the table displayed (ie select * from <word>.  Optionally you can be prompted for the table name, and a WHERE clause.

Describe a table (see column names and datatypes).

Describe a stored procedure (see input and output datatypes).

Visually highlight statements and execute them against the database.

Parsing Statements
-----------------------------
By default any statement will be parsed looking for input parameters (host variables), if any are found you are prompted to enter a suitable value for the parameter.  This option can be turned off either globally or on a per
buffer basis.
        SELECT first_name, city
          FROM customer
         WHERE last_name    = @name
In the case you will be asked for a value for @name.  The rules for defining input parameters are customizable either globally or on a per buffer basis. See help file for more details.

FileType Support
--------------------------
SQL can be used from a variety of languages.  Each development language (PHP, Perl, Java, ...) language has different syntax for creating SQL statements that are sent to the database.  dbext has support for several
different filetypes, so that it can understand and correctly parse a SQL statement.

The current supported languages are:
        PHP, Java, JSP, JavaScript, JProperties, Perl, SQL, Vim

For example assume you had the following Java code:
String mySQL =
    "SELECT s.script, ts.event, t.name                  " +
    "     , s.script_language, sv.name                  " +
    "  FROM ml_script s, ml_table_script ts, ml_table t " +
                "     , ml_script_version sv                        " +
    " WHERE s.script_id   = " + script_version +
    "   AND ts.version_id = "+obj.method() +
    "   AND ts.table_id   = t.table_id                  ";

If you visually select from the "SELECT ... to the "; and ran
:'<,'>DBExecSQL    (or used the default map <Leader>se)

The Java filetype support would concatenate each individual string into one
single string.  In this case it removed the " + " and concatenated  the
lines to result in the following (assuming this is on one line):
       SELECT s.script, ts.event, t.name , s.script_language, sv.name
       FROM ml_script s, ml_table_script ts, ml_table t
                  , ml_script_version sv
      WHERE s.script_id   = " + script_version + "
        AND ts.version_id = "+obj.method() +"
        AND ts.table_id   = t.table_id

Next, it will prompt you for replacement values for the various variables or  objects you used in the string.
Assuming you had the default behaviour turned on, you would be prompted  to supply a value for:
                " + script_version + "
                "+obj.method() +"

So assuming you entered:
                100
                'Project_Yahoo'

Then the resulting string sent to your database would be (again, this would technically be on one line):
       SELECT s.script, ts.event, t.name , s.script_language, sv.name
       FROM ml_script s, ml_table_script ts, ml_table t
                  , ml_script_version sv
      WHERE s.script_id   = 100
        AND ts.version_id = 'Project_Yahoo'
        AND ts.table_id   = t.table_id

Benefit:  
You did not have to test your SQL by cutting and pasting it into a separate tool and replacing all the object and host variables yourself.  Just by visually selecting the string and running the command DBExecSQL (or the default mapping <Leader>se) the SQL statement was executed against the database and allowed to you enter host variables.

Additional Commands
---------------------------------
DBExecSQL - Enter any command you want sent to the database
DBExecRangeSQL - Enter any range of statements you want executed
Select  - Enter the remainder of a select (ie :Select * from customer)
Call  - Call a stored procedure
Update  - Enter the remainder of an update
Insert  - Enter the remainder of an insert
Delete  - Enter the remainder of an delete
Drop    - Enter the remainder of a drop
Alter   - Enter the remainder of an alter
Create  - Enter the remainder of a create
 
install details
Copy the dbext.vim script to your $HOME/.vim/plugin (or $VIM/vimfiles/plugin for windows) directory.  Copy dbext.txt to your $HOME/.vim/doc (or $VIM/vimfiles/doc for windows) directory.
To update the Vim Help, run:
:helptags $HOME/.vim/doc  (Unix)
:helptags $VIM/vimfiles/doc  (Windows)

Dependencies
----------------------
dbext version 4.0 and higher have no dependicies on any other plugins.

If you are using a dbext version < 4.0 it requires these 2 plugins:
Hari Krishna Dara's two plugins:
multvals.vim (3.5.1) - http://www.vim.org/script.php?script_id=171
genutils.vim (1.10.1) - http://www.vim.org/script.php?script_id=197

Upgrading
----------------
If you had a previous copy < 2.00 the plugin has been renamed from db_ext.vim to dbext.vim.  Remove the previous version, and remove any options specified in your vimrc.

Maintainer
--------------
David Fishburn, for issues please post to the Vim mailing list, or to David directly.  
:h dbext.txt has contact details.
 

rate this script Life Changing Helpful Unfulfilling 
script versions (upload new version)

Click on the package to download.

package script version date Vim version user release notes
dbext_420.zip 4.20 2006-12-19 7.0 Peter Bagyinszki Posted by David Fishburn.
New Features
- Improved support for Cygwin.  If you are using a Cygwin compiled Vim (on   Windows) and are accessing Windows compiled binaries (i.e. sqlplus.exe) the binary will complain since it does not understand Unix path names.  Added the option g:dbext_default_use_win32_filenames which allows you to indicate the binaries must use translated Windows paths instead. (Richard)
- DBGetOption displays more information.
                                                                              
Bug Fixes
- SQL Server support had issues with the queries when running DBCompleteTable, DBCompleteProcedure, DBCompleteView which also affected the sqlcomplete.vim plugin included with Vim7 (Albie Janse van Rensburg).
dbext_410.zip 4.10 2006-12-05 7.0 Peter Bagyinszki Posted by David Fishburn.
New Features
------------
- Updated DBGetOption to additionally display a list of all database profiles and their types.  All dbext options that have been overriden via the vimrc are also displayed.
                                                                          
Bug Fixes
------------
- db2 support had issues with the queries when running DBCompleteTable, DBCompleteProcedure, DBCompleteView which also affected the sqlcomplete.vim plugin included with Vim7 (Peter Princz).
- The documentation was still indicating there was a plugin dependency which has been removed with Vim7.
dbext_400.zip 4.03 2006-09-23 7.0 Peter Bagyinszki Posted by David Fishburn.
New Features
------------
- dbext.vim now requires Vim7.
- dbext.vim no longer has dependencies on other plugins by utilizing the new Vim7 features (Lists and Dictionaries).
- When using the DBCompleteTable, DBCompleteProcedure, DBCompleteView commands errors are displayed instead of silently ignored.  This makes them more useful with the sqlComplete plugin (see |sql.txt|).
- Added new option, dbext_default_MYSQL_version, for MySQL to indicate the version you using.
- You can optionally define a function, DBextPostResult, in your .vimrc, this function will be called each time the result window is updated.  This function can be used to do anything, for example, syntax highlighting the result set in the result window.

Bug Fixes
------------
- Added version support with MySQL to control what SQL is sent for version 4 and 5 servers.
dbext_350.zip 3.50 2006-06-20 6.0 Peter Bagyinszki New Features
------------
- g:dbext_default_inputdialog_cancel_support = 0 will prevent inputdialog from providing a cancel option.  This works around a Vim7 bug.  dbext will automatically detect this and set the option the first time it is encountered.
- Changed the order of some of the text in the dialog boxes to make them more readable when using the console version of Vim.
- dbext can parse SQL statements and prompt the user to replace variables with values prior to sending the statement to the database to be executed.  This is useful for testing SQL which is embedded in your code without having to manually replace variables and string concatentation.  A new identifier (the until flag) allows you to specify the beginning of a string and what to prompt for until a finishing string.  This makes it more flexible for you to configure what you would like prompting for when looking for variables.

Bug Fixes
------------
- DBPromptForBufferParameters can report "E180: Invalid complete value: -1" if running the console version of Vim.  dbext will detect this problem and automatically set g:dbext_default_inputdialog_cancel_support = 0 to work around this Vim7 bug.

dbext_300.zip 3.00 2006-05-15 6.0 Peter Bagyinszki New Features
------------
- dbext supports a history of previous commands.  The DBHistory command will display a numbered list of previous SQL statements.  Pressing <enter> or double clicking on one of the items will execute the statement.  The number of items in the list is configurable via your vimrc.  The history items are stored in a file, dbext_sql_history.txt.  The location of the file can also be controlled.
- The 'refresh' feature added in version 2.30 has been updated to take advantage of the history feature.
- The PHP parser has improved and can handle single or double quoted strings, string concatenation and host variables.  It will correctly strip the quotes, join the concatenated strings and prompt the user for host variables before executing the SQL statement.
- Updated documentation for Vim 7 SQL code completion.
- Table, procedure and view dictionaries include the owner name of the object.  This is on by default, but can be controlled via a new global option, dbext_default_dict_show_owner.  This has not been enabled for all databases it depends on whether the database supports this feature.  The autoload\sqlcomplete.vim plugin takes advantage of this feature.
- Added support for stored procedures / functions in MySQL 5.
                                                                            
Bug Fixes
------------
- Updated the PHP parser to work with a more varied string quotes and string concatenation.
- The "extra" feature did not add a leading space for MySQL.  Using the tabbed output required updates to the parsing of the output generated by MySQL.
- Miscellaneous documentation updates.
dbext_230.zip 2.30 2005-09-13 6.0 Peter Bagyinszki New Features
------------
- The result window has a local buffer mapping 'R', which will 'refresh' the window.  This means it will re-run the previous statement which is useful if you are repeatedly checking something in the database.
- SQL Anywhere (ASA) no longer relies on the jcatalogue tables to be installed in the database.  System views are used instead.
- Support for MySQL 5.0 has been added, which includes stored procedures and views (as much as the beta allowed).
- For Postgress SQL you can now optionally enter an owner name to filter by when showing list of objects (tables, procedures, views).

Bug Fixes
------------
- The alternate-file is no longer changed the first time the result buffer is opened.
- Using DB2 with db2cmd incorrectly specified the command terminator with td, instead of -t.
- On win32 platforms, if the bin_path has been specified for DB2, then add this to the system path since db2cmd relies on other batch files to operate correctly.
- The connection string is displayed by both the Result buffer (first line) and the titlestring to the buffer (if enabled).  This was not correctly appending the user used to connect to the database.
- When parsing Vim scripts we did not correctly remove a leading line continuation slash from the from of a query.
dbext_220.zip 2.20 2005-03-10 6.0 Peter Bagyinszki New Features
------------
- Added new connection parameter called "extra", you can place any
additional command line parameters in this option.
- DBGetOption displays all options including the dbext version.
- Better support for command terminators that have newline or special
characters in them.  For example ASE/SQLSRV use "\ngo\n", now the command
DBExecSQLUnderCursor will correctly find the statement.
- Use_tbl_alias defaults to "ask", instead of "default on".
- For most supported databases, when displaying the Table / Procedure /
View List, you can now enter a partial string "DBA.", and if a . is included
it will only display objects created / owned by that userid.
- DBExecSQLUnderCursor would sometimes stop in the middle of a query if the
command terminator was included (inside a quoted string), now it ensures
there is no text following the terminator.
- The result window also includes the error code returned by the binary
used to execute the command.  This can be useful for debugging.
- The first line of the result window includes a line showing the
connection information, if you have many buffers open, it can be difficult
to determine which database you are executing commands against.  A glance at
this line will tell you immediately.
- g:dbext_default_always_prompt_for_variables = 0 will prevent you from
being prompted for substitution parameters.  Setting this value to 1 will
always prompt the user.
- You can now abort the execution of a statement if you are prompted for
substitution parameters.
- If you are prompted for parameters, if you choose "Stop Prompting" the
command will be executed immediate.  If "Never Prompt" is chosen, no further
prompting will occur for this buffer.

Bug Fixes
---------
- SQLSRV did not have a default command terminator, now it is "\ngo\n".
- Changed the Oracle command terminator to ";", and the routine that
executes the statements automatically adds the "\nquit;\n" so that   sqlplus
will complete.
- Spaces were not correctly removed from column lists in all cases, this
showed up as an issue with the SQL Intellisense plugin.
- When executing SELECT statements the INTO clause (if present) is removed
so the results are displayed in the result window.  Refined the removal of
the INTO clause to ensure it does not interfer with an INSERT or MERGE
statement.
dbext_211.zip 2.11 2004-09-24 6.0 Peter Bagyinszki Bug fix: On some platforms the temporary file created to execute SQL
statements must end in ".sql".  Corrected this for all databases.
dbext_210.zip 2.10 2004-09-15 6.0 Peter Bagyinszki New Features
- Support for the SQLite database has been added (thanks to Ron Aaron).
dbext-2.01.zip 2.01 2004-07-22 6.0 Peter Bagyinszki New Features
- Tutorial - Based on feedback from users, created a step by step tutorial.
All users should try the tutorial to learn how to use the various features
of the plugin.
- DBGetOption - without parameters populates the Result window with all
options.
- DBGetOption and DBSetOption support command line completion for the
various options (DBGetOption d<tab>, will cycle through all options
beginning with "d").
- New option, replace_title, changes the title of the buffer to show
connection information which is useful if you are using  a scratch buffer to
test SQL.
- IntegratedLogin support for Windows users.
- DBExecRangeSQL - useful for custom mappings, and executing commands
without visual mode.
- New maps <Leader>sea - sql - execute - all (lines in the file),
<Leader>sel - sql - execute - line (the current line)
- New filetype support - jproperties


Bug Fixes
- dbname was not defaulting correctly.
- bin_path did not work correctly on windows platforms.
- Updated the connection text in the Result buffer.
- Overhauled the DB2 support.
dbext-2.0.zip 2.00 2004-07-11 6.0 Peter Bagyinszki dbext.vim has been greatly enhanced for version 2.00.

Additional features include:

Connection Profiles
You can create as many profiles as you like in your vimrc.  Each profile specifies various connection information.  When you are prompted for connection information, for ease of use,  you can choose from a numbered list.

Modeline Support
Similar to Vim modelines, you can specify connection information as comments within your buffers.  To prevent sensitive information (ie passwords) from being visible, you can specify a connection profile as part of your modeline.  

Object Completion
dbext ties into Vim dictionary feature.  You can complete table names, procedure names and view names.

Viewing Lists of Objects
You can browse through the various objects in the database you are connected
to and specify wildcard information.  For example you can say, "Show me all
tables beginning with 'ml_' ".  These objects are currently supported:
Tables, Procedures, Views,  Columns (for a table).

FileType Support Added
dbext has support for various filetypes.  For example, assume you dynamically build a SQL statement by joining strings together, using calls to functions, host parameters.  By visually selecting the code and executing it dbext will parse the statement, strip out the various string joining commands (+, ||,  .) and prompt you for host values before sending the string to be executed against the database.  This way it is very easy to test your SQL statements without having to cut and paste the commands into a SQL tool.  Currently supported filetypes are: PHP, Java, JSP, JavaScript, Perl, SQL, Vim.

Intellisense Addin Support
The Intellisense  plugin now has SQL supported.  To enable greater functionality (table, procedure, view lists), it uses dbext.vim to retrieve this information.
db-ext-0.61.tar.gz 0.61 2003-03-04 6.0 Peter Bagyinszki New DB types added. Now supported: Mysql, PostgreSQL, Ingres, Oracle, Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, Microsoft SQL Server, DB2 and Interbase.
Added the ability to parse the statement being executed and prompt the user for input parameters.  The logic differs based on what type of file you are editing.
db_ext.tar.gz 0.52 2002-11-28 6.0 David Fishburn When setting up buffer parameters, I default to ones already entered (useful when you made a mistake - saves typing).

Fixed some ASE related issues.
db_ext.tar.gz .51 2002-11-26 6.0 David Fishburn Added menus to gvim (if enabled).

Updated the documentation for extra configuration details.

Fixed a minor bug indicating it was referencing an undefined variable, b:db_ext_buffer_lines.  A workaround for this is to add the following to your _vimrc file:
let g:default_db_ext_buffer_lines = 5
db_ext.tar.gz 0.5 2002-11-25 6.0 David Fishburn New DB types added.  Now supported: Sybase Adaptive Server Anywhere(ASA), Ingres, Interbase, MySQL, PgSQL,  Sybase Adaptive Server Enterprise(ASE), Microsoft SQL Server,  Oracle.  

New, buffer specific connection parameters, so two buffers can connect to different databases.

Borrowed from Decho.vim (thanks Chip), now the results of any SQL statement are stored in a read only buffer called Result.  If the command fails diagnostic information is displayed plus connection information.

The Result buffer is reused for new commands, and can be moved where ever the user wishes.

Added the ability to describe not only tables, but also stored procedures (if your database supports it).

Reworked the mappings and public interfaces to follow the tip:  How to write a plugin (thanks Chip).

Added the ability to prompt the user for connection parameters.  If you do not define defaults, or want to connect to a different database, a simple wizard takes you through setting up the parameters.

Updated the documentation to reflect the new changes.  :h db_ext
db_ext-0.4.tar.gz 0.4 2002-08-20 6.0 Peter Bagyinszki New DB types added. Now supported: Sybase ASASQL, Ingres, Interbase, MySQL, PgSQL

Queries can now be executed from visual block.
db_ext.0.3.tar.gz 0.3 2002-07-28 6.0 Peter Bagyinszki Initial upload

If you have questions or remarks about this site, visit the vimonline development pages. Please use this site responsibly.
Questions about Vim should go to vim@vim.org after searching the archive. Help Bram help Uganda.
   
Sponsored by Web Concept Group Inc. SourceForge.net Logo