|
|
|
|
|
|
| |
o Stored procedure written in T-SQL to indent T-SQL source code and also
show a method to open and read a text file and write to text file in
the hard disk.
o Show error if there is a not equal number of commands begin than commands end
| ---------------------------------------------------------------------
-- APPLICATION : UTILITY TO INDENT T-SQL SOURCE CODE
-- AUTHOR : AITOR SOLOZABAL MERINO ( SPAIN )
-- EMAIL : aitor-3@euskalnet.net
-- TITLE : T-SQL-INDENTER
-- DESCRIPTION : PROCEDURE WRITTEN IN T-SQL TO BASIC INDENT OF SOURCE
-- TRANSACT SQL CODE
-- DATE : 21/08/2006
-- RETURN VALUE : 0 SI NO HA HABIDO ERRORES
----------------------------------------------------------------------
IF OBJECT_ID('TSQLINDENTER') IS NOT NULL DROP PROCEDURE TSQLINDENTER
GO
CREATE PROCEDURE TSQLINDENTER
(
-- sample : EXECUTE TSQLINDENTER 'PROGRAM.SQL'
@NOMFIC VARCHAR(250), --'PROGRAM.SQL'
@RETURNVALUE INT=NULL OUT
)
AS
BEGIN
-- VARIABLES DECLARATION
------------------------------
DECLARE @NUMBEGIN INT
DECLARE @NUMTABS INT
DECLARE @COMANDO VARCHAR(8000)
DECLARE @COMAND_BEGIN VARCHAR(10)
DECLARE @COMAND_END VARCHAR(3)
DECLARE @CARACTER1 INT
--FILE SYSTEM OBJECT VARIABLES
------------------------------
DECLARE @FS1 int
DECLARE @FS2 INT
DECLARE @RecordsExist int
DECLARE @FileID1 int
DECLARE @FileID2 int
DECLARE @File1 VARCHAR(1000)
DECLARE @File2 VARCHAR(1000)
DECLARE @ForReading int
DECLARE @ForWriting int
DECLARE @ForAppending int
DECLARE @FileLine varchar(8000)
DECLARE @RC INT
------------------------------
SET @ForReading = 1
SET @ForWriting = 2
SET @ForAppending = 8
SET @RecordsExist = 0
------------------------------
SET @NUMBEGIN = 0
SET @NUMTABS = 0
SET @RETURNVALUE = 1
------------------------------
IF @NOMFIC IS NOT NULL
BEGIN
SET NOCOUNT ON
SET @NOMFIC=RTRIM(LTRIM(@NOMFIC))
EXEC master..xp_fileexist @NOMFIC, @RC OUTPUT
IF (@@ERROR = 0) AND (@RC = 1)
BEGIN
------------------------------
SET @File1 = @NOMFIC
SET @File2 = LEFT(@NOMFIC,LEN(@NOMFIC)-4)+'-NEW.SQL'
-- Crear un objeto de fichero para la creación del archivo de lectura
-- Create a file object to read a file
EXECUTE @RC = sp_OACreate 'Scripting.FileSystemObject', @FS1 OUT
IF (@@ERROR|@RC > 0 Or @FS1 < 0) RAISERROR ('No se puede crear un objeto fichero FS1.',16,1)
-- Crear un objeto de fichero para la creación del archivo de escritura
-- Create a file object to write a file
EXECUTE @RC = sp_OACreate 'Scripting.FileSystemObject', @FS2 OUT
IF (@@ERROR|@RC > 0 Or @FS2 < 0) RAISERROR ('No se puede crear un objeto fichero FS2.',16,1)
-- Obtener un identificador de fichero para la apertura del archivo de lectura
-- Obtain a file identifier to open the file to read
EXECUTE @RC = sp_OAMethod @FS1, 'OpenTextFile', @FileID1 OUT, @File1, @ForReading,True
IF (@@ERROR|@RC > 0 Or @FileID1 < 0) RAISERROR ('No se puede abrir el fichero FileID1=%s',16,1,@File1)
-- Obtener un identificador de fichero para la creación y/o apertura del archivo de escritura
-- Obtain a file identifier to create/open the file to write
EXECUTE @RC = sp_OAMethod @FS2, 'OpenTextFile', @FileID2 OUT, @File2, @ForWriting,True
IF (@@ERROR|@RC > 0 Or @FileID2 < 0) RAISERROR ('No se puede abrir el fichero FileID2=%s',16,1,@File2)
------------------------------
EXECUTE @RecordsExist = sp_OAMethod @FileID1, 'ReadLine', @FileLine OUTPUT
WHILE @RecordsExist >= 0
BEGIN
------------------------------
SET @COMAND_BEGIN=''
SET @COMAND_END=''
SET @COMANDO=RTRIM(LTRIM(@FileLine))
------------------------------
IF LEN(@COMANDO)>0
BEGIN
-- Bucle para extraer el primer caracter de la cadena si es menor ó igual que el espacio en blanco chr(32) y mayor que el co¡digo 126
-- Loop to extract the first character of the string if it is lower or equal to space chr(32) or upper than 126
SET @CARACTER1=ASCII(LEFT(@COMANDO,1))
WHILE (LEN(@COMANDO)>1) AND ((@CARACTER1<=32) OR (@CARACTER1>=127))
BEGIN
IF LEN(@COMANDO)>1
BEGIN
SET @COMANDO=RIGHT(@COMANDO,LEN(@COMANDO)-1)
SET @CARACTER1=ASCII(LEFT(@COMANDO,1))
END
END
IF LEN(@COMANDO)>=10
BEGIN
SET @COMAND_BEGIN=UPPER(RTRIM(LEFT(@COMANDO, 10)))
IF @COMAND_BEGIN <>'BEGIN TRAN' SET @COMAND_BEGIN=UPPER(RTRIM(LEFT(@COMANDO, 5)))
END
ELSE
BEGIN
IF LEN(@COMANDO)>=5 SET @COMAND_BEGIN=UPPER(RTRIM(LEFT(@COMANDO, 5)))
END
IF LEN(@COMANDO)>=3 SET @COMAND_END=UPPER(RTRIM(LEFT(@COMANDO, 3)))
IF @COMAND_BEGIN='BEGIN'
BEGIN
SET @NUMBEGIN=@NUMBEGIN+1
SET @NUMTABS=@NUMTABS+1
IF (@NUMTABS>0) SET @COMANDO=SPACE((@NUMTABS*3))+@COMANDO
SET @NUMTABS=@NUMTABS+1
END
IF @COMAND_END='END'
BEGIN
SET @NUMBEGIN=@NUMBEGIN-1
SET @NUMTABS=@NUMTABS-1
IF (@NUMTABS>0) SET @COMANDO=SPACE((@NUMTABS*3))+@COMANDO
SET @NUMTABS=@NUMTABS-1
END
IF (@COMAND_BEGIN<>'BEGIN') AND (@COMAND_END<>'END')
BEGIN
IF (@NUMTABS>0) SET @COMANDO=SPACE((@NUMTABS*3))+@COMANDO
END
END
EXECUTE @RC = sp_OAMethod @FileID2, 'WriteLine', NULL, @COMANDO
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido escribir en el fichero ',16,1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t write to the the file ',16,1)
EXECUTE @RecordsExist = sp_OAMethod @FileID1, 'ReadLine', @FileLine OUTPUT
END
EXECUTE @RC = sp_OAMethod @FileID1, 'Close', NULL
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido cerrar el fichero FileID1=%s',16,1,@File1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t close the file FileID1=%s',16,1,@File1)
EXECUTE @RC = sp_OAMethod @FileID2, 'Close', NULL
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido cerrar el fichero FileID2=%s',16,1,@File2)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t close the file FileID2=%s',16,1,@File2)
EXECUTE @RC = sp_OADestroy @FileID1
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir la identidad del fichero FileID1',16,1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the identity of the file FileID1',16,1)
EXECUTE @RC = sp_OADestroy @FileID2
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir la identidad del fichero FileID2',16,1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the identity of the file FileID2',16,1)
EXECUTE @RC = sp_OADestroy @FS1
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir el objeto FS1',16,1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the file object FS1',16,1)
EXECUTE @RC = sp_OADestroy @FS2
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir el objeto FS2',16,1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the file object FS2',16,1)
PRINT @NUMBEGIN
IF @NUMBEGIN<>0
BEGIN
--PRINT 'ERROR: El fichero '+@NOMFIC+' tiene un número desigual de BEGIN y END'
PRINT 'ERROR: The File '+@NOMFIC+' have not an equal number of commands BEGIN and END'
END
ELSE
BEGIN
--PRINT 'El fichero '+@NOMFIC+' tiene un número igual de BEGIN y END'
PRINT 'The File '+@NOMFIC+' have an equal number of commands BEGIN and END'
SET @RETURNVALUE=0
END
END
ELSE
BEGIN
--PRINT 'ERROR: El fichero '+@NOMFIC+' no existe'
PRINT 'ERROR: The File '+@NOMFIC+' not exist'
END
END
ELSE
BEGIN
PRINT 'ERROR: El argumento con el nombre del fichero esta vacio'
END
END | |
|
|
| Point and Click Interface ala MS Access for creating SQL statements.
Categories : MySQL, Complete Programs, General SQL, PHP, Databases | | | Is there some possibility to link a database to an htaccess file, so that instead of having a passwd file you would have a database with DES-crypted password and username fields?
Categories : Authentication, PHP, General SQL, Databases | | | generic sql insert non-literal values
Categories : General SQL, Databases | | | phpEasySQL - Easily connect to your MySQL database with just 1 php file and 3 easy steps!
Categories : Databases, PHP, MySQL, General SQL | | | Logs hits to any page which includes it. Automatically utilises page access information left behind by PHP/FI2.0.
Categories : Databases, PHP, mSQL, Databases | | | This function will populate the options in a drop down HTML select list
in a form from a database query.
Categories : MySQL, General SQL, PHP, HTML and PHP, Databases | | | I`d like to use the mysql_fetch_row function along with a "randomizer"
function that would give me a random result from a mySQL table.
Categories : General SQL, MySQL, PHP, Databases | | | Function to check connection to MySQL and redirect to an error page if an error occurs
Categories : MySQL, PHP, Debugging, Databases | | | Sql Builder
Categories : PHP, HTML and PHP, Databases, General SQL, Form Processing | | | Recordset Class for MSSQL database
Categories : PHP Classes, Databases, PHP, MS SQL Server | | | Guestbook sort by descending date and can view page by page
Categories : PHP, MS SQL Server, Databases | | | Scripts to build APACHE - PHP and SQL 7.0
Categories : PHP, Databases, MS SQL Server | | | Newbie Notes #4 - Trapping dumb MySQL query errors
Categories : PHP, Databases, MySQL, Debugging, Beginner Guides | | | How to connect to MS SQL 6.x+ database server via ODBC functions of
PHP3 compiled with iODBC and Openlink drivers under Linux.
Categories : Databases, MS SQL Server, PHP, ODBC | | | Convert SQL from oracle,mysql,mssql,sqlite and odbc to SQL compatible
Categories : PHP, PHP Classes, Databases, MySQL, MS SQL Server | |
| | | | aitor solozabal wrote :1658
This utility perform a basic indentation of the source code, only indent the source between begin and end commands but it is useful to check the readability and the quality of the code and show if any begin and/or end commands is missing.
| |
|
|
|
