DOCUMENT:Q113752 24-OCT-2000 [foxpro] TITLE :HOWTO: Create a Table Using FoxPro Connectivity Kit PRODUCT :Microsoft FoxPro PROD/VER::2.5x,2.6x,3.0 OPER/SYS: KEYWORDS:kbcode ====================================================================== ------------------------------------------------------------------------------- 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 2000.