LEER XML EN SQL (SELECT XML)

Buenos Días. Volviendo de vacaciones y compartiendo lo siguiente.

Muchas veces nos encontramos con situaciones que las soluciones suelen ser algo extravagantes, en esta entrada compartiré un tip que luego estará en el esquema del curso, ¿de qué se trata?, leer los XML como una tabla.

XML en SQL contiene muy buenas ventajas para quienes lo usen adecuadamente, es un tipo de variable especifico, es nativo, es de fácil declaración del lado de la programación, es flexible para trabajar, etc…

Entonces la pregunta es, a fines prácticos ¿en que nos puede ayudar?, enumero algunas situaciones.

• Envió de múltiples variables desde un sistema a la base de datos
• Envió de tablas desde un sistema a la base de datos en un esquema altamente entendible
• Recepción de servicios SOAP y operarlo directamente sobre el SQL sin pasar por ningún tipo de software externo, parsing, etc… ¿dije SOAP?, SI… En algún momento les mostrare como consumir servicios desde SQL y que configuración adicional tenemos que incorporar, pero eso lo dejaremos para más adelante :)

Estos son algunos de los usos posibles del XML sobre SQL, y sin duda tendremos infinidades de casos en que usarlo será el camino más óptimo a nuestro problema, y sin duda, nos puede ayudar a brindar una solución mucho más profesional.

Obviare la explicación de los que es una cadena en formato XML, por lo tanto, lo explicare muy muy simple… Es un METALENGUAJE, que permite etiquetar y organizar los elementos, muchos archivos y sistemas utilizan XML para trabajar, inclusive una extensión tan popular como el SVG se apoya sobre XML para definirle las características.

Empecemos…

Utilizaremos para este ejercicio un ejemplo escrito para poder trabajar con el, donde definiremos una variable XML, la cargaremos y luego realizamos la lectura del mismo, tenemos muchas formas de leer XML en SQL, les mostrare unas de tantas, para mi gusto la mas simple, pero eso es solo una apreciación mía.

Lo primero que haremos es crear la variable XML y cargarla con contenido.

DECLARE @VARIABLE_XML XML

SET @VARIABLE_XML = '<tabla1>
<fila><campo1>216000</campo1><campo2>3</campo2></fila>
<fila><campo1>217000</campo1><campo2>4</campo2></fila>
<fila><campo1>218000</campo1><campo2>5</campo2></fila>
<fila><campo1>219000</campo1><campo2>6</campo2></fila>
</tabla1>'

Como se puede apreciar, existe un tipo de variable en SQL que es XML, y al mismo luego le incorporamos un valor XML, en este caso muy sencillo, se puede apreciar que es un XML donde pasamos una TABLA1, el mismo esta compuestos por “FILA”, y luego está dividido en “CAMPO”, si nos fijamos cada “CAMPO” tiene valores, vamos a aprender a leerlos!!!

SELECT 
TABLA1.ID.value('campo1[1]','INT') AS CAMPO_1,
 TABLA1.ID.value('campo2[1]','NVARCHAR(4000)') AS CAMPO_2
  FROM @VARIABLE_XML.nodes('/tabla1/fila') as TABLA1(ID) 

Bien, en la anterior consulta se puede apreciar la forma de leerlo, esta es un tipo de consulta utilizando Xquery en SQL, el mismo funciona por defecto, por tanto no necesitamos de realizar ningún tipo de configuración adicional.

Explicando la query anterior, esto funciona de la siguiente forma:

Como se puede ver es una consulta SELECT clásica, donde seleccionamos los valores de un FROM, en este caso el FROM es un XML, donde instanciamos la variable XML, y luego buscamos los NODOS, nosotros por el tipo de estructura del XML llegamos hasta el nodo FILA, y luego una vez instanciado hasta el nodo fila, generamos un ALIAS y dentro del mismo le generamos una columna (lo que esta entre paréntesis), no detallare mucho sobre porque se define de esta forma, pero SQL devuelve una especie de TABLA en memoria.

Vamos ahora al SELECT, aquí como se puede ver, la estructura es, TABLA.COLUMNA.VALOR([NODO XML], [TIPO DE VALOR]) as NOMBRE_COLUMNA.

Esto es, llamamos a la tabla y al valor de la columna, y entre paréntesis instanciamos el valor que queremos utilizar dentro de la estructura XML, en estos casos CAMPO1 y CAMPO2, y luego muy importante tenemos que definir qué tipo de dato es, si es un INT, VARCHAR, DATETIME, etc…

Muy bien!, sé que puede parecer un poco confuso al principio, pero es cuestión de practicarlo un poco y veremos que es muy práctico. A continuación pondré un ejemplo un poco más complejo en el que podemos observar que se pueden realizar cruces sin ningún tipo de problema, tanto dentro del XML como con tablas externas, en este caso estoy enviando dos tablas dentro del mismo XML y cruzándolas en la consulta.

 
DECLARE @VARIABLE_XML XML

SET @VARIABLE_XML = '<tabla1>
<fila><campo1>216000</campo1><campo2>3</campo2></fila>
<fila><campo1>217000</campo1><campo2>4</campo2></fila>
<fila><campo1>218000</campo1><campo2>5</campo2></fila>
<fila><campo1>219000</campo1><campo2>6</campo2></fila>
</tabla1>
<tabla2>
<fila><campo1>316000</campo1><campo2>3</campo2></fila>
<fila><campo1>317000</campo1><campo2>4</campo2></fila>
<fila><campo1>318000</campo1><campo2>5</campo2></fila>
<fila><campo1>319000</campo1><campo2>6</campo2></fila>
</tabla2>'


SELECT  TABLA1.ID.value('campo1[1]','INT') AS TABLA1_CAMPO_1,
   TABLA1.ID.value('campo2[1]','NVARCHAR(4000)') AS TABLA1_CAMPO_2,
   TABLA2.ID.value('campo1[1]','NVARCHAR(4000)') AS TABLA2_CAMPO_1
    FROM  @VARIABLE_XML.nodes('/tabla1/fila') as TABLA1(ID)
INNER JOIN  @VARIABLE_XML.nodes('/tabla2/fila') as TABLA2(ID) 
 ON
  TABLA1.ID.value('campo2[1]','NVARCHAR(4000)') = TABLA2.ID.value('campo2[1]','NVARCHAR(4000)')

Excelente!, invito a probar la siguiente consulta para que vean el comportamiento  del mismo!!!

Bueno eso es todo, cualquier consulta no duden en preguntar.

Espero que les sea de mucha utilidad, hasta la próxima.

CONSULTA SELECT 1

Buenos Días.

Hoy estaremos empezando con una serie de artículo, del famoso SELECT *, mejor dicho, cláusula de selección para SQL, una forma de obtener RecordSets.

Dicha sentencia que parece muy simple, en realidad puede tener altísimos niveles de complejidad, dependiendo del tipo de resultado que se necesita obtener, dichos resultados pueden ser, tablas planas, agrupamientos, cruces, pivotes, etc... Iremos viendo todos estos en su debido artículo. Hoy empezaremos con el más básico, y algunos TIPS para no tan básicos.

****************
NOTA:

Nuestra base de datos de trabajo será la base de datos: AdventureWorks2008R2, sobre Microsoft SQL 2008R2, y utilizando como IDE el Microsoft Server Management 2008. En caso de no tener nada de esto instalado, por favor entrar en Guia de Instalación Básica, para tener todo lo necesario y luego proseguir.

*****************

Empecemos!!! :)

La consulta SELECT se utiliza para indicar a la base de datos que nos devuelva cierta información en un objeto que almacena registros (RecordSet), esto es sumamente importante, porque si tenemos código que necesita de devoluciones de la base de datos, la misma puede obtenerla por medio de RecordSet o variables de retorno tipo OUTPUT (aunque esto lo dejaremos para mucho más adelante), Sigamos.

Tal cual lo comentado la sentencia SELECT obtiene un RecordSet, y la estructura es la siguiente.

SELECT [CAMPOS/VALORES] FROM [NOMBRE_TABLA]

Por lo general, y en el 99% de los casos, utilizaremos los campos de cierta tabla que necesitemos, aunque podemos tener excepciones!, en el cual necesitemos incorporar valores específicos a la consulta SELECT, no nos mareemos, un ejemplo vale más que mil palabras, vamos a lo práctico.

Sabiendo que ya estamos trabajando dentro de la base de datos AdventureWorks, disfrutaremos de unas sentencias. Antes que nada, voy a aclarar que mostraré las sentencias en formato PURISTA, eso significa no obviando cierta sintaxis que el motor de base de datos sabe reconocer y por otra parte la base de datos adventureWorks utiliza la notación de SCHEMA.TABLE, esta notación la utilizan muy pocos sistemas (la mayoría utiliza un único esquema DBO) y es una buena práctica para trabajar con las tablas (no es la única).

Primer Ejemplo:

SELECT 
  PE.BusinessEntityID,
  PE.PersonType,
  PE.NameStyle,
  PE.Title,
  PE.FirstName,
  PE.MiddleName,
  PE.LastName,
  PE.Suffix,
  PE.EmailPromotion,
  PE.AdditionalContactInfo,
  PE.Demographics,
  PE.rowguid,
  PE.ModifiedDate 
   FROM [PERSON].[PERSON] AS PE

Como podemos apreciar, es una consulta muy muy sencilla, donde la estructura anterior se cumple, se encuentran los campos escritos en la cláusula SELECT y estos vienen del esquema PERSON tabla PERSON = PERSON.PERSON.

De la anterior consulta uno solo puede utilizar los campos que necesita, ya que estos se encuentra declarados, una forma práctica de instanciar todos los campos sin la necesidad de escribirlos todos, es de la siguiente forma.

SELECT * FROM [PERSON].[PERSON]

De esta manera el motor de SQL reconoce que antes de ejecutar la consulta en el motor de base de datos, tiene que instanciar todas las columnas (reconoce la estructura), esto reduce en forma insignificante la performance debido a que el motor antes de disparar la consulta la tiene que interpretar, y agrega un paso más al plan de ejecución. En caso de ser una consulta que se ejecuta un par de centenas de veces por minuto la pérdida de rendimiento no es significativa, pero en caso de ser una consulta que se ejecute miles de veces por segundo, la merma es para tenerla en cuenta (sobre todo si hay campos que se nos devuelven y son innecesarios). Sepamos que cuanto más optimicemos las consultas de un sistema, mejoraremos el rendimiento del motor de base de datos y tendremos mejores resultados en nuestros sistemas.

Bien, sepamos que cuando utilizamos el *, no tenemos forma de excluir en la sentencia los campos que no queremos, mostrando siempre todos.

Siguiendo con lo anterior, realizaremos algo simple y más practico, supongamos que queremos un listado de todos los nombres que estuvieron en AdventureWorks, con los siguientes campos, Primer Nombre, Segundo Nombre, Apellido.

Para ello usamos la siguiente consulta:

SELECT 
  PE.FirstName,
  PE.MiddleName,
  PE.LastName
   FROM [PERSON].[PERSON] AS PE

Realizado esto, supongamos que queremos ordenarlos por primer nombre ASCENDENTE (ASC), también podemos realizarlo en forma DESCENDENTE (DESC)

Nos queda de la siguiente forma:

SELECT 
  PE.FirstName,
  PE.MiddleName,
  PE.LastName
   FROM [PERSON].[PERSON] AS PE
ORDER BY 
  PE.FirstName ASC

En esta lista vamos a tener nombres que se nos repiten, por lo tanto queremos solamente los distintos, utilizamos el predicado DISTINCT:

SELECT DISTINCT
  PE.FirstName,
  PE.MiddleName,
  PE.LastName
   FROM [PERSON].[PERSON] AS PE
ORDER BY 
  PE.FirstName ASC

Supongamos que en el informe que nos piden, necesitan que el formato sea de la siguiente manera: Apellido, primer nombre segundo nombre.

La forma de realizarlo es la siguiente:

SELECT DISTINCT
  ISNULL(PE.LastName, '') + ', ' + ISNULL(PE.FirstName, '') + ' ' + ISNULL(PE.MiddleName, '') AS NOMBRE
   FROM [PERSON].[PERSON] AS PE
ORDER BY 
  NOMBRE ASC

¿¿¿PERO QUE PASO ACA???, Aparecen cosas nuevas, y aparte en el ORDER BY tenemos el nombre de un ALIAS, y no el de un campo de la tabla. Acá vemos como a veces hacer algo que parece simple, requiere de cierta técnica para realizarlo correctamente. Empecemos a aclarar las aguas!!!

En primer punto estamos usando el operador '+', este operador se comporta de diferentes maneras dependiendo los tipos de campos afectados, por otra parte, los tipos de campos deben de ser del mismo tipo, para solucionar inconvenientes en caso de que no sean del mismo tipo, siempre podemos castear pero eso ya lo veremos más adelante con detalle, por lo pronto sabemos que es de la siguiente manera.

SELECT TEXTO1 + ' ' + TEXTO2 = TEXTO1 TEXTO2

SELECT NUMERO>1 + NUMERO>2 = NUMERO>3

En lo anterior se resume la forma, se puede apreciar que en el caso de texto incorporo un literal de espacio, para que no quede todo junto, caso contrario queda TEXTO1TEXTO2.

Bien, con esto anterior sabemos que podemos concatenar textos, o realizar operaciones con los números, para el caso de los números se soportan todas los operadores matemáticos por otra parte el motor de SQL contiene también funciones matemáticas avanzadas.

Sigamos con lo que sigue, ¿porque utilizamos ISNULL en los campos?, Acá es importante entender el concepto de lo que es NULL. Cuando listamos la tabla anteriormente se podrá haber visto que el campo NULL aparte de decir esta palabra, estaba en otro color, otros IDE ponen el NULL en itálica, esto es así porque en realidad NULL es un estado adicional en un campo, que significa NO INICIALIZADO o VACIO.

Por ejemplo, un campo tipo BIT en una base de datos se puede trabajar con tres valores, ¿cómo es eso?, de la siguiente forma. El campo puede tener el valor 0, el valor 1 y el valor NULL, y con cada una podemos realizar operaciones diferentes. ¿Entonces porque estoy utilizando el ISNULL en la concatenación?, veamos en forma ejemplificada.

TEXTO1 + ' ' + TEXTO2 + ' ' + TEXTO3 = TEXTO1 TEXTO2 TEXTO3
TEXTO1 + ' ' + NULL + ' ' + TEXTO3 = NULL

Como se puede apreciar en el ejemplo anterior, si cualquiera de los campos que se está concatenando se encuentra NULL, toda la cadena será NULL y es por eso que utilizamos ese condicional en este caso, ya que en el algunos el campo middlename se encuentra NULL, al pasar esto, la información que obtendremos será incompleta.

La función del sistema ISNULL necesita de dos argumentos.

ISNULL (EXPRESION_A_VERIFICAR , VALOR_DE_REEMPLAZO)

Donde sí, EXPRESION_A_VERIFICAR es NULO, entonces este se reemplaza con VALOR_DE_REEMPLAZO, en nuestro ejemplo anterior, se reemplaza con un literal vacío ''. Aclaro que VALOR_DE_REEMPLAZO puede ser, una sub-consulta, variables,  funciones, etc... Pero eso lo iremos viendo más adelante.

Entonces analizado esto, nos quedan dos cosas más, el AS NOMBRE. Esto es, a cada campo, o campo creado de un literal, o campo concatenado, etc... Podemos utilizar un ALIAS y asignarle un nombre de campo, en caso de no hacerlo podemos tener ciertos inconvenientes al trabajar con las tablas, siempre es importante asignarle un ALIAS en caso de crear campos adicionales, si nos fijamos con detenimiento, entendemos que en dicha consulta, nosotros estamos creando el campo NOMBRE, con la concatenación de tres campos. Esto último contesta, porque en el ORDER BY ponemos NOMBRE, esto es así, porque DISTINCT solo admite en el ORDER BY, nombres de campo en la consulta, como el campo que mostramos, es un campo que creamos nosotros, la única manera de realizar un orden de nuestro resultado es instanciando nuestro campo NOMBRE, también en el ORDER BY podemos poner la suma de los tres campos tal cual en la consulta SELECT (sin el AS), pero queda un poco más desprolijo, pruébenlo y verán que funciona!

Por último, supongamos que solo queremos ver los primeros 100 que aparecen en la tabla y los demás descartarlos... Bueno para eso podemos utilizar el predicado TOP, y es tan simple como lo siguiente:

SELECT DISTINCT TOP 100
  ISNULL(PE.LastName, '') + ', ' + ISNULL(PE.FirstName, '') + ' ' + ISNULL(PE.MiddleName, '') AS NOMBRE
   FROM [PERSON].[PERSON] AS PE
ORDER BY 
  NOMBRE ASC

Donde el número 100 es la cantidad de registros que queremos mostrar.

Bueno, esto es todo, pero no nos olvidemos que esto es solamente una pequeña parte de la consulta SELECT, es muchísimo más completa!, pero lo dejaremos para la próxima, en el siguiente artículo.

Si encuentran algún detalle en la entrada no duden en decírmelo, espero que les sea de gran utilidad. Hasta la próxima.

No duden en visitar todos los articulos de esta tematica en la recopilacion de articulos para SQL

Valores del Sistema

Hablar de un AS/400 en el mundo de IT en algunos lugares suena muchas veces como hablar de una caja negra antigua y/o rara, y esto generalmente es debido a la falta de conocimientos sobre esta tecnología y de todo su potencial, que viene y sigue en evolución constante tanto a nivel hardware como a nivel software. Esta tecnología que muchos nunca han tenido la posibilidad de conocerla es sin duda un eslabón fundamental tanto en la evolución de la informática de los últimos tiempos como de los grandes sistemas computarizados, los cuales por suerte bancarios, seguros e industrias han sabido aprovechar y cosechar excelentes resultados sin altibajos o sorpresas como si han sufrido con otras tecnologías que llamaban "modernas". Pero de algo estoy seguro, esta tecnología que viene desde la escuela clásica de sistemas esta tan vigente hoy en día hasta el punto que todos nosotros tenemos contacto o la utilizamos sin saber que lo estamos haciendo.-

En esta ocasión les compartiré material básico desde la fuente sobre los valores del sistema del OS/400, aquellos que el sistema operativo dispone y utiliza para determinar parte de su comportamiento respecto a la seguridad, almacenamiento y valores básicos como ser los relacionados a la fecha y hora. El usuario con privilegios (administrador) puede cambiar los valores del sistema para definir el entorno de trabajo como por ejemplo son la fecha del sistema, la lista de biblioteca principal, la zona horaria, el separador de fecha por defecto, el nivel de seguridad, etc...

El AS/400 tiene más de 150 valores del sistema configurables, los cuales algunos tienen más relevancia que otros, como sucede con los relacionados a la fecha y hora a saber:

QTIME: Hora del día.  Este valor del sistema puede también establecerse desde la pantalla Opciones IPL también llamada Opciones de Arranque.

QTIMSEP: Separador de hora.

QTIMZON: Huso horario a fin de calcular la hora del sistema local.

QDATE: Fecha del sistema.  El formato de la fecha del sistema se especifica en el valor del sistema QDATFMT. El sistema soporta las fechas incluidas en el rango 24 de Agosto de 1928 a 6 de Julio de 2053. QDATE se compone de los siguientes valores del sistema: QYEAR, QMONTH y QDAY.                                        

QDAYOFWEEK: Día de la semana según QDATE. Ej. *SUN para el domingo, *MON para el lunes.

Los relacionados a ciertos procesos ya sea de arranque, reinicio del sistema, inactividad de un usuario, bibliotecas básicas del sistema operativo o bien configuración de la tecla "Esc" por defecto para los usuarios:

QATNPGM: Establece el Programa de atención por defecto. Dicho programa se ejecuta mediante la tecla "Esc" en un teclado QWERTY. Dicho valor se utiliza generalmente para implementar alguna aplicación de uso común para los usuarios que ingresan de forma interactiva bajo la clásica emulación 5250 como ser la administración de archivos de spool, la visualización de mensajes, etc..

QINACTITV: Intervalo de tiempo de espera en minutos de trabajo interactivo inactivo. Donde concluido el tiempo se puede determinar mediante otro valor de sistemas (QINACTMSGQ) la cola de mensaje y/o la acción como ser finalizar o desconectar el trabajo interactivo inactivo.

QIPLDATTIM: Fecha y hora de IPL automática para realizar un reinicio del sistema programado.

QIPLTYPE: Indica el tipo de IPL que debe realizarse. Si este será desatendido, o atendido con herramientas de servicio dedicado o en modalidad consola de depuración donde dejará el sistema de uso exclusivo de consola.

QCTLSBSD: La descripción del subsistema de control. El subsistema de control es el primer subsistema que se arranca después de una carga del programa inicial, luego de un IPL.

QSTRUPPGM: Indica el programa de arranque cuando se inicia el sistema operativo. Este programa realiza funciones de configuración tales como iniciar subsistemas, aplicaciones especificas necesarias, etc..

QSYSLIBL: Indica la lista de bibliotecas por defecto. Los archivos y programas se buscan o ejecutarán si no se especifica la biblioteca según esta lista antes que en ningún otro sitio. Algunas funciones de cliente utilizan esta lista para buscar objetos. No obstante luego a nivel de usuario se pueden especificar cambios o variantes según se requiera como también el valor QUSRLIBL que indica la lista por defecto de los usuarios.

Los relacionados a los datos propios del sistema:

QMODEL: Número de modelo del sistema.

QPRCFEAT: Característica de procesador.

QSRLNBR: Número de serie del sistema.

QQRYTIMLMT: Límite de tiempo de proceso de consultas.

QRMTIPL: Indicador de encendido remoto e IPL.

O los relacionados al idioma y nivel de ayuda por defecto de las pantallas:

QLANGID: Indica la abreviatura del idioma por omisión para el sistema. Este identificador determina el CCSID por omisión para un trabajo de usuario si el CCSID de trabajo es 65535. Los clientes y servidores utilizan este valor CCSID de trabajo por omisión para determinar la conversión correcta de los datos a intercambiar entre cliente y servidor. Ejemplo: ESP para ESPAÑOL.

QASTLVL: Indica el nivel de asistencia operativa de las pantallas nativas del sistema operativo. Como ser Básica, Intermedia o Avanzada.

QDSPSGNINF: Determina si se visualiza una pantalla informativa luego del inicio de sesión la cual indica fecha, hora, número de intentos de inicio no válidos.

Otros valores de sistemas que debemos tener muy presentes son los seguridad:

QMAXSIGN: Controla el número de intentos de inicio de sesión consecutivos incorrectos por parte de usuarios locales y remotos. Una vez alcanzado el valor indicado, el sistema determina la acción con otro valor del sistema (QMAXSGNACN) el indicará si se desactiva el dispositivo, el perfil o ambos.

QPWDEXPITV: Indica el número de días de validez de las contraseñas de los usuarios o bien si nunca caducan.

QPWDLMTAJC: Limita el uso de números inmediatos en una contraseña. Como por ejemplo evitar que usuarios coloquen como contraseñas las mismas pero solamente modificando un número final. PEPE1, PEPE2, etc.

QPWDLMTCHR: Limita el uso de ciertos caracteres en una contraseña.

QPWDLMTREP: Limita el uso de caracteres que se repiten en una contraseña.

QPWDLVL: Indica según unos parámetros, la longitud de las contraseñas que se admiten. Como ser de 1 a 10 dígitos, de 1 a 128 dígitos.

QPWDMAXLEN: Establece el número máximo de caracteres en una contraseña.

QPWDMINLEN: Establece el número mínimo de caracteres en una contraseña.

QPWDPOSDIF: Controla la posición de los caracteres en una contraseña nueva respecto a la anterior.

QPWDRQDDGT: Requiere de al menos un carácter numérico en una contraseña nueva.

QPWDRQDDIF: Controla si la contraseña debe ser si o si distinta de las anteriores.

QSECURITY: Indica el nivel de seguridad del sistema. Por ejemplo el nivel 20 significa que el sistema necesita una contraseña para iniciar la sesión. El nivel 30 indicará que el sistema necesita seguridad por contraseña al iniciar la sesión y seguridad a nivel de objeto en cada acceso. Es decir que deberá tener autorización para acceder a determinados recursos del sistema. El nivel 40 establece que el sistema necesita seguridad por contraseña al iniciar la sesión y seguridad a nivel de objeto en cada acceso. Los programas que intentan acceder a objetos mediante interfaces a las que no se da acceso, fallan. El nivel 50 indica que el sistema requiere seguridad por contraseña al iniciar la sesión y el usuario debe tener autorización especifica para acceder a objetos y recursos del sistema. Se aplica la seguridad e integridad de la biblioteca y los objetos de dominio de usuario de QTEMP. Fallarán los programas que intenten acceder a objetos mediante interfaces a las que no se da acceso, o que intenten pasar valores de parámetro no soportados a interfaces a las que se da acceso.

QAUDCTL: Dispone el valor de Control de auditoria. Este valor del sistema contiene los conmutadores de activado y desactivado para la auditoria a nivel de objeto y de usuario.

QAUDLVL: Indica el nivel de auditoria de seguridad. Como por ejemplo Eventos de supervisión de intrusiones que deben examinarse para determinar si la condición es una intrusión o un falso positivo, o los errores de autorización, o bien auditar todas las creaciones de objetos, o todas las supresiones, etc..

QAUTOVRT: Determina si el sistema permite crear automáticamente dispositivos virtuales al momento de realizar una conexión telnet o desde Client Access y la cantidad que se permiten.

Como verán esto se asemeja a los valores de sistemas de otros sistemas operativos tanto en el uso como en los conceptos que estos aplican.

Más adelante en otra entrada veremos casos específicos para tener en cuenta y el uso potencial de algunos de los mencionados con mayor detalle y ejemplos prácticos.

Hasta luego...

SQL EXPRESS y el problema del BACKUP AUTOMATICO

Hoy vamos a incorporar una entrada que seguramente le será útil a más de uno que esté utilizando SQL EXPRESS como servidor de base de datos, el gran problema que nos encontramos con este motor de base de datos, es la ausencia del agente, y por lo tanto no podemos automatizar nuestros procesos de respaldo con el planificador.

Sin embargo, con un pequeño script, y un archivo de ejecución por lote podemos conseguir, en el caso del respaldo, el mismo resultado que utilizando el agente, nada más que en vez de utilizar el planificador de SQL, utilizaremos el planificador de Windows.

Lo primero que tendremos que hacer, es generar un archivo con extensión SQL, este archivo contendrá nuestro script y lo armaremos de tal forma que modificarlo para diferentes utilidades sea muy sencillo.

--BACKUP DE TODAS LAS BASES

PRINT 'REALIZO BACKUP DE TODAS LAS BASES' 

DECLARE @NOMBRE                 VARCHAR(50) 
DECLARE @UBICACION              VARCHAR(256)   
DECLARE @NOMBRE_ARCHIVO         VARCHAR(256)  
DECLARE @FECHA_ARCHIVO          VARCHAR(20) 
  
-- Especifico la ubicacion del directorio de respaldo

SET @UBICACION = 'C:\bases\'   

-- Me grabo en variable la fecha que contendra todas las bases que estaremos respaldando

SELECT @FECHA_ARCHIVO = CONVERT(VARCHAR(20),GETDATE(),112)   

DECLARE VAL_CURSOR CURSOR FOR  

SELECT NAME FROM master.dbo.sysdatabases  
WHERE NAME NOT IN ('master','model','msdb','tempdb')   -- Incorporo las bases de datos que NO QUIERO RESPALDAR 

--RECORRO EL CURSOR Y GENERO LOS RESPALDOS UTILIZANDO EL SIGUIENTE BUCLE

OPEN VAL_CURSOR   

FETCH NEXT FROM VAL_CURSOR INTO @NOMBRE    
  
WHILE @@FETCH_STATUS = 0   
BEGIN   
    --GRABO EN VARIABLE EL NOMBRE DEL ARCHIVO FINAL
    SET @NOMBRE_ARCHIVO = @UBICACION + @NOMBRE + '_' + @FECHA_ARCHIVO + '.BAK'  

    --INSTANCIO LA SENTENCIA PARA GENERAR EL BACKUP EN DISCO
    BACKUP DATABASE @NOMBRE TO DISK = @NOMBRE_ARCHIVO   
    FETCH NEXT FROM VAL_CURSOR INTO @NOMBRE   
END     

--LIBERO EL CURSOR DEL SERVIDOR

CLOSE VAL_CURSOR   
DEALLOCATE VAL_CURSOR
 
GO

Podemos ejecutar dicho script por consola, el mismo tiene que guardar en el destino configurado todos los respaldos no excluidos.

Una vez realizado este script y probado lo guardamos en la carpeta correspondiente, como por ejemplo ‘\BASES\SCRIPTS\BACKUP.SQL’

Ahora utilizaremos el planificador de Windows para poder ejecutar dicho script, para ello emplearemos el muy poco usado SQLCMD, este es un ejecutable que abre un intérprete de SQL bastante simple a nivel de línea de comandos.

REM sqlcmd -S NOMBRE\INSTANCIA -E  -i C:\bases\scripts\backup.sql

REM Instancio el motor de base de datos con SQLCMD, y utilizo el script generado anteriormente ni bien se genera la conexion

sqlcmd -S LOCALHOST\SQLEXPRESS -E  -i C:\bases\scripts\backup.sql

REM En caso de que el servidor no este en dominio y no pueda utilizar un usuario de red, puedo generar una unidad de red y guardar el respaldo en una carpeta de red

xcopy "c:\bases\*.bak" "z:\backup"

REM Puedo borrar los archivos de respaldos una vez realizada la copia a una unidad de red.

del "c:\bases\*.bak"

Creamos un BAT con el contenido anterior y lo instanciamos desde una consola de comandos, el mismo debería de conectarse al servidor de base de datos y generar los respaldos utilizando el QUERY anteriormente generado, si esto mismo lo incorporamos en el planificador de Windows, el resultado será tener cada día los respaldos en dicho carpeta, diferenciados por la fecha de creación.

Espero que les sea de mucha utilidad, hasta la próxima.

Adjunto ambos script para Descargar

DDL y DML ¿qué significan?

Cuantas veces uno, en la búsqueda del conocimiento de algo nos encontramos, sobre todo en IT, con los famosos acrónimos de cada cosa, donde no solo tenemos que aprender las nuevas tecnologías y técnicas, sino que a eso, tenemos que sumar en nuestro pequeño diccionario de rubro, la gran mayoría de acrónimos que nos rodean el cual no se hará detalle del mismo pero si su MENCION.

Googleando un poco (nuestro gran aliado), estuve buscando cual es el significado y me base en la primer entrada que apareció, un texto simple, casi en formato ASCII en los tiempos que corren del HTML5 y me llamo la atención.

Lenguaje de definición de datos (DDL: Data Definition Language):Sencillo lenguaje artificial para definir y describir los objetos de la base de datos, su estructura, relaciones y restricciones. En la práctica puede consistir en un subconjunto de instrucciones de otro lenguaje informático. Aparte suele poseer dos subconjuntos de instrucciones:   Lenguaje de definición del almacenamiento de los datos (DSDL: Data Storage Definition Language): permite especificar características físicas de la base de datos (volúmenes y archivos donde van a ser almacenados los datos, etc).   Lenguaje de control de datos (DCL: Data Control Language): encargado del control y seguridad de los datos (privilegios y modos de acceso, etc).   Lenguaje de manipulación de datos (DML: Data Manipulation Language): Lenguaje artificial de cierta complejidad que permite el manejo y procesamiento del contenido de la base de datos. En la práctica puede consistir en un subconjunto de instrucciones de otro lenguaje informático. Las aplicaciones que trabajan sobre la base de datos se programan en un lenguaje de programación (C, Cobol, ...) insertando en el código fuente sentencias del DML. Al utilizar un DML se deben especificar los datos que serán afectados por las sentencias del lenguaje. Un DML puede tener o no procedimientos, según sea necesario especificar ademáns cónmo deben obtenerse esos datos. Los DML con procedimientos tienen sentencias de control de flujo como bucles o condicionales. Los DML sin procedimientos son conocidos también como declarativos.

Entonces acá viene mi pregunta, en mi interior, vos que buscas sobre lo nuevo, sobre lo que cuesta entender. ¿Entendiste? Si lograste hacerlo realmente te felicito, pero para la gran mayoría de los mortales seguramente leer esto, simplemente genere una confusión adicional a la ya gran tarea de la comprensión, sin tomar en cuenta algunos errores ortográficos. (utilicen el buscador si quieren saber su procedencia)

Con esto no se pretende simplificar las cosas, pero si, explayarlas de forma correcta y contundente, para de esta forma comprender con una base sólida y consistente. Sin más explicaciones, pongo simplemente el texto anterior por ser la primer entrada del blog, y de esta forma también exponer mi forma de pensar con mi tan apreciado rubro del cual cada día disfruto y enfrento, igualmente sacando lo bueno de lo malo, utilizare los anteriormente mencionado para de esta forma explayar a nivel de DBA, todo lo que esto significa desde sus cimientos.

“Lenguaje de definición de datos (DDL: Data Definition Language):Sencillo lenguaje artificial para definir y describir los objetos de la base de datos, su estructura, relaciones y restricciones. En la práctica puede consistir en un subconjunto de instrucciones de otro lenguaje informático. Aparte suele poseer dos subconjuntos de instrucciones:”

En el caso de SQL las reglas son bastante claras, la gran mayoría de motores de base de datos utilizan prácticamente un estándar (aunque existen salvedades), empecemos con lo que esto se refiere.

DDL (Data Definition Languaje) > Lenguaje de Definición de Datos.

El lenguaje de definición de datos, tal cual se comenta en parte en el texto anterior, sirve para Crear, Modificar, Borrar y Truncar el contenido de una base de datos.

Para ello en el caso de SQL se utilizan las siguientes sentencias específicas para manipular las estructuras de la base de datos.

CREATE (se utiliza para crear objetos en la base de datos), por ejemplo:

CREATE TABLE MI_TABLA (

                CAMPO_NUMERICO INT,

                CAMPO_ALFANUMERICO NVARCHAR(50)

)

Lo anteriormente mencionado crea una tabla con nombre MI_TABLA, y crea dos columnas en la misma, una llamada CAMPO_NUMERICO con valores enteros y otra llamada CAMPO_ALFANUMERICO con 50 posiciones y UTF-16 (esto permite utilizar el ASCII extendido, el cual se puede utilizar para ingresar alfabetos especiales)

ALTER (se utiliza para modificar objetos en la base de datos)

La sentencia ALTER, es una sentencia MUY UTILIZADA para quienes no utilizan las ayudas WYSWYG de los IDE para los DBA. Este tal cual se explica, te permite, modificar tablas, campos, funciones, triggers, procedimientos almacenados, y todo lo que corresponda a estructura de la base de datos, tiene un uso muy amplio y con características diferentes dependiendo que es lo que se modifica, el siguiente ejemplo mostrara un uso sencillo sobre una tabla, en la que se agregara un campo, se modificara el tipo de valor de otro campo, y se eliminara un campo.

Creación de un campo sobre una tabla ya existente:

ALTER TABLE MI_TABLA

ADD CAMPO_AGREGADO INT

Modificación de un campo sobre una tabla ya existente, modificando el tipo de valor de entero a formato alfanumérico UTF-8 de 20 posiciones:

ALTER TABLE MI_TABLA

ALTER COLUMN CAMPO_AGREGADO VARCHAR(20)

Eliminación de un campo sobre una tabla ya existente:

ALTER TABLE MI_TABLA

DROP COLUMN CAMPO_AGREGADO

Con el ejemplo anterior, simplemente eliminamos el campo que creamos inicialmente.

Más adelante veremos muchos más usos que se pueden realizar con la sentencia ALTER.

DROP (se utiliza para borrar objetos de cualquier índole en una base de datos)

Prestando atención a la sentencia DROP, vemos como en el último ejemplo mencionado en ALTER, esta sentencia aparece. DROP borra objetos de la estructura, sirve para borrar tablas, campos, procedimientos almacenados, funciones, etc... Al igual que ALTER, si utilizamos comandos por consola esta sentencia es utilizada, no con la misma frecuencia que la anterior.

DROP tiene un funcionamiento sencillo y una estructura de escritura bien simple ‘DROP [TIPO ESTRUCTURA] NOMBRE’, veamos sus ejemplos más sencillos:

DROP TABLE MI_TABLA
DROP PROCEDURE MI_PROCEDIMIENTO
DROP VIEW MI_VISTA

Se puede apreciar el uso de la sentencia DROP en los ejemplos anteriores, inclusive en el ejemplo de ALTER TABLE > COLUMN donde utiliza el mismo formato.

TRUNCATE (se utiliza para borrar todos los elementos de una tabla, sin posibilidad de filtro y generando un LOG simple, esto último es muy importante a niveles de performance)

TRUNCATE muchas veces es confundido con la sentencia DELETE que inclusive no corresponde a la familia de sentencias (DDL), ya que ambas tienen un resultado final SIMILAR, pero  funcionan de manera muy diferente, TRUNCATE no permite filtros, es mucho más rápido y utiliza menos el LOG a la hora de ejecutarse y aparte libera el espacio que estaba utilizando dicha tabla, mejorando el mantenimiento de la base de datos.

El ejemplo de TRUNCATE es muy sencillo:

TRUNCATE TABLE MI_TABLA

Hasta aquí en forma general se explicó DDL, en los siguientes párrafos se explicara DML en forma genérica, ya que es lo que mayoritariamente utilizamos en nuestro entorno. DSDL y DCL serán explicadas en otras entradas ya que se necesitan de otras herramientas para poder comprenderlas en profundidad.

“Lenguaje de manipulación de datos (DML: Data Manipulation Language): Lenguaje artificial de cierta complejidad que permite el manejo y procesamiento del contenido de la base de datos. En la práctica puede consistir en un subconjunto de instrucciones de otro lenguaje informático. Las aplicaciones que trabajan sobre la base de datos se programan en un lenguaje de programación (C, Cobol, ...) insertando en el código fuente sentencias del DML. Al utilizar un DML se deben especificar los datos que serán afectados por las sentencias del lenguaje. Un DML puede tener o no procedimientos, según sea necesario especificar ademáns cónmo deben obtenerse esos datos. Los DML con procedimientos tienen sentencias de control de flujo como bucles o condicionales. Los DML sin procedimientos son conocidos también como declarativos.””

Esta parte es la famosa SELECT *, me encanta esa definición por la gran profundidad que tiene, y por el gran potencial que podemos obtener en base a esto, pero en esta ocasión empezaremos por lo más simple y en parte a lo que explica la definición anterior, DML (Data Manipulation Languaje) > Lenguaje de Manipulación de Datos, es todo lo que corresponde a bucear el contenido de las tablas, muchas veces se confunde a un motor de base de datos, con una especie de muchas planillas de cálculos, o se lo considera una especie de ACCESS con flechas, pero es mucho más que eso, la manipulación de datos que permite SQL simplemente está limitada por la imaginación, y haciendo mención a lo primero que puse, es mucho más que un SELECT * .

Vamos a describir en forma básica, los ejemplos más comunes que son SELECT, INSERT, UPDATE, DELETE. Estas sentencias son muy utilizadas para el manejo de la información, cada una con sus respectivas técnicas y formas de uso tanto para obtener la información acomodada, como así también en forma eficiente.

SELECT (Utilizada para obtener registros/datos desde los objetos de la base de datos)

La sentencia SELECT puede utilizar, literales, tablas, vistas, funciones entre los campos, etc… Por otra parte permite el uso de cruces, condiciones, agrupamientos, condiciones de agrupamientos, pivotes, etc… Tiene un gran potencial y es en la mayoría de los casos, la gran utilizada para generar resultados, o result-set, su estructura es SELECT CAMPOS FROM MI_TABLA

 INSERT (Utilizada para insertar registros en una tabla)

 La sentencia INSERT, soporta inserción estática o por medio de selecciones, su estructura más simple es INSERT INTO MI_TABLA (CAMPOS) VALUES ('DATO'), su comportamiento es lineal, el mismo se lo puede utilizar dentro de transacciones, condicionales, bucles, etc...

UPDATE (Utilizada para modificar registros ya existentes en una tabla)

La sentencia UPDATE, soporta modificaciones completas, con condiciones, o por medio de cruces de datos, en el caso de los cruces de datos es algo más compleja de utilizar y puede derivar a mayores catástrofes, pero bien utilizado optimiza el tiempo en forma considerable, su estructura más simple es UPDATE MI_TABLA SET CAMPO = 'DATO', esto anterior pondrá el valor DATO en CAMPO en todos los registros de la tabla MI_TABLA.

DELETE (Utilizada para borrar registros en una tabla)

La sentencia DELETE, soporta eliminación de registros completa, con condiciones y también por medio de cruces, tal cual en el caso de UPDATE, utilizar DELETE con cruce de datos, simplifica los tiempos pero dicha sentencia tiene que ser muy bien estudiada antes de su ejecución, su estructura más simple es DELETE MI_TABLA, esto anterior elimina todos los registros de la tabla MI_TABLA.

Hasta aquí repasamos los ejemplos más simples de DDL y DML, dejaremos los ejemplos más prácticos para la siguiente entrada, donde se empezara a hablar en más detalle, el uso de los mismos con su entorno y ejemplos.

Hasta la próxima.