Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: amro fathi
  Where is amro fathi?
 cairo
 Egypt
 amro fathi
 Tags
Subject: RE: Tableupdate does not work with my table
Thread ID: 104338 Message ID: 104349 # Views: 3 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, August 15, 2006 10:40:52 AM         
   


To make a SPT cursor to be updatable is a little tricky. It doesn't act like RV or CursorAdapter. That is why your TableUpdate doesn't works. Also it is always a good idea to check the result of your SPT commands:
lnMySQL = SQLSTRINGCONNECT("Driver={MySQL ODBC 3.51 Driver};"+;
 	"Server=my_server;"+;
 	"Port=3306;"+;
 	"Uid=my_id;"+;
 	"Pwd=my_pass;"+;
 	"Database=my_db;"+;
 	"Option=3",.t.)
IF lnMySQL < 0
   AERROR(laError)
   MessageBox([Can not connect to server ]+laError[1,2]
   RETURN
ENDIF
 
TEXT TO lcSQL NOSHOW PRETEXT 15
        create table item_tab (code varchar (15) not null default '',
                               name varchar (60) not null default '',
                               balan decimal (15, 3) not null default 0)
ENDTEXT
 
lcSq2 = 'Select * From item_tab'
lcCursor = 'myCursor'

lnExec = SQLExec(lnMySQL,lcSql)
IF lnExec < 0
   AERROR(laError)
   MessageBox([Can not create table ]+laError[1,2]
   RETURN
ENDIF

lnExec = SQLExec(lnMySQL,lcSq2,lcCursor)
IF lnExec < 0
   AERROR(laError)
   MessageBox([Can not get result set ]+laError[1,2]
   RETURN
ENDIF

append blank && insert does not work! Why? What happens? 
replace code WITH "150",name WITH "amro",balan WITH "10"
BROWSE NORMAL && Didi you have inserted record here?
IF NOT TABLEUPDATE(1,.t.,lcCursor)
   AERROR(laError)
   MessageBox([Error while updating table ]+laError[1,2]
   RETURN
ENDIF

The code above also will NOT update your remote table, but at least you will know WHY.


Here an article writen by Vlad Grynchyshyn How to make SQL Pass-Through cursor updatable
in FAQ section of UniversalThread forum:


Summary

The SQLEXEC() function in VFP allows downloading of data from any data source through ODBC. Unlike remote views, SQL Pass-Through approach does not provide default (automatic) settings in the returned cursor to update data on the server after changes in the cursor at the client side. Anyway, it is possible to customize required properties to organize updating.

First of all, it is recommended to read chapters about SQL Pass Through commands in the MSDN, specially, chapters that explain properties used in this article: Tables, KeyFieldList, UpdatableFieldList, UpdateNameList, SendUpdates. These five properties are essential in organizing updates to remote database.

This article is just a source code of the routine that makes SPT cursor updatable for MS SQL Server ODBC data source. It might require changes for other data sources because different format of the database, table and field specifications. Ina ddition, the sample is very simple and lack of processing such things as fields containing spaces and might contain certain problems. This is a sample only that you can use to improve. It is provided as is.

It is easy to see the format required for preparing these five properties to make cursor updatable. Create a simple temporary database, connection in it to data source, then create a view with required (or simplified) query. Open that view and use CursorGetProp() function to see mentioned five properties what is stored in them. This will give you a LOT of information about how you should prepare these properties for SQL Pass-Through cursor.

You can organize updating of data on the serevr any way you want. For example, query data from one table or view, and update data in another table. Map field from one field to another on the backend. Create your own custom field that is not returned by query, but pre-filled at the client side and used just to update data. Query data using very complex SELECT statement, sequence of SELECT statements, T-SQL script or stored procedure, then update data from client side (this is completely impossible for remote views, but possible for SQL Pass-Through).


Description


* convert SQL Path Through cursor or remote view cursot to updatable and
* fetchable cursor.
* parameters: cursor name (required)
* ID field name(s) (required)
* source table name(s) on SQL server (required
* comma-separated list of non-updatable OR non-fetchable fields,
* expression fields (optional)
* logical .T. if ID fields should be updatable too. (optional)
* NOTE - to use this routine properly, all fields in cursor should match
*        fields on server.
* NOTE2 - you may manually specify "UpdateNameList" to match cursor field
*         with server fields.
*   This list must be in following format:
*   {cursor field} {server table}.{server field}[, ...]
*   note spaces after ','.
lparameters pcCursorName, pcIDFieldName, pcSourceTables, pcNONUpdatableFields,;
 plUpdateIDField

if cursorgetprop('SourceType', m.pcCursorName)#2
   * cursor not compartible
   return .F.
endif
if !empty(cursorgetprop("Tables",m.pcCursorName))
   * already updatable
   return .T.
endif

m.pcSourceTables = chrtran(m.pcSourceTables, ' ', '')
local llResult, lnFCount, lcFieldsList, lcField, lcKeyField, m.lcUPDFieldsList, lcNewList
m.llResult = CursorSetProp("Tables", ;
 iif("." $ m.pcSourceTables, m.pcSourceTables, ;
 substr( STRTRAN( ',' + m.pcSourceTables, ',', ',dbo.'), 2) ), m.pcCursorName)

if m.llResult
  && prepare strings for exact substring search
   if !(vartype(m.pcNONUpdatableFields) == "C")
      m.pcNONUpdatableFields = ""
   endif
   if !empty(m.pcNONUpdatableFields)
      m.pcNONUpdatableFields = ',' + upper(chrtran(m.pcNONUpdatableFields,' ','')) + ','
   endif
   m.pcIDFieldName = chrtran(m.pcIDFieldName,' ','')
   m.lcKeyField = ',' + upper(m.pcIDFieldName) + ','

   if ',' $ m.pcSourceTables
      m.pcSourceTables = left(m.pcSourceTables,at(',',m.pcSourceTables)-1)
   endif
   if '.' $ m.pcSourceTables
      m.pcSourceTables = substr(m.pcSourceTables, at('.',m.pcSourceTables)+1)
   endif

   m.lcFieldsList = '' && fields for UPDATENAMELIST
   m.lcUPDFieldsList = '' && fields for UPDATABLEFIELDLIST
   for m.lnFCount = 1 to FCOUNT(m.pcCursorName)
      m.lcField = FIELD(m.lnFCount, m.pcCursorName)
      if !(',' + m.lcField + ',' $ m.pcNONUpdatableFields)
         m.lcFieldsList = m.lcFieldsList + ',' + m.lcField + ' dbo.' +;
          m.pcSourceTables + '.' + m.lcField
         if !(',' + m.lcField + ',' $ m.lcKeyField)
            m.lcUPDFieldsList = m.lcUPDFieldsList + ',' + m.lcField
         endif
      endif
   endfor

   m.lcNewList = cursorgetprop("UpdateNameList", m.pcCursorName)
   if !empty(m.lcNewList) and '.' $ m.lcNewList
      m.lcNewList = STRTRAN(m.lcNewList, ', ', ',')
      m.lcNewList = STRTRAN(m.lcNewList, ' ', ' dbo.')
      m.llResult = CursorSetProp("UpdateNameList", ;
      STRTRAN(m.lcNewList, ',', ', '), ;
      m.pcCursorName)
      else
      m.llResult = CursorSetProp("UpdateNameList", substr(m.lcFieldsList, 2),;
       m.pcCursorName)
   endif
   if m.llResult
      m.llResult = CursorSetProp("KeyFieldList", m.pcIDFieldName, m.pcCursorName)
      if m.llResult
         m.llResult = CursorSetProp("UpdatableFieldList",;
          substr(m.lcUPDFieldsList,2), m.pcCursorName)
         if m.llResult
            m.llResult = CursorSetProp("SendUpdates", .T., m.pcCursorName)
         endif
      endif
   endif
endif
return m.llResult


Created by Vlad Grynchyshyn


Borislav Borissov



ENTIRE THREAD

Tableupdate does not work with my table Posted by amro fathi @ 8/15/2006 9:28:25 AM
RE: Tableupdate does not work with my table Posted by Rey Magdalaga @ 8/15/2006 9:42:48 AM
RE: Tableupdate does not work with my table Posted by amro fathi @ 8/15/2006 12:26:12 PM
RE: Tableupdate does not work with my table Posted by Borislav Borissov @ 8/15/2006 10:40:52 AM
RE: Tableupdate does not work with my table Posted by amro fathi @ 8/15/2006 12:27:52 PM