TableUpdate Succeeds If "Set Step On" is Before, Otherwise It Fails 1

[stanlyn]

Hi,

Can someone please explain why a tableupdate succeeds by putting "Set Step On" before it and fails if after. Operating with remote views with a property set to: CursorSetProp("Buffering", 3, "rv_Session")

If I turn on the dowhile loop it gets stuck in the loop indefinitely. Make no sense to me as the only thing I have done is move the debugger call before and after it.

With Set Step On BEFORE TABLEUPDATE()

AFTER TABLEUPDATE()

 

[mjcmkrsr]

Hi,

Set STEP ON suspends the program flow (see below: from Hacker's Guide to Visual Foxpro 7)

Usage

SET ECHO ON | OFF
cEchoMode = SET( "ECHO" )
SET STEP ON | OFF
cStepMode = SET( "STEP" )

In VFP 3 and earlier, SET ECHO ON opens the Trace window without pausing; in VFP 5 and later, it's ignored. SET STEP ON opens the Trace window and suspends execution of the program. SET ECHO OFF and SET STEP OFF don't do anything.

SET("ECHO") returns "ON" when the Trace window is open, and "OFF" otherwise. SET("STEP") returns "OFF" regardless of the last setting of STEP.

The relationship between SET STEP and the Trace window is a little strange. Issuing SET STEP ON, whether in a program or from the Command Window, opens Trace and gives it focus. If a program is running, it's suspended at the line following SET STEP ON. You can then use the menu to step through the code.

On the whole, it's better to open the Debugger, set some breakpoints, and run an unmodified program than to insert SET STEP ON (or SUSPEND) in a program.

hth

MarK

 

[Mike Lewis]

After TABLEUPDATE() returns .F., you should call AERROR() to determine the reason for the error. That might give you a clue as to the behaviour you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I second Mike,

it makes no sense to try TABLEUPDATE() again when it returns .F., because the reason usually is missing a mandatory field in a new record, or updating a record that was updated by another user in the meantime (causing a conflict) and not just timing or queueing/connection problems.

And when the force option didn't help, then the problem isn't a conflict only, but force doesn't mean forced success, when you break rules like referential integrity or violate uniqueness of a key the tableupdate will fail, also with lForce = .T. parameter.





Chriss

 

[Gerrit Broekhuis]

Stanlyn,

Not sure if it will work for you in this case, but please try

GOTO RECORD(RECNO())

Before your tableupdate command.
Somehow this refreshes in the background and may allow a proper database action.

Regards, Gerrit

 

[stanlyn]

First off, The use of my "set step on" before and then one later after (each in their own runs) was only for troubleshooting why tableupdate() was failing. Remember I said at full speed it fails and has NO "set step on" anywhere. Then I tried inserting a "wait 5" to induce a 5 second wait, just in case it is truly busy and I'm giving it 5 seconds to finish. Well that didn't work, so I created a dowhile loop where it can exit the loop whenever when successfull. Well that never happened after waiting minutes.

So pointing back to what Mark referenced in the Hackers Guide, what is it about putting the "set step on" before it makes the tableupdate() work, and taking it out altogether or placing it after it makes it fail?

Also note, that there are no other users, so contention SHOULD NOT be an issue here.

GOTO RECORD(RECNO())

I'll try that and report back.

because the reason usually is missing a mandatory field in a new record

I will verify that, but seriously I cannot see why it would apply here, as it would fail in all cases, regardless of where a set step was used or not. Remember it succeeds by putting a set step before it.

And when the force option didn't help, then the problem isn't a conflict only, but force doesn't mean forced success, when you break rules like referential integrity or violate uniqueness of a key the tableupdate will fail, also with lForce = .T. parameter.

Thanks, I'll need this info later I'm sure...

AERROR()

I looked and no useful info.

Thanks,
Stanley

 

[mjcmkrsr]

Hi,

Then I tried inserting a "wait 5" to induce a 5 second wait,

WAIT alone requires user input - usually ENTER or mouse click (please have a look at the help file). The correct syntax would be e.g.

WAIT WINDOW "... updating ..." TIMEOUT 5

hth

MarK

 

[Mike Lewis]

The point about GOTO RECORD(RECNO()) is that it forces movement of the record pointer. If row buffering is in force, moving the record pointer does an implicit commit of the buffer. That's a useful thing to remember, but I'm not sure that it's relevant in this case.

What we don't yet know is whether a genuine error was generated, and if so, what error it was. Stanley, you say that AERROR() produced no useful info. But exactly did it return? If TABLEUPDATE() returns .F., then AERROR() will create an array, the first cell of which contains the error number. Have you checked that?

We also don't why you are using SET STEP ON in the first place. Its aim is to help you track down bugs in your code. Why do you think there are bugs in the code? What errors are you trying to debug here?

AERROR() and SET STEP ON are quite different: one is not a substitute for the other. SET STEP ON is a development tool. AERROR() is meant to trap errors in production running. Whatever the solution to the current problem, you should always call AERROR() after TABLEUPDATE() returns .F.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

regardless of where a set step was used or not.

Oh no, a SET STEP ON can make a big difference, not just because it's a pause. Others already told you it moves focus. You know what happens when an active control loses focus: A value in that control gets stored into the workarea, and that can make the difference of succeeding or not. It's also the idea of GOTO RECNO().


Chriss

 

[mjcmkrsr]

Hi,

From the help file

You can use FOUND( ) to determine if a child table has a record that matches the parent record.

AFA I can read from your code that's not the case here. How do you search for "rv_Session" and are you sure that FOUND(...) returns .T.?

Btw I would prefer IF ALIAS() = UPPER("rv_Session")

hth

MarK

 

[Mike Lewis]

I would prefer IF ALIAS() = Upper("rv_Session")

Better still, [tt]REPLACE ... IN Rv_session[/tt] and [tt]TABLEUPDATE(0, .T., "rv_Session")[/tt].

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mike,

Better still, REPLACE ... IN Rv_session and TABLEUPDATE(0, .T., "rv_Session").

Of course, IF "rv_Session" exists - but since Stanlyn is searching for it, there is a chance that it does not exist or is closed

marK

 

[Mike Lewis]

but since Stanlyn is searching for it, there is a chance that it does not exist or is closed

I take your point, Mark, although my impression is that he is not searching for rv_Session, but searching for a record in rv_session.

But, yes, there is a chance that it does not exist or is closed. Wouldn't [tt]IF USED("rv_Session")[/tt] be the way to test for that?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

You see I'm not the only one having questions about the structure and what you do, beginning to end, not just part of the code.

How about starting over?

Say you have a table Sessions and want a new session, while purely using remote views instead of mixing data access methods.

Suppose the Session table has this structure:

SessionId (Integer Identity/PK)
SystemId  (Integer, FK into Systems table)
UserId (Integer, FK into Users table) 
SessionStartTime (datetime2 Default GetDate())
SessionEndTime (datetime2 Nullable)

Your application knows its SystemId and the UserId logged into it as properties of goApp, your application object. So those two column values come from your application and are in the updatable field list. SessionId is the PK and is generated by SQL Server.

Then you could define a view with the query

Select * From Sessions Where SystemId = ?m.goApp.nSystemId And UserId = ?m.goApp.nUserId And SessionEndTime Is Null

The updatable field list must be SystemId, UserId. The keyfieldlist is just SessionId.

I know you said only a combination of SessionId and SystemId is the PK, but then SessionId is Identity. You can stay with that and make it the PK, the SystemId doesn't need to be part of the PK at all, it may be mandatory because a session must be for a specific system, but that's a referential integrity constraint, not a PK constraint. When each SessionId is a number of an Identity sequence it's unique in itself already and sufficient for the PK, don't overcomplicate things.

To get all sessions of a specific system you can have a separate view without the UserId clause. And yes, it is okay to define many such views, you're not limited to one per table.
But that other view definition without UserId has nothing to do with starting a new session of a user, it may be of interest when there is a number of licenses usable concurrently, but not to check whether UserID X has a session or not.

Stop thinking the VFP way to USE a table and LOCATE the record of interest. Go straight to your goal with the query.

When you want to start a new session you set both SystemId and UserId to see whether there already is a running session (notice the view definition checks whether the SessionEndTime is NULL). You get the user's session record or an empty view cursor. If it's empty you can APPEND BLANK in the view and then TABLEUPDATE(0,.t.,'rv_sesssion'), then Requery() and you have your session record including its SessionId.

This you don't need @@IDENTITY nor SCOPE_IDENTITY(), as there can only be one active record for a user/system combination, with a NULL for the session end time and the UserId, SystemId combination.

So the session creation and/or reconnection to a running session could be done this way:

1. A User logs into your app, therefore you know goApp.nSystemID and goApp.nUserId
2. Looking for an already started session by the view with goApp.nUserId and goApp.nSystemId known.
3.1 Finding a record in the view cursor you are at your goal and have a session.

3.2 If - and only if - the view has no record, Insert a new record to it without setting SessionId at all, only set UserId, SystemId and even the Session StartTime will be Server time by its default value. You then TableUpdate(0,.t.,'rv_session') and Requery() to get your own SessionId.

Maybe set that in goApp.nSessionId in both cases 3.1 and 3.2

And then to end a session you can update the single record in your rv_sessions cursor with the end time and once more TABLEUPDATE(0,.T.,'rv_sessions') to set the SQL Server session record to its archived state by having its end time.

[bbox][/bbox]

You see you can solve things like getting the generated Id just defining tables in a way you have a single record of interest. With 1:n:m relations there always is one head, and even if you use a table like Sessions to also archive the past sessions you still have a way to filter only the active session by having an end session time, as you already have it.

So you thought about all the necessary aspects but just didn't think it down to the bottom line.

I know getting a new ID is still a topic of interest in the general case, it's simple in VFP to APPEND BLANK and have Sessions.SessionId in the current record, but SQL Server has no such concept.
So let's see how to get there, but I'll post that in your other thread, where @@IDENTITY and SCOPE_IDENTITY() is the more central topic.

 

[stanlyn]

PK (Integer Identity/PK/autoincrement)
SessionId (Integer)
SystemId  (Integer, FK into Systems table)
UserId (Integer, FK into Users table) 
SessionStartTime (datetime2 Default GetDate())
SessionEndTime (datetime2 Nullable)

Chriss, I added new auto-increment field to you table definition as well as changed Session to just an integer, and all else is same.

For every table discussed here, there are no parent/child relationships going on.

Every table in the database has a "PK" (Integer Identity/PK/autoincrement) field that is used only for quick access or to create parent / child relationships. SQL server only creates/writes this field when created and never changed afterwards.

There can duplicate Session.SessionID records and only unique when paired with Session.SystemID. The app that I'm building now is like a gateway for many different locations and has its own SessonID list as no new data is generated from it, instead it is acting as a gateway for all locations that generates data. Each location has a unique Session.SystemID and since each location started out with sessionId = to 1 and increments.

So to start the new logon Session creation process, the user open the app and is presented with a usernamee and password logon form. At this point we know where they are coming from, datetime, time zone, IP address, and several other metrics.
We do NOT know who the user is as they have not yet attempted to logon, but have landed on the logon page which is good enough to start a session, so that in the event the user decides to quit we have activity that can help us in making changes. This creates a new record in the Session table with only known data. If we have a high rate of quits, we need to improve that process. If a person does not have an account and repeatedly tries username and password guesses, we can block them for xx minutes. Also we can see if user (authorized or not) is attempting sql injection, and if evidence is found, we add their IP to our router's blacklist firewall rule. Note all of this and they are still not logged in.

Now a legitimate user starts the logon process by supplying their credentials. The system then searches the users table for username and password and if found access is provided. At this point, we now know who the user is, so we need to add all additional data know so far (username, userid, user role, etc). Currently steps 1 and 2 are so close together, all I do is update step 2 as it is really part of steps 1's session. In step 1, I save the result of @@identity to the goApp for use later on after all forms have been opened and closed, and prgs and functs have run. So when closing, I do a simple Session.PK = goApp.SessionPK and write the session end timestamp to et.

Hope that explains the process better.

Thanks,
Stanley

 

[stanlyn]

Hi Mike,

We also don't why you are using SET STEP ON in the first place. Its aim is to help you track down bugs in your code. Why do you think there are bugs in the code? What errors are you trying to debug here?

I put it there to find why it is failing. If I put a browse last after it, I see the record when it succeeds and no records when it fails. I also have SSMS open and looking at the sql data and it is there, yet taking all "set step on" commands out, found() is .F.

WAIT WINDOW "... updating ..." TIMEOUT 5

I will test your long way, but believe result is the same with as my simple throw-away "wait 5" as the long documented version, except I press enter instead of it running thru it automatically. Note, I do not use "wait x" in production code, only when dev and to replace a messagebox() to see values in cases where the ? does not work. I have found it to be the quickest way to see values outside the influence of "set step on" and works in all cases.

No matter what or how, the result of found() should be .T. in this case regardless of how the result is shown. That is what started this whole thread, why is found() = .T. in one pass and .F. in another pass on the same record? And I can see it via VFP's browse or SSMS. SSMS shows the record in all .T. or .F. cases for found().

More testing of everyone's suggestions and will post back. The electric has just been restored after a 3-day outage...

Thanks,
Stanley

 

[Rajesh Karunakaran]

Hi stanlyn,

I think as below
You're not mentioning the table which has to be updated in your TABLEUPDATE call. At that moment, the selected table/alias is something else by chance? Please try issuing a SELECT <your table name which has to be updated> before the TABLEUPDATE call.

Rajesh

 

[Mike Lewis]

Stanley, this is a bit confusing. You wrote:

taking all "set step on" commands out, found() is .F.

and

found() should be .T. in this case regardless of how the result is shown. That is what started this whole thread, why is found() = .T. in one pass and .F. in another pass on the same record

Are you perhaps misunderstanding the role of FOUND() here? In the code that you first posted, your test of llSuccess has nothing to do with FOUND(). It is a flag which indicates whether the table update succeeded or whether it returned an error.

As I (and others) said before: You need to call ARROR() after TABLEUPDATE() returns .F.. AERROR() is not an alternative to SET STEP ON. They serve completely different roles.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Thanks, stanlyn for clearly describing the process. I was wrongly assuming you start a session after the login.
But it's not essential whether you know the userid. As far as I see the systemid already is unique, isn't it? Or do all clients of a location use the same sysstemid?

Maybe look into my last post "Remote views and getting the PK after committing data."

Your case could be solved a little simpler.

Create a sequence for generating sesionids:

CREATE SEQUENCE [dbo].[sessionidsequence] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 2147483647
 CYCLE
 CACHE 100

A simple view (non-updatable) can retrieve the next id:

Create SQL View GetSessionId CONNNECTION yourconnectionname As SELECT NEXT VALUE FOR [dbo].[sessionidsequence] as sesionid

The rv_session view should be defined as

Select PK, SessionId, SystemId , UserId, SessionStartTime, SessionEndTime WHERE SessionId = ?m.pnSessionId

With PK as key, all fields except PK as updatable, SendUpdates, of course, etc.

To start a new session:

Set Multilocks On
Use GetSessionId In 0
pnSessionId = GetSessionId.SessionId

USE rv_session In 0 && No record has this new sessionid, so no record will be returned, but you have the updatable view to append a new record.
Select rv_session
CursorSetProp("Buffering",5)
APPEND BLANK  && set SessionId to pnSessionId and other fields as you need to.
* you could also INSERT INTO rv_session
CursorSetProp("KeyFieldList",'SessionId') && temporarily use SessionId as key.
TABLEUPDATE(0,.f.,'rv_session')
Refresh(1)
CursorSetProp("KeyFieldList",'PK')
? rv_session.PK && you will now have the PK (@@IDNETITY) for your new record. 
* any further operations on the view will now use the PK as key.

You could define multiple sequences, one for each location. Then set KeyFileldList to 'SystemId,SessionId' at first, and later to 'PK'.

Chriss

 

[stanlyn]

As far as I see the systemid already is unique, isn't it?

Yes and No, Yes in the sense that this version of the app is only acting as a gateway for web users whose identities are only web related and did not come from a location. Therefore the system id for the gateway is "WEB99" whereas each location is totally standalone and had no knowledge of "WEB99", however WEB99 does have knowledge of ALL of the locations.

I just reviewed docs on "create sequence" at

https://www.sqlservertutorial.net/s.....,for the sequence object. ... More items...

Thanks for that... I have never seen this command before and as I studied it, it appears the values are not persisted and not shared (other concurrent users). For example, the CACHE 100 command, I understand that it sets aside the next 100 values so a callback to the database can be avoided. Lets say user1 get values 100-199 set aside for them. Now, somewhere else in the world attempts a logon which call the exact code and delivers the cached values of what?, 200-299 or 101-200? or what. I do not see how user10's process can determine what sessionids that users1 -9 have consumed.

Also using the "cycle" argument suggests that it restarts all over from min_value, which clearly looks like an issue.

The approach I'm using will always consult with the session table by asking what the highest contained value is, then inserting a new record with the sessionid value incremented by one. SQL server will handle the assignment of PK. I then get the value of the PK in the very next @@identity call, store that in a global object and the rest of this session will use it when talking to the session table. I do not see any way to avoid this one additional call to get the pk using a sequence without duplicates (across different users) or gaps in the session id numbers.

And Yes, PK is not updated by SendUpdates.

You're not mentioning the table which has to be updated in your TABLEUPDATE call.

Yes, I did select the table a few line up in the code. If tableupdate() ing a table still requires its name within the tableupdate() command even though I have it already selected in the current work area, then I probably have it wrong. Its just not shown as not relevant to the error. If relevant, the same error would occur every time. Here is more code previously omitted.

m.gcUserLevel = Upper(Alltrim(rv_WebUser.role))
m.gcUserName = Upper(Alltrim(rv_WebUser.UserName))
m.gcPassword = Upper(Alltrim(rv_WebUser.Password))
*m.gcSystemId = Upper(Alltrim(Right(gcUserId, 5)))
m.pcStatus = 'ok'

Select 'rv_Session'
Requery()

Locate For rv_Session.PK == m.gnSessionPK
* .And. UPPER(ALLTRIM(rv_Session.SystemId)) = m.gcSystemId

*Locate For rv_Session.SessionId == m.gnSessionId ;
*	.And. UPPER(ALLTRIM(rv_Session.SystemId)) = m.gcSystemId
If Found('rv_Session')
Replace rv_Session.WebUserId With m.gcWebUserId
Replace rv_Session.Userid With m.gcUserId
Replace rv_Session.UserName With m.gcUserName
Replace rv_Session.UserLevel With m.gcUserLevel

m.llSucess = .F.
*Do While llSucess = .F.   &&
       	m.llSuccess = Tableupdate(0, .T.)
*Enddo

Set Step On
			
If m.llSuccess = .F.
	Wait 'no update'
Else
	Wait 'updated'
Endif


Use In Select('rv_Session')

I have been operating under the assumption that tableupdate() updates the currently selected table and using tableupdate(.T., .T., 'tablename') is for updating a table that is not currently selected. I will start using it for everything as several years ago I started adding the Alias name to anything where alias name was an option such as Used('tablename'), found('tablename') and etc... Is my thinking correct?

Are you perhaps misunderstanding the role of FOUND() here? In the code that you first posted, your test of llSuccess has nothing to do with FOUND(). It is a flag which indicates whether the table update succeeded or whether it returned an error.

As I (and others) said before: You need to call ARROR() after TABLEUPDATE() returns .F.. AERROR() is not an alternative to SET STEP ON. They serve completely different roles.

I use and have used found() for decades. Right before I use found() here I issued a locate statement in which found() should should know whether or not it was found, simple enough... But not here...

Also, aerror() is an array object containing the details of the last error and very useful in non-odbc error reporting in which its usefulness is very limited to a 1541 error number, which was already reported by the error dialog displaying "Busy Connection Error".

So, my adding aerror() after the tableupdate() and getting 1541, what do I do to resolve that?

Thanks, Stanley