The macro takes the currrently selected text, writes it to the zeus_sql.tmp temp file, executes the file using iSql.exe and captures the resulting output to a Zeus tool window.
Jussi
Code: Select all
--
-- Name: SQL Macro
--
-- Author: Jussi Jumppanen
--
-- Language: Lua Macro
--
-- Description: A simple Lua macro that takes the currently selected
-- text, saves it to the c:\temp\zeus_sql.tmp file, runs
-- it through the isql.exe utility and displays the result
-- of the query in an SQL window.
--
-- How To Run: Save to sql.lua in the zScript directory.
--
-- All Zeus macros can be run using the macro execute menu
-- item or they can be bound to the keyboard or can be
-- attached to the macro drop down or popup menus.
--
function key_macro()
-- see if we have any marking
marked = is_marked()
screen_update_disable()
if (marked == 0) then
-- macro needs some marked text so select the entire file
MarkSelectAll()
end
-- get the marked text
local sql_text = "SET NOCOUNT ON\n" .. macro_tag("$M")
if (marked == 0) then
-- remove the mark that was added
MarkHide()
end
-- file to contain the selected sql command
local file_name = "d:\\temp\\zeus_sql.tmp"
-- open the temp file in binary write mode
local file_out = io.open(file_name, "w+b")
if file_out ~= nil then
-- write the text to file
file_out:write(sql_text)
file_out:close()
-- get the server and database details
local server = get_global_string("SERVER_NAME", true)
local database = get_global_string("DATABASE_NAME", true)
-- ask for a user to check the details
server, database = user_input("Server Name:", server, "Database Name:", database)
if (string.len(server) == 0) and (string.len(database) == 0) then
message("Operation cancelled by user.")
return 1
end
if (string.len(server) == 0) then
message("No Server was specified!")
beep()
return 1
end
if (string.len(database) == 0) then
message("No Database was specified!")
beep()
return 1
end
set_global_string("SERVER_NAME", server, true)
set_global_string("DATABASE_NAME", database, true)
-- start to build up the command line options
local cmd_options = "-S " .. server .." -d ".. database .. " -E -w 10000 -n"
-- build up the command line
local command = "isql.exe" .. " " .. cmd_options .. " -i " .. file_name
-- build a title for the tool
local title = "iSql [" .. server .. ":" .. database .. "]"
-- command options: capture output
local flags = 2
-- run command which should display a DOS window
if system(command , 0, flags, title) ~= 0 then
-- failed to run tool
message_box(1, "Error running the command line:\n\n" .. command)
end
else
-- failed to create file
message_box(1, "Error creating the '" .. file_name .. "' file.")
end
screen_update_enable()
end
key_macro()