How to Insert Records in SQL Table Using Connectivity Kit

• Microsoft FoxPro Connectivity Kit, version 2.5
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, and 2.6
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, and 2.6

SUMMARY

By using the FoxPro Connectivity Kit's DBExec() command, you can insert records in a native platform's database that you are connecting to.

MORE INFORMATION

Using the standard SQL INSERT command with the Connectivity Kit's DBExec() command, you can insert records into a table that exists 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 inserts a record in the sales table in the pubs database.

With minor modifications, this same code can be used to insert a new record in an Oracle database or in any other data source that may be in use.

NOTE: If you have problems running the following code in FoxPro for MS-DOS, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q112482
   TITLE     : How to Make an MS-DOS Level SQL Server Connection

Code Sample

 *****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 the pubs database
      *****in order to insert a record into the sales table that is
      *****in the pubs database.  In other words, databases
      *****on SQL Server contain tables and we are inserting
      *****into a table that is part of the pubs database.

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

      *****INSERT_IT-- THE PROCEDURE THAT INSERTS RECORDS INTO
      *****THE SALES TABLE.

      DO insert_it

      *****Displays the change made after the insert is completed.

      sqlcomm= 'select * from sales'
      err=DBExec(handle,sqlcomm)
      DO errhand WITH err,"DBExec(handle,"+sqlcomm+")"

      IF err > 0
         WAIT WINDOW 'PRESS ESC TO CLEAR BROWSE WINDOW AND TO DISCONNECT'
         BROWSE
      ENDIF

      *****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 INSERT A RECORD

   PROCEDURE insert_it

      m.stor_id="0000"
      m.ord_num="QA7442.3"
      m.date="09/13/85"
      m.qty="75"
      m.payterms="Pauls Play"
      m.title_id="PS2091"

      insertcomm= "insert sales ;
                values ("+"'"+ALLTRIM(m.stor_id)+"',"+ ;
                          "'"+ALLTRIM(m.ord_num)+"',"+;
                          "'"+ALLTRIM(m.date)+"',"+;
                          + ALLTRIM(m.qty)+","+;
                          "'"+ALLTRIM(m.payterms)+"',"+;
                          "'"+ALLTRIM(m.title_id)+"'"+")"

      err=DBExec(handle,insertcomm)
      DO errhand WITH err,"DBExec(handle,"+insertcomm+")"
      IF err > 0
         WAIT WINDOW 'Insert went ok'
      ENDIF

   RETURN
   * End of Code