Creating CursorAdapter Completely in Code? 1

[stanlyn]

Hi,

What is the best way of creating a cursor adapter for use with a form, report, or a .prg in code other than hacking the .srx?

Can it be placed in a .prg file so that it can be easily edited by reading variables.

Currently, it seems too hardcoded, as either I have to open the form in the gui, and edit the adapters property sheet, or run the builder, or hack the .scx file.

What is standard practice?

Thanks, Stanley

 

[stanlyn]

Olaf,

>> It prevents sql injection. For example you can't break a string by expanding with a user input including a ', ending a string.

1. I get that, but was wondering how do I troubleshoot on my end to see what is really happening, instead of just trusting.


>> no matter if you query 'KY123', 'KZ987' or anything else, the query is static and if SQL Server is clever enough it reuses the previous compilation of the same SQL.

2. If it used the "previous compilation of the same SQL", are you saying that it would or would not change the query to reflect the new and current variable that was sent? If the previous query had 'KY123', would it be replaced with 'KZ987' on the subsequent query if that was the value of the variable?

Thanks,
Stanley

 

[Olaf Doschke]

1. You can check, can't you? Besides the query text you can inspect the variables.

2. No, thery QUERY is the same, the VALUES differ, of course the new values are sent. But SQL Server can resuse the same code, as the query did not change.


Think about it in a seperation of code and data:

You wirte a prg:
? 'hello'
and compile to an fxp. Calling it will aways display 'hello'
If you want to change to some other value you need to rewrite the prg and recompile.

You write another prg:
? yourvar
and compile to an fxp. Calling it without predefining a global or private variable yourvar will error, but when you set the variable and call the fxp it displays that value. You can now change the variable value in between calls and the fxp will display that value. Without being recompiled, correct?

The same goes for such SQL queries.

You can force that to be done, using SQLPREPARE, in SQL Passthrough. I assume SQL does also do this on it's own without being told, if it detects the same query.

Bye, Olaf.

 

[stanlyn]

Olaf,

>> You can check, can't you? Besides the query text you can inspect the variables.

1. Yes I can only see the SelectCmd and the varaibles values, seperately. My question arose when I was passing different variable values and the results was not changing. I wanted to see the actual SelectCmd string just to make sure the query string was different. You are saying that we cannot see that and therefore we can only trust that it gets done server side.

2. I was thinking that I read somewhere where I can change and/or totally reconstruct the SelectCmd string just before passing it to the server. I do know that I will be needing to do this in the near future, where the string will need to be completely written based on options that the user selects,

EG. Lets say that user wants to search only a single county, the string would need to be something like:
"select * from page where .............. and county_id = ?County"

Now, if they wanted to search all counties, the command would need to be something like this:
"select * from page where .............." dropping the county_id selector component.

I know that I will need to construct many sql commands, one each for each possible senerio based on what the customer wants to see.

3. Am I going to run into a problem doing this when I need to a little later, because of using the CA style, or would SPT be best. If CA supports this, I'll stay with it. Previously I have quite a bit of SPT experience from VFP and I know that there is no problems with that, its that for most queries, the CA should save a lot of time, where SPT requires me to handle everything manually.


Thanks,
Stanley

 

[Olaf Doschke]

1. Yes, you can trust

2. No, there is no such thing, as the query isn't resolved and expanded by foxpro, it leaves foxpro and arrives SQL Server with the ? syntax for parameter. If you could see the expanded sql, then in a lo by SQL Server, or indirectly in it's transaction log, besides in the tables, you will see data added, changed etc.

3. If you mean SQLPREPARE, CA has no such method, but you can call parameterized (table valued) stored procedures instead of queries and get back their result, stored procs are compiled by default.

I do rather not use stored procs, as it moves too much into the server layer for my taste, and also is more rigid in it's where clause, for example, but it's not unusual o have many (hundreds) of stored procs instead. PArtly because you would create multiple TVFs for different where clauses or subqueries.

The query optimiser is very good and will also optimise individual queries sent by CA.

Don't go back or over to SPT, just for that single advantage.

Bye, Olaf.

 

[stanlyn]

Olaf,

Can this type of functionality be done with the CA stuff?

thisform.de_class.ca_class.SelectSQL = "select * from table"
cursorfill()

and then on the fly change it to:

thisform.de_class.ca_class.SelectSQL = "select * from table where color = 'blue'"
cursorfill()

Say yes to this...

Thanks,
Stanley

 

[Olaf Doschke]

yes, but once select * from table runs changing selectcmd doesn't change the result, you have to change sqlcmd before cursorfill().

Again, yes, I even proposed to see CA as a view having a static resultset schema, but not a static sql, even not a parameterized, but you can change SelectCmd any way you like, before doing either cursorfill() or cursorrefresh().

So you can start with "select * from table where 1=0", then cursorfill(), get an empty cursor you can bind to a grid already, then let a user filter for color = 'blue', change SelectCmd to "select * from table where color = 'blue'" and then cursorrefresh().

An easy way to do so is to introduce a property cWhere in your base CA class, and both in BeforeCursorFill() and BeforeCursorRefresh() you can call a third method BeforeQuery() with code putting togehter a query body with the current cWhere clause and setting SelectCmd, then Return .T. to signal GO to the real deal CursorFill() or CursorRefresh().

That's the beauty of the CA you have events firing in which you can act. If you want to change a view SQL, you can only do so by creating a new view on the fly with the new SQL. You can of course also set SelectCmd completely from outside, it does not need to be done in the Before... events, it's just a possibility to program that once in a base class and reuse the code. OOP. Welcome to OOP.

Bye, Olaf.

 

[Olaf Doschke]

Oh, and if you mean to filter the result you already got from the initial SELECT * FROM Table, you rather SET FILTER TO on the cursor, because why requery what you already have retrieved?

The idea really is to NOT query all data as the start and then filter, but to first ask a filter or apply a default filter (last 3 mohts data or wheatever) and then query. This is really the essential differnce in thinking. You have a repository of data. You only laod from it, what you need.

You also don't take out all your plates from the cupboard just to decide, you want to go with a blue plate today, you decide before you take out the plate.

This is the thing you need to beat VFP veterans to, to get rid of that bad habit. You're too used to foxpro thinking for you, if you just USE a table and can access all table data.

Bye, Olaf.

 

[stanlyn]

>> yes, but once select * from table runs changing selectcmd doesn't change the result

My understanding from what I read elsewhere is that you can change the SelectCmd when running, and to get different results based on the new SelectCmd command I need to do a new cursorfill() command. If I done a cursorrefresh() I got the same data, as refresh does not change the result set.

I understand you as saying that the results will never change regardless of how the SelectCmd value is... Correct????


>>you have to change sqlcmd before cursorfill()

Please be specific here...

If the thisform.de_class.ca_class.SelectCmd property = "select * from table"

Why couldn't I just before the cursorfill() method change it to:
SelectCmd=SelectCmd + "where ?color = blue"


Thanks,
Stanley

 

[Olaf Doschke]

It's a bit cumbersome to explain something...

OK. I said "once select * from table runs changing selectcmd doesn't change the result" but that was just adressing the order of doing things. During a run of CursorFill() changing SelectCmd doesn't do anything, the command is sent to SQL Server in it's old form and executes, changing SelectCmd only has influence on the next run.

I also said: but you can change SelectCmd any way you like, before doing either cursorfill() or cursorrefresh().

And you're wrong about cursorrefresh(), Cursorrefresh actually does not only refresh the records you already have, it does the selectcmd, also the changed selectcmd, if you changed it.

In contrary repeatedly doing CursorFill() will error with "Alias already in use" error.

Simply try it. You don't need me to answer these kind of questions, simply try.

Help says: CursorRefresh re-executes the value of the SelectCmd.
"re-executes" obviously is only valid, if SelectCmd doesn't change. CursorRefresh DOES NOT go to sql server with each key or recno() and refreshes these records. This is what you think, but before thinking, look into the help and read, please.

By the way: I already initially said, but it's way past: Requery() does trigger CursorRefresh(), you don't need to call CursorRefresh()

>>you have to change sqlcmd before cursorfill()

Please be specific here...

If the thisform.de_class.ca_class.SelectCmd property = "select * from table"

Why couldn't I just before the cursorfill() method change it to:
SelectCmd=SelectCmd + "where ?color = blue"

You took my quote out of the context. I said: "but once select * from table runs changing selectcmd doesn't change the result, you have to change sqlcmd before cursorfill()."

So yees, I already you need to change and you can change SelectCmd before running CursorFill(). And you can also change it afterwards, but that will not affect the current cursor, that will only affect the next run of CursorRefresh() or Requery().

I did never say you can't change SelectCmd, I only pointed you toward when it takes effect and how the order of execution must be.

Bye, Olaf.

 

[Olaf Doschke]

I take back my stement about CursorRefresh(), looking into the CA class of the framework I use, it only does CursorRefresh(), if the SelectCmd hasn't changed since the last CursorFill(), otherwise CursorFill() is used to query with the new SelectCmd.

At this point understand please, that the CA class of that framework is of course licensed, but still the (C) and intellectual property of it's vendors, so I can't hand out the code. I can just forward general ideas and concepts and sometimes I can also error, as I'm not using CA on a low level anymore, I mainly use a buisiness logic object having some userdefined properties and methods controlling CAs, eg a requery() and save().

I wonder though, as I've seen "alias already in use" errors while playing with the native CA class.

I now tried, set a breakpoint in BeforeCursorRefresh(), and though I chnage CA.SelectCmd after CursorFill(), CursorRefresh() causes the BeforeCursorRefresh() to be called with a parameter containing the original SelectCmd string, so the CA remembers that somewhere behind the scenes.

Bye, Olaf.

 

[stanlyn]

Olaf,

>> I now tried, set a breakpoint in BeforeCursorRefresh(), and though I chnage CA.SelectCmd after CursorFill(), CursorRefresh() causes the BeforeCursorRefresh() to be called with a parameter containing the original SelectCmd string, so the CA remembers that somewhere behind the scenes.

Do it before Cursorfill(), not after.



The docs I've read suggests that:

1. doing a cursorfill() and changing the SelectCmd value requeries using the previous SelectCmd value, not the new one, very similar to cursorrefresh().

2. changing the SelectCmd value and then issue a Cursorfill() creates a new query with the new SelectCmd value all the way back to the sql server, as that is one of the reasons why we have to set the grids recordsource property before and after, because it is getting a brand new cursor.


I have actually tried using this and it is working as I've described. The CA only has a "where 1=2" clause, and in code imediately before the Cursorfill() I changed it to "where party_type = '1- Grantor' and it returned only those records. On the next goAround, I changed it to "where party_type = '2- Grantee' and it shows them correctly...

Olaf, a lot of stuff is being covered here, so please be patient as I fix and break and learn this stuff... Thanks so much...

And yes, I read a ton, as at any given moment, I have at least 40-50 Firefox tabs loaded with de, sa, sql, vfp and other related google search results...

Thanks,
Stanley

 

[Olaf Doschke]

Well, you'll be surprised, if you read the change of the AfterRowColChange event in the other thread.

Bye, Olaf.

 

[stanlyn]

>> if you read the change of the AfterRowColChange event in the other thread.

I've looked for the thread on this site you are referring to and have not located it yet... What is is the thread number?

Thanks,
Stanley

 

[Olaf Doschke]

We have two threads running, stanlyn. If I say the other one, I mean the other one, not this one, "Connect is Busy - OK or Revert?" You already reacted there, so why do you ask?

Bye, Olaf.

 

[stanlyn]

OK Olaf, its time to print and review all the things we've talked about in my threads.

Thanks for teaching and words of wisdom...
Stanley