View Full Version : SQL statement for Virtual Tables in section attributes
barrymavin
2013-09-17, 22:48
To simplify the use of Virtual Tables that are used in parent->child section relationships, in the next build I have added a new attribute "SQL statement" in the section attributes.
The SQL statement is used to populate a Virtual Table whenever a ParentDataChanged event occurs. Use { } macros in the WHERE condition to relate parent->child sections and ORDER BY to order the display of records. This is native SQL for the target SQL database and may call stored procedures as well as SQL SELECT.
This should simplify client/server development with minimum code. This can also be applied as an inline delegate:
$("section:section1?action=sql&text=select * from sales.orders where customerid='{customer.customerid}'")
The "sql" attribute is also exposed on the "section" object and is ReadWrite so it can be changed dynamically and the section will be automatically refreshed.
e.g.
Lianja.getElementByID("mygridsection").sql = "select * from sales.orders where customerid='{customer.customerid}'"
You do not need to do this manually if you set the "SQL" attribute to "select * from sales.customer where customerid='{customer.customerid}'" in the section attributes then as you navigate records in a parent section the related child section(s) will automatically be requeried using the specified SQL statement.
For this to function correctly you should check "Automatically relate" in the section attributes.
To prevent initial loading of related child sections as I have mentioned previously, specify the SELECT statement on the Virtual Table AS SELECT * FROM table WHERE 1=0
This needs properly tested by the beta testers. Let me know how you get on with it.
Hi barry, you want to gain weight with my lasagna .. ;)
As soon as it is available, I do all the tests.
avianmanagement
2013-09-18, 02:09
Hi Barry,
In the example above sales.orders is a VT I assume.
barrymavin
2013-09-18, 02:30
No. The VT is opened and the SQL SELECT resultset retrieved.
So now you can override the SQL statement (it should be against the same table) but with a different WHERE condition, and ORDER BY, GROUP BY or whatever you want in the child section.
The VT is still active, it is requeried using the specified SQL statement.
Performing a SQL SELECT on a VT will read the VT and then apply the query on the client. Doing it this way only those records satisfying the query are returned from the server.
You only query a VT with a SQL SELECT if you want to joing disparate data sources e.g. Lianja and MySQL and produce a resultset.
avianmanagement
2013-09-18, 02:49
Hi Barry,
In the above you say "it should be against the same table"
Can it be against multiple tables, i.e. if the VT is bases on fields from more than one table, with this work and they all get updates if changes are made to them?
barrymavin
2013-09-18, 04:28
SQL UPDATE only works against one table. You can't update multiple tables like that. If you need to do that you use stored procedures.
SQL UPDATE only works against one table. You can't update multiple tables like that. If you need to do that you use stored procedures.
I think David was referring to what is common many apps: having the main table update, but the JOIN tables display. Yes, the SET/GET attributes can be used for single-level relations, but in complex relational apps the values can be 2 or more levels away. The UpdateTable is always singular, and specified (in the VFP view), etc.
thanks,
Hank
barrymavin
2013-09-18, 05:18
To use this functionality specify your SQL SELECT (or stored procedure call) as below in the screenshot.
460
barrymavin
2013-09-18, 05:23
I think David was referring to what is common many apps: having the main table update, but the JOIN tables display. Yes, the SET/GET attributes can be used for single-level relations, but in complex relational apps the values can be 2 or more levels away. The UpdateTable is always singular, and specified (in the VFP view), etc.
thanks,
Hank
Yes and thats how VTs work in fact.
I'm testing this new feature.
I've put this code into the refresh delagate of a Partners table
////////////////////////////////////////////////////////////////
// Event delegate for 'refresh' event
proc page1_section1_refresh()
Lianja.getElementByID("section2").sql = "select * from tdoc_ddt where partners='{partners.partners}'"
Lianja.getElementByID("section2").refresh()
? "Refresh delegate " + partners.partners
endproc
but I obtain this error..
mer 18. set 13:19:13 2013
**** Lianja error ****
CURSORSETPROP('buffering', .t., 0)
^
Workarea value of 0 is out of the range of 1 to 100
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 5
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 5
mer 18. set 13:19:13 2013
**** Lianja error ****
CURSORSETPROP('buffering', .t., 0)
^
Workarea value of 0 is out of the range of 1 to 100
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
CURSORSETPROP('buffering', .t., 0)
^
Workarea value of 0 is out of the range of 1 to 100
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 5
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 5
mer 18. set 13:19:13 2013
**** Lianja error ****
CURSORSETPROP('buffering', .t., 0)
^
Workarea value of 0 is out of the range of 1 to 100
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 4
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 5
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
Called from procedure - page1_section1_refresh at line 5
mer 18. set 13:19:13 2013
**** Lianja error ****
CURSORSETPROP('buffering', .t., 0)
^
Workarea value of 0 is out of the range of 1 to 100
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
mer 18. set 13:19:13 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
Table is not indexed
barrymavin
2013-09-18, 06:43
The refresh() event? No. Please read my forum post again. You don't need to do that.
I said it needed tested.
Sorry..
Now I've only insert this code into the SQL statement of the grid:
select * from tdoc_ddt where partners='{partners.partners}'
tdoc_ddt is the VT displayed into the grid
but the error is the same.
barrymavin
2013-09-18, 07:01
You need to read the forum post again. You are doing it all wrong. You don't query the VT you override the SQL SELECT statement. I will upload another build soon. Please go and read the forum post again.
barrymavin
2013-09-18, 07:27
Also as I wrote, you don't need to refresh() a section after changing the sql, it is done automatically.
The SQL statement is a SQL SELECT *against* the VT connection, so don't query the VT specify a *new* SQL statement,
e.g.
CREATE VIRTUALTABLE barry CONNSTR 'odbc_dsn' AS select * from sales.orders where 1=0
Then change the SQL like this in a delegate.
Lianja.getElementByID("section2").sql = "select * from sales.orders where customerid='{customers.customerid}'"
You do not need to refresh(), also notice you reference sales.orders NOT the VT which is named barry.
There will be a new build available shortly. Try that following the technique I specify above.
ok, still reading post .. I will learn English .. :D
Then change the SQL like this in a delegate.
Lianja.getElementByID("section2").sql = "select * from sales.orders where customerid='{customers.customerid}'"
what is the most appropriate delegated?
I still need to set up a relation between the two section?
barrymavin
2013-09-18, 08:00
ok, still reading post .. I will learn English .. :D
what is the most appropriate delegated?
I still need to set up a relation between the two section?
"Parent data changed"
As that is when you need to have the grid requeried and refreshed. Just change the SQL in that or use an inline delegate.
Ok, I've tried...
but I've a error.
the real table is called "partners" and "tdoc_ddt"
the VT is called "vt_partners" and "vt_tdoc_ddt"
I've no code on the parent section ("vt_partners")
In the parent data changed delegate
proc page1_section2_parentdatachanged()
Lianja.getElementByID("section2").sql = "select * from test.rdoc_ddt where partners='{vt_partners.partners}'"
// insert your code here
endproc
then I've related parent section and child section
But Ive a error..
the "use" statement use the VT name.. not the real DB name..
mer 18. set 15:06:47 2013
**** Lianja error ****
USE "VT_TDOC_DDT" AS select * from test.rdoc_ddt where partners='03/01/0000'
^
File 'VT_TDOC_DDT' does not exist
Called from procedure - page1_section2_parentdatachanged at line 4
mer 18. set 15:06:47 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
No table is in USE
mer 18. set 15:06:47 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
No table is in USE
mer 18. set 15:06:47 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
No table is in USE
mer 18. set 15:06:47 2013
**** Lianja error ****
SEEK "03/01/0000 "
^
No table is in USE
mer 18. set 15:06:47 2013
**** Lianja error ****
SET ORDER TO TAG partners
^
No table is in USE
barrymavin
2013-09-18, 08:20
Did you download the build from 10 minutes ago? If not do so and try that. I still have a few things to be tested.
done, download and install, but I've the same error:
One question:
if need to set the relation between the 2 section?
if yes, and with the "automatic relate" = true, Lianja set in automatic the Parent and Child relation.. it's correct?
mer 18. set 15:40:51 2013
**** Lianja error ****
USE "VT_TDOC_DDT" AS select * from test.rdoc_ddt where partners='03/01/0000'
^
File 'VT_TDOC_DDT' does not exist
Called from procedure - page1_section2_parentdatachanged at line 4
mer 18. set 15:40:51 2013
**** Lianja error ****
COMPANYID
^
PROPERTY 'COMPANYID' not found
mer 18. set 15:40:51 2013
**** Lianja error ****
COMPANYID
^
PROPERTY 'COMPANYID' not found
mer 18. set 15:40:51 2013
**** Lianja error ****
COMPANYID
^
PROPERTY 'COMPANYID' not found
barrymavin
2013-09-18, 08:56
Yes, it ignores the relationship but is used when inserting new rows. I will look into this. What is your VT? Let me see with DIR.
Barry,
Organise a webcast on this to bring us all upto speed.
Yes, it ignores the relationship but is used when inserting new rows. I will look into this. What is your VT? Let me see with DIR.
Tables in Database: sem_vdb
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ vt_partners C:\Lianja\data\sem_vdb\vt_partners.dbf VirtualTable
| connstr 'test2'
| alias 'vt_partners'
| as select * from partners
+ vt_tdoc_ddt C:\Lianja\data\sem_vdb\vt_tdoc_ddt.dbf VirtualTable
| connstr 'test2'
| alias 'vt_tdoc_ddt'
| as select * from tdoc_ddt where 1=0
barrymavin
2013-09-18, 09:04
That's a good idea once it is all working to my satisfaction. It is currently not complete but I'm working on it.
Hi Barry,
it's my error or Lianja error?
Tables in Database: sem_vdb
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ vt_partners C:\Lianja\data\sem_vdb\vt_partners.dbf VirtualTable
| connstr 'test2'
| alias 'vt_partners'
| as select * from partners
+ vt_tdoc_ddt C:\Lianja\data\sem_vdb\vt_tdoc_ddt.dbf VirtualTable
| connstr 'test2'
| alias 'vt_tdoc_ddt'
| as select * from tdoc_ddt where 1=0
barrymavin
2013-09-19, 03:51
You should always test your VTs in the console to make sure they are working before you use them in a page.
You are a beta tester so things change on a daily basis.
barrymavin
2013-09-19, 03:53
You should always test your VTs in the console to make sure they are working before you use them in a page.
You are a beta tester so things change on a daily basis.
Also I have not seen how you created this VT.
Try the example_virtualtables app in the next build and work through the steps one at a time until you are familiar.
This work fine...
simply ... simple.
great
I do other test with VT..
barrymavin
2013-09-19, 05:26
We'll organize some webcasts soon... Let us know what thing specifically you would like covered.