Exportar SQL a Foxpro

Jajaja entras a todos los foros
Estoy terminandolo para enviártelo
Si tienes hotmail
mi correo es [email protected]
Atentamente. Tu sobrino
Fitocava
Ing. Cesar augusto vasquez alfaro Trujillo peru

Introducción a Cliente-Servidor
Traducido por: Esparta Palma
SysOp Portalfox.com
Introducción
La arquitectura Cliente-Servidor parece ser muy simple en los ejemplos: cree una conexión, cree una vista remota hacia los datos, y estará en el negocio, ¿correcto? . En esta sesión, Ted se introducirá en la potencia detrás del GUI, con la revisión de los comandos y funciones necesarias para crear un sistema Cliente-Servidor real. SQL Pass-throuhg, control manual de transacciones, manipulación de propiedades en conexiones, vistas y cursores, procesamiento en lote, precompilación y muchos otros temas que necesitará para la lucha con un sistema real a ser considerado.
Tecnologías Cliente Servidor y Visual FoxPro
Este pequeño documento no pretende ser una referencia completa hacia todo lo relacionado con Cliente-Servidor. En vez de eso, su intención es de servir como una pequeña introducción hacia los conceptos y las técnicas usadas para realizar una aplicación Cliente-Servidor con Visual FoxPro. Los lectores deberán estar familiarizados con Visual FoxPro y con el uso del contenedor de Base de Datos de FoxPro (DBC) y las vistas trabajando con datos locales. Empezaremos con algunas simples definiciones de los términos, revisaremos por qué deberías estar con Cliente-Servidor, y entonces cavaremos en los comandos y funciones para hacerlo posible. Concluiremos con algunas discusiones acerca de técnicas más avanzadas y cómo debería considerar la integración de Cliente-Servidor en su Framework.
¿Qué es Cliente-Servidor?
¿Hay una gran cantidad de definiciones acerca de qué es exactamente? Cliente-¿Servidor?, existen más o menos estudiantiles, más o menos robustas. Un vendedor hace algunos años diría: ¿? Cliente-¿Servidor?, bueno, eso es cualquier cosa que debo vender ahora. Típicamente, para nuestros propósitos, el elemento clave de Cliente-Servidor es que dos procesos separados van a ser ejecutados, uno con Visual FoxPro, y otro que proveerá datos a Visual FoxPro. Estos dos programas pueden ser ejecutados tanto en la misma máquina como en forma separada, esto con algún tipo de conexión entre ellos (red, puerto serial o Internet) para permitir la comunicación.
¿Por qué Cliente-Servidor?
Muchos clientes ven interesante al esquema Cliente-Servidor por razones que están más allá de los méritos técnicos. Esto es ciertamente válido, pero tenga cuidado con las trampas en las que pueda caer si la solución Cliente-Servidor no fue realizada por una razón técnica primaria. Hay tres razones primordiales a considerar en Cliente-Servidor:
? Tamaño de Datos: Visual FoxPro, al igual que todas las variantes de xBase anteriores a él, tenia la limitación de 2 GB en cualquiera de sus tablas o archivos. Esta limitación tiene que ver con la manera en que los bloqueos son realizados en los registros individuales y, mientras que es ciertamente factible que este límite sea alcanzado, esto es diferente a cómo normalmente lo hace Microsoft. A pesar de que hay muchos workarrounds para estos límites, ¿estos incrementan los retos con Visual FoxPro en cuanto las tablas se agranden? Tiempos prolongados para reindexar en caso de corrupción, por ejemplo. En resumen, si el tamaño se vuelve un factor mayor, considere si el uso de Cliente-Servidor es adecuado.
? Seguridad: Fundamentalmente, todos los accesos a las tablas FoxPro van a través de la red del sistema operativo, así que los usuarios necesitan tener acceso a los directorios conteniendo las tablas VFP. Cualquiera que pueda tener acceso a las tablas puede, eventualmente, imaginarse como leerlos. También es simple usar un driver ODBC y Excel, o si ellos necesitan usar un editor hexadecimal para romper su esquema de encriptación. Muchos sistemas Cliente-Servidor pueden eliminar esta amenaza en conjunto con la restricción de acceso de los clientes a la interface del servidor, y no necesariamente a todos los datos. Si esta tratando con material altamente confidencial, Cliente-Servidor tiene sentido por razones de seguridad.
? Bajo Ancho de Banda: Visual FoxPro es el producto para manejo bases de datos escritorio y basado en LAN más rápido y con mejor mejor desempeño disponible en el mercado hoy en día. Pero VFP obtiene su desempeño fenomenal tomando ventaja del ambiente LAN, pre-obteniendo información de columna, haciendo localmente algún tipo de caching de encabezados de tablas y contenidos de índices. Mientras que el proceso de adquirir esta información es casi imperceptible en un ambiente de red, haciendo lenta la apertura inicial de tablas en milisegundos, ¿esto puede ser un retardo substancial si se está en un? ¿Cable delgado? (Por ejemplo conexiones Dial-Up, WAN o Internet saturado) entre el cliente y los datos. En estas situaciones, poner los datos, la responsabilidad de hacer las consultas y procesamiento de los datos en el servidor minizará los costos de comunicación y mejorará la velocidad.
¿Por qué NO Cliente-Servidor?
Como se mencionó anteriormente, los clientes pueden estar decepcionados si ellos han seleccionado una arquitectura Cliente-Servidor y sólo encuentran que el producto resultante es menos que satisfactorio. Hay muchas razones a considerar para no escoger Cliente-Servidor:
? Complicaciones de Recursos: Cliente-Servidor es fundamentalmente más complejo que un single-tier o aplicación monolítica. Nuevas necesidades de aplicación deben ser aprendidas, con esta terminología extranjera, sintaxis y lenguaje. Connectividad, ODBC y temas de red necesitan ser tomadas en cuenta. Nuevas APIs pueden ser necesitadas y dominadas. Anomalías, desdichas y plantear viejos bugs en la interface del vendedor, además de la búsqueda de driver. Si su equipo es maximizado y agendado, ¿no piense que Cliente-Servidor será una? ¿Bala de plata?.>
? Mantenimiento en Curso: ¿Mientras qué los vendedores están tratando de hacer sus aplicaciones más? ¿Instálelo y Olvídelo?, ¿No existen instalaciones? Hands-¿Free?. Un administrador de Base de Datos (DBA, Database Administrator) tiene que estar disponible, al menos por periodos, revisar logs, asegurarse que los respaldos estén trabajando, el espacio en disco sea adecuado, etc. En realidad, esto es un requisito para muchas instalaciones de computadoras, pero los server típicamente necesitan un poco de atención adicional. Asegure el factor de costo de un DBA como parte de su vida en Cliente-Servidor.
? Inestabilidad de Infraestructura: La percepción entre la gerencia superior es que Cliente-Servidor es inherentemente más robusto, y que una instalación de este tipo es menos propensa a caídas que una instalación FoxPro. De hecho, la estabilidad de la red del sistema operativo y sus servidores no tienen relación con el software ejecutándose en ellos, y una inestabilidad no supone que las caídas y requerimientos de las aplicaciones FoxPro para reindexar todos sus archivos van a requerir un servidor que vaya a través de los procedimientos de recuperación. Un mejor uso del esfuerzo en mucho de estos casos es reparar los servidores con problemas.
? Rendimiento: Como condición de que el suficiente ancho de banda está disponible, el rendimiento por sí solo nunca es razón para considerar al Cliente-Servidor, a menos de que los clientes requieran que la aplicación funcione más lentamente. En hardware equivalente, Visual FoxPro ejecuta muy decentemente en comparación de un software de servidor de datos
¿Cómo funciona Cliente-Servidor?
La diferencia principal entre trabajar con datos locales (datos VFP nativos) y datos cliente servidor es la necesidad de establecer una conexión con el servidor. Después de que la conexión es establecida, una vista puede ser usada para leer y escribir datos. En adición, la equivalencia de las sentencias SQL nativas de VFP pueden ser usadas para proporcionar un control más directo que el usualmente disponible vía Vista. Finalmente, como casi todo en Visual FoxPro, existe otra capa de fino control disponible a los desarrolladores que lo requieran.
Conexiones
Una conexión usa porciones del Microsoft Open Dabatase Connectivity (ODBC) para establecer una conexión de dos vías de comunicación entre Visual FoxPro y el servidor destino. Cada vendedor tiene su propio mecanismo para proveer, instalar, configurar y soportar esos drivers. Una vez establecida, la conexión de FoxPro puede ser configurada. Hay cierta confusión en la aplicación de la terminología VFP con respecto a conexiones. Cuando usted crea o modifica una conexión usando su sintaxis correspondiente, está modificando la definición de una conexión, y no está afectando una conexión existente. Las conexiones son almacenadas en el DBC y las definiciones son usadas cuando son llamadas. Las conexiones FoxPro pueden ser definidas visualmente con el Database Container (DBC) o directamente desde el Command Window con CREATE CONNECTION. Situación similar sucede con los equivalentes a los comandos DELETE, MODIFY, RENAME y DISPLAY CONNECTION. Las conexiones pueden referirse a un ODBC Data Source Name (DSN), ¿definidas con el Applet? ¿Panel de Control ODBC?, o se pueden especificar directamente los parámetros equivalentes. Por código, la conexión podría lucir como el siguiente:
** La primera conexión depende de un DSN pre-establecido
DEFINE Connection cnTest
DATASOURCE dsnAccessNWind
DATABASE C:\Access\nWind.MDB
** La segunda conexión, una conexión sin DSN (DSN-Less)
DEFINE Connection cnVFPFile
CONNSTRING "DRIVER={Microsoft Visual FoxPro};" + ;
"SOURCETYPE=DBF;" + ;
"SOURCEDB=C:\VS98\VFP98\
Finalmente, las conexiones no necesitan ser mantenidas en un DBC. Si el resto de el sistema está usando SQL Pass Through, las conexiones pueden ser creadas al vuelo, usando las funciones SQLConnect() y SQLStringConnect():
** Abrir una conexión usando un DSN, UserID y PassWord
lnHandle = SQLConnect("mySQLServerDSN","SA","")
** Conectar a una tabla via el Driver ODBC driver sin un DSN:
lnHandle = SQLStringConnect("DRIVER={Microsoft Visual FoxPro};"+;
"SOURCETYPE=DBF;"+ ;
"SOURCEDB=C:\VS98\VFP98\")
Vistas
Las Vistas Remotas no son muy diferentes a las vistas locales, pero hay un par de áreas que nos pueden preocupar. La primera preocupación es sobre los tipos de datos almacenados en la base de datos remota pueden no compararse exactamente con los tipos de Visual FoxPro. Por ejemplo, no hay concepto de tipo de datos Lógico o Booleano en el esquema de Oracle. ¿Esto puede ser fácilmente resuelto usando el Diálogo? ¿Propiedades del Campo? Y especificar el tipo de datos deseado. Use esta opción para mapear campos DateTime a datos tipo Fecha. Los campos Fecha son poco comunes en backends de base de datos, pero las funciones matemáticas de fechas de VFP están en días y las tipo DateTime en segundos. Tenga cuidado de asegurarse que se usan las unidades de medida correctas. La segunda preocupación es uso apropiado de conexiones. En el diseñador de Vistas, bajo las Opciones Avanzadas -> Menú Opciones, ¿en la sección? ¿Query?, ¿Verá un checkbox etiquetado? ¿Compartir Conexión? (? ¿Share Connection? Para la versión en inglés). Por el simple hecho de que las vistas utilizan la misma conexión, no significa que cada uno intentará compartir la conexión actual, por el contrario, cada vista creará su propia copia de la conexión definida a menos de que ese checkbox esté marcado. Hay una configuración global equivalente, esta se encuentra en Herramientas/Opciones/Datos Remotos, esto no hacía nada en VFP 3 y 5. Finalmente, en VFP 6, esta ocasionaba que la compartición de conexión fuera seleccionada por default cuando estaba marcada. ¿Vea? ¿Vistas y Conexiones? Más adelante para una discusión sobre los temas envueltos. Al igual que las conexiones, las Vistas pueden ser definidas y manipuladas programáticamente, al igual que visualmente. Hay algunas ventajas reales al definir las vistas programáticamente. Es fácil para todos el ver la definición de la vista. No necesita meterse en problemas con el Diseñador de Vistas cuando las relaciones complejas no son visualizadas correctamente. Finalmente, puede extender fácilmente la naturaleza del meta-data del sistema para generar la Vista al vuelo. Revise el GenDBC (incluido con Visual FoxPro en el directorio (HOME()+?Tools\GENDBC?) para algunas ideas al extraer meta-data desde el DBC o usarlo para generar los comando requeridos para regenerar el DBC.
El comando CREATE VIEW crea una vista remota en el contenedor de base de datos actual. El formato de el comando parece un poco extraño, ¿debido a que el comando SQL SELECT contenido dentro del CREATE VIEW no está delimitado de ningún modo? No se le establece comillas o paréntesis o algo. Este comando SQL SELECT describe como obtener los datos para producir la Vista. ¿Recuerde qué Visual FoxPro no está dando los datos? Una aplicación servidor lo hace. Así, el SQL usado en el comando CREATE VIEW es el SQL y su extensiones nativas al server, no así a Visual FoxPro. ¿Esto significa que debería evitar? ¿FoxProismos? O funciones integradas de FoxPro (TRIM(), STR(), VAL()), a menos de que sepa que son soportadas por el server. Hay una excepción hablando del lenguaje materno del servidor, y esta situación de pedirle al ODBC que traduzca algunas frase en el proceso. Recuerde que se está usando ODBC para conectar Visual FoxPro al driver de base de datos correcto. ODBC también provee una API e incluye la habilidad para traducir la requisición desde un SQL más genérico hacia uno entendible por el backend. Las funciones pedidas al ODBC se desactivan del código SQL al poner llaves (símbolos { } ). Estos códigos pueden incluir peticiones para funciones internas del ODBC, traducciones de ciertos datos en formatos fecha y tiempo, y hasta extensiones específicas soportadas por algunos drivers. Revise la documentación de su driver ODBC llamando el Applet ODBC y seleccionando ayuda.
* Ejemplo de una vista definida por código:
CREATE VIEW l_Customers_State ;
REMOTE CONNECTION cnOracle SHARED AS ;
SELECT * ;
FROM Customer ;
WHERE Customer.State = &pcState ;
ORDER BY Customer. Name
Vistas y Conexiones
La razón por la cual usted necesita entender cuantas conexiones establecer con el servidor es por que cada conexión consume recursos, recursos que tienden a ser preciosos para el rendimiento del server y tienden a limitar la escalabilidad del sistema entero. También, algunos vendedores tienen licencias de sus servidores por conexión, en vez de usuarios, así que esto podría convertirse en un problema de licencias. Entonces, ¿Por qué no compartir toda la comunicación con el server sólo con una conexión? Bien, hay algunas restricciones que hacen esto más complicado al usar sólo una conexión. Primero, las conexiones son definidas en el DBC y sólo pueden ser usadas por vistas en el DBC. Si tiene múltiples DBCs en su aplicación (y hay una o muchas buenas razones para no hacer esto), entonces estará manteniendo múltiples conexiones. Segundo, mientras que los comandos SQL Pass Through pueden compartir una conexión con una vista, consultando su conexión y entonces usar el mismo manejador, una vista no compartirá una conexión [Nota del Editor: Esto ha cambiado con la llegada de Visual FoxPro 8, en esta versión si es posible compartir conexiones entre SPT y las Vistas Remotas]. Finalmente, ¿los comandos están diseñados para tomar algún tiempo o que requiera de más de un simple comunicación de? Pedir-¿Responder? Entre el Cliente y el Servidor, ¿serán propensos a? ¿Bloquear la línea telefónica? Y necesita estar en su propia conexión. Dichos comandos incluyen peticiones progresivas, comandos por lotes, comandos precompilados y muchas otras situaciones. Es estos casos, planee una o más conexiones para los procesos en lotes, y también planee una conexión compartida, si es posible, una para vistas y otra para sentencias cortas en el lenguaje SQL. SQL Pass-Through (SPT) Como se mencionó en secciones previas, toda la funcionalidad que puede ser obtenida usando el Contenedor de Base de Datos y las herramientas visuales también está disponible programáticamente con el lenguaje. Como siempre, esto es una de los puntos destacados de Visual FoxPro, los cuales están bien implementados aquí:
Función Propósito
SQLTables() Retorna una lista de tablas o vistas para una conexión específica. ¿Útil para utilerías de? ¿Caja negra?
SQLColumns() Retorna especificaciones de campos en uno de varios formatos, útil para comparar capacidades de VFP con base de datos externas.
SQLConnect(), SQLStringConnect() Descritos anteriormente, son usados para establecer una conexión con el servidor
SQLExec() Pasa comandos al server vía ODBC, estos serán ejecutados.
SQLCommit(), SQLRollback() Provee la funcionalidad para completar o abortar una transacción. La transacción debe ser empezada en modo manual, utilizando SQLSetProp() descrita más abajo.
SQLSetProp(), SQLGetProp() Lee y establece propiedades de una conexión existente.
DBSetProp(), DBGetProp() Lee y establece propiedades de un Contenedor de Base de Datos con respecto a vistas, conexiones, tablas, etc. Estos son los únicos valores persistentes entre sesiones.
CursorSetProp(), CursorGetProp() Establece o lee propiedades en un cursor abierto, incluyendo el buffering.
Cliente Servidor Avanzado
Puede que los detalles de estos tópicos estén lejos de lo que es apropiado cubrir en una sesión introductoria, exponer estos conceptos puede darle algunas ideas sobre de lo que puede considerar estos productos en aplicaciones más avanzadas:
Batch SQL: Múltiples estatutos SQL pueden ser enviados al servidor, con procesamiento continuo en el cliente, y ocasionalmente revisar el estatus del procesamiento.
Progressive Fetching: Muy conveniente cuando se descarga un conjunto de datos potencialmente grande, este método retorna el control a FoxPro después de que una cantidad predeterminada de registros han sido devueltos, y automáticamente obtiene registros adicionales en medida de que se vayan necesitando. Un ejemplo excelente sería un Grid para tomar la lista de clientes, donde el operador podría especificar un criterio de búsqueda, y el cliente podría estar listado si está dentro del criterio. El operador podría recorrer la página en la lista, y los registros adicionales podrían ser obtenidos así sean necesitados.
Precompilación: La función SQLPREPARE() permite al servidor recibir una sentencia SQL a ejecutar, ¿y realizar sus funciones sobre ella? Revisión, análisis, desarrollo del plan de trabajo, etc. - y entonces ejecutar la operación mucho más rápido que cuando es llamado.
En todos estos tres casos, estas funciones requerirán una conexión separada, ¿ya qué ellas? ¿Bloquearán la linea telefónica? Como fue mencionado anteriormente en la sección de Vistas y Conexiones. Sin embargo, estas funciones proveen esta necesidad de conexión.
Sugerencias en el aprendizaje de Cliente-Servidor
Las aplicaciones Cliente-Servidor pueden verse como algo muy complicad, pero no son muy difíciles si se aprende una parte a la vez. Recomiendo aprender a usar la funcionalidad básica de ODBC con Visual FoxPro a través de la experimentación directa con otra tabla o base de datos en su máquina, usando el driver ODBC de VFP. Una vez que gana experiencia, intente con conexiones sin DSN (DSN-Less), conexión directas usando SPT, e intenta cancelar y aceptar varios cambios a los datos. Después de que se haya familiarizado con los comandos y funciones básicas con Visual FoxPro, mire en el software del server. Aprenda cómo instalarlo y configurarlo, si es necesario, y aprenda que componentes son requeridos para instalar conectividad con clientes y driver. Muchos productos vienen con una sencilla ventana de comandos o interface interactiva, úsela para verificar que la conectividad está trabajando correctamente. Puede ser bastante frustrante el tomar algo desconocido y buscar por uno mismo que es lo que no funciona en el sistema, más aún si no hay manera de saber que sucede debido a la mala especificación de protocolo de red, direcciones o puertos incorrectos, o si falta algo por configurar.
Soporte del Framework
Todos los desarrolladores deberían estar usando un framework consistente para desarrollar sus aplicaciones; en vez de empezar de la nada y reinventar la rueda, un framework puede proveer un buen punto de inicio para desarrollo, y un siempre creciente nivel de sofisticación en el servicio proveído. Una de las características clave que debería proveer un framework debería ser las rutinas de servicio para obtener y guardar datos. Visual FoxPro es ideal proveyendo soporte para frameworks de servicios de datos debido a esto hay mucha consistencia y ricos modelos para trabajar con datos. Seleccionando uno de estos modelos y usándolos consistentemente con su framework proveerá un robusto mecanismo de datos.
Conclusión Visual FoxPro provee excelentes herramientas para obtener un control completo de Cliente-Servidor, tanto en variedad gráfica, como a través de la línea de comando. La tecnología Cliente-Servidor es apropiada para tamaños de datos extremadamente grandes, para situaciones de seguridad de alto riesgo, y para ambientes con comunicaciones con bajo ancho de banda desde el cliente al servidor. Además de que el Diseñador de Vistas y el Diseñador de Conexiones pueden permitir un completo diseñado gráficamente, Visual FoxPro también provee las herramientas y funciones para mezclar diseños programáticos y gráficos(o hasta basado en metatablas). Esta combinación hace fácil de aprender combinando el poder en la implementación.

FoxPro Client-Server Architecture for Enterprise Database Connectivity
Special thanks go to Yair Alan Griver of Flash Creative Management, Pat Adams of DB Unlimited, and Melissa Dunn of MicroEndeavors, Inc., for their valuable input and thorough review of this paper.
Overview
The ability to access heterogeneous data that resides on different hardware platforms, different operating systems, different network operating systems, and different databases is a fundamental need for client-server computing. Client-server computing is beginning to move into the mainstream of corporate information systems. With this move comes the need for client-server applications that can access enterprise-wide data. Much of this data is currently stored in mainframe and mini-computer databases, and one of the challenges facing implementers of client-server technology today is how to bring this mission-critical data to the desktop and integrate it with the functional, easy-to-use graphical user interfaces (GUIs) that are associated with personal-computer (PC) -based tools.
Each computer company, each corporation, and, in some cases, each individual user has its own definition of what client-server computing means. In order to eliminate confusion, client-server architecture in this white paper refers to distributing an application between a front-end client workstation component and a back-end server component. Ideally, the server has responsibilities for managing all the requests it receives from other processes, including request queue management, buffer management, execution of the service, results management, and notification of service completion. ¿It is the client? S task to initiate communications, request specific services, acknowledge service completion notifications, and accept results from its server. User-intensive functions, such as handling input and displaying data, ¿are left to the user? S FoxPro® application. Data-intensive functions, such as file I/O and query processing, are left to the relational database management system (RDBMS).
In comparison, when an application running on a PC can transparently access data located on a file server, this is known as the file server architecture. Essentially, the PC application requests data from a shared file, the networking software automatically retrieves a block of the file from the server. However, in a scenario where FoxPro repeatedly requests blocks of data from the network server, heavy network traffic is produced.
Microsoft® FoxPro and SQL Server provide the best of both worlds in client-server development. ¿FoxPro has superior decision-support capabilities with its unsurpassed speed through the Rushmore? Technology. SQL Server provides a high level of security and data integrity at the database level for robust data entry systems. These products working together provide the capability to develop on-line transaction processing and decision support client-server applications.
The purpose of this white paper is to outline some of the basic issues involved in accessing heterogeneous databases, outline general approaches to achieving heterogeneous database access, and outline how FoxPro can access heterogeneous databases. The database connectivity solutions developed by Microsoft are discussed in depth, with an emphasis on how these products relate to each other. Finally, this white paper provides some general guidelines for designing applications for enterprise database connectivity using Microsoft FoxPro, SQL Server, and database connectivity products.
Heterogeneous Database Access Issues
Think of accessing heterogeneous databases as a subset of using distributed databases. The technical challenges of delivering fully distributed database management systems (DBMS) in commercial products are difficult and have not yet been solved. These problems include distributed query processing, distributed transaction management, replication, location independence, as well as heterogeneous database access issues. The ability to access heterogeneous databases (that is, data that resides on different hardware platforms, different operating systems, different network operating systems, and different databases) is a fundamental need today, and it can be addressed without having to wait for fully distributed databases to arrive.
When thinking about the problems involved in accessing heterogeneous databases, it is useful to consider the problems at different levels. Figure 1 identifies some of the levels and interfaces encountered when accessing data in a client-server environment.
Figure 1. Levels and interfaces in a client-server environment
Some of the areas that need to be addressed when attempting to access heterogeneous databases are application programming interfaces (APIs), data stream protocols, interprocess communication (IPC) mechanisms, network protocols, system catalogs, and structured query language (SQL) syntax.
Application Programming Interfaces
Each back-end database typically has its own application programming interface (API), through which it communicates with clients. A client application that must access multiple back-end databases therefore requires the ability to transform requests and data transfers into the API interface supported by each back-end database it needs to access.
Client/server applications communicate with SQL Server for Microsoft Windows NT? Through two application programming interfaces: open database connectivity (¿ODBC) and DB-Library?.
ODBC is a C programming language interface for generic database connectivity. The ODBC interface permits maximum interoperability, allowing a single application to access diverse database management systems. The application developer can develop, compile, and ship an application without targeting a specific DBMS. ODBC achieves interoperability by forcing all clients to adhere to a standard interface. The ODBC driver will automatically interpret a command for a specific data source. ODBC has been designed to be a general purpose Call Level Interface (CLI) for any database backend, including non-relational DBMSs.
ODBC provides the following advantages:
¿Microsoft Windows? Operating system universal data access: ODBC is the Microsoft strategic direction for access to relational databases from the Windows platform. New Windows-based client-server applications should use ODBC as their database access API. In the future, Microsoft will also support ODBC on the Macintosh® and other platforms.
Flexible heterogeneous data access: ODBC was designed as an API for heterogeneous database access.
ODBC preserves the semantics of the target DBMS data types.
ODBC provides a connection model that is generic and extensible to allow for different networks, security systems, and DBMS options.
¿Access to? ¿Local? Data. ODBC enables easy access to local data such as Xbase or Microsoft Access®. It will treat local data that is not in a relational format as if it were a relational database. From a single FoxPro application you can access local and remote data through the same ODBC API.
DB-Library is an API designed specifically for Microsoft SQL Server or Sybase® SQL Server. DB-Library is a set of C functions and macros that allow an application to access and interact with SQL Server. DB-Library offers a full set of APIs for: (1) opening SQL Server Connections, (2) formatting queries, (3) sending query batches to the server and retrieving the resulting data, (4) bulk-copying data from files or program variables to and from the server, (5) performing two-phase commit operations, and (6) executing stored procedures on remote servers.
Data Stream Protocols
Every DBMS uses a data stream protocol that enables the transfer of requests, data, status, error messages, etc. between the DBMS and its clients. Think of this as a? ¿Logical? Protocol. The API uses interprocess communication (IPC) mechanisms supported by the operating system and network to package and transport this logical protocol. The Microsoft SQL Server data stream protocol is called Tabular Data Stream (TDS). ¿Each database? S data stream protocol is typically a proprietary one that has been developed and optimized to work exclusively with that DBMS. This means that an application accessing multiple databases must have the ability to use multiple data stream protocols. Using ODBC helps resolve this problem for application developers.
Interprocess Communication Mechanisms
Depending on the operating system and network it is running on, different interprocess communication (IPC) mechanisms might be used to transfer requests and data between a DBMS and its clients. For example, Microsoft SQL Server on OS/2® uses named pipes as its IPC mechanism, Sybase SQL Server on UNIX® uses TCP/IP sockets, and Sybase on VMS® uses DECnet? Sockets. The choice of IPC mechanism is constrained by the operating system and network being used. In a heterogeneous environment, multiple IPC mechanisms may be involved.
SQL Server for Windows NT has the ability to communicate over multiple Interprocess Communication Mechanisms. SQL Server communicates on named pipes (over either NetBEUI or TCP/IP network protocols) with clients running Windows, Windows NT, MS-DOS®, and OS/2 operating systems. It can also simultaneously support TCP/IP Sockets for communication with Macintosh, UNIX, or VMS clients and SPX sockets for communications in a Novell® Netware® environment. As the networking components for Banyan® VINES® become available for Windows NT, it will be supported as well.
Network Protocols
A network protocol is used to transport the data stream protocol over a network. ¿It can be considered the? ¿Plumbing? That supports the IPC mechanisms used to implement the data stream protocol, as well as supporting basic network operations such as file transfers and print sharing. Popular network protocols include NetBEUI, TCP/IP, DECnet, and SPX/IPX.
Back-end databases can reside on a local-area network (LAN) that connects it with the client application, or it can reside at a remote site, connected via a wide-area network (WAN) and/or gateway. In both cases, it is possible that the network protocol(s) and/or physical network supported by the various back-end databases are different from that supported by the client or each other. In these cases, a client application must use different network protocols to communicate with various back-end databases.
System Catalogs
A relational database management system (RDBMS) uses system catalogs to hold metadata, or information about the data being stored. Typically, system catalogs hold information about objects, permissions, data types, and so on. Each RDBMS product has an incompatible set of system catalogs with inconsistent table names and definitions. Many client tools and applications use system catalog information for displaying or processing data. For example, system catalog information can be used to offer a list of available tables, or to build forms based on the data types of the columns in a table. An application that makes specific reference to the SQL Server system catalog tables will not work with another RDBMS such as DB2® or Oracle®.
SQL Syntax and Semantics
Structured Query Language (SQL) is the standard way to communicate with relational databases. In a heterogeneous environment, two main problems arise with respect to SQL syntax and semantics. First, different database management systems can have different implementations of the same SQL functionality, both syntactically and semantically (for example, data retrieved by a SQL statement might be sorted using ASCII in one DBMS and EBCDIC in another; or the implementation of the UNION operator in different database management systems might yield different result sets). Second, each implementation of SQL has its own extensions and/or deficiencies with respect to the ANSI/ISO SQL standards. This includes support for different data types, referential integrity, stored procedures, and so on. An application that needs to access multiple back-end databases must implement a lowest common denominator of SQL, or it must determine what back-end it is connected to so that it can exploit the full functionality supported.
Heterogeneous Database Access Approaches
When developing client-server applications in a heterogeneous environment, it is important to first understand the different approaches to accessing databases. These database access approaches can be classified into three possible classes: the common interface approach, the common gateway approach, and the common protocol approach, ¿as defined by R.D. Hackathorn in his article?Emerging Architecture for Database Connectivity? In InfoDB.
Common Interface Architecture
A common interface architecture, shown in Figure 2, focuses on providing a common API at the client side that enables access to multiple back-end databases. Client applications rely on the API to manage the heterogeneous data access issues discussed earlier. Typically, a common API would load back-end? Specific drivers to obtain access to different databases. An example of a common interface architecture is Microsoft Open Database Connectivity (ODBC), discussed later in this technical article.
Figure 2. Common interface architecture
Common Gateway Architecture
A common gateway architecture, shown in Figure 3, relies on a gateway to manage the communication with multiple back-end databases.
An example of a common gateway architecture is a gateway based on Microsoft Open Data Services, discussed later in this article.
Figure 3. Common gateway architecture
In his book Introduction to Database Systems, C.J. Date states:¿?... there are clearly significant problems involved in providing satisfactory gateways, especially if the target system is not relational. However, the potential payoff is dramatic, even if the solutions are less than perfect. We can therefore expect to see gateway technology become a major force in the marketplace over the next few years.? (Page 635)
Common Protocol Architecture
The common protocol approach, shown in Figure 4, focuses on a common data protocol between the client and server interfaces. Conceptually, this is perhaps the most elegant way of addressing the problem of heterogeneous data access.
Figure 4. Common protocol architecture
Two common data protocol architectures are the proposed ANSI/ISO Relational Data Access (RDA) standard, and the IBM® Distributed Relational Database Architecture (¿DRDA?). Both of these architectures are in their infancy, and it is too early to determine how well they will function as commercial products.
It is important to note that these approaches to enabling heterogeneous database access are not exclusive. For example, an ODBC driver might connect through an Open Data Services gateway to a back-end database. Alternatively, ¿an ODBC driver or Open Data Services gateway that? ¿Speaks? DRDA or RDA is possible.
Achieving Heterogeneous Database Access
We have looked at the basic issues involved in accessing heterogeneous databases, and generalized ways of approaching solutions. We will now look at specific connectivity products from Microsoft that enable heterogeneous data access. The SQL Server building blocks to data access, Tabular Data Stream (TDS) and the Net-Library architecture, are an integral part of products enabling connectivity to heterogeneous databases. We then discuss Microsoft Open Database Connectivity (ODBC) and the FoxPro Connectivity Kit. Finally, we make recommendations to help you decide which API, DB-Library or ODBC, to use and identify considerations that you should be aware of when developing client-server applications.
SQL Server Building Blocks (TDS and Net-Library)
Tabular Data Stream (TDS) and Net-Library are part of the core SQL Server technology that Microsoft connectivity products build on to integrate SQL Server?based applications into heterogeneous environments. Figure 5 shows how TDS and Net-Library fit into the client-¿Server architecture of SQL Server? Based applications.
Figure 5. FoxPro and SQL Server building blocks
TDS is the data stream protocol used by Microsoft SQL Server, Open Data Services, and SYBASE software to transfer requests and responses between the client and the server. Because TDS can be considered a logical data stream protocol, it must be supported by a physical network interprocess communication mechanism (IPC) which is where the Net-Library architecture comes in. A DB-Library application makes calls to the generic Net-Library interface. Depending on which Net-Library is loaded, communication with SQL Server is achieved using named pipes, TCP/IP sockets, DECnet sockets, SPX, and so on.
The Net-Library architecture provides a method of sending TDS across a physical network connection, as well as a transparent interface to the DB-Library application programming interface (API) and the SQL Server driver for ODBC. Net-Libraries are linked in dynamically at runtime. With the Microsoft Windows NT, Windows, and OS/2 operating systems, Net-Libraries are implemented as dynamic-link libraries (DLLs), and multiple Net-Libraries can be loaded simultaneously. With the MS-DOS operating system, Net-Libraries are implemented as terminate-and-stay-resident (TSR) and only one can be loaded at any given time.
Note The SQL Server Driver for ODBC also uses Net-Libraries and the TDS protocol to communicate with SQL Server and Open Data Services.
Microsoft Open Database Connectivity
Open Database Connectivity (ODBC) is a universal database connectivity API that enables applications to access data in a heterogeneous environment of relational and non-relational database management systems. Based on the SQL Access Group? S Call Level Interface (CLI) specification, ODBC is an open, vendor-neutral way to access data stored in a wide range of proprietary databases. ¿ODBC takes the? ¿Common API? Approach, discussed earlier, to achieving heterogeneous data access.
The ODBC architecture consists of three components:
Application. Calls ODBC functions to connect to a data source, send and receive data, and disconnect.
Driver Manager. Provides information to an application such as a list of available data sources; loads drivers dynamically as they are needed; and provides argument and state transition checking.
Driver. A DLL that processes ODBC function calls and manages all exchanges between an application and a specific DBMS. If necessary, the driver may translate the standard SQL syntax into the native SQL of the target data source. All translations are the responsibility of the driver developer.
The Driver Manager and driver appear to an application as one unit that processes ODBC function calls. Applications are not limited to communication with one driver. A single application cam make multiple connections, each through a different driver, or multiple connections to similar sources through a single driver.
Figure 6 shows the components of the ODBC architecture.
Figure 6. The ODBC model
Each ODBC driver supports a set of core ODBC functions and data types and, optionally, one or more extended functions or data types, defined as extensions:
Core functions and data types are based on the X/Open and SQL Access Group CLI specification. If a driver supports all core functions, it is said to conform to X/Open and SQL Access Group core functionality.
Extended functions and data types support additional features, including date, time, and timestamp literals, scrollable cursors, and asynchronous execution of function calls. Extended functions might not be supported by a specific driver. Extended functions are divided into two conformance designations, Level 1 and Level 2, each of which is a superset of the core functions.
ODBC can be used in different configurations, depending on the database being accessed. It can be used in one-, two-, or three-tiered implementations. Microsoft and SYBASE SQL Servers and Open Data Services ODBC drivers conform to the highest level of ODBC extended functionality (level 2), supporting scrollable cursors and asynchronous communication.
For additional information about ODBC, see the Microsoft ODBC Application Developer?s Guide and the Microsoft ODBC Driver Developer?s Guide. A complete list of ODBC drivers can be found in the Microsoft ODBC Driver Catalog. Microsoft FoxPro can connect to any of the ODBC drivers listed in the catalog.
Microsoft Open Data Services
Microsoft Open Data Services is a server-side development platform that provides application services to complement the client-side APIs discussed earlier. Open Data Services provides the foundation for multithreaded server applications to communicate with DB-Library or ODBC clients over the network. When the client application requests data, Open Data Services passes the request to user-defined routines, and then routes the reply back to the client application over the network. The reply looks to the client as if the data were coming from SQL Server. Figure 7 illustrates how Open Data Services integrates into an enterprise.
Figure 7. Open Data Services and an enterprise
Open Data Services allow you to extend your FoxPro applications to reach enterprise data. The most common use of Open Data Services is as a gateway to data sources which may not have ODBC drivers available. There is an ODBC driver for Open Data Services which would provide you with open connectivity to any data source. Your FoxPro application would connect to the Open Data Services ODBC driver as if you were connecting to a SQL Server or Oracle database. Two types of gateways are:
General-purpose Gateways: Can handle any ad hoc SQL request from a DB-Library or ODBC client. The Database Gateway from Micro Decisionware, for example, implements a general-purpose gateway into DB2. A component that understands the SQL language and can act on SQL requests is essential to the operation of a general-purpose gateway. This SQL interpreter usually resides in the back-end database itself (as is the case with DB2), but it can also be implemented in the gateway.
Custom Gateways: Not all data server applications need to understand and respond to SQL requests (for example, a data server application that returns the contents of a specific flat file as a results set). This type of application could be designed to respond to only one particular procedure call (such as GetFileA). The Open Data Services application would define the column names and the data types of the fields in the flat file, and then return the records in the file to the requesting client as rows of data. Because this results set would look exactly like a SQL Server results set, the client could process it. This approach works when the information required from the existing system is well-defined, not ad hoc in nature. For ad hoc queries, the better approach is to extract the data from the existing system and load it into a relational database.
Designing Fox Pro Client-Server Applications
This section gives general guidelines to follow when developing applications for enterprise database connectivity using Microsoft FoxPro, Microsoft SQL Server and the Microsoft database connectivity products discussed in the previous section.
FoxPro Connectivity Kit Architecture
Microsoft FoxPro provides immediate and direct access to heterogeneous data through the architecture of the FoxPro Connectivity Kit. The Microsoft FoxPro Connectivity Kit consists of a set of libraries and drivers that enable developers to build client-server applications using FoxPro for MS-DOS or FoxPro for Windows. The Connectivity Kit gives the developer the ability to query external databases, send and retrieve data, update external databases, administer external databases, and execute DBMS specific features such as stored procedures. Essentially, through the FPSQL libraries in the Connectivity Kit, there is a channel opened directly to the external data source. (See Figure 8 on the following page.) The SQL syntax is transmitted directly without being interpreted.
Applications created with FoxPro for MS-DOS can access SQL Server data through the FPSQL library included in the Connectivity Kit. The FPSQL library, FPSQL. PLB, is bound to DB-Library, the API which allows connectivity to Microsoft SQL Server and Sybase SQL Server.
In a Windows environment, the Connectivity Kit includes the FPSQL library that provides ODBC connectivity. Using this library, applications created with FoxPro for Windows can connect to any external database, provided that there is an ODBC driver for that particular DBMS. The ODBC drivers that ship with the Connectivity Kit are for Microsoft SQL Server and Oracle. For a comprehensive list of ODBC drivers that FoxPro can connect to, refer to the Microsoft ODBC Driver Catalog.
Figure 8. Microsoft FoxPro client-server architecture
FoxPro 2.6 Professional Edition Connectivity Updates
Microsoft FoxPro 2.6 adds several new ease-of-use enhancements and dBASE compatibility extensions. In addition, programmability enhancements have been made to client-server connectivity. The Connectivity Kit is now included within FoxPro 2.6 Professional Edition.
Client-server connectivity enhancements include:
A client-server wizard to assist developers in generating the code necessary to connect to ODBC data sources. (Further details are provided in the Processing/Managing Data section.)
Improved support for handling NULL values from ODBC data sources. (Further details are provided in the Processing/Managing Data section.)
Ability to return multiple error messages back to the FoxPro client application.
The Connectivity Kit will now be able to return a list of up to 5 error messages per connection. They are displayed in the error message window and can be obtained by calling DBError(). DBError now has a fourth optional parameter with values from 1 to 5 that indicates which message is being requested from the list. If the parameter is not specified, the latest error message is returned.
New DBVersion() function
This function will send back the version number of the connectivity kit that is currently installed on the FoxPro client. No parameters are required to execute this function.
Logical values return (T,F).
Date fields from the ODBC data source will automatically be converted to character fields within FoxPro.
FoxPro Using the Gateway Approach
FoxPro for Windows applications are able to access a back-end database through a direct-connect (two-tiered) ODBC driver loaded at the workstation, ¿or by connecting to an Open Data Services?based gateway using ODBC (a three-tiered solution). FoxPro for DOS applications will be able to access a back-end database through DB-Library, ¿or by connecting to an Open Data Services? Based gateway via DB-Library.
Figure 9. FoxPro applications using DB-Library or ODBC can connect to SQL Server and Open Data Services.
SQL Syntax and Semantics
SQL is a widely accepted industry standard for data definition, data manipulation, data management, access protection, and transaction control. SQL originated from the concept of relational databases using tables, indexes, keys, rows, and columns to identify storage locations. SQL is different from the FoxPro language which is a record oriented data manipulation language. FoxPro excels in locating specific rows of data. SQL excels in locating records of data..
Microsoft FoxPro natively supports some standard SQL commands against FoxPro data only. The SQL commands supported by FoxPro are INSERT INTO, CREATE TABLE, and SELECT.
ODBC defines a core grammar level that corresponds to the X/Open and SQL Access Group CAE SQL draft specification. The Core SQL grammar provides the following:
Minimum SQL grammar.
Data Definition Language (DDL): CREATE TABLE and DROP TABLE
Data Manipulation Language (DML): simple SELECT, INSERT, UPDATE, SEARCHED, and DELETE SEARCHED
Expressions: simple (such as A>B+C)
Data types: CHAR
DDL: ALTER TABLE, CREATE INDEX, DROP INDEX, CREATE VIEW, DROP VIEW, GRANT, and REVOKE
DML: full SELECT, positioned UPDATE, and positioned DELETE
Expressions: subquery, set functions such as SUM and MIN
Data types: varchar, decimal, numeric, smallint, integer, real, float, double precisión
Beyond the FoxPro SQL and the ODBC SQL implementation, you can use the SQL language specific to the external database server. ¿The decision as to whether to use? ¿Generic? SQL that is common to all databases being accessed, ¿or to? ¿Sense? The back-end being accessed and make use of SQL extensions such as stored procedures, depends on the type of application being developed. Using ODBC as the client API, you can rely on the ODBC driver to take care of some of the differences in SQL syntax and semantics.
¿The decision on the level of? ¿Generic? ¿Versus? ¿Specific? SQL to use depends, among other things, on:
The set of features you want to access from your applications, including features that may not be available from all data sources.
How much interoperability you want to provide.
How much conditional code you want to include to determine whether a function or data type is supported by the data source.
Performance requirements. In general, performance is increased through the use of specific back-end data source features, and it can be adversely affected if you use a minimum set of SQL common to all databases.
Using Microsoft FoxPro, a developer has the best of both worlds. SQL Server (or any ODBC database) can be utilized to locate sets of information and FoxPro?s Data Manipulation Language can be used to manipulate the rows of the result sets from the SQL Server query.
Considerations Using FoxPro and SQL Server
When developing applications using Microsoft FoxPro the question comes up about when to use Microsoft FoxPro and when to move the data to SQL Server. It is important to understand when to use both and what the differences are. The following sections outline some of the advantages to storing data on SQL Server and how FoxPro can leverage those features.
Processing/Managing Data
Using SQL Server for data storage provides you with advantages such as built in data validation, referential integrity, user and data security, and the ability to store larger amounts of data than in FoxPro. SQL Server on the Windows NT platform can store and manage terabytes worth of data whereas FoxPro can handle 2 GB or a billion records in a single database, which ever comes first. On the other hand, FoxPro?s use of the Rushmore technology to execute queries against such large quantities of data is very efficient and therefore can be faster that native database server queries. With this in mind, a developer may want to split an application up between SQL Server and FoxPro. For example, a decision support application that does not modify any data and involves complex queries which slow server processing, may be best suited to run under FoxPro. Using the speed of Rushmore, immediate decisions can be made due to the quick response time. The sensitive data or data that is being dynamically updated should be kept on the backend SQL Server.
When designing a FoxPro client-server application, the FPSQL function library requires that a connection handle be acquired in order to communicated with an external database. Once a connection has been established, this connection handle will be used for all future FPSQL function calls.
Establishing a connection is the same whether you are connecting to SQL Server, Oracle, or another ODBC database. Using FoxPro for DOS, the connection is always to a SQL Server database due to DB-Library. DBConnect() is used to establish the connection handle. In the event that your application requires connections to multiple data sources, you can open multiple connections. Each connection is a separate data stream. ¿In order to establish a connection to a SQL Server called? ¿Foxsqlnt? you would type in the following:
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
In the example above, ¿the server name is? ¿Foxsqlnt?, ¿The user name is? ¿Sa?, ¿And there isn? T any password.
Querying
In FoxPro, a query extracts information from your tables and places it into another, temporary table called a cursor. Using the FoxPro Connectivity Kit, FoxPro can query SQL Server data and place the result set into a FoxPro cursor or table. Once the data is retrieved, you can use FoxPro to browse, query, analyze, and report data stored on external data sources. The query that FoxPro uses to request data from SQL Server must be contained within a DBExec() command written in Transact-SQL which is the SQL implementation that SQL Server understands.
Using the ODBC API in FoxPro for Windows, ODBC extensions to SQL can be used through the DBExec() command. ODBC defines 4 different extensions to ODBC:
Date, time and timestamp
Scalar functions: numeric, string, and data type conversion functions
Outer joins
Procedures
¿The SQL statement syntax is based upon the SQL Access Group? S standard escape clause to cover vendor specific extensions to SQL. The format is:
?--*(vendor(vendor-name),product(product-name)SQL extension--*)?.
The following examples create the same result set of upper case employee names. The first statement below uses the escape clause syntax for a scalar function. The second statement uses the native syntax for SQL Server.
select=--*(vendor(Microsoft),product(ODBC) fn UCASE(NAME)--*)
from employee
select upper(NAME)
from employee
Results that are returned to the Fox application can be returned in a cursor or table. The cursor is similar to any other table in FoxPro, however it is temporary and the data can not be modified unless a read/write cursor is created. Cursors can be fast to work with because they can be held in RAM memory. The cursor that the results are sent back to can be given a specific alias name or it will default to the alias name of dbresult. When you want to call the cursor in your application it is difficult to find out what name has been assigned to the cursor. If you want to modify the data then the result set should be put into a Fox table in which you assign a name to. Note that, by putting the result set into a table, it then becomes a FoxPro table or cursor which is not linked back to SQL Server. When you modify the table, it is the local table that is changed. When you want to modify the SQL Server data, you must send an UPDATE request to the server.
The following example modifies the ytd_sales column to reflect the most recent sales recorded in the sales table in a SQL Server database. This assumes that only one set of sales is recorded for a given title on a given date and that updates are current.
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
ELSE
= DBExec (handle,"update titles
set ytd_sales = ytd_sales + qty
from titles, sales
where titles.title_id = sales.title_id
and sales.date in (select max(sales.date)from sales)")
ENDIF
Complex queries may take time to process, in which case, you may want to have more control of the type of processing that is done. This can be done using the DBSetOpt() function to specify the type of result set processing you want to have in the application. FPSQL functions default to synchronous processing unless otherwise specified. This means that the client will not have control of the application back until the processing of the result set is complete. The other option, asynchronous processing, is when the control is given back to the client application while the result set is still being processed. However, each function must be called in the application repeatedly until it returns a value other than 0. A 0 value indicates that the database server is still executing the query.
Here is an example of how to change from synchronous processing to asynchronous processing and display the result set in a browse window:
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
result = DBSetOpt(handle,?Asynchronous?,1)
ENDIF
= dbexec (handle, ?select * from bigtable?)
IF result > 0 result = DBSetOpt(handle,?Browse?,?ON?)
Endif
In addition to the type of processing you can request, multiple result sets can be handled in a batch mode or a non-batch mode. Batch mode processing, which is the default setting, will only return results from the DBExec() call once all of the individual result sets have been received. When Non-Batch mode processing is selected, the first result set is returned by the DBExec() call. In order to receive the rest of the results, your FoxPro application must call DBMoreRes() continuously until there are no additional results available. No more results are available when a value of 2 is returned.
Transaction Processing
Transaction processing guarantees the consistency and recoverability of SQL Server databases. A transaction typically consists of several SQL commands that read and update the database, ¿but isn?t actually executed until a commit command is issued.
By definition, transaction processing guarantees either that an entire transaction is completed and all resulting changes are reflected in the database or that the transaction is rolled back to a predetermined save-point without changing the database. Transactions can even span multiple servers.
Transaction processing assures that all transactions are performed as a single unit of work - even in the presence of a hardware problem or general system crash. For example, in the scenario below it is crucial that the user-defined transaction be processed in its entirety or not at all:
BEGIN TRANsaction X
Debit savings account $1,000
Credit checking account $1,000
COMMIT TRANsaction X
User-defined transactions are created by surrounding SQL data modification statements with BEGIN TRANsaction and COMMIT TRANsaction commands. Without these commands, SQL Server treats each SQL command it receives as a single transaction. Uncommitted transactions can be canceled by rolling them back, ROLLBACK TRANsaction.
The key component to transaction processing is the write-ahead transaction log that is maintained by SQL Server. This log ensures that data can be recovered. When a request is made to modify a database is received, ¿a copy of both old and new states of the database? S affected portions is recorded in the transaction log. These changes are always made before they are made to the database itself. At any point in time, SQL Server knows which transactions are in progress and which have been committed.
During recovery from a system failure, SQL Server uses the transaction log to restore that database to a consistent state by backing out incomplete transactions. The log is also used to ensure that all changes associated with committed transactions are fully reflected in the database.
Using the FoxPro Connectivity Kit, the need for using BEGIN TRANsaction and END TRANsaction has been simplified. You can specify how you want FPSQL to manage the transaction processing within the application. Using the DBSetOpt() command, the execution of DBExec() and DBMoreRes() functions is modified based upon the mode of processing you request. You can specify the following modes of processing:
Auto: Every SQL statement is considered a complete transaction that is automatically committed.
Manual: For each SQL statement, if no transaction is open, the driver begins a transaction which will remain open until the application commits or rolls back a transaction using DBTransact().
In the example below, each DBExec() command will be embedded in a transaction.
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
result = DBSetOpt(handle,?Transact?,?A?)
ENDIF
Support for NULL Fields
SQL Server has a facility built in that prevents NULLs from slipping into columns where they do not belong. A NULL in SQL Server indicates that the user has not made an entry into a field. The value is unknown rather than blank or 0. Essentially, a NULL indicates that if the user does not make an entry at insert time and there is no default entry for this column, SQL Server assigns the value NULL.
FoxPro does not know the concept of a NULL value when passed from SQL Server and will show inconsistent results. Close equivalents in FoxPro are completely blank strings (?), completely blank dates ({_/_/_}), and zero in the case of a numeric field. However, there is a way to work around the issue of accepting NULL data.
When designing a FoxPro application that queries SQL Server tables, it is helpful to know whether or not the columns in the table allow NULLs. You can get this information by typing in the stored procedure sp_help from the FoxPro command window or ISQL/W, the query tool that ships with SQL Server.
For example, sp_help authors brings back the following information on the authors table:
Name Owner Type Data_located_on_segment When_created
authors dbo user table default Jul 27 1993 9:54AM
Column_name Type Length Nulls Default_name Rule_name
au_id id 11 0 (null) (null)
au_lname varchar 40 0 (null) (null)
au_fname varchar 20 0 (null) (null)
phone char 12 0 phonedflt (null)
address varchar 40 1 (null) (null)
city varchar 20 1 (null) (null)
state char 2 1 (null) (null)
zip char 5 1 (null) ziprule
Contract bit 1 0 (null) (null)
In the table above, there are four columns that will allow NULL values; address, city, state, and zip. ¿The? ¿1? in the NULL column indicates that NULLs are allowed. You will also notice that this same command lists the defaults and rules that are bound to a particular column.
There are several ways to handle NULL values from a backend data source once the columns have been identified that may contain NULL values. The recommended option is to use the new feature within FoxPro 2.6 Professional Edition that allows constants for NULL values from the external data source to be defined by a user.
Users can define four types of NULL values:
? ¿CharNull?: Size 30
? ¿IntNull?: Size 20
? ¿FloatNull?: Size 20
? ¿DateNull?: Size 23
The values can be set/get using the DBSetOpt() and DBGetOpt() functions:
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
result = DBSetOpt(handle,?FloatNull?,?9999999.9999999?)
ENDIF
If a constant for NULL values is not specified, the default for all cases is an empty string, NULLs become spaces in FoxPro.
The second option is to use the ISNULL function within SQL Server to change the value. The ISNULL function will replace each NULL entry it finds with a value that you specify. For example, this SELECT statement will not catch any NULL entries:
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
ELSE
= DBExec (handle,"select au_lname, phone, state
from authors")
ENDIF
This SELECT statement will catch NULL entries for the state column and change the NULL value to an asterisk (*):
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
ELSE
= DBExec (handle,"????????????????????????????????????????")
ENDIF
For columns with a character datatype, you may want to substitute an asterisk (*) or a word, such as ?unknown?, for a NULL value. Numeric columns with NULLs could be replaced with a 0.
Client-Server Query Wizard
Microsoft FoxPro 2.6 Professional Edition includes powerful new wizards, designed to make everyday database tasks easier for users and developers. In order to simplify connectivity to heterogeneous databases, a Client-Server Wizard has been included. The Client-Server Wizard will generate the code necessary to connect to a remote database and execute a query against the data. Once the connection code has been generated it can be reused within other FoxPro applications.
The wizard stores the connectivity code in a program file with a .CSQ extension instead of a .PRG extension. Here is an example of the code generated:
m.passwd=""
WAIT WINDOW NOWAIT "Connecting..."
m.Handle=DBCONNECT("FOXSQLNT","sa",m.passwd)
WAIT CLEAR
m.RetVal=DBExec(m.Handle,"use pubs")
mFldLst='titles.title,titles.pub_id,titles.type,titles.price'
mFrom='titles'
mWhere=""
mOrderBy=' ORDER BY titles.title'
mGroupby=""
m.RetVal=DBExec(m.Handle,"SELECT "+mFldLst+" FROM
"+mFrom+mWhere+mGroupBy+mOrderBy,"Result")
Each of the variables represents a step of the wizard. For example, m.Handle represents Step 1 that asks which ODBC data source do you want to connect to.
Data Integrity
Relational databases organize data in a simple, tabular form, and provide many advantages over other databases. One of the key advantages is the ability of an external database to automatically maintain integrity between entities. This data integrity is initially set up by the database administrator, the application developer does not have to worry about programming this into the database application. FoxPro does not have the ability to enforce data integrity.
It is possible however to provide some forms of data integrity in FoxPro. However, this is done programmatically, it can be changed at any time, and the integrity relationships must be put into each FoxPro application.
When designing applications that access company data, it is important that the data is protected and consistent. Incorporating data integrity rules and business policies with your data will ensure that the data does not become corrupt or disorganized. FoxPro can maintain the integrity of the data programmatically. SQL Server enforces data integrity within the database itself, guaranteeing that complex business policies will be followed by all client-server applications. By storing the data on SQL Server, all of your FoxPro applications can take advantage of advanced data integrity features such as user-defined data types, defaults, rules, stored procedures, and triggers.
User-Defined Data Types
SQL Server provides an extensive list of pre-defined system datatypes for developers to use, such as: char, int, varchar, etc. In addition, FoxPro developers have the ability to create their own datatypes to supplement the system datatypes.
For example, a state_code datatype could be defined as two characters (char[2]). The Transact-SQL code is:
sp_addtype state_code, ?char(2)?
User-defined datatypes can be created within FoxPro using a VALID or WHEN clause in an application. These datatypes can be shared among multiple applications by copying the code from application to application. The user-defined datatypes in SQL Server are created once, stored in one central location, and can be shared throughout the database. The integrity of your data is consistent with minimal programming. Another advantage of user-defined datatypes is that rules and defaults can be bound to them for use in multiple tables, and tailored to specific applications.
Defaults
Defaults allows that application develop to specify a value that SQL Server inserts if no explicit field value is entered into a particular column. For example, the current date could be set as a default value for a purchase_date field in a customer purchase record. If a user or the FoxPro application doesn?t make an entry in the purchase_date field, SQL Server will automatically insert the current date.
Rules
In FoxPro you can create a PICTURE clause to specify how fields, memory variables, and arrays are edited and displayed. Rules are similar to the PICTURE clause in an @...GET command. However, rules also provide integrity constraints that go beyond the column datatype parameters to enforce business policies. Whenever values are entered into a database SQL Server checks it against any rule that has been bound to that column. A rule can require that a value must match a particular pattern, match one of the entries in a specified list, or fall within a particular range.
Triggers
Triggers enforce referential integrity at the table and view level to supply cascading deletions and to supply cascading updates. In essence, triggers are a special type of stored procedure that are explicitly called for execution and triggers are automatically invoked by SQL Server whenever an attempt is made to modify the data that they protect. Triggers are invoked when an INSERT, UPDATE, or DELETE action is called.
Triggers can be nested 16 levels deep for a cascading integrity check of the database tables. If a trigger changes a table on which there is another trigger, the second trigger activates and can then call a third trigger, and so on.
The FoxPro VALID and WHEN clauses are similar to triggers. VALID and WHEN clauses are used for data validation but can be used in different ways throughout an application which could lead to gaps in your code. These clauses also can be very precise for a particular field, allowing modification of the data one field at a time. For integrity checks among tables, this get very complex and affect productivity.
Although integrity checks may be faster within FoxPro, you will have to copy common code to each application. By keeping integrity checks within the external database, the integrity rules and triggers are maintained with the data in one place. ¿When changes are made to your corporation? S integrity rules, they are made at the server in one place. The FoxPro client application will not have to be modified. It is best, when accessing SQL Server data from Fox, to leave the integrity of the SQL Server tables up to that database engine and use the VALID and WHEN clauses for field validation within the FoxPro client applications.
Stored Procedures
Every time a SQL command is sent to SQL Server for processing, the server must first parse the command, check to make sure that the syntax is correct, check to see if the client has the correct permissions necessary to execute the command, and create a query execution plan to process the request. For complex queries this process can take some time to process. Stored procedures ensure consistent access to data resources and increase the speed of query execution. Essentially, stored procedures are groups of compiled SQL statements that are stored on SQL Server for later recall.
Stored procedures have already been through the parser, the query optimizer, and pre-compiled in the procedure cache waiting to be executed. (See Figure 10 below.) SQL Server stores this compiled version in cache and uses it to process subsequent calls. As a result of being precompiled, stored procedures will dramatically increase the execution speed of your query. Stored procedures will also reduce your network traffic because you are sending a small data stream.
Figure 10
Stored procedures will be recompiled for efficiency whenever changes are made to objects that they affect. In addition, stored procedures will accept parameters, so a single procedure could be used by multiple applications using different input data.
FoxPro does not have an equivalent function to a stored procedure. However, FoxPro developers can code complex queries and transactions into stored procedures and then invoke them directly from any FoxPro application, whether that be in Windows or MS-DOS. In addition, using stored procedures on the server side can reduce the amount of Transact-SQL code required in the client application.
For example, the query below finds all au_ids in the titleauthor table for authors who make less than 50 percent of the royalty on any one book, and then selects from the authors table all author names with the au_ids that match the results from the titleauthor query. The following FoxPro statement will have to be added to your application:
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
ELSE
= DBExec (handle,"select au_lname, au_fname
from authors
where au_id in
(select au_id
from titleauthor
where royaltyper < 50")
ENDIF
To simplify the FoxPro code within your application, create a stored procedure on SQL Server called get_authinfo that executes a query to retrieve the same data from the customers and orders tables:
create proc get_authinfo as
select au_lname, au_fname
from authors
where au_id in
(select au_id
from titleauthor
where royaltyper < 50)
When using the stored procedure, the FoxPro code will be the following:
handle = DBConnect(?foxsqlnt?,?sa?,??,??)
IF handle>0
WAIT WINDOW ?Successfully connected?
ELSE
= DBExec (handle,"exec get_authinfo")
Endif
Extended stored procedures
SQL Server provides a way to dynamically load and execute a function within a DLL in a manner identical to a stored procedure. Actions external to SQL Server can be easily triggered and external information returned. Both return status codes and output parameters identical to their counterparts in regular stored procedures are also supported.
Examples of extended stored procedures are supplied with SQL Server. One is xp_cmdshell. This function allows any Windows NT command or process to be executed from within SQL Server. For example, you can use xp_cmdshell from within a trigger to send a broadcast on the network about changes that have been made to the data. Another example would be a trigger that could test to see if the inventory has fallen below a certain level, automatically execute a reorder transaction, and send a Microsoft Mail message to the purchasing manager via MAPI.
Data Security
SQL Server implements comprehensive user-level security protections on database objects (tables, records, views, and so forth) and SQL commands. It also supports column-level security, where access to particular columns in a database can be restricted to certain users. Stored procedures can also be used to permit certain users to execute specific operations, without giving them permissions to access the underlying data.
All security information and logic is stored in the data dictionary, where it can be accessed and updated by the system administrator. Since all security is handled by SQL Server, FoxPro client-server applications can safely ignore these issues. This security scheme is in addition to that imposed by Microsoft Windows NT? Advanced Server.
Views
In SQL Server, views allow users to see and modify a subset of information contained within an existing database table or tables. For example, a company employee database might contain name, department, supervisor, performance rating, and salary columns (fields). A view of the table may contain only the name, department, and supervisor columns. Employees who do not need access to salary information can be given access to this predefined view rather than the actual table which contains sensitive information.
Views are created dynamically and behave like other tables - they can be displayed and operations performed on them. When data seen through a view is modified, the data in the underlying table(s) is modified as well. Conversely, changes to data in the underlying table(s) are automatically reflected in the views derived from them.
Users can only query and modify the data they see. The rest of the database is not accessible. By defining different views and selectively granting permissions on them, a user, or any combination of users, can be restricted to different subsets of the data.
Summary
This article has addressed some of the issues involved in enabling FoxPro client-server applications to access enterprise data stored in a wide variety of heterogeneous databases. FoxPro has powerful database functionality within, but, combined with the security, transaction processing, and data integrity of SQL Server, you can develop powerful client-server applications that access and maintain mission critical data.
References
Date, C.J. An Introduction to Database Systems, Volume 1 (5th edition). Addison-Wesley, 1990.
Hackathorne, R.D. "Emerging Architectures for Database Connectivity.? InfoDB, January 1991.
Additional Information
To receive more information about Microsoft FoxPro or Microsoft SQL Server, contact Microsoft Inside Sales, Systems Software, at (800) 227-4679.
¿FoxPro Connectivity Kit User? S Guide
FoxPro Goes Client-Server: DB-Library programming techniques for client applications
part number 098-30194
Microsoft SQL Server Transact-SQL Reference manual
Microsoft Open Data Services: Application source book
part number 098-32078
Discussion of the ANSI SQL Standard and Microsoft SQL Server
part number 098-34656
Information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
Companies, names, and data used in examples herein are fictitious unless otherwise noted.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Visual FoxPro
Preparar el Asistente para upsizing a SQL Server
Antes de ejecutar el Asistente para upsizing a SQL Server, debe preparar tanto la parte del cliente como la parte del servidor.
Preparar la parte SQL Server
Antes del upsizing, debe asegurarse de que dispone de los permisos necesarios en el servidor, estimar el tamaño de la base de datos y comprobar que el espacio en disco del servidor es suficiente. También existen preparativos especiales para un upsizing a múltiples discos o dispositivos.
Comprobar el espacio libre en disco
Asegúrese de que el espacio en disco disponible en el servidor es suficiente.
Precaución Si el Asistente para upsizing a SQL Server se queda sin espacio libre en el servidor, se detendrá, dejando en el servidor una base de datos parcial y los dispositivos que haya creado. Puede eliminar los dispositivos, las bases de datos y las tablas con la herramienta de Administración de SQL Server.
Establecer permisos en las bases de datos de SQL Server
Para ejecutar el Asistente para upsizing a SQL Server, tiene que disponer de determinados permisos en el servidor SQL Server hacia el que desea realizar el upsizing. Los permisos necesarios dependen de las tareas que desee realizar.
? Para un upsizing hacia una base de datos existente, necesita los permisos CREATE TABLE y CREATE DEFAULT.
? Para generar una nueva base de datos, necesita los permisos CREATE DATABASE y SELECT para las tablas del sistema de la base de datos master.
? Para crear nuevos dispositivos, tiene que ser un administrador del sistema.
Para obtener más información acerca de la concesión de permisos del servidor, consulte la documentación de SQL Server.
Estimar el tamaño de la base de datos y los dispositivos de SQL Server
Cuando usted crea una nueva base de datos, el Asistente para upsizing a SQL Server le pide que seleccione dispositivos para la base de datos y el registro. También le solicita que defina el tamaño de la base de datos y de los dispositivos.
Estimar el tamaño de la base de datos de SQL Server
Cuando SQL Server crea una base de datos, reserva una cantidad fija de espacio para dicha base de datos en uno o varios dispositivos. La base de datos no utiliza necesariamente todo este espacio; el tamaño solamente limita el crecimiento máximo de una base de datos antes de que se agote su espacio.
Nota Puede aumentar el tamaño de una base de datos de SQL Server después de haberla creado. Para obtener más información al respecto, consulte el comando ALTER DATABASE en la documentación de SQL Server.
Para estimar el espacio necesario para la base de datos, calcule el tamaño total de los archivos .dbf de Visual FoxPro para las tablas en las que desea realizar el upsizing más la velocidad a la que crecerá la nueva base de datos de SQL Server. En general, cada megabyte de datos de Visual FoxPro requiere por lo menos entre 1,3 y 1,5 megabytes en SQL Server.
Si dispone de mucho espacio en disco en el servidor, multiplique por dos el tamaño de las tablas de Visual FoxPro. Esto le garantiza que el Asistente para upsizing a SQL Server tendrá suficiente espacio para realizar el upsizing de la base de datos y dejará espacio para el crecimiento. Si piensa agregar gran cantidad de datos a la base de datos, aumente el múltiplo.
Estimar el tamaño de los dispositivos de SQL Server
Todas las bases de datos y los registros de SQL Server se sitúan en dispositivos. Un dispositivo es una ubicación lógica donde se introducen bases de datos y registros, además de un archivo físico. Cuando se crea un dispositivo, SQL Server crea un archivo, reservando de esta forma una cantidad fija de espacio en disco para su uso propio.
El Asistente para upsizing a SQL Server muestra la cantidad de espacio libre disponible en los dispositivos de SQL Server existentes. Seleccione un dispositivo que tenga como mínimo suficiente espacio libre para el tamaño estimado de la base de datos.
Si ninguno de los dispositivos existentes tiene espacio libre suficiente, puede crear un nuevo dispositivo con el Asistente para upsizing a SQL Server. Los dispositivos nuevos deben tener como mínimo el tamaño estimado de su base de datos. Si es posible, defina el dispositivo con un tamaño mayor que el de la base de datos, lo que le permitirá ampliarla posteriormente o situar otras bases de datos o registros en el mismo dispositivo.
Nota El tamaño de los dispositivos no se puede modificar. Asegúrese de crear dispositivos con un tamaño suficientemente grande.
Usar múltiples discos o dispositivos de SQL Server
En la mayoría de los casos, el Asistente para upsizing a SQL Server proporciona un control más que suficiente sobre los dispositivos de SQL Server. No obstante, si el servidor tiene múltiples discos o si desea situar una base de datos o un registro en múltiples dispositivos, quizá desee crear dispositivos antes de ejecutar el Asistente para upsizing a SQL Server.
Servidores con múltiples discos físicos
Si su servidor tiene dos o más discos duros físicos, es posible que desee situar la base de datos en un disco y su registro en otro disco distinto. En caso de error del disco, tendrá más probabilidades de recuperar la base de datos si el registro y la base de datos están almacenados en distintos discos físicos.
El Asistente para upsizing a SQL Server le permite crear nuevos dispositivos, pero solamente en un disco físico: el mismo en el que está situado el dispositivo de base de datos master.
Para situar una base de datos y un registro en discos separados, asegúrese de que dispone en ambos discos de dispositivos con el tamaño suficiente, creando nuevos dispositivos en caso necesario. A continuación, ejecute el Asistente para upsizing a SQL Server.
Colocar bases de datos o registros en múltiples dispositivos
SQL Server permite que las bases de datos y los registros abarquen varios dispositivos. No obstante, el Asistente para upsizing a SQL Server solamente le permite especificar un dispositivo para la base de datos y un dispositivo para el registro.
Para especificar múltiples dispositivos para una base de datos o un registro, convierta dichos dispositivos (y ningún otro) en predeterminados. Posteriormente, ejecute el Asistente para upsizing a SQL Server y elija Predeterminado para el dispositivo de la base de datos o del registro.
Nota Si el tamaño de la nueva base de datos o del nuevo registro de SQL Server no necesita usar todos los dispositivos predeterminados, SQL Server solamente utilizará los dispositivos necesarios para alojar la base de datos o el registro.
Preparar el cliente
Antes de realizar el upsizing, debe tener acceso a SQL Server a través de un origen de datos ODBC o de una conexión con nombre. También necesita tener una base de datos de Visual FoxPro, de la que debe realizar una copia de seguridad antes de ejecutar el Asistente para upsizing a SQL Server.
Crear un origen de datos ODBC o una conexión con nombre
Al crear una nueva base de datos remota, se selecciona un origen de datos ODBC o una conexión con nombre en la base de datos de Visual FoxPro que tiene acceso al SQL Server al que quiere realizar el upsizing. Como no puede iniciar el Asistente para upsizing hasta que seleccione una conexión con nombre o un origen de datos, debe crear la conexión con nombre o el origen de datos adecuado antes de iniciar el proceso de upsizing.
Para obtener más información acerca de la creación de una conexión con nombre, Para crear un origen de datos ODBC, ejecute el Administrador de ODBC. Para obtener información acerca de cómo configurar orígenes de datos ODBC.
Copia de seguridad de la base de datos
Es conveniente crear una copia de seguridad de la base de datos (archivos .dbc, .dct y .dcx) antes de realizar el upsizing. Aunque el Asistente para upsizing a SQL Server no modifica los archivos .dbf, sí maneja el .dbc directamente abriéndolo como tabla a veces e indirectamente cambiando el nombre de las tablas y vistas al crear nuevas vistas remotas. Si realiza una copia de seguridad de la base de datos, podrá devolverla a su estado original previo al upsizing sobrescribiendo los archivos .dbc, .dct y .dcx modificados con las copias originales de la copia de seguridad, lo que invierte los cambios de nombre y la creación de vistas nuevas.
Cerrar tablas
El Asistente para upsizing a SQL Server intenta abrir de forma exclusiva todas las tablas de la base de datos cuyo upsizing se desea realizar. Si alguna de las tablas ya está abierta y compartida, el asistente la cierra y la vuelve a abrir de forma exclusiva. Al abrir las tablas de forma exclusiva antes del upsizing, se protege contra usuarios que intenten modificar los registros de las tablas que se van a exportar durante la exportación de datos. Si alguna tabla no se puede abrir de forma exclusiva, el Asistente para upsizing a SQL Server mostrará un mensaje y estas tablas no estarán disponibles para el upsizing.