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()

 

[Chris Miller]

You missunderstand the cache. It's internal, it doesn't mean you get 100 ids reserved, it just accelerates the NEXT VALUE FOR call.

You get one id, if you'd like to locally cache a contingent of reserved ids you'd need to set the increment higher.

Cycle: Before you reach maximimum it'll take a while, so don't worry about that. If you reach it, you can also decide to fail creating further ids, but by then the oldest session should not matter anymore, you can delete them, which makes way to reuse the old ids once more. So a cycling is quite normal. And you can decide to not cycle.

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.[/quote
Well, and that's a bad idea. Sequences are intended to create a sequence returning unique ids for each caller, ie calls are queued and fullfilled one after the other, nobody gets the same id. I don't know what makes you conclude this isn't the case, it would be helpful to cite things leading you to your conclusions. Just think about the intent of sequencess - why would MS design something half baked that does not work as intended?

Chriss

 

[Mike Lewis]

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

1541 is "Connection busy". We discussed how to deal with that at length in another thread. But that's not the only error code that's relevant here. More importantly, you might get a 1526, or some other server-related error code. Those are the VFP error numbers. To find out what actually went wrong, look in the third element of the error array. That will tell you the error code actually returned from the server. And with most servers (including SQL Server), the fifth element will contain the actual text of the message.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

OK, here is the screenshot with aerror() in place and as said before, nothing useful.

I'll do another test

Thanks, Stanley

 

[Mike Lewis]

Stanley,

So the error you are seeing is "connection busy". I wish we had known that at the start of this thread. (I know we discussed that error in thread184-1808576, but there was nothing in your original post in this thread to indicate that was the error.)

With that in mind, the explanation is simple. It is clearly a timing issue. Doing SET STEP ON before the update, you are single-stepping through the code, which obviously takes much longer than running the code normally - long enough for the connection handle to be no longer busy. That's not the case if you do it after the update.

(Actually, just running with the debugger open will slow it down, even if you are not actually single-stepping the code.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

OK, here is the entire form.init where the issue arises. Note that WITHOUT a "Set Step On" and only a breakpoint, the program run correctly. Starting the program without the debugger up (which ignores the breakpoint) it errors at the line: SQLExec(m.lnConn, "SELECT @@IDENTITY AS Ident", "csrTemp") with the "connection is busy error. No error when breakpoint is activated. I have no idea on what to do next to fix or troubleshoot.

Thanks, Stanley

Parameters pStatus
m.pcStatus = pStatus

With Thisform As Form
	Public m.gnMissed
	m.gnMissed = 0

	Select 'rv_Session'
	m.lnConn = CursorGetProp("ConnectHandle", "rv_Session")
	CursorSetProp("Buffering", 3, "rv_Session")

	Select Max(SessionId) As MaxSessionId From rv_session Where SystemId = ?m.gcSystemId Into Cursor 'curMaxCount'

	If Isnull(curMaxCount.MaxSessionId)
		m.gnSessionId = 1
	Else
		m.gnSessionId = curMaxCount.MaxSessionId + 1
	Endif

	Use In Select('curMaxCount')

	Select 'rv_Session'
	Append Blank
	Replace AppTitle With m.gcAppTitle
	Replace SystemId With m.gcSystemId
	Replace SessionId With m.gnSessionId
	Replace StartTime With Ttoc(m.gtStartTime,3)

	=Tableupdate(0, .T. , 'rv_Session')
	Requery('rv_Session')

	*lnConn = CursorGetProp("ConnectHandle", "rv_Session")

	SQLExec(m.lnConn, "SELECT @@IDENTITY AS Ident", "csrTemp")

	Public m.gnSessionPK
	m.gnSessionPK = csrTemp.Ident

	Use In Select('csrTemp')

	If Vartype(m.gcRunLevel) = 'U' .Or. m.gcRunLevel = "DEVELOPER"
		.txtWebUserId.Value = 'testuser@domain.com'
		.txtPassword.Value = 'myPassword4'
		.cmdLogon.Enabled = .T.
	Endif
Endwith

 

[Chris Miller]

Please stop using queries on views.

Doing something like SELECT Max(field) FROM viewname WHERE conditionA AND conditionB causes VFP to USE viewname first, without the additional WHERE clauses and VFPs SQL engine can't forward the WHERE, nor can it pass in eventually only needing MAX(sessionid). When you query a view, you're querying the query result of the view.



Chriss

 

[stanlyn]

But Chriss, I need the max number for the sessionid field for the group belonging to a systemid (location). Removing the where clause would get the largest number table-wide. I'm after the largest number for a location, which is a sub-set, which needs a where clause (query).

Apart from that, what is causing the "connection is busy" error? This is the form's init event and there is no load event. The views contains all data when created and attached to the form as nodata is not used. How would the select statement make the connection busy as it is flying by that with the correct data (max sessionid) for its group. All of that is working as expected with no errors.

How is best way to troubleshoot, cause this sort of stuff will surface again, I'm sure? I am aware of the limited error reporting from the odbc drivers. By simply invoking the breakpoints, no errors occurs as the title of this thread suggests. Someone suggested that "set step on" in code has some wierd behavior and asked that I use breakpoints instead, and here they are with exact same behavior. Here is another screenshot with execution suspended so varaibale can be viewed.

When you query a view, you're querying the query result of the view

Thanks for pointing out a where clause queries only the records the view pulled down when instantiated, as I was really unsure about that. But wait, how do you tell the view to get some records from a sql table when using nodata. I understand that instantiates the view with no data, which is useless if we can't query the view as you are asking that I do when you say "Please stop using queries on views.".

I'm actually trying to use a view much like a vfp table where most vfp commands will work. To do this, is there an acceptable way of creating the view with no selection, no ordering, and with nodata on? Wouldn't that work well as a view and allow selection, ordering and filtering of any field? Seems overly-complicated for me to build 10 specific views and attach them all to a form and swap them in and out based on the job at hand. I had rather construct the sql command on the fly with only one view for a needed table.

Any ideas on how to get past this?

Thanks so much,
Stanley

 

[stanlyn]

Chriss,

FROM viewname WHERE conditionA AND conditionB causes VFP to USE viewname first, without the additional WHERE clauses and VFPs SQL engine can't forward the WHERE, nor can it pass in eventually only needing MAX(sessionid).

Is there a reference somewhere that explains this in detail as I'm still unsure on how to interpret what you said is going on with the way I wrote it. I believe this has nothing to do with the issue I'm trying to understand and fix, but you raised some points that I need to understand. Understanding what the engine does and thinks, is so important.

Thanks again,
Stanley

 

[Chris Miller]

Stanlyn,

first, have you ever posted the query of rv_session, its view definition? I still assume you're fetching all data from the SQL Server sessions table, which is not at all what you need, neither to find the max(sessionid) nor to add a new session record. This is costly, in terms of network load and time. Possibly the reason the connection is busy.


SELECT FROM rv_session will either work on the already opened view, so it is by definition a query on the query result. If rv_session isn't open, VFP will see if there is a table or view of that name in the currently set database and USE that. Which means in case of a view it runs the view query.

So no matter if rv_sessions is already used before the query or not, you will at first query the server table and then query the VFP workarea. And this will not be done combined and optimized to give you the max(sessionid) you only need in the first place, it'll load all data and then query on the view workarea, even without having an index to optimize the max(). Bad idea.

Besides that, you can never be sure the maximum you determine is the actual maximum, that would also be true, if you directly query the server for the max() value. Just after that was retrieved any other user could have added is session. With a sequence you're getting the value exclusively, the next one getting an id gets a higher number, even before you actually used it to store it in rv_sessions and then the SQL Server sessions table.

It's the same pattern like using IsRLocked() to find out whether a record is locked. Even if it's .F. you can't be sure your RLOCK will get the lock.


Chriss

 

[Gerrit Broekhuis]

Stanlyn,

Have you tried the goto record(recno()) command? I couldn’t find any comment on that.
And what happens if you add a BROWSE?

Do the errors still occur?

Regards, Gerrit

 

[stanlyn]

Have you tried the goto record(recno()) command?

I just tried it again. I placed if right after to last replace command, then moved it to right after the tableupdate() command, and finally after the requery() command. I get the same "busy" error in all places.

I still assume you're fetching all data from the SQL Server sessions table

Yes, for now all records with no order set. Right now, I need to know why the error and how to make it process 1-1000 users with no "Busy" error. The performance is easy to fix. Also, I cannot see why that could be the reason (all records). I've said it over and over, adding a "set step on" causes it to pause for about 1/2 second at the requery() and all is well. If fetching all the records was the issue, then it would also be busy with the step on. How would we build it to wait for 1 user or 1000 users as the same mechanism needs to manage them. What does the aerror() within a try catch structure look like? I've also tried turning asynchronous on and off with same result.

If I move the requery() command out of the form's init and place it in a command button, the form loads and works as expected until it hits the requery() and here we go again with the busy error.

In a test within the command button, I do not have to requery() as I'm closing the table, re-opening it and selecting with this code. This is the extremely long way around the requery(). The newly created record from the form's init code is now in the view so the user specific fields can be updated.

USE IN SELECT('rv_Session')

USE 'dri-gateway!rv_Session' AGAIN IN 0

Select 'rv_Session'
*Requery('rv_Session')

Locate For rv_Session.PK == m.gnSessionPK

If Found('rv_Session')
Replace rv_Session.WebUserId With m.gcWebUserId
Replace rv_Session.Userid With m.gcUserId

I have also noticed 3 additional behaviors going on and suspect they are all related.

1. After removing the requery() and closing the form it shows the "busy" error as well and the debugger is returned to the line in the main.prg that calls the form.
2. issuing a "close tables all" command. This suggests something upstream is the culprit.
3. Issuing a sqldisconnect(0) also generated the same error.

Any other troubleshooting tips or tricks.

 

[stanlyn]

Chris,

Somehow I had missed your post better describing the sequence process on 19 Feb 21 08:10. Thanks for that and I will start using it, and yes there is a remote possibility that a duplicate could happen using my code.

While changing the way I get the max number to the sequence way, it still doesn't help in understanding this busy error and how to troubleshoot it. I just need to reliably and predictably do crud operations with a view.

Thanks,
Stanley

 

[stanlyn]

Why am I seeing a different name from what is wxpected in the aerror() output in elements 2 and 3. The name is "GLSystemId" and nowhere have I created or named this. The only place in code that this is used is here and to me (beginner in views) has nothing to do with the error.

Select Max(SessionId) As MaxSessionId From rv_session Where SystemId = ?m.gcSystemId Into Cursor 'curMaxCount'

So, if by some special magic it is coming from here, how can it be closed or talked to (not the resulting cursor)? I actually close the result cursor a few line down without issue.

 

[atlopes]

Stanley

"GCSYSTEMID" seems to be the name of the connection. What do you get when you issue the following command?

DISPLAY CONNECTIONS

Do you have any SQLCONNECT() or SQLSTRINGCONNECT() in your code? If so, can you paste the statements here verbatim (credentials hidden, if necessary)?

 

[mjcmkrsr]

Hi

"GCSYSTEMID" is a variable's name Stanlyn uses

Select 'rv_Session'
Append Blank
Replace AppTitle With m.gcAppTitle
Replace SystemId With m.gcSystemId
Replace SessionId With m.gnSessionId
Replace StartTime With Ttoc(m.gtStartTime,3)

hth

marK

 

[Chris Miller]

The connection is busy when it is still loading data, for example. Or other users use this connection. Depending on your server edition, concurrent connections are limited, connection poooling is used usually, theat can make the connection busy for others as well as yourself. When your're hving the only connection (in development, for example), you might have made it work asynchronous, which means you commit your new record while a previous request is still not finished fetching data. It could also be timeout.

The reaction to the busy error is waiting, then repeating, your query was rejected, not queued, so you have to retry.

But the underlying reason surely is making the SQL Server too busy. Don't query all data of some table. Only query the data you actually need. You have to design views, which only read as little data as possible. In case you know you want to use a view to create a new record use one with nodataonload or WHERE 1=0, or - what I often also use WITH idfield=?param. In that case, I might get the one record I want or no record and can react in the second case to add it. And if necessary design 2,5, 10, 100 views on a table or a set of related tables with all the queries your application needs, they are mainly just that, queries or more general code, so a view DBC you maintain belongs to your EXE and should be treated as code and installed local for each user. You can make it integral part of your EXE by creating the code for the view DBC and create or update it at application start, users temp folder is okay for that.

And then, when your view has loaded (with data or nodataonload) you can use APPEND or INSERT into the view, then TableUpdate(), look into the parameter options, they are crucial to know and I spare to rewrite what the help topic has on them. Never forget SET MULTILOCKS ON, not only for remote views but for table buffering in general. And use table buffering, which makes a lot of things easier.

Chriss

 

[stanlyn]

atlopes,

Do you have any SQLCONNECT() or SQLSTRINGCONNECT() in your code?

No, not for any of the remote view stuff.

I do have this function that I use for pass-thru, and not views.

*********************************************************************************************************
*CREATE SQL conection string to the local SQL DRI Database...
Procedure CreateConnection
	SQLSetprop(0, "ConnectTimeOut", 40)
	SQLSetprop(0, "IdleTimeout", 0)		&& minutes

	lcDSNLess = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=Yes"
	gnConnectionHandle = Sqlstringconnect((lcDSNLess), .T.)

	If gnConnectionHandle < 0
		Messagebox('Cannot connect to data source: ' + Left(lcDSNLess, 20) + '...', ;
			16, 'SQL Connection Error', 10000)
		Return
	Endif

	* SQLDisconnect(gnConnectionHandle)
	Return
Endproc

Mark is correct, GCSYSTEMID is a variable name and only a var. Somehow it is becoming a connection, or thinks its a connection. There are no references in code for this. Is there any conditions that would make it become one?

I've been studying the chapters about views in VFP client/server from Henzenwerke Publishing. Looks like I have a lot of changes to do. A question arose... Instead of making a pre-defined views via the gui and adding it to the form's DE, can I create the view (ad-hoc at design time) in the form's load event where it only lives with the form. When form is destroyed, so is the view? The book is talking a lot about creating the views in code. I also see that views must belong to a database. When creating a view with code, does the code 1. define the view and 2. add the view to the dbc? And why can't we build all the views as procs?

Thanks, Stanley

 

[stanlyn]

Hi all,

Thanks for all the valuable info received as I have learned a lot!

However not enough to get past the issue as stated in the title... This was never solved. After studying the Client Server book by Hentzenwerke and other references, most everyone seams to think cursor adapters are the way to go, and I should spend my time there as it is supposed to be much more flexible.

I originally chose to use the remote view approach as it could more easily produce browsable results without a ton of coding. I have again failed to get satisfactory and predictable results because of issues. I remember trying this stuff back many years ago, and probably (too long to remember) got the same unpredictable results, causing my abandonment.

So, I'll see you later in some cursoradapter threads.

Thanks again,
Stanley

 

[mjcmkrsr]

Hi,

A view (local or remote) belongs to a DATABASE. You may create it in code (LOAD or INIT of a form) and it is saved in the DBC. See sketch of code below .

LOCAL laViews[1]

Open Database (gcDrive + gcPath + "Subsidesscolaires.DBC")

ADBObjects(laViews,"VIEW")

IF ASCAN(laViews, "lvAllNames") > 0

WAIT WINDOW + "The required view already exists" TIMEOUT 3

ELSE

Create SQL View lvAllNames As ;
	Select tblNoms.Matricule, tblNoms.Fichier, tblNoms.Nom ;
		From tblNoms ;
	Union All Select tblPopulation.Matricule, tblPopulation.Fichier, tblPopulation.Nom ;
		From tblPopulation ;
	Union All Select tblStudents.Matricule, tblStudents.Fichier, tblStudents.Nom ;
		From tblStudents

ENDIF

In addition please find below what the Hacker's Guide to VFP 7 (T. Granor & al) says about views

Create SQL View, Delete View

These commands add and remove both local and remote views from a database. CREATE SQL VIEW is rather long-winded, but it must be, because CREATE VIEW is an old (useless) Xbase command.

Usage
CREATE SQL VIEW [ ViewAlias ] [ REMOTE ]
[ CONNECTION Connection [ SHARE ]
| CONNECTION DataSource ]
[ AS SQLSelect ]

Parameter
Value
Meaning

ViewAlias
Name
The name to assign the new view.

Omitted
If the AS clause is also omitted, open the View Designer for a new view. Otherwise, FoxPro prompts for a name for the new view.

Connection
Name
The name of an existing connection in the database that should be used to access remote data.

DataSource
Name
The name of an existing ODBC data source that should be used to access remote data.

SQLSelect
Included
Define the view to use the specified query.

Omitted (along with the AS keyword)
Open the View Designer for a new view.


This command has two modes. In one, it opens the View Designer so you can specify the view visually. In the other, it lets you define a view programmatically (and invisibly). There are so many other ways to open the View Designer, and CREATE SQL VIEW is such a long command (15 characters, counting embedded blanks) that we think its importance lies in programmatic use. Behind the scenes, you can specify either a local or remote view and have it added to the open database.

Omitting both the REMOTE keyword and the CONNECTION clause results in a local view. Including either gives you a remote view. You can specify CONNECTION without REMOTE and still keep it behind the scenes. If you specify REMOTE and don't indicate a connection, you'll be prompted to choose one.

In VFP 7, the connection information specified when you define a remote view can be overridden when you open the view; see Use for details.

SQLSelect can be any valid query. It doesn't get an INTO clause, though, because view data is put in a cursor automatically. Don't wrap the query in quotes. Strange as it seems to us, too, you explicitly issue the query as part of this command.

One warning on queries for remote views: Because they'll be interpreted by the server, make sure you use only standard SQL syntax and functions understood by the server, and not VFP's extensions or functions.

The View Designer actually does more than CREATE SQL VIEW. To specify the items on the Update Criteria tab of the View Designer, use DBSETPROP().

...

One of the really cool things about Visual FoxPro is the parameterized view. A parameter is simply a variable that belongs to the view. You can use it in the WHERE clause of the query that defines the view—just precede it with "?" to make it a parameter. If the variable exists when you USE (or REQUERY()) the view, its current value is substituted. If the variable doesn't exist, the user is prompted (with a pretty decent-looking dialog) to supply a value.

Coolest of all, if you change the parameter's value and REQUERY(), the view is re-created with the records that now match the condition. Parameterized views let you write a query once, but use it for a wide range of conditions.

Parameterized views can't be accessed from the VFP OLE DB provider, new in VFP 7. If you try to open a parameterized view through the provider, you get a "SQL column parameter name not found" error. Creating an ADO Parameter object with the proper name and value doesn't help. The reason for this problem is that the way VFP handles parameters is incompatible with the way OLE DB handles them. The only workaround is to issue the SQL SELECT command that the view uses as a query to the provider.

Example
CREATE SQL VIEW EmployeesByBirthDate ;
AS SELECT * FROM TasTrade!Employee ;
WHERE Birth_Date >= ?Birthdate

The example creates a parameterized view called EmployeesByBirthDate that chooses employees from TasTrade's employee table who were born on or after a specified date. When you open the view with USE EmployeesByBirthDate, if the variable Birthdate exists, its current value is substituted into the query. If it doesn't exist, a dialog appears, prompting for a value.

This one's really cool. When you use a parameterized view as the source for a control in a form (say, a grid or list), the parameter can be something like "ThisForm.SomeProperty." You can set up the view just as you want it on the form and not have to use a separate variable for the parameter. When you open the view outside the form, you get prompted for the parameter just as you do in any other case. This is the only place we know of in Visual FoxPro where you can use ThisForm notation in something other than method code or a property definition. Here's an example, letting you filter Employee on country.

CREATE SQL VIEW EmpsByCountry AS ;
SELECT First_Name,Last_Name FROM TasTrade!Employee ;
WHERE Country = ?ThisForm.Country

In the form that displays this view, you'd create a Country property and, perhaps, have a combo box with ThisForm.Country for a ControlSource. The combo's InteractiveChange method could REQUERY() the view and Refresh the form.

In VFP 7, the CREATE SQL VIEW command fires the database's BeforeCreateView and AfterCreateView events if database events are turned on.

...

hth

Mark

 

[Chris Miller]

OK, it's a pity nothing worked for you, but indeed views and dynamic WHERE clauses are a topic on their own. There is a way to use macro substitution in a view definition, so it does not act at the view creation but when later, each time the view is used. I also think views have some ease of use, but CAs offer new features, too.

In general, what's less practical in OOP is first needing the CREATEOBJECT() to then use the class, Well, apply the OOP idea and create just one class to rule them all, a factory, which for example could offer methods like factory.useca() and factory.doform() that only take a class name (just like procedural code only needs the view, table, or form name). These methods should then do the CREATEOBJECT() plus the Show() call for forms and the CA initialization, whatever you need for it.

Then your only CREATEOBJECT when starting VFP will be creating this factory object and then using its useca and doform methods.

It's all just a matter of using the OOP idea in itself, instead of complaining to now need two lines of code.

Chriss