PDA

View Full Version : Virtual table created as encrypted



avianmanagement
2013-09-11, 01:51
I'm just staring to use Virtual Tables in place of my old VFP Views.

Using the following code it creates the VT, but is is listed as encrypted.

What am I missing here. The databases was imported from vfp.

close data all
open data aim
if tableexists("vt_danimal_custom_comp_all","aim")
drop table vt_danimal_custom_comp_all
endif

create virtualtable vt_danimal_custom_comp_all ;
connstr "local" ;
as ;
SELECT dAnimal.* , dTaxon.cCommon , dTaxon.cLatin , dTaxon.cClass , dSex.cSex , ;
dSexing.cSexing , dIDType.cIDType , drearingMethod.cRearing , dEnclosure.cEnclosureCode , ;
dTaxon.cMasterKey , dAnimalStatus.cAnimalStatus , dEnclosure.lSystem ;
FROM ( ( ( ( ( ( ( Aim!dAnimal LEFT OUTER JOIN Aim!dMovement ON ;
dAnimal.cPK_dAnimal = dMovement.cFK_dAnimal ) LEFT OUTER JOIN Aim!dTaxon ON ;
dAnimal.cFK_dTaxon = dTaxon.cPK_dTaxon ) LEFT OUTER JOIN Aim!dSex ON ;
dAnimal.cFK_dSex = dSex.cPK_dSex ) LEFT OUTER JOIN Aim!dSexing ON ;
dAnimal.cFK_dSexing = dSexing.cPK_dSexing ) LEFT OUTER JOIN Aim!dIDType ON ;
dAnimal.cFK_dIDType = dIDType.cPK_dIDType ) LEFT OUTER JOIN Aim!drearingMethod ON ;
dAnimal.cFK_drearingMethod = drearingMethod.cPK_drearingMethod ) LEFT OUTER JOIN Aim!dEnclosure ON ;
dMovement.cFK_dEnclosure = dEnclosure.cPK_dEnclosure ) LEFT OUTER JOIN Aim!dAnimalStatus ON ;
dAnimal.cFK_dAnimalStatus = dAnimalStatus.cPK_dAnimalStatus ;
WHERE empty(dMovement.tOut) and dAnimal.cPK_dAnimal = ( ?PKVALUE )

barrymavin
2013-09-11, 01:56
It cannot parse the SQL query. You need to test your SQL queries first before applying them to a Virtual Table.

First thing i notice is ?PKVALUE

How can a Virtual Table know about external variables? Answer it can't and the SQL query will fail when it tries to open it.

So before creating virtual tables test the SQL query beforehand to make sure it can be parsed otherwise you will get "encrypted" -- in other words -- can't open it or perform the query.

avianmanagement
2013-09-11, 02:02
Thanks Barry,

Because tables can be encrypted I had assumed that Lianja was creating the VT and encrypting it.

For future release would it be possible for create virtualtable to return .T. or .F. so that we can test this so that if we update a field we can just run the create virtualtable command and it would tell us itself if it worked or not.

If one is doing this is code, say in a business class, and you need the variable, in this case PKVALUE, can you set that as a memory variable and then do the call to create virtualtable ?

If not what is the best way to do this in Lianja, not in a ui with the data open but in a prg used for business rules ?

barrymavin
2013-09-11, 02:09
CREATE VIRTUALTABLE is a command not a function so it does not return a value.

It also does not query and open the VT as you may want to setup other things for this to work.

Just test the SQL query beforehand to make sure it is correct and is parsed correctly.

Its better to use { macro } in Lianja Virtual Tables that ? parameter markers.

avianmanagement
2013-09-11, 02:13
Thanks Barry,

I'm testing the SQL query by itself at the moment without the PKVALUE and there are some issues with the left outer joins.

avianmanagement
2013-09-11, 02:19
I needed to remove the reference to the Aims databases that VFP sql had in there

avianmanagement
2013-09-11, 05:22
I have now added a check to the end of my prg to create the vt

if !tableexists("vt_danimal_custom_comp_all","aim")
messagebox("Virtual Table vt_danimal_custom_comp_all could not be created")
endif

This could be refined to a call to a generic prg just passing the vt name as a parameter.

HankFay
2013-09-11, 06:29
In the framework you are used to using in VFP, the ?PKValue would have been assigned an empty value before the querying (or you would have gotten the parameter input dialog from VFP). As Barry says, you can use macros; or you can declare pkvalue = '' in a scope visible to the VT, both when testing, and also before the Lianja database is opened when opening the app.


Thanks Barry,

Because tables can be encrypted I had assumed that Lianja was creating the VT and encrypting it.

For future release would it be possible for create virtualtable to return .T. or .F. so that we can test this so that if we update a field we can just run the create virtualtable command and it would tell us itself if it worked or not.

If one is doing this is code, say in a business class, and you need the variable, in this case PKVALUE, can you set that as a memory variable and then do the call to create virtualtable ?

If not what is the best way to do this in Lianja, not in a ui with the data open but in a prg used for business rules ?

avianmanagement
2013-09-11, 06:57
Thanks sHank,

I'll use the macro substitution and get the prg I'm writing to auto create these from the vfp views to do the changes for me when it creates the vt prg.

So all these parameters need to be set up before the databases is opened, a little like some of the stuff I was doing recently with the Xcase2vpm stuff for sql server you showed me where all the parameters are declared, as empty values, in a prg which is run early on in the process.

Cox
2013-09-11, 09:34
Avian,
Please confirm this is correct, as per the wiki documentation, the tableexists function uses database followed by tablename in the parameter list.


if tableexists("southwind","customers")

in your code below, does appear you have switched this around, is this correct?



I'm just staring to use Virtual Tables in place of my old VFP Views.

Using the following code it creates the VT, but is is listed as encrypted.

What am I missing here. The databases was imported from vfp.

close data all
open data aim
if tableexists("vt_danimal_custom_comp_all","aim")
drop table vt_danimal_custom_comp_all
endif

create virtualtable vt_danimal_custom_comp_all ;
connstr "local" ;
as ;
SELECT dAnimal.* , dTaxon.cCommon , dTaxon.cLatin , dTaxon.cClass , dSex.cSex , ;
dSexing.cSexing , dIDType.cIDType , drearingMethod.cRearing , dEnclosure.cEnclosureCode , ;
dTaxon.cMasterKey , dAnimalStatus.cAnimalStatus , dEnclosure.lSystem ;
FROM ( ( ( ( ( ( ( Aim!dAnimal LEFT OUTER JOIN Aim!dMovement ON ;
dAnimal.cPK_dAnimal = dMovement.cFK_dAnimal ) LEFT OUTER JOIN Aim!dTaxon ON ;
dAnimal.cFK_dTaxon = dTaxon.cPK_dTaxon ) LEFT OUTER JOIN Aim!dSex ON ;
dAnimal.cFK_dSex = dSex.cPK_dSex ) LEFT OUTER JOIN Aim!dSexing ON ;
dAnimal.cFK_dSexing = dSexing.cPK_dSexing ) LEFT OUTER JOIN Aim!dIDType ON ;
dAnimal.cFK_dIDType = dIDType.cPK_dIDType ) LEFT OUTER JOIN Aim!drearingMethod ON ;
dAnimal.cFK_drearingMethod = drearingMethod.cPK_drearingMethod ) LEFT OUTER JOIN Aim!dEnclosure ON ;
dMovement.cFK_dEnclosure = dEnclosure.cPK_dEnclosure ) LEFT OUTER JOIN Aim!dAnimalStatus ON ;
dAnimal.cFK_dAnimalStatus = dAnimalStatus.cPK_dAnimalStatus ;
WHERE empty(dMovement.tOut) and dAnimal.cPK_dAnimal = ( ?PKVALUE )

avianmanagement
2013-09-11, 10:50
Yes you are right I seem to have gotten it the wrong way around according to the wikki docs.

I copied the code from a section Hank posted and edited my own values in, the wrong way around.

Thanks for pointing this one out.

On testing this with teh script teh right way around:

if !tableexists("aim","vt_danimal_custom_comp_all")
messagebox("Virtual Table vt_danimal_custom_comp_all could not be created")
endif

The check does not work as it does exist in the database if the sql fails. It is marked encrypted and also if you try to select it Lianaj gives you a message file can't be found

Cox
2013-09-11, 11:07
Also staring out on Lianja hence whenever I see code, I try it out to understand what it does and thats how I found that entry.
On VT, I have tried to connect a MySQL database to an already imported database into lianja also from MySQL. I have noted that if the MySQL server is down, then on the list of tables for the application, the virtual table will be marked as "encrypted".

avianmanagement
2013-09-11, 11:16
That is interesting, but I don't think one could use the "encrypted" to check if the vt exists as I would assume one could create a VT which was actually encrypted.

barrymavin
2013-09-11, 11:24
Yes we should look into providing some more meaningful indication.

avianmanagement
2013-09-11, 11:34
Hi Barry,

If the SQL fails is there any way you could, in the future, simply not add the VT to the databases. That way one could check for it's existence after the code to create it.

HankFay
2013-09-11, 11:42
Thanks sHank,

I'll use the macro substitution and get the prg I'm writing to auto create these from the vfp views to do the changes for me when it creates the vt prg.

So all these parameters need to be set up before the databases is opened, a little like some of the stuff I was doing recently with the Xcase2vpm stuff for sql server you showed me where all the parameters are declared, as empty values, in a prg which is run early on in the process.

exactly: guess what -- the same thing is happening on the backend. <s>

HankFay
2013-09-11, 11:43
Yes we should look into providing some more meaningful indication.

oops

avianmanagement
2013-09-11, 11:50
exactly: guess what -- the same thing is happening on the backend. <s>

You lost me there Hank, not hard to do as you well know!

Do you mean the databases keeps a list of parameters used and runs empty values for them as it opens?

HankFay
2013-09-11, 12:26
You lost me there Hank, not hard to do as you well know!

Do you mean the databases keeps a list of parameters used and runs empty values for them as it opens?

In both cases, the view/vt's sql is run against the backend db: that's why the values must be there.

avianmanagement
2013-09-11, 12:33
Been playing with vts a lot today.

I thought views in VFP were good, but these are streets ahead.

Just change a memory variable value, for the conn string, and I'm hitting a new back end, awesome!