Hace un par de días, un amigo me pidió lo siguiente:
"Necesito una sitio en internet que se alimente de los datos que tengo en la base de datos (MS-SqlServer) en el servidor local (ubicado en el sótano de la oficina) para que se puedan realizar consultas actualizadas. Tengo el hosting (en Apache/mySql) en bla, bla, bla... Eso sí, evita, dentro de lo posible, abrir puertos del router ni nada de eso, bla bla bla..."
Una vez estudiada la tabla (por cierto, una muy sencilla y sin datos comprometidos) llegué a la conclusión de que no valía la pena volverse loco y lo mejor sería hacerlo lo más sencillo posible, evitar crear una base de datos (o tabla) nueva en el servidor (que, encima está en mysql, aunque esto no sea un gran problema), etc. En definitiva, lo mejor sería trabajar con un fichero cómodo como el XML. Esto me facilitaría luego la programación usando simplexml.
Pues eso, manos a la obra.
NOTA: Trabajé mediante una conexión de Escritorio Remoto al servidor ya que no tengo instalado el SqlServer en mi equipo.PASO 1. Convertir tabla de SqlServer a XML mediante la cláusula FOR XMLSi se hace una consulta a la base de datos con la cláusula FOR XML, SqlServer nos devolverá los datos en formato XML en uno de los siguientes formatos: RAW, AUTO o EXPLICIT. No pretendo entrar en detalle pero voy a explicarlos muy por encima para que puedan hacerse una idea básica.
RAW:Si uno hace una consulta a una tabla como la siguiente:
"SELECT id, nombre FROM una_tabla_cualquiera FOR XML RAW"
Nos devolverá una respuesta en el siguiente formato:
<row id="1" nombre="Juanito"/>
<row id="2" nombre="Panchito"/>
<row id="5" nombre="Pepito"/>
<row id="7" nombre="Chucho"/>
etc... Como pueden ver, devuelve filas genéricas ("row") y con las columas de la tabla como atributos.
Ahora, si queremos que cada columna sea "elementos" del "row", basta con especificarlo en las opciones usando ELEMENTS:
"SELECT id, nombre FROM una_tabla_cualquiera FOR XML RAW, ELEMENTS"
Esto nos dará:
<row>
<id>1</id>
<nombre>Juanito</nombre>
</row>
etc..
La verdad es que con esto una ya podría empezar a trabajar, pero voy a comentar sobre los otros formatos.
AUTO:Si volvemos a hacer la consulta pero con la opción AUTO:
"SELECT id, nombre FROM una_tabla_cualquiera FOR XML AUTO"
tendremos algo así como:
<una_tabla_cualquiera id="1" nombre="Juanito"/>
<una_tabla_cualquiera id="2" nombre="Panchito"/>
<una_tabla_cualquiera id="5" nombre="Pepito"/>
<una_tabla_cualquiera id="7" nombre="Chucho"/>
Como podemos ver, las filas se nombran según la tabla... Pero hay más. Otra de las diferencias del AUTO es que muestran mejor las cláusulas JOIN. No voy a entrar en detalle así que
aquí les dejo un artículo donde podrán ver un ejemplo bastante claro y mejor explicado (lo malo que está en inglés, yeah!). También funciona la opción ELEMENTS si queremos mostrar las columnas como elementos del xml (muy práctico, por cierto).
EXPLICIT:Esta es la opción más completa, la que te deja "tunear" tu xml. Su uso es un poco más complejo así que les dejo un
link para que puedan ver con detalle sus características. Como dice en ese artículo, con este método podemos mostrar elementos y atributos a la vez. Léanlo, es interesante.
Una vez logrado que SqlServer nos muestre los resultados en XML, lo que nos interesa ahora es que se guarde el resultado como en un archivo para, posteriormente, poder subirlo al FTP.
PASO 2. Guardar el archivo XML usando sqlcmdComo mi intención es ejecutar un fichero batch (.bat) con el taskmanager del windows para que se ejecute cada cierto tiempo, vamos a crear el fichero XML a través de la línea de comandos del servidor. Para poder consultar la base de datos desde la línea de comandos usamos el comando sqlcmd.
Una vez dentro de la ventanita negra del cmd (INICIO>EJECUTAR...>cmd), si ejecutamos:
"SQLCMD -S [nombre_del_servidor\nombre_de_la_base_de_datos] -U [nombre_del_usuario] -P [contraseña]"
conectaremos, siempre que los datos estén bien, a la base de datos. Lo sabremos porque saldrá un 1>. Ya estamos "conectados" al SqlServer y podemos poner las consultas que querramos ejecutándolas con "GO". Así, por ejemplo, si ponemos:
1>USE mis_amiguetes (esto es para elegir la base de datos)
2>GO
nos saldrá "Se ha cambiado el contexto de la base de datos a 'MIS_AMIGUETES" es decir, que se ha seleccionado la base de datos "mis_amiguetes"... Con esto, queda claro que se puede hacer cualquier otra consulta del SqlServer y, al ejecutar GO, nos mostrará los resultados.
Sin embargo, nos interesa que sea automático, es decir, no queremos estar "escribiendo" cada vez que ejecutemos el sqlcmd. Para ello, creamos un fichero .sql en la que se ponen las consultas necesarias. En el caso del ejemplo he creado un fichero llamado "consultas.sql" en la raíz del servidor (en mi caso c:\).
consultas.sql:
USE mis_amiguetes (para seleccionar la base de datos)
SET NOCOUNT ON (esto es para quitar la enumeración final que nos da sqlcmd que nos dice las filas afectadas y que es un poco coñazo)
:XML ON (importante, sino no sale en formato XML)
SELECT '<xml version="1.0" encoding="utf-8" ?>'; (para poner el encabezado, opcional)
SELECT id, nombre FROM una_tabla_cualquiera FOR XML AUTO, ELEMENTS, ROOT ('amigos'); (la consulta de antes, con el ROOT para crear la raíz "amigos" y así evitar conflictos con el formato del XML)
Ahora, volviendo a la línea de comandos, si ejecutamos:
"SQLCMD -S [nombre_del_servidor\nombre_de_la_base_de_datos] -U [nombre_del_usuario] -P [contraseña] -i [nombre del archivo .sql que hemos creado] -o [nombre del fichero XML que queremos que se genere]"
En mi caso:
"SQLCMD -S servidor\sqlexpress -U minombre -P micontraseña -i c:\consulta.sql -o c:\resultados.xml"
¡Voilà! en c:\ del servidor se ha creado un fichero llamado resultados.xml. ¡Perfecto! Lo abro y puedo verificar que está bien. Hemos logrado exportar una consulta al SqlServer en un fichero xml. Ahora necesitamos subirlo al FTP.
PASO 3. Subir un archivo al FTP desde la línea de comandosPara conectar con el servidor FTP desde la línea de comandos, basta ejecutar el comando ftp seguido del sombre del servidor:
c:\>ftp miservidorftp.com
Nos pedirá el nombre de usuario y la contraseña. Lo escribimos (la contraseña no se mostrará así que atinad bien a los botones) y uno ya está conectado al FTP.
Una ves en el ftp> tenemos comandos útiles como: cd (para elegir el directorio), put (para subir el archivo en cuestión), etc... Probamos y todo funciona correctamente.
ftp>cd directorio_donde_va_el_xml
OK. Current directory is /directorio_donde_va_el_xml
ftp>put c:\ok.xml
PORT command successful
bla bla bla...
Todo va estupendamente. Compruebo y, efectivamente, el archivo se ha subido al FTP. Ya sólo nos queda crear el archivo bat para poder ejecutarlo desde el taskmanager y poder olvidarme de todo.
PASO 4. Crear el archivo bat.Abro el editor de textos y creo un archivo llamado "actualizador.bat" (usando "guardar como") en el C:\ del servidor. Primero me aseguro que el archivo bat me crea el xml así que elimino el resultados.xml que ya tenía en c:\ y edito el .bat poniedo
:
SQLCMD -S servidor\sqlexpress -U minombre -P micontraseña -i c:\consulta.sql -o c:\resultados.xml
Guardo y lo ejecuto dándole doble click et voilá, se crea el archivo resultados.xml. Luego añado las siguientes líneas:
@echo off
echo user nombredeusuariodelftp>ftpcmd.dat
echo contraseñadelftp>>ftpcmd.dat
echo bin>>ftpcmd.dat
echo cd directorio_donde_va_el_xml>>ftpcmd.dat
echo put resultados.xml>>ftpcmd.dat
echo quit>>ftpcmd.dat
ftp -n -s:ftpcmd.dat miservidor.com
del ftpcmd.dat
EXIT
Todo parece intuitivo. Lo que hace es crear un archivo de comandos temporal (ftpcmd.dat) y va metiendo datos importantes como nombre de usuario, contraseña, etc... Luego ejecutamos el comando ftp junto a la cláusula -n (que evita que pida el login al ftp automáticamente) y -s (que especifica el archivo de comandos). Se ejecuta, funciona y elimina el archivo temporal. Ejecutamos el archivo actualizador.bat y crea el archivo xml y lo sube al ftp.
Luego, ya con el taskmanager, hemos hecho que se actualice cada hora. Es decir, cada hora se ejecuta el actualizador.bat y sube el xml que ha creado al ftp para que la página web pueda consultar datos actualizados.
Descarga aquí los dos archivos (actualizador.bat y consulta.sql) para que puedan ver lo sencillo que es. Les recuerdo que lo más sencillo es ejecutarlo desde el propio servidor.
Nada más, espero que esto le haya sido útil a alguien.
¡Saludos!
