Updating Underlying SQL Table from a Grid and a Form

[stanlyn]

Hi,

Just wondering if there is a magic bullet for writing back/updating the SQL table from the cursor that was generated from the SQL select statement. The sqlexec() commands run and I get back a cursor that is the datasource for the grid. I make a change to the grid's cursor and now its time to writ it back to the sql table. It could also be coming from textboxes on a form as well.

One way is to use sqlexec() with a sql update statement in the grid's afterrowcolchange event.

A couple years ago, I tried the sql cursor adapters and had limited success. The SQL cursor adapter just seams like a late entry into VFP and still has unresolved issues.

I know how to do the SQL passthru stuff, and that all seams like a lot of code for something as simple [replace field1 with "new"]. Just hoping for a more efficient method (in terms of time and code).

And are there any commands or functions for processing a whole grid of changes all at once, like inside a save button?

If there are no better ways, I will probably create several generic SQL pass-thru classes, one for selects, one for updates and one for deletions and pass the specifics in by parms.

Just wondering what the options are.

Thanks, Stanley

 

[Olaf Doschke]

>The SQL cursor adapter just seams like a late entry into VFP and still has unresolved issues.
Yes, it's latest, no it has no issues.

To get an updatable cursoradapter or SPT cursor needs the same things as creating and updatable view, you need to know about either the assistants/designers/builders asking things of you for making the necessary settings or the core properties of KeyFieldList, Tables, UpdatableFieldList, and UpdateNameList. See CURSORSETPROP() for their meanings, in case of views see the designer tabs for setting them, in case of CA see its properties for spcifying them or the builder tabs.

The core requirement for any of these to update the underlying table is to have a key, primary key, unique identifier of a record, other than the RECNO. That's what many foxpro databases lack, still in 2016. I don't know about you, but legacy coding makes it too easy to live without them, as you simply can rely on buffers to be written in the recnos you changed. For any backend to work, yes, either you yourself need to write update statements, or they are generated behind the scenes. Using SQL PRofiler you can see the queries coming from foxpro when doing TABLEUPDATE() on updatable views, CAs, and SPT cursors, they all make use of the primary key as identifier of where updates have to go.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

is to have a key, primary key, unique identifier

Are you saying I need a key and a primary key, and a unique identifier? Or any one of these?

I've been using unique identifier keys for years now, but not all 3.

Good to here its has no issues...

Thanks,
Stanley

 

[Olaf Doschke]

Key is just short for primary key, ie KeyFieldList is short for PrimaryKeyFieldList. And uniqueness is just an attribute of a primery key.

So in short: You need a Primary key in all tables and it is all of this. In FoxPro you can say a primary key consists of a) a field with a unique value per record and b) a Primary type index on that field, that doesn't only confirm the intention of the field, but ensures the uniqueness will also be granted for now and the future. Besides designers will take this as info about what is the primary key of a table.

Bye, Olaf.

 

[Mike Lewis]

Stanley,

Another option would be to use a remote view. This would be instead of SQL pass-through.

Once you have created a remote view into the remote table, it acts pretty much like an ordinary VFP table. You can use it as the RecordSource of the grid, and have it update the underlying tables automatically. You still need to specify the primary key for the updates (it's the only way that the back-end knows which record you want to update), but it is otherwise completely straightforward.

Remote views are not a complete replacement for SQL pass-through. There are many things that you can do with SPT that cannot be done with remote views. But they are a simple option for the situation you have described.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I already mentioned views. It doesn't change the concept of what is needed as backlink for having an updatable cursor, no matter if CA, SPT or local/remote view cursor.

Bye, Olaf.

 

[stanlyn]

Concerning the remote view, do I have to have the connections pre-built? I would rather use dsnless style of connection strings while storing them encrypted in a local table with my own cryption routine.

Also, is it best to always close the connection after each database access, or open one connection at the app startup and use it thru the app's duration, the closing it on exit?

Thanks, Stanley

 

[Mike Lewis]

Concerning the remote view, do I have to have the connections pre-built?

No. You can use DSN-less connection string, which you are free to store in a table (encrypted or otherwise), or anywhere else you like.

You first create the connection:

[tt]CREATE CONNECTION <connection name> ... CONNSTRING <connection string>[/tt]

Then create a remote view based on that connection:

[tt]
CREATE VIEW <view name> REMOTE CONNECTION <connection name> AS <SQL statement>[/tt]

Both the connection and the string are normally persistent, that is, you only need to define them once. The definitions are stored in a DBC. This means that a DBC is a pre-requisite for using remote views.

However, if you want to create connections and views on the fly, that's perfectly possible. You can create a temporary DBC (typically stored in the user's Temp folder), then add the connection and the view to it. At the end of the session, you can delete the DBC. I've done this myself in an application where I needed to import arbitrary data from various sources which could not be defined in advance.

Also, is it best to always close the connection after each database access, or open one connection at the app startup and use it thru the app's duration, the closing it on exit?

Opinions differ on this. In general, in takes time to create a connection. If you keep the connection open the whole time, you will probably get better performance. On the other hand, most back-end servers (including SQL Server) impose a limit on the number of open connections, in which case it might be preferable to keep the connection open for the shortest possible time.

Personally, I always open the connection at the start and leave it open until the end. I've always found that works well. But other people might have a different experience.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

With MSSQL Servers connection strings don't need to contain credentials, if the database is configured for Windows Authentication. So there is no need for encrypting the connection string.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

So there is no need for encrypting the connection string

Just wondering on how Windows authentication would work if the users are coming in from all over. Their systems would be on different network segments and NOT part of our AD domain. Wondering how the authentication process would work. I had planned on giving each user a MSSQL user identity with encrypted uid and pwd. The encryption is for hiding it from the user and/or whomever they decide to share it with (our competitor).

What ways can I protect the credentials other than SSL to send the uid and pwd from client to the server without them being in the clear. I need to make this as simple as possible with NO setup on the client side, and fully protected from packet sniffers.

Thanks, Stanley

 

[stanlyn]

Hi,

Why is the updating of the sql failing here with no errors?
County is the remote sql table and SqlCounty is the local vfp cursor.

Clear

zz='ja%'

If Indbc("SqlCounty", "VIEW")
	Delete View 'SqlCounty'
Endif

Create Sql View SqlCounty Remote Connection MyConn Share ;
	AS Select Top 100 * From county ;
	Where county.county_name Like ?zz ;
	Order By county_name

Use 'SqlCounty'
SET MULTILOCKS ON 
CURSORSETPROP('Buffering', 5, 'SqlCounty' )
CURSORSETPROP('Sendupdates',.T.,'SqlCounty')
CURSORSETPROP('Tables','County','SqlCounty')
CURSORSETPROP('KeyFieldList','PK','SqlCounty')
CURSORSETPROP('UpdatableFieldList','PK, fc_meetings','SqlCounty')
CURSORSETPROP('UpdateNameList','PK PK,fc_meetings fc_meetings','SqlCounty') 

Select 'SqlCounty'
Count To lnCount
?lnCount
Browse

TABLEUPDATE(0,.t.,'SqlCounty')

Thanks,
Stanley

 

[Olaf Doschke]

On what setting are you thinking? A web MSSQL Server as Backend to your Desktop app? Azure? Then, sure, credentials are part of the connection.
But even if you encrypt the connection string, at the connection itself it will need to be unencrypted.

In a web app situation there only will be your web apps scripts addressing a database, that wouldn't mean a connection from users anyway.

You could go about this in limiting their access as it should be limited anyway, if you assume the connection info can get breached, limit them to be able to read/write tables, but not do any administrative tasks, db updates, etc. And then it can be an additional feature for them to have connection to their data without your application.

Or you rather go for a API approach (RESTful) and thereby they don't need any SQL Server credentials, that's simply all done by your API code server side.

Bye, Olaf.

 

[Olaf Doschke]

In regard of your code. What error do you get? You haven't set WhereType.

And a recommendation: Use

https://www.universalthread.com/ShowHeaderDownloadOneItem.aspx?ID=9474

Bye, Olaf.

 

[stanlyn]

Olaf,

NO errors... The update is NOT happening.

Thanks, Stanley

 

[stanlyn]

Olaf,

Adding
CURSORSETPROP('WhereType',2,'SqlCounty')

produces same "not updating" results.

Thanks, Stanley

 

[Mike Lewis]

Why is the updating of the sql failing here with no errors?

If the error is happening on the server (which is most likely), you won't see a VFP error message.

To see the error in VFP, you need to pass .F. as the second parameter to your TABLEUPDATE(). Then check for the reply from TABLEUPDATE().

IF NOT TABLEUPDATE(0, [b].F.[/b], 'SqlCounty') 
  AERROR(laErrorArray)
  lnError = laErrorArray(1)
  
 * Do something about the error here

ENDIF

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

OK, the error returned is 1491 "No update tables are specified. Use the Tables property of the cursor (Error 1491)"

I DO have
CURSORSETPROP('Tables','County','SqlCounty')
in the code where County id the sql table and SqlCounty is the vfp cursor. (see original posting)

Thanks,
Stanley

 

[Olaf Doschke]

Add the table name county to each field in the updatenamelist:

[tt]CURSORSETPROP('UpdateNameList','PK county.PK,fc_meetings county.fc_meetings','SqlCounty')[/tt]

Using eView and setting up an updatable view it would give you all code, all CURSORSETPRO, and you'd learn it from that, if not from the help topic:

pairs of local and full remote field names

The error message is misleading, but results from not specifying the full names here, so it doesn't recognize remote table names as cross reference of both these settings.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

Your suggestion fixed the back-end updating, ans thanks...

1. How do I tell if I already have an active connection?

2. If the connection is shared, is it true that subsequent connections will use it?

3. I did not see a "close connection" command. Instead I see a "delete connection MyConn" command. But issuing that removes the connection from the database?

4. So, referring to #3 above, if there is a connection under the Connections treeview folder in the database, is it connected? In other words, connections that shows up in the database are ALWAYS connected and un-connected connections are ALWAYS deleted and do not show in the database??? If not, then where is the "close connection" command?

5. Is it standard practice to always delete and create the same named view when a parameter changes such as
first view's where clause is:
zz='Ja%' - Where county.county_name Like ?zz
now, if the value for zz changes to 'Br%', do I delete the previous view and rerun the create sql view again with the new changes?

6. I also noted that after I get a cursor of records from the view, if I issue browse I see all the expected records. Next I issue a "use in select('curcor') and the cursor closes as expected. Now if I issue a "use cursor in 0 NoData", it is loaded again but with no data. I did not delete the view, only closed and reopened the cursor. I can then close it and reopen it again without the NoData keyword and the original records reappears. Question, why is the cursor empty with NoData and not otherwise? I am not issuing a create sql view again? At this point I'm only opening and closing the result cursor. Does not make any sense...

Thanks,
Stanley

 

[Olaf Doschke]

ASQLHANDLES gives you an array of handles (answering 1), but you'll store your handle somewhere for long time usage, if you don't close it with SQLDisconnect (answering 3, just look at all SQL... functions for an overview).

In regard of the connection object in a DBC - it is persistent and should be persistent, you reuse it, the remote views reuse it, you don't have to care about this. When you define a remote view it'll use the DBC connection object, you have to specify the connection object in the view definiton with the CONNECTION ConnectionName option of CREATE VIEW or in the view designer, so you don't have to care for the connections. The mere existance of a connection in a dbc does not connect, in the same manner as the mere existance of a user/system/file DSN also doesn't connect, they are merely the definition of whats used for connecting (answerring 4).

You only need to change variable value, not recreate view (answering 5), how comes you even just think about this? If the view definition would take the variable value at definition time literral, it wouldn't be a view parameter, would it?

And about your last question: What is unclear about the key word NoData? You use this to create the empty view cursor and be able to later set view parameter values and REQUERY() it, so a) grids have a data source at form instanciation and b) users may specify view parameters in the form and all still works, you may not use NoData, if you don't like, but in case you have 2GB data you don't want to fetch 2GB data and then ask the user what he want. Google also doesn't display the whole internet before you enter a search term, does it?

About the last sentence: create sql view just creates the view, like the connection only is created. USEing a view executes it.

Bye, Olaf.