1115 Invalid operation for the cursor. 3

[Steve Yu]

Hello,

Need help.
Got this error trying to run any Alter Table command (alter column for example) against the cursor which was created via SPT SqlExec().
But ONLY when the cursor was empty.
If I inserted several records first, then the same Alter Table command would work.
Very odd.

Steve Yu in Houston

 

[Scott24x7]

It's usually because the cursor is read only.
Since you didn't include any code with it, I can't say.

But check these points:
Check the properties of the cursor to ensure it's compatible with the operation you're trying to perform.
Ensure that the cursor is not read-only if you are trying to modify data.
If you're modifying the structure, make sure to do it when the cursor is not open.
Adjust the buffering mode of the cursor if necessary to suit your operations.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Mike Lewis]

Steve,

In general, I would always avoid using ALTER TABLE with a cursor. There are so many things that can go wrong. Just look at the "Remarks" section of the ALTER TABLE topic in the VFP Help, and you will see what I mean.

Here is one example:

ALTER TABLE might not produce consistent results when used with Visual FoxPro cursors created by the CREATE CURSOR command. In particular, you can create a Visual FoxPro cursor with features, such as long field names, that are normally available only with tables that are part of a database container. ALTER TABLE saves a temporary copy of the cursor, so the rules that apply to free tables also apply, and any features requiring database support are lost or changed in an unpredictable manner. Therefore, you should generally avoid using ALTER TABLE with Visual FoxPro cursors unless you have tested and understood the outcome.

(My italics.)

I would also question your reasons for wanting to alter the structure of an existing cursor. If the field specification that you need for your application is different from that of the back-end (for example, if there are differences in the data types or field widths), it wold be better to leave the SPT cursor as you find it, but then do a SELECT into a new cursor, performing any necessary conversions or other changes along the way.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Yu]

Scot,

Thanks for the suggestion.
It's not read only issue b/c I can insert and delete.
Can't close the SPT cursor for Alter Table command b/c the cursor will be gone.

Steve Yu

 

[Steve Yu]

Mike,

It's been quite a while since last you guys helped me with my VFP to SQL conversion, which has gone live for over one year now and is working fine.
Now you have done it again.
Sounds trivial first to me but the 10-character field name limit was the actual culprit in this case.
It never happened before was b/c all other SQL tables were converted from VFP to begin with while this trouble one was created within SQL Visual Studio and the developer (not me) uses long field names, therefore the crash.
As you guessed, the reason we performed Alter Table on SPT cursor was date type mismatch: from DateTime in SQL to Date in VFP (can't use Datetime in VFP unless we want to modify the untold number of lines of code in our software package made in the 1980's).
Can't do another Select on the SPT cursor to another cursor and then Alter Table either - the SPT cursor is massaged in old logic, then changes are posted back to the linked SQL table using the common unique ID field.
I sounds convoluted but let me assure you it has worked wonders for over one year for us; we get to keep the old character-based user interface with a powerful SQL engine inside; users (100+ real-time users in three cities) noticed the improved performance with minimum changes to their daily routine.
Thanks again.

Steve Yu

 

[Chris Miller]

Can't do another Select on the SPT cursor to another cursor and then Alter Table either

Why are you stating that, of course you can do a select, and then you don't need the alter table. You even have the option to change the SQL of the SQLEXEC to give you a date field in the first place with CONVERT() in T-SQL using this:

SQLEXEC(handle,'SELECT ...,CONVERT(DATE, fieldname) as fieldname FROM dbo.servrtable ...','resultcursor')

that's the way to convert datetime to date within the SQL Server dialect.

Or directly after the SQLEXEC you do

SELECT fieldlist, CAST(fieldname as date) as fieldname FROM sptcursor into cursor tempname.

And this is the way to use CAST in VFP sql to do the conversion after you have the cursor.

You surely will depend on the rest of the code acting on the original alias name, so either you select into the same cursor name or you change the name of the SQLEXEC curosr to something temporary and then finally SELECT INTO CURSOR with the alias name you need.

No problem either way. I wonder what you don't have in your own hands in all of this that you say what I quoted. Maybe you didn't understand Mikes suggestion when he said:

do a SELECT into a new cursor, performing any necessary conversions or other changes along the way.

Performing conversions or adding new fields is always possible with SELECT-SQL and expressions, you can add a new field with CAST(value as type) as fieldname or other expressions and you avoid the ALTER problem, because then you already altered the datatype by the query. "Along the way" is the key here, not afterwards.

It doesn't hurt a cursor to have long field names in itself, it just may lead to the ALTER TABLE not applying to it, but a cursor can have features a free table can't have, that's not the problem, so long field names within the SQL Server table are also not problematic unless you want to finally create a free table from the cursor, but I don't think you need that.

The major point is that you don't need ALTER TABLE at all, because the SQL query can do the field conversions already, also creating new field and even computing new fields like lastname+','+firstname as fullname. Very generally speaking, if you do a query anyway, to get a cursor, SQL is there to give you the result as you need it, not only by joining tables or using group by or other SQL features, also with type conversions. You may need to dig a bit deeper into features available in SQL.-

The rest of your code then can be running untouched, once you fixed the datetime to a date with either already doing the conversion when selecting from the SQL Server table within SQLExec or when you just do a SELECT on the result curosr of the SQLEXEC as described. You have everything in your hands, I think, even if you have to query SELECT * FROM sql_server_view and the view is unchangable to you, you can use a fieldlist including all fields unproblematic and then the CONVERT expression for the datetime field.

You're not bound to anything unless you keep using * as a fieldlist, which is a bad design anyway, even though it is so nicely short and has the aspect of selecting further fields, if the structure of the databasde table or view changes. If you will have a changed structure and use a new field, you have to modify code anyway, and at that time can also add a field to a fieldlist not using the asterisk.

Besides, you can easily get a fieldlist using SQL Server Management studio to create a generic SQL SELECT of a table or view. In the Object Explorer section (typically on the left side), expand the tables node and on the table of interest right click, then pick "Script Table as..." which pops out a secondary context menu where you can pick "...SELECT To" and finally a third popout menu offers "New query editor Window" or simply "clipboard". And that query can be edited with CAST or CONVERT and other expressions making the necessary alterations on the go while you query.

You can use the syntax of the query within the SQLEXEC as is, for example the square brackets around field names. If you want to use the field list for an aftermath SELECT as Mike suggested, you have to strip off the square brackets, of course. But that's also done with two search&replace. It's of course easier when already doing that in the SQL of the SQLEXEC call, I wonder why you wouldn't have that in your hand.

Chriss

 

[Mike Lewis]

Interestingly, the Help for Error 1115 says that it is caused by an attempt to pack the cursor, which is true. But while you can't PACK it, it is OK to ZAP it. I wonder why.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Yu]

Chris,

Here is my explanation why the following won't work for us:

SQLEXEC(handle,'SELECT ...,CONVERT(DATE, fieldname) as fieldname FROM dbo.servrtable ...','resultcursor')

The conversion is fine - change data type on the fly, the resultcursor will have the desired data type.
However in our case we needed a shared procedure that can generate a cursor from any SQL table that may or may not have a datetime field that needs to be converted.
Here is an example of one of the procedures in our tool box:

dc_sqlexec("select * from &m0sotranf ","c_b")

the xBase code it replaces is:

select b
use &mosotranf

where &m0sotranf = 'sotran01', and it could be any one of the tables in the SQL database.
The dc_sqlexec() will generate a SPT cursor first; then cycle through all fields and perform an "Alter Table .. Alter column" when it sees a DateTime field. It may sound awkward, but it's the only way our conversion project could have worked.

And we do use type conversions a lot in SPT cursor generation, example:

mfilter = dc_ctod('purdate') + " <= ?menddate and (puramt = paidamt+disamt+adjamt or apstat = 'V') "
dc_sqlexec("select * from &m0apmastf where &mfilter","c_a")

where dc_ctod() is another function in our tool box:

Function dc_ctod
Parameters cFieldname  &&  date as char(8) mm/dd/yy
Return "CAST(IIF(isdate(" + cFieldname + ") = 0,'01/01/1900'," + cFieldname + ") As Date)"

here the field 'purdate' in SQL table m0apmastf is c(8) - this was before Date type was added to dBase IV (?)
it is converted to Date type for the query
this function also takes care of compatibility issue (blank date OK in VFP but not in SQL)

We also use SQL inner join logic wherever appropriate to replace old-fashion 'set filter', 'set relation to' constructs.

Steve Yu

 

[Chris Miller]

Wow, how often will I see variants of bad SQL Server conversions with any flavor of Select * from table?

This is not only bad, it's the worst you can do for the application performance, as you always load all data of a table, no matter what part you actually need. Well, I notice you also use the variant with "where &mfilter". May be okay. Still, you better convert the whole data access logic and not just the USE commands.

In a Foxpro application, legacy or not, a table is used to do something with it and the conversion has to begin on the level of that, not on the level of USE, the USE or the DE of forms at best tell you which tables are involved in what the form does, no more, no less. It's a bad idea to just imitate the FoxPro mechanic with a database server.

It's also quite simple to solve the datetime->date conversion generally using SQLCOLUMNS() or store meta data about tables and which fields to query how more generally, not only about datetime->date, also about char->date. Besides SQLColumns() you also have system views of MSSQL like INFORMATION_SCHEMA.COLUMNS.

And you don't have to keep legacy debts of developers (no matter if other devs or yourself) by having dates in char(8) fields, do you? This is nonsense that needs to be addressed. Don't run around with a bag of problems that don't need to be kept. Why would you do that? Would you do that in real life with anything?

Chriss

 

[Steve Yu]

Chris,

Appreciated the critique and advise.
We were very practical in our approach to the conversion process. To start, we evaluated all available software packages for our industry in today's market: NetSuite, SAP, VFP-based AccountMate, and many others. But none of them offer features to handle more than 50% of our processing needs (customizations are either not possible or extremely costly) and all of them require complete re-training of users, another costly and time consuming task. And almost all of the packages require perpetual licenses that will be adjusted upward in time.
On the other hand, we have a tailor made system that has been working but running close to VFP limits (2 GB file size) and suffers occasionally data freezes. And a long overdue system upgrade was needed.
But we love the character-based user interface with the 80x25 screen display. It gets the job done quickly and efficiently.
Our conversion coding may seem awkward and non-conforming, and I agree, but the constructs had been carefully thought out and tested over and over. The main consideration is to minimize steps needed to convert literally tens of thousands line of legacy code. The debate over keeping the legacy code in the first place is a subject for another day.
As I mentioned earlier, we've gone live for over a year. Except for the initial couple of weeks during which we had to figure out and fix a couple of critical bugs, the system has been performing well. And we are continuing to add logic to handle new business requirements.
Again always appreciate the critique and advise from all of the gurus in this forum.

Steve Yu

 

[Chris Miller]

Nothing against that, but what does it mean technically? I don#t read anything into this, you could have dates in MSSQL, there's no need to continue having chars in that, why? Because the legacy frontend still works with them? What about converting dates into a char(8) value for that.

If you had to modernize the application logic in the area of data access, that was exactly in that area and then havng tha actual proper datatype you can alwayss make use of all the T-SQL date/time functions without first converting to date, instead you can deliver results in char(8), when that's absolutely what the frontend lives with and you want it to stay.

I bet there also is some code that is necessary for validation of wrong dates that you could get rid of, now you don't have the necessety to store dates as strings anymore, you're arguing with the wrong justifications.

Chriss

 

[Steve Yu]

Chris,

The date conversion was one of the toughest tasks we had to deal with.

you can alwayss make use of all the T-SQL date/time functions without first converting to date, instead you can deliver results in char(8),

The problem was we have both c(8) and Date data types in the legacy system that function as date.

For all practical purpose it would have been next to impossible to covert all c(8) date logic to proper date type, simply because of the number of lines of code involved. Long and extremely tedious manual work were already required without the date headache.
Our approach was awkward but it works with no noticeable performance degradation.

Steve Yu

 

[Gerrit Broekhuis]

Hi Steve,

I’m just curious what your user interface looks like, with the 80 x 25 screen.

Regards, Gerrit

 

[Chris Miller]

next to impossible to covert all c(8) date logic to proper date type

You're not listening to what I suggest. The main concern should be pure data storage. If the frontend needs a date as a char(8) string, you have conversion functions to do that. It takes next to nothing, because you can generate all that with the help of meta data.

tha major goal must be to store dates where data is a date, to at least have the correct storage at the backend. Toi be able to compare every datre with any other date without needing a conversion there. To create new apps or other frontends based on a clean well defined database.

What did I say:

there's no need to continue having chars in that, why? Because the legacy frontend still works with them? What about converting dates into a char(8) value for that?

You implicitly ansewered the 2nd question with yes. And my last question is including the answer to that need.

I also said

you can deliver results in char(8), when that's absolutely what the frontend lives with and you want it to stay.

Are you listening? Are we havng a translation problem here?



Chriss

 

[Steve Yu]

Gerrit,

Here is a sample of 80x25 menu:

Mostly keyboard input. Very old but efficient and gets the job done.

Steve Yu

 

[Steve Yu]

Chris,

Criticism well received. Here is my rebuttal:

you can deliver results in char(8), when that's absolutely what the frontend lives with and you want it to stay.

As I mentioned we have both c(8) and date types in our legacy system that work as date type.
If we were to store both types as Date in SQL as you suggested, then how do I instruct the SPT routine which field to return as c(8) or as Date so that we don't crash the legacy coding ?

Respectfully yours,

Steve Yu

 

[Chris Miller]

You can "store" that in views you define for the legacy system that know which fields the legacy system needs as c(8) or you store data about this as meta type data.

The legacy system code has been adopted to SQL Server, right? So you don't just have the system, you have the full code? And you did develop SPT routines. Then you can also adapt it to your needs.

It might not look like a big advantage, but once the data structure is cleaner and you also tackle bad design in terms of redundant storage of not normalized tables, you gain a data quality levels that will allow further improvements and new tiers of the data in the form of (web) apps, for example.

Chriss

 

[Gerrit Broekhuis]

Hi Steve,

Tnanks for the screenshot. It has been a long time since I saw this type of interface in VFP, but why break something that works?

Regards, Gerrit

 

[Mike Lewis]

I agree with Gerrit. The screen shot reminds me of some of the earliest PC systems I worked on - mainly in dBASE II, dBASE III Plus and MBASIC. We had addressable cursors in those days, but pointing devices were rare, hence the need to key the menu options in the way shown in Steve's screen.

This sort of interface worked by the standards of the day. At least, users rarely complained about not knowing which key to press.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

In 2014, I got called in to help a company with a system written in FoxPro 1 with this sort of menu. They were running it in a full version of VFP 6 with a shortcut that started VFP with a particular FXP.

The truly amazing part was that the original developer had died around 1992 and no one had touched anything in the interim. There were known issues that they worked around, but until the incident that led them to call me (which turned out to be a corrupted index), most of it just worked.

Tamar