Updating the base tables of a Parameterized View 1

[andyk]

Dear All,

I've got a parameterized view that I have created as follows:

create sql view as SELECT T_worklog.ip_work_lo,;
T_worklog.i_activity,;
T_worklog.i_bf,;
T_worklog.f_date,;
T_worklog.f_ncode,;
T_worklog.f_ndesign,;
T_worklog.f_ntest,;
T_worklog.f_ndocs,;
T_worklog.f_ninfoman,;
T_worklog.f_notes,;
T_activities.ip_activity,;
T_activities.f_name as v_act_name,;
T_bfs.ip_bf,;
T_bfs.f_abbrev as v_bf_abbrev;
FROM (worklog_db!t_worklog;
INNER JOIN worklog_db!t_activities;
ON T_worklog.i_activity = T_activities.ip_activity);
INNER JOIN worklog_db!t_bfs ;
ON T_worklog.i_bf = T_bfs.ip_bf ;
WHERE T_worklog.i_owner = cp_iOwner;
ORDER BY T_worklog.f_date


dbsetprop( "v_worklog.f_ncode", "field", "Updatable", .T.)
dbsetprop( "v_worklog.f_ndocs", "field", "Updatable", .T.)
dbsetprop( "v_worklog.f_ndesign", "field", "Updatable", .T.)
dbsetprop( "v_worklog.f_ntest", "field", "Updatable", .T.)
dbsetprop( "v_worklog.f_ninfoman", "field", "Updatable", .T.)
dbsetprop( "v_worklog.i_bf", "field", "Updatable", .T.)
dbsetprop( "v_worklog.i_activity", "field", "Updatable", .T.)
dbsetprop( "v_worklog.f_notes", "field", "Updatable", .T.)
dbsetprop( "v_worklog.f_date", "field", "Updatable", .T.)

dbsetprop( "v_worklog", "view", "SendUpdates", .T.)

dbsetprop( "v_worklog", "view", "tables", "worklog_db!t_worklog,worklog_db!t_activities,worklog_db!t_bfs&quot

dbsetprop( "v_worklog.f_ncode", "field", "UpdateName", "worklog_db!v_worklog.f_ncode&quot
dbsetprop( "v_worklog.f_ndesign", "field", "UpdateName", "worklog_db!v_worklog.f_ndesign&quot
dbsetprop( "v_worklog.f_ntest", "field", "UpdateName", "worklog_db!v_worklog.f_ntest&quot
dbsetprop( "v_worklog.f_ndocs", "field", "UpdateName", "worklog_db!v_worklog.f_ndocs&quot
dbsetprop( "v_worklog.f_ninfoman", "field", "UpdateName", "worklog_db!v_worklog.f_ninfoman&quot
dbsetprop( "v_worklog.f_date", "field", "UpdateName", "worklog_db!v_worklog.f_date&quot
dbsetprop( "v_worklog.i_bf", "field", "UpdateName", "worklog_db!v_worklog.i_bf&quot
dbsetprop( "v_worklog.i_activity", "field", "UpdateName", "worklog_db!v_worklog.i_activity&quot
dbsetprop( "v_worklog.f_notes", "field", "UpdateName", "worklog_db!v_worklog.f_notes&quot
dbsetprop( "v_worklog.ip_work_lo", "field", "UpdateName", "worklog_db!v_worklog.ip_work_lo&quot

It works fine for viewing the data, changing the value of the parameter cp_iOwner and issueing a Requery() produces the expected results.

However when I actually edit any values using the application or the browse window I get the following error:

No Update tables are specified. Use the tables properties of the cursor. (Erro 1491).

The output from Display Database is:

View v_worklog
*UpdateType 1
*WhereType 3
*FetchMemo TRUE
*ShareConnection FALSE
*SendUpdates TRUE
*Prepared FALSE
*CompareMemo TRUE
*FetchAsNeeded FALSE
*UseMemoSize 255
*FetchSize 100
*MaxRecords -1
*SourceType 1
*BatchUpdateCount1
*Tables worklog_db!t_worklog,worklog_db!t_activities,worklog_db!t_bfs
Field ip_work_lo
*KeyField FALSE
*Updatable TRUE
*UpdateName worklog_db!v_worklog.ip_work_lo
*DataType I

etc.

I've ommitted the SQL Property since it has been stated earlier.

Can you spot the problem??!!! I'm sure its ones of those blindingly obvious ones to the experienced eye, however it's driving me mad!


Many Thanks Andy.

 

[jonscott8]

Try CursorSetProp('Tables','MyTable1,MyTable2,MyTable3','MyView')

You may wanna take a look at the UpdatableFieldList & UpdateNameList properties of the CursorSetProp function. They might help you reduce the amount of lines of code by using comma delimited lists instead of setting each value independantly. Good day.

 

[foxdev]

When you learn the answer, Andy, I want to know, too.

I tried to develop a routine that built a remote view (yours is local, but the concepts are similar), then turn it into an updatable view - and I got the exact same error as you (the feature was important because updatable views must be stored in a DBC and must reference an existing named connection that includes user/password data, and this is undesirable).

 

[andyk]

Jon/Robert,

I tried setting the cursor property to no avail. When Robert said he had had the same problem well I knew I was in trouble!! Thanks for you rapid responses though - both within an hour or so, that's service for you.

I've got around the problem by only allowing edits within a set of edit boxes, rather than in the grid itself. The grid is purely read-only now. A bit naff but the interface isn't too bad.

Thanks Again. s-)

Andy.

 

[foxdev]

A bit naff

UK?

 

[andyk]

Sorry Rob, I'll keep it to American in future!

 

[TomasDill]

Guys, look to Programmer's guide for VFP. Don't be afraid about SQL server stuff.
Chapter 21: Implementing Client/Server Applications, 'Working with remote data using SQL Path-Through", than "Setting cursor properties for remote data".
It describes exactly what need to do to change cursor to be completely updatable, when it is created by EITHER SQL path-through query or local view query.
I think it will be very useful for Robert (FoxDev). Andyk may skip all that concerned to SQL servers, but many cursor properties listed in the table there may be used for local views too. [sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

Thank you for the reference, Volodymyr, but I did follow their examples (plus Hacker's Guide) and did exactly what they specified, but still I get the same error as AndyK - &quot;no update tables specified.&quot;

AndyK: I certainly wasn't complaining about &quot;naff&quot;; I've gone to school in Wales and thought that &quot;naff&quot; was Brit, but wasn't sure; I was just asking for verification. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

Ok, I'm going to make special custom control to work with SQL Server data using SQL Path Through functions. Will try it and send results here. Hope I will find a way to let it work. I cannot beleive that all that MS written in guide is not usable at all. [sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

Volodymyr, I certainly want it to work, because for one of the apps I'm writing, I don't want DBCs - but unless I can get my SQLEXEC() created cursors to be updatable, I'll be stuck with them. You have my permission to work overtime. Its probably already snowing in the Ukraine, so the weather's too nasty to go outside anyway. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

I'm really in Connecticut not at Ukraine, and will be here for 2 months, so don't worry about overhead and weather, it will not be a cause of bad results [sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[TomasDill]

I FOUND WHERE IS A PROBLEM.
Just in correct format of all properties. What I did is just took a look to a remote view's similar properties to investigate correct format of all these properties. Make remote view that is similar to cursor you want to have from SQL Exec function, make it updatable. Browse it. Than use cursorgetprop to see values of 5 properties, listed in the programmers guide (&quot;Updating Remote Data with SQL Pass-Through&quot. Note the format used by native VFP remote view. Than kill view (now forever! . Kill temporary database when you created it just for this test. Write your code in application after SQLExec command to set up properly 5 updating properties. Thats all.

Example of how to set properties properly for MS SQL server:
I use following SQL:

&quot;Select doc_id, cName, cType, tentered, tupdated, cEntBy, cUpdBy, cont_id, lPrivate, cOrigExt, convert(bit,0) lView from documents&quot;

Than, following commands work without errors:

<code>
select documents && suggest we already queried data into this cursor by SQLExec

&& set 5 updating properties
CURSORSETPROP('Tables', 'dbo.Documents')
CURSORSETPROP('UpdateNameList', ;
'doc_id dbo.Documents.doc_id, cname dbo.Documents.cName, ctype dbo.Documents.cType, ' + ;
'tentered dbo.Documents.tentered, tupdated dbo.Documents.tupdated, cEntBy dbo.Documents.cEntBy, cUpdBy dbo.Documents.cUpdBy, cont_id dbo.Documents.cont_id, lPrivate dbo.Documents.lPrivate, cOrigExt dbo.Documents.cOrigExt')
CURSORSETPROP('KeyFieldList','doc_id')
CURSORSETPROP('UpdatableFieldList', 'doc_id,cName,cType,tentered,tupdated,cEntBy,cUpdBy,cont_id,lPrivate,cOrigExt')
CURSORSETPROP('SendUpdates', .T.)

&& try it
CURSORSETPROP('buffering', 3)
go 2
repl cName with 'Whatever'
tableupdate(.T.,.T.)
</code>

It WORKS! I don't know however, why it required 'dbo.' before database name. It looks like it is depended on the SQL server.
Tried also editing in Browse window - it works too.

I suppose that all these properties may be set for local view too, but just need to know exactly format of all these settings to set up them properly.
[sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[TomasDill]

<i><color blue>I don't know however, why it required 'dbo.' before database name.</color></i>

OOps, sorry, it should be :
I don't know however, why it required 'dbo.' before <i>table</i> name.
[sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[jonscott8]

Volodymyr,
FWIW, the markup language used here uses [] with it's tags, unlike HTML's <>. [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>Carpe Diem! - Seize the Day![/sig]

 

[foxdev]

Thanks, Vlad - the addition of &quot;dbo.&quot; fixed it! My SPT-created cursor is now updatable.

I modified my generic &quot;cursor to remote view&quot; program to the following (I removed some error checking for the sake of brevity):

#DEFINE  K_Filter_IDs   .T.   && if .T., do not include Identity fields in UpdatableFieldList

lParameters tcCursorName, tcSourceTables, tcKeyFields

if parameters() <> 3    && clueless
	return .F.
endif

local lResult
lResult = CursorSetProp(&quot;Tables&quot;, iif(&quot;.&quot;$tcSourceTables, tcSourceTables, &quot;dbo.&quot; + tcSourceTables), tcCursorName)
assert lResult

cOrigNameList = CURSORGETPROP(&quot;UPDATENAMELIST&quot;, tcCursorName)
cOrigNameList2 = strtran(cOrigNameList, &quot;, &quot;, &quot;,&quot;)
cUpdateList = strtran(cOrigNameList2, &quot; &quot;, &quot; dbo.&quot; + tcSourceTables + &quot;.&quot;)
lResult = CursorSetProp(&quot;UpdateNameList&quot;, cUpdateList)
assert lResult

lResult = CursorSetProp(&quot;KeyFieldList&quot;, tcKeyFields, tcCursorName)

* -- build field list from cursor - assume they are all updatable
local aCursorFields, cFieldList
dimension aCursorFields(1)
cFieldList = &quot;&quot;
AFields(aCursorFields, tcCursorName)
for nCounter = 1 to alen(aCursorFields, 1)
	if K_Filter_IDs and nCounter=1 and upper(right(aCursorFields(nCounter,1), 2)) = &quot;NO&quot;
		loop
	endif
	cFieldList = cFieldList + iif(empty(cFieldList), &quot;&quot;, &quot;,&quot;) + aCursorFields(nCounter, 1)
endfor

lResult = CursorSetProp(&quot;UpdatableFieldList&quot;, cFieldList, tcCursorName)
assert lResult

lResult = CursorSetProp(&quot;SendUpdates&quot;, .T., tcCursorName)
assert lResult

[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

Robert, note not updatable field in my sample (lView). In your sample no way to handle such fields. Your routine might not work for cursor like I made, just because lView field cannot be updatable at all. Do you have any ideas how to track such fields in routines like your?
I'm very interested in such way of working with SQL Server too, but still problems exist, as you see... [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

In situations such as an internal application where there is someone to setup DSNs or copy DBCs locally, using named connections and persistent remote views are perfectly fine. They are not good, though, for external apps where you have no control over the security or configuration, and that is the project on which I work in my spare time (Thursdays between 9 and 10 pm).

From your sample code, Vlad, it looked like you were explicitly stating the properties for remote views that you had identified in advance. For problems such as you just mentioned above, that may be the only solution.

I was attempting a more generic approach, trying to create a program that would take any single-table remote view and make it updatable. It looks like, with your help, I have succeeded. It won't work with multi-table views, but I don't need that. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

I need generic approach too, but more generic to cover problem I mentioned Will try to find a way to detect such field that cannot be updated at all.
I suppose it should work for multi-table views. I see no limitations for this. Data table specified before each field for UPDATENAMELIST property. However, it is hard to determine which field from which table in generic routine. Will try to investigate this too.
Probably there is some way to do all this, because VFP does all this for remote views when creating them [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]