KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q113752: HOWTO: Create a Table Using FoxPro Connectivity Kit

Article: Q113752
Product(s): Microsoft FoxPro
Version(s): 2.5x,2.6x,3.0
Operating System(s): 
Keyword(s): kbcode
Last Modified: 24-OCT-2000

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual FoxPro for Windows, version 3.0 
- Microsoft FoxPro for Windows, versions 2.5x, 2.6x 
- Microsoft FoxPro for MS-DOS, versions 2.5x, 2.6x 
-------------------------------------------------------------------------------

SUMMARY
=======

By using the FoxPro Connectivity Kit's DBExec() command, you can create a table
on the native platform that you are connecting to.

MORE INFORMATION
================

Using the standard SQL CREATE TABLE command with the Connectivity Kit's DBExec()
command, you can create a new table on the native platform. "Native platform"
refers to the DBMS that you are connecting to. In the example below, the source
is SQL Server and the code example creates a table called testtab in the pubs
database on the SQL Server machine.

With minor modifications, this same code can be used to create a new table called
testtab on an Oracle server or on any other data source that may be in use.

Code Sample for FoxPro 2.x
--------------------------

     *****SET THE LIBRARY AND INITIALIZE VARS
     IF _DOS
        SET LIBRARY TO SYS(2004)+"fpsql.plb"
     ELSE
        SET LIBRARY TO SYS(2004)+"fpsql.fll"
     ENDIF

     PUBLIC errval
     PUBLIC errmsg
     PUBLIC handle
     PUBLIC do_more

     errval=0
     errmsg=' '
     do_more=.T.

     *****SOURCE INFORMATION
     sourcename= 'test'
     user= 'sa'
     passwd=''

     *****CONNECT

     handle=DBConnect(sourcename,user,passwd)
     IF handle > 0
        WAIT WINDOW 'Successfully Connected'
     ELSE
        error=DBError(0,@errmsg,@errval)
        WAIT WINDOW STR(error)+' '+STR(errval)+' '+errmsg
        do_more=.F.
     ENDIF

     IF do_more=.T.
        *****SET SESSION DEFAULTS

        =DBSetOpt(handle,'Asynchronous',0)
        =DBSetOpt(handle,'BatchMode',1)
        =DBSetOpt(handle,'ConnTimeout',0)
        =DBSetOpt(handle,'Transact',1)
        =DBSetOpt(handle,'UseTable',0)

        *****USE THE PUBS DATABASE ON SQL SERVER; On SQL Server
        *****it is necessary to open an existing database (pubs)
        *****in order to create a table.  In other words, databases
        *****on SQL Server contain tables and we are creating a table
        *****that will be part of the pubs database.

        err=DBExec(handle,'use pubs')
        DO errhand WITH err,'USE PUBS'

        *****CREATE_IT--CREATES TESTTAB TABLE ON SQL SERVER
        *****IN THE PUBS DATABASE.

        DO create_it

        *****DISCONNECT

        err=DBDisconn(handle)
        DO errhand with err,"DBDisconn()"
        SET LIBRARY TO
        CLOSE ALL
     ENDIF

     *****Simple Error Handler Program
     PROCEDURE errhand
     PARAMETERS err,command
        IF err > 0
           WAIT WINDOW ALLTRIM(UPPER(command))+" Completed Successfully"
        ELSE
           ? UPPER(ALLTRIM(command))+" NOT Completed Successfully"
           error=DBError(handle,@errmsg,@errval)
           ? ALLTRIM(STR(error))+" "+ALLTRIM(STR(errval))+" " ;
             +ALLTRIM(errmsg)
        ENDIF
     RETURN

     *****THE DBExec() FUNCTION IS USED TO CREATE A TABLE
     PROCEDURE create_it
        createcomm= "CREATE TABLE testtab "+ ;
           "(ID char(4), NAME char(45), STATE char(2), ZIP char(10))"
        err=DBExec(handle,createcomm)
        DO errhand WITH err,"DBExec(handle,"+createcomm+")"
        IF err > 0
           WAIT WINDOW 'THE CREATE Command Worked.'
        ENDIF

     RETURN
     * End of Code

Code Sample for Visual FoxPro
-----------------------------

     *****SET THE LIBRARY AND INITIALIZE VARS
     PUBLIC errval
     PUBLIC errmsg
     PUBLIC handle
     PUBLIC do_more

     errval=0
     errmsg=' '
     do_more=.T.

     *****SOURCE INFORMATION
     sourcename= 'test'
     user= 'sa'
     passwd=''

     *****CONNECT

     handle=sqlConnect(sourcename,user,passwd)
     IF handle > 0
        WAIT WINDOW 'Successfully Connected'
     ELSE
        if !(type('aTemp')="U")
        release aTemp
        endif
        aerror(aTemp)
        errval=aTemp(1,1)
        errmsg=aTemp(1,2)
        WAIT WINDOW STR(errval)+': '+errmsg
        do_more=.F.
     ENDIF

     IF do_more=.T.

        *****USE THE PUBS DATABASE ON SQL SERVER; On SQL Server
        *****it is necessary to open an existing database (pubs)
        *****in order to create a table.  In other words, databases
        *****on SQL Server contain tables and we are creating a table
        *****that will be part of the pubs database.

        err=sqlexec(handle,'use pubs')
        DO errhand WITH err,'USE PUBS'

        *****CREATE_IT--CREATES TESTTAB TABLE ON SQL SERVER
        *****IN THE PUBS DATABASE.

        DO create_it

        *****DISCONNECT

        err=SQLDisconnect(handle)
        DO errhand with err,"SQLDisconnect()"
        CLOSE ALL
     ENDIF

     *****Simple Error Handler Program
     PROCEDURE errhand
     PARAMETERS err,command
        IF err > 0
           WAIT WINDOW ALLTRIM(UPPER(command))+" Completed Successfully"
        ELSE
           ? UPPER(ALLTRIM(command))+" NOT Completed Successfully"
  *         error=SQLError(handle,@errmsg,@errval)
  *         ? ALLTRIM(STR(error))+" "+ALLTRIM(STR(errval))+" " ;
  *           +ALLTRIM(errmsg)

        if !(type('aTemp')="U")
        release aTemp
        endif
        aerror(aTemp)
        errval=aTemp(1,1)
        errmsg=aTemp(1,2)
        WAIT WINDOW STR(errval)+': '+errmsg
        do_more=.F.
       ENDIF
     RETURN

     *****THE SQLExec() FUNCTION IS USED TO CREATE A TABLE
     PROCEDURE create_it
        createcomm= "CREATE TABLE testtab "+ ;
           "(ID char(4), NAME char(45), STATE char(2), ZIP char(10))"
        err=SQLExec(handle,createcomm)
        DO errhand WITH err,"SQLExec(handle,"+createcomm+")"
        IF err > 0
           WAIT WINDOW 'The CREATE Command Worked.'
        ENDIF

     RETURN
     * End of Code

Additional query words: VFoxWin FoxDos FoxWin 2.50 2.50a 2.50b 2.60 ODBC CK

======================================================================
Keywords          : kbcode 
Technology        : kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbVFP300
Version           : :2.5x,2.6x,3.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.