View Creation and wrong Meta-Data version

[TNGPicard]

Howdy All -

I use a software app that is a 'relational psql' / btreive hybrid. When I do upgrades of the product, i lose custom indexes and such and recreate them. The software i use does not utilize views and coming from the MS-SQL world, I use them frequenly.

So from help files, and what not, I've hammered out the following in my attempt to create a view. The SQL select statement works fine (it is a little odd, but I have some odd data structures and data problems and am trying to use a view to aid in some reporting structures, realizing that after every system upgrade, I will need to recreate the view and what not).

CREATE VIEW vw_normCusts (trueCustNum,CustomerNum,MasterCustNum,LinkedMasterCustNum,subLinkMaster) 
WITH EXECUTE AS 'MASTER' AS 
select coalesce(subMaster.LinkedMasterCustNum,customer.LinkedMasterCustNum) as trueCustNum,  customer.CustomerNum, customer.MasterCustNum, customer.LinkedMasterCustNum, subMaster.LinkedMasterCustNum as subLinkMaster
from {oj "CUSTOMER" "CUSTOMER" LEFT OUTER JOIN "CUSTOMER" "subMaster" ON "CUSTOMER"."MasterCustNum"="subMaster"."CustomerNum"}

When I try to execute the above, I get the following error:

[LNA][Pervasive][ODBC Engine Interface][Data Record Manager]This feature is not supported for the current Metadata version.

So my question is, how do I change the meta data version? What are the dangers, what should I be aware of. I will of course do this first on my non production system first and test before making changes to my production system(s). I suspect I will need to "redo" whatever I do when I upgrade but how to get a little further would be greatly appreciated.

Based on the help file, I think I use the command pvmdconv but start to get a little confused with all the various switches.

Other than hopefully "view" creation, what are advantages (and/or disadvantages) to upgrading meta data version?

Please advise

 

[mirtheil]

A couple of questions and comments:
- What version of PSQL are you using?
-- Older versions don't support coalesce and some older versions don't support joins in Views.

As far as converting the meta data, you would want to talk to the vendor of the application to find out how that would affect it. It shouldn't affect things but at the same time, I've seen apps that access the meta data directly, instead of one of the PSQL methods, and changing the meta data version would probably break that. You're right, you'd have to redo the conversion and recreate the View(s).

One more thing, you will probably not see any performance improvement by using Views. Views in PSQL are simply stored statements. It might be easier to just execute the statement directly.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[TNGPicard]

Mirtheil -

As usual, thank you for your prompt responses.

I am currently on Pervasive 10.10 x64 and will likely be installing the service packs available as I've nto really patched since I installed 2 years ago within the next 2 months.

The query part of the view code works on this version of Pervasive and I use it in several places already, I'm just trying to simplify life.

I realize view creation in Pervasive does not yeild the performance gains that views give one on other RDBMS systems, however in some simple reporting applications which don't allow complex SQL statements but rather like to build their own fairly simple database queries that join table 1 to table 2 to table 3, having this view that I can treat as a table would give me a lot greater flexibility. That said, the runtime performance of said view could potentially be O(n*n) or worse and I have a very large data set, this is primairly for overnight reports and limited applications when users are not on the system and I have the resources of Pervasive almost all to myself.

I am indeed checkign with the application vendor, they do a lot of btreive access to data via cobol, but I do not know how that works with meta data. I'm approaching this from multiple angles -- how to do the conversion since that will be on me, technical specifics I'm seeking assistance here. I am incontact with the vendor about the potential implications in their application (or heck, even ask if they can ship with new meta data since they no longer support pervasive Pre 9.5).

One of my alternatives is replicating the tables and information I need to MS-SQL or mySQL but I'd rather not do that. I do run weekly replications to my non-production Pervasive database so I'd still like to figure out the "how to there". Worst case scenario, I have a 2nd non-production a few days out of date copy that I can run these reports on since they do not necessairly need to be "up to the minute" data.

-- Mark/TNG

 

[mirtheil]

I found a little more. THe error is caused by the "WITH EXECUTE AS ‘MASTER’" clause. That causes the view to be a "Trusted View" which requires v2 metadata. The trusted view is documented at

http://docs.pervasive.com/products/...p.htm#href=sqlref/syntaxref.4.49.html#2233480

If you don't really need the "WITH EXECUTE AS ‘MASTER’" clause, you should be able to create the view with the metadata as it is.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com