¿Cliente/Servidor con Procedimientos Almacenados en el Servidor?

Por Abel Sorzano
© Copyrights 1998 , All rights reserved

En las aplicaciones Cliente/Servidor o en los más novedosos diseños de aplicaciones realizadas en varias capas, una de las preguntas más frecuentes que todos los programadores se hace es ¿cual es la mejor y más optimizada forma de acceder a los datos del servidor?

Como siempre la respuesta no es fácil y hay que matizarla mucho. Para acceder a los datos del servidor desde aplicaciones Visual FoxPro a través de ODBC tenemos tres formas:

• 1.- Usando Vistas Remotas
• 2.- Usando órdenes SQL mediante el SQL Explícito
• 3.- Usando Procedimientos Almacenados Remotos mediante el SQL Explícito.

  Las Vistas Remotas

  Las Vistas Remotas son como las Vistas Locales pero acceden a los datos por medio de ODBC. Hay algo que diferencia radicalmente las Vistas Remotas de las Vistas Locales y es que el cursor que te muestra los datos es actualizable (los cursores de las órdenes SELECT locales no son actualizables).

  Las Vistas son el sistema más cómodo de trabajar en los entornos Cliente/Servidor pues casi todo el trabajo te lo realiza FoxPro, pero tienen pequeñas problemáticas difíciles de controlar como son:

  1.- No se puede establecer un órden en las actualizaciones.Cuando una Vista actualiza los datos de una tabla no hay forma de decirle el orden en el que queremos que realice la actualización. Esto es muy importante para el caso de que uses claves primarias pues si intentas borrar el registro que contiene la clave primaria antes de borrar el registro de la clave dependiente se producirá un error de actualización. Para sobreseer este problema tendrías que diseñar una vista por tabla con clave primaria.

  2.- El control de Errores del Servidor. No hay forma de controlar los errores que te devuelve el servidor o el Driver ODBC con sus crípticos y extraños mensajes que ponen nerviosos a los usuarios.

  Esto ha llevado a determinar que las Vistas Remotas son muy útiles exclusivamente en los casos de:

• 1.- "solo lectura"
• 2.- Mantenimiento de trablas sin relaciones.
• 3.- A la hora de preparar o hacer informes.

  El uso de órdenes SQL mediante el SQLExplícito

  El uso del SQLExplícito es más versatil y permite solucionar los dos principales problemas de las vistas (control de errores del servidor y orden de actualización) pero tiene en su contra:

• 1.- Es más trabajoso. Tienes que codificarte todo. Si comparamos con las vistas. Las vistas, ellas solas, se preocupan de comunicarse con el servidor, pasar los Insert, las actualizaciones (Updates) y los Deletes. Las Vistas son más fáciles de usar y de configurar.
• 2.-Pierdes Interoperabilidad. Las órdenes envíadas al back-end via SQLExec() son escritas en el lenguaje del back-end. Si tu aplicación la estabas diseñando para atacar a un SQL Server. ¿cuanto de esto habría que reescribir para transportarlo a Oracle? Es cierto que las bases de Select, Insert, Update o Delete no varían mucho de back-end a back-end. Sin embargo, este punto es importante y depende de la complejidad de las órdenes SQL que uses ya que podrían limitar tu capacidad para saltar de un servidor a otro. Por supuesto, si la aplicación se ha escrito para uno y solo un back-end esto no será un gran problema.
• 3.- Ganas en interoperabilidad.Considera lo que puede suceder si estás usando Vistas Remotas y tratas de borrar un registro que por la razón que sea la Integridad Referencial nos impida que se borre, el mensaje de error que devuelve Oracle difiere del enviado por SQL Server. Podrías controlar el mensaje y traducirlo a un formualario mucho más amigable pero tendrás que cambiarlo para cada back-end. Esto limita tu interoperabilidad debido a que tendrás que crear rutinas de control para cada servidor. Con la visión del SQL explícito enviarías una órden Select a un back-end. Si el Select encuentra el registro y ve que no puede ser borrado el mensaje que se muestra al usuario es el mismo independientemente de lo que se ha enviado en el SQLExec(). Esto sirve para aumentar tu interoperabilidad, asumiendo que la sintaxis de los Selects, Inserts, Updates y Deletes no varían gran cosa entre los varios back-ends, lo que es una razonable presunción.

  Usando Procedimientos Almacenados Remotos mediante el SQL Explícito.

  Este es propiamente el tema de este artículo y en él nos vamos a detener de modo especial.

  En este diseño, el trabajo se realiza casi todo en el servidor. El Servidor manipula los datos actuales, que envuelve búsquedas de registros, añadir, actualizar y borrar registros. Sin embargo, el cliente controla la lógica, diciendo al servidor qué hacer y en qué orden lo debe de hacer.

  Un Procedimiento Almacenado es un precompilado conjunto de órdenes SQL. Debido a que están precompiladas se gana en optimización a la hora de usarlar. Además, son esencialmente cajas negras.

  La función SQLExec() se puede usar para enviar no solo órdenes SQL tales como Insert y Update, sino también se puede usar para decir a SQL Server que corra un procedimiento almacenado. Visual FoxPro le dice al servidor qué procedimiento almacenado debe ejecutar pero no sabe qué es lo que hay en cada uno de los procedimientos almacenados. Visual FoxPro solo sabe, y de hecho, sólo necesita saber, si los procedimientos almacenados funcionan.

  Pero vayamos por partes....

  El uso de procedimientos almacenados en el servidor para acciones que se realizan frecuentemente mejora radicalmente la velocidad de los procesos. Una vez que el Procedimiento Almacenado ha sido satisfactoriamente creado en el servidor, el servidor no tiene que comprobar la sintaxis y compilar antes de ejecutar que son los dos aspectos que ralentizan las operaciones. Además, el Proc. Almacenado también puede recibir parámetros para que nos entregue una información precisa.

  Los Procedimientos Almacenados -por contra- son junto con el Grid una de las "Bestias negras" del programador y deben ser usadas con mucho cuidado. Tienen la ventaja (o desventaja) de que pueden escribirse en un lenguaje procedural (un lenguaje de tercera generación ) y permite a los programadores desembarazarse de los límites del SQL, así como manejar los conjuntos de datos como si fueran archivos secuenciales. En el caso de Sybase/SQLServer, el T-SQL está basado en C. El lenguaje de cuarta generación de Informix se basa en la familia de lenguajes ALGOL/Pascal. Existe un estandar de lenguaje SQL/PSM (Modulo almacenado persistente) basado en ADA que de momento unicamente lo sigue la Base de Datos sueca Mimer.

  Pero los problemas crecen. Una sentencia IF... ENDIF, elemento fundamental de la programación estructurada, utiliza lógica booleana de dos valores (VERDADERO, FALSO), mientras que SQL utiliza una lógica de tres valores (VERDADERO FALSO, DESCONOCIDO). Cuando un procedimiento de segmento IF devuelva un valor DESCONOCIDO ¿debe ejecutarse el segmento THEN, el segmento ELSE o ninguno de ellos? SQL carece de directrices claras. DESCONOCIDO actúa como FALSO en el segmento WHERE de una sentencia SELECT, UPDATE o DELETE, pero actúa como VERDADERO en la limitación CHECK() y en otros segmentos declarativos de la integridad referencial.

  Pero entremos en la sustancia.

  ¿Cómo crear un procedimiento Almacenado en el Servidor?

  Usaremos la Base de Datos Pubs y la tabla Authors que viene con Microsoft SQL Server. Se supone que existe un origen de datos.

  El siguiente ejemplo nos muestra como saber los autores de un estado en concreto. Para crear un procedimiento almacenado en SQL Server, deberías tener la autoridad suficiente como para hacerlo. Una vez comprobado que se tienen los privilegios suficientes. En el ejemplo que hay a continuación usaremos un data source llamado test y crearemos un stored procedure para conseguir sólo aquellos autores que viven en California.

  hand = sqlconnect("test","sa","")
   
  ************************************
  * Comprueba un buen handle         *
  ************************************
  IF hand > 0
  z = SQLEXEC(hand, "create procedure ;
    pick_state @mystate char(2) as " + ;
    "Select * from authors where state;
       = @mystate")
  *******************************
  *Comprueba una buena ejecución *
  *******************************
  IF z > 0
     WAIT WINDOW "Stored Procedure;
        created"
  ELSE
         WAIT WINDOW "Stored Procedure ;
           failed"
      =SQLDISCONNECT(hand)
       CANCEL
    ENDIF
   
      ****************************
      * Ejecuta el Proc. Almacenado *
      ****************************
      =SQLEXEC(hand, "execute pick_state CA")
      * Display Result Set *
      BROWSE
   
      =SQLDISCONNECT(hand)
   
   ELSE
      WAIT WINDOW "Bad connection"
   ENDIF
  

  En este ejemplo la varibale @mystate nos permitiría pasar la variable que según los casos nos interese.

  Esto unicamente es para abrir boca. Lo verdadermanete interesante para poder evaluar el poder de esta herramienta es verla funcionando por completo en una aplicación.

  Una aplicación completa

  Cargar la Base de Datos en el Servidor

  A continuación se detallan todos los pasos necesarios para montar la Base de Datos del ejemplo

• 1.- Crear los Devices usando el fichero DISKINIT.SQL

  2.- Crear la Base de Datos usando el fichero de scripts CREABASE.SQL

  3.- Creamos los tipos de datos definidos por el usuarioque se encuentran en el fichero CREATYPE.SQL

  4.- Creamos la estructura de las Tablas mediante CREATABL.SQL

  5.- Creamos los índices

  6.- Creamos los Indices Primarios mediante PRIMEKEY.SQL

  7.- Creamos los Indices dependientes (Foreign Keys) mediante FORGNKEY.SQL

  8.- Creamos condiciones por defecto mediante DRIDEFLT.SQL

  9.- Creamos reglas de validación mediante DRICHECK.SQL

  10.- Creamos las Vistas mediante CREAVIEW.SQL

  11.- Creamos los Procedimientos Almacenados mediante CREAPROC.SQL

  12.- Preparamos los ficheros intermedios de carga de datos mediante CREAGENS.SQL

  13.- Carga de Datos mediante LOADDATA.SQL.

  14.- Borrado de ficheros intermedios mediante DROPGENS.SQL

  Cargar la Aplicación Cliente

  Descomprimir el fichero en el directorio que queremos. Luego unicamente nos tenemos que preocupar de que exista un origen de datos que vamos a llamar SQLServer que apunte a la Base de Datos recién creada y que se llama Library.

  La aplicación es un formulario que nos permite buscar información en una tabla de miembros o socios de una biblioteca. Extendernos sobre el código puede hacerse pesado y es mucho mejor verlo pero sólo trascribiremos la información necesaria para localizar un Miembro y mostrarlo en el formulario.

  Localizar un Miembro

  Un procedimiento almacenado de SQL Server llamado GetMember se usa para traer la información sobre un member. Este procedimiento almacenado toma como parámetro el ID del miembro cuya información se quiere. Este procedimiento almacenado se llama cuando el usuario pulsa el botón Locate.

  If SQLExec(ThisForm.nHandle, "Execute
          GetMember " + ;
          ThisForm.txtMemberID.Value,;
          "c_member") < 0
      ThisForm.ShowError
      Return
  Endif
  

  El procedimiento almacenado ejecuta un Select y devuelve un conjunto de resultados al cursor c_member. Si el cursor esta vacío entonces no se encontrará ningún member con ese ID. De otra manera la información del miembro se encuentra en los campos del cursor y se puede mostrar en el formulario.

  El código de SQL Server que crea el Procedimiento Almacenado GetMember es como sigue. Puedes ver que toma un parámetro y ejecuta un Union Select para encontrar la información del member.

  CREATE PROCEDURE getmember @member_no member_no
   AS
  SELECT member.member_no, firstname,  lastname,middleinitial, street, city, state, zip,phone_no, expr_date, birth_date = NULL,
     adult_member_no = NULL
   FROM member, adult
   WHERE member.member_no = adult.member_no
     AND member.member_no = @member_no
   UNION
  SELECT member.member_no, firstname,  
    lastname,
       middleinitial, street,
       city, state, zip,
       phone_no, expr_date,
       birth_date, adult_member_no
    FROM member, adult, juvenile
    WHERE member.member_no =
         juvenile.member_no
      AND juvenile.adult_member_no = 
         adult.member_no
      AND member.member_no = @member_no
    RETURN (@@ROWCOUNT)
  

  Las Ventajas e Inconveniente del uso de SQL pass-through con ordenes SQL en comparación con el SQL pass-through con procedimientos almacenados son:

  Es menos trabajo

  Se asume que los procedimientos almacenados ya existen y por tanto es mucho menos trabajo para el programador de Visual FoxPro que tiene que trabajar en este escenario. Todo lo que necesitas saber es qué procedimientos almacenados tienes que llamar, qué parámetros usa y cuales devuelve. No necesitas saber como los procedimientos almacenados funcionan o en qué orden lo hacen.

  Tienes menos control en Visual FoxPro

  Debido a que todos los acceso al SQL Server se realizan a través de procedimientos almacenados tienes muy poco control desde Visual FoxPro sobre lo qué sucede en el servidor. Por supuesto, se asume que los procedimientos almacenados hacen lo que ellos se supone que hacen. Esto es algo que no debes de perder de vista.

  Tienes mas control en el SQL Server

  Si eres responsable no solo del front-end Visual FoxPro sino también del SQL Server back-end entonces lo controlarás de cualquiera de las dos maneras. Sin embargo, si estás escribiendo un front end en Visual FoxPro para atacar los datos del SQL Server y esos datos son controlados por algún otro, proveer el acceso a los datos solamente a través de procedimientos almacenados es una gran forma para los responsables de los datos del SQL Server de ejercer el control sobre esos datos. Ellos pueden estan seguros de que el front-end no les va a destrozar los datos. Les daría igual que haya un front-end Visual FoxPro, Visual Basic o Access atacando a los misms datos.

  Esto nos da mayor interoperabilidad

  Debido a que todo el trabajo se hace con procedimientos almacenados debería ser fácil interogar al back-end.

  Por último y antes de finalizar sólo recordar que el driver ODBC de Visual FoxPro no soporta lo que se ha venido en llamar cursores en el lado del servidor (server side cursors). Para entendernos, cuando hacemos un SELECT, la información nos la deja en un cursor en el lado del cliente y VFP la manipula. Lo que no puede hacer es crear un cursor y dejarlo en el lado del servidor para desde ahí manipularlo. Si se pudiera hacer eso mejoraría notablemente el rendimiento de la red.

  Uno de los que sí soporta esto es el control RDO 2.0 que en su versión original apareció con Visual Basic 4.0 o con el más novedoso ADO del que ya se habló hace algunos números. En las siguientes líneas vamos a hacer una leve incursión en la programación del Word utilizando, como no, nuestra herramienta favorita, el Fox. El Word, desde hace ya unas cuantas versiones, viene siendo una herramienta bastante flexible en cuanto a la personalización a través de macros o de lenguajes como lo que se llamó WordBasic y que ha evolucionado para acabar integrándose en el Visual Basic for Applications (VBA). Lenguaje común de programación para todas las herramientas de Microsoft Office.