Q:
I am using the Lianja SQL Server DSN Configuration utility to setup an ODBC connection to my app's database. The utility came pre-loaded with a connection to southwind and this works just fine when I click 'Test' but when I try the same thing to any of the databases I have created I get the error: "Database 'x' - The system cannot fine the file specified." I am using '?' for user and password because I don't know what else to put in there.
A:
Have you deployed the databases you want to access?
Q:
Im'm try to access FoxPro 2.6 data.
From console, if I write:
Code:
use E:\cogema\dati\articoli
the table are opened correctly, I can use this way?
I've also try to configure ODBC to access the data through ODBC, then I've created a VT.
The first time, I've opened the table...
then, close and open Lianja, the table is showed as "Encrypted"
Now crash every time I try to open the vt.
using the ODBC?
I can not import the DB... I've a ERP solution working on this data... I can not convert all the ERP.
FoxPro data stored on E:\cogema\dati
table name = Articoli
I've create ODBC named Cogema and whit other application, work fine.
I've created DB on Lianja named Cogema, then a VT: ODBC = Cogema, SQL= select * from articoli
Dir command:
Tables in Database: cogema
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ vtarticoli C:\Lianja\data\cogema\vtarticoli.dbf VirtualTable
| connstr 'Cogema'
| alias 'vtArticoli'
| as select * from articoli
If I try to open the table, Lianja crash; if I open the database, is empty..
I need to read write on the original table.. For that I think to use odbc..
The ODBC driver is the 32 bit "Microsoft dBase Driver"..
A:
'encrypted' will be displayed if there is a problem with the VT and the data cannot be accessed.
Read/Write to FoxPro tables is not supported.
You can only import foxpro data. You can't update any indexes.
Open you database and issue a DIR in the console then paste a screenshot.
If you have that table open exclusively somewhere else ODBC will not be able to open it.
Try closing the database and deleting the VT files in the C:\Lianja\data\cogema\ folder, then rebuild the VT. If that does not help, can you post the articoli structure? Were autoninc integers available in FoxPro 2.6 ?
you can just drag and drop Foxpro files onto the database panel and they will automatically be converted. You don't need to use ODBC to do this.
This crash has now been fixed in the next 2.0.2 beta build. It was due to an incorrect VT (permission denied to folder or table does not exist) with connection pooling.
The "(encrypted)" message has now been changed to "(Access denied)" which is less confusing.
If you double click on a VT in the data workspace any error is displayed in a notification message at the top of the window.
the problem is the MEMO field... now I try to reinsert the general.
Q2:
I've found a new "Visual Fox Pro" ODBC and I can access to "Fox Pro" table, with Memo and General field.
if I can try to update a record... apparently nothing happens, and the record is not saved.
A2:
If nothing is being updated the WHERE condition is failing.
You can either cut and paste that into VFP and see if you can SELECT using it or preferably set a primarykey on the virtual table definition.
Perhaps the ODBC driver you are using is not handling proper ODBC dates.
Found!
in the property of the virtual table, just set the "Key field list"... and now work fine.
You have to setup your virtual table correctly with primarykey and/or updatefieldlist or keyfieldlist
http://www.lianja.com/doc/index.php/ALTER_VIRTUALTABLE
Q:
For external data VT's are the only way to go but what about sql Views to the local database? Perhaps I'm under the wrong impression but I'd expect a View to be treated almost as tables that can be dragged on to forms etc. Anyway here are some observations I made when looking into this, listed here for discussion:
1. Virtual tables to your own database are tricky because they use odbc (and hence connect to the deployed db). So in the app builder in dev mode, forms are using your local data/database but anything referencing a VT is getting data from the deployed db - very difficult to work with.
2. There is also no way to see views in the app builder, obviously they can be queried in the console but to get a list of views available and see their definitions is very cumbersome - should this be a tab in the database editor?
A:
Search for "local" VTs.
Virtual Tables are displayed along with tables in the tables tab of the database. You can click on one to see it's definition. With the database open, you can issue DIR from the console (vfp window) to see tables and views, and the views will contain the SQL for each view.
Q:
Have app with single form section populated by a VT with a dsn to VFP ODBC. Works as expected in Builder, so I wanted to test on the Cloudserver with an outside browser.
I Deployed the app and the database and VT tables to the Cloudserver because I understand that the deploy button in the Web App View only updates the app.
When I run Preview, it takes some time for the data to appear (it does take some time for the VT to load as I am doing a Select * for about 15,000 records), and occasionally a "server disconnected" appears, but then the first record's data loads. Looks fine, I still have some work to do on formatting. Anyway, pressing the next icon (on the Action Bar?) does nothing. None of the buttons seem to be functioning.
A:
What you have discovered is that VFP does not support the SQL LIMIT clause which Lianja uses if it is available. It determines this heuristically when the VT is opened.
Lianja SQL, MySQL, and PostgreSQL all support the LIMIT clause.
MSSQL does not but we get round it using some other strange MSSQL syntax that we generate internally.
So, in other words, when selecting one record in a web form Lianja has to read the whole table and extract that one record.
This is a limitation of VFP SQL which does not exist in Lianja SQL.
If you can it would be better to convert the VFP database into a native Lianja database.
Q:
How to work with virtual table programmatically.
I have in section1 a field (field1) which is filled by the login process according to the user role. I have to select records in a grid (section2) populated by a virtual table created with "where 1=0" at design time.
here is the recommandation from yvonne, but the grid shows nothing even if i remove the "where" condition to filter all the data.
If the query condition is to be set on loading the App - as opposed to when another section is navigated, put it in the ready of the grid. I can only use :
Code:
////////////////////////////////////////////////////////////////
// Event delegate for 'ready' event
proc page1_section2_ready()
Lianja.get("page1.section2").requery([select * from <table> where userid='{Lianja.get("page1.section1.field1").text} '] )
endproc
If you were navigating a section, I would use the datachanged event delegate in the section being navigated to requery the grid section
A:
You have a ] at the end of the string.
The ready event only fires when the app is loaded. You should requery after a successfull authentication and before you switch pages.
The requery replaces the where condition. It is a requery of the existing select statement. It can contain everything after a where including an order by.
You can try all this interactively in the console.
Code:
oSection = Lianja.get('page1.section1'')
oSection.requery('value > 100')
console workspace. You can test your requery() calls from there. Alternatively use the app insoector console and you should see the data changing in the section.
You create a virtual table where 1=0 then you can drag that virtual range to create a form or grid section.
Once you have that virtual table bound to a section you use the requery() method to change the selected records against the select clause that you have specified in the virtual table definition.
if the virtual table is bound to a child section you need to include the foreign key or no records will match and nothing will be displayed.
major performance improvement when fetching VT data into a local cursor, which with very large tables is multiples of that. You can specify the "fetchsize" in the VT properties (default 100). This removes any delays when initially opening a virtual table.
The "CursorAdaptor" class in Lianja v2.1 has an interesting new method in it which I use for QA and testing.
The "SqlCommandBuilder()" method returns the database dependent SQL generated for a given SQL statement. It is used internally (by OData) to generate a database independent SQL SELECT statement that handles "pagination" of the result set.
The SQL LIMIT clause is common place now but... MSSQL does not support it. MSSQL 2012 and greater support an OFFSET ... FETCH clause. Versions prior to 2012 we need to use the OVER clause. Unfortunately the OVER clause does not seem to function as advertised consistently so we use the OVER clause for all MSSQL versions.
So let's see what a SQL SELECT looks like to select a page of records from a resultset in MSSQL.
Code:
open database cloudlibtest
// create the VT
create virtualtable vt_cust;
connstr 'lianja_mssqltest';
primarykey 'customerid';
as select sales.customer.customerid, sales.customer.territoryid, sales.salesterritory.name from sales.customer;
inner join sales.salesterritory on sales.customer.territoryid = sales.salesterritory.territoryid
// open the VT.
use vt_cust
// obtain a reference to the cursorAdaptor
ca = cursoradaptor()
// generate the SQL for paginating rows
sqlstmt = ca.SqlCommandBuilder(;
"MSSQL", // dbType MSSQL, MYSQL, LIANJA, POSTGRESQL, ORACLE, OTHER
"SELECT", // commandType
"sales.customer", // baseTable
"customerid", // primaryKey
"", // columnNames
"", // columnValues
"", // columnTypes
"", // filter
"11,10", // limit
"", // other
"select sales.customer.customerid, sales.customer.territoryid, sales.salesterritory.name from sales.customer;
inner join sales.salesterritory on sales.customer.territoryid = sales.salesterritory.territoryid") // SQLSelectCommand
? sqlstmt
// The output produced is:
Code:
select * from (select row_number() over (order by customerid) as __rowid,sales.customer.customerid,
sales.customer.territoryid, sales.salesterritory.name from sales.customer
inner join sales.salesterritory on sales.customer.territoryid = sales.salesterritory.territoryid ) a where __rowid > 0 and __rowid <= 10
Tip: You can connect to your ODBC DSN in the console|ODBC Tab and paste the SQL statement in to see what results it retrieves
Q:
It would be handy to have some way of telling the sql generator engine to not add the paging stuff, where one knows that, e.g., only 1 record (if any) will be returned. I was referring (ambiguously) to the paging code. It would be useful to have a switch that said "don't insert the paging SQL changes as they aren't needed here."
A:
various new VT PROPERTIES that can be specified including "pagination=0".
Q:
I'm using the app builder in a windows environment and the data is stored on a Linux machine and accessed via ODBC.
1. How do I verify what indexes are created on a table?
- Using the ODBC console tab doesn't seem to help since it appears to just be creating a temporary table and LIST STAT doesn't show indexes (a .DBX file may or may not exist).
2. How do I create an index for a table?
- I don't know if INDEX on fieldname tag tagname is working (see point number 1).
3. How do I set and use an index when virtual tables are used?
A:
If you are doing it all remotely from the ODBC Console in the App Builder, with the DSN open, you can:
1. Use the SQL CREATE INDEX command to create indexes.
2. Query the SYSINDEXINFO system table to determine which indexes exist, their keys etc. Just use a WHERE clause (the structure is listed on that wiki page) to restrict to the table and/or key you are interested in.
I notice that at least some commands (e.g. list tables) work directory from the odbc console. Based on the time delay, it appears that the command is wrapped in a sqlexec call, and the return is marshalled back to the console screen.
I'm thinking this will be a neat way to make experimental (in a test setting) or crises (n an emergency) data schema changes on an ad hoc basis.
Q:
Preparing SQL Queries is a good way to defeat injection.
But when I change my WHERE clause, that is done through the Requery parameter.
Does that mean that when I reset the WHERE clause, I am unable to prepare the SQL statement?
A:
This appears to be desktop related only.
Web / Mobile uses OData calls for native and virtual tables so manipulating virtual tables using requery() Is not relevant with these unless you are working with custom actions.
If you need to work with multiple queries on the backend, relating them, applying business rules, etc. you would create a function on the backend and call the function from the client with the needed parameters. When you've got the cursor you want, you would then move it to JSON using the SELECT....INTO JSON against the dbf() of the cursor (unless it's a database VT, in which you can select directly against it -- I think) and return that to the JS client.
Reasons to use virtual tables:
1) the ability to switch backends (to mssql, mysql, oracle, Amazon's Aurora, etc.)
2) the ability to scale the database (using a common, clustered backend, with multiple LSQL instances). (Note: this requires high-speed connections, database tuning, etc.)
3) the ability to select a sub-set of records for the user to work with, based on their search criteria.
...am in the process of removing loads of indices or is it indexes, from my tables. Many existed to optimize view performance and for reporting purposes.
If you are going to do reporting the old-fashioned way (SET RELATION), you will need the same indexes to make this happen.
Can you write a web app that works against tables? Yes: been there, done that.. However, as soon as you run into a table with lots of records, you will want to select from among them for displaying to the user: you're right back to virtual tables.
Will it allow you the flexibility you need to scale, or meet customers' needs for a particular backend database? No.
Will it complicate reporting? Yes.
views are a throwback: they were in the product before VT's were created. Lianja rapidly evolves in response to real-world developer needs. That was a big part of my attraction to Lianja, and still is. If views were removed from the product, only the confusion they create in new users learning process would be lost.
Q:
I created 2 vts with "where 1=0" at design time so that the VT are empty in teh DB until the program is running.
So in the section Ready event, I use the lianja("page.section").sql="sql statement without where clause".
But when I run the form, the grid is not filled. When i switched back to design envireonment and re-type the sql statement; the grid is filled with the data.
If I create teh VT without "where 1=0" option, everything is fine, but I would like to keep the VT empty and populate it only at runtime when needed. I dont know if it can be the DB issue, because when I tested the same form using the southwind db I got the same issue.
I'm using single section grid at this time not a parent-child structure.
A:
lianja("page.section").sql="sql.,.."
try
Code:
Lianja.get("page.section").where = "1=1"
If in JavaScript, put a ; at the end of the line.
Works for me (tested in JavaScript -- I did have to put 1=0 in the WHERE of the section, as it appears the empty expression was over-writing the WHERE of the VT, which I had set to 1=0).
Note that the WHERE section attribute is not observed in browser/mobile. I seem to remember this as a known discrepancy, Perhaps someone with a better memory than I can chime in on this.
This is how I create my virtual tables against SQL Server.
create virtualtable <vtname> connstr 'driver={SQL server};server=<servername>;Trusted_Connection=Yes ;database=<dbname>' as select * from <tableName>
Recall that VirtualTables are select statements. You can verify this by tracing (or profiling) your backend.
Your changing of the size of the field will be reflected only in your local cursor.
I decided to trace this to prove it out.
I created a table on my backend with a char(10) field called name and an int field called num.
I created the virtual table as a select * from vtest.
I then locally modified the column to be 15 characters in the App Builder.
I browsed the data and manually modified the column with 'abcdefghijklmn'
The update statement that Lianja sent, ignores (rightly so) the modified field length and uses the length that the base table has.
Code:
update vtest set NAME='abcdefghij' where isnull(NAME,'')='herb' and isnull(NUM,0)=1
I too did need to change the size of a field earlier, so I created a view on my backend with the correct size.
I could also have created the definition at the time I created the virtualtable
create virtualtable.... select left(name,2),num from vtest
Different ways to skin a cat.
create the definition you want in the select statement or in your backend.
Create virtualtable... as select field1 as 'NewFieldName', field2 as 'anothername'
Without a primary key it is not possible to handle any updates but also it is not possible to handle any pagination which will not scale and would be unusable in a web / mobile app scenario. The primarykey should be specified in the VT properties.
This is a core problem if stored procedure calls are used as the VT SQL statement.
When no SQL SELECT is specified Lianja makes use of the basetable property for updates.
It does however need a primarykey as it generates the backend SQL SELECT statement dynamically when fetching a row or a page of rows to display in the Web Client. It is impractical to read millions of rows into a web browser or a mobile device. Lianja therefore reads on demand and the client fetches x rows starting at a specific offset.
This is all done independent of the backend database server. So it handles MSSQL, MySQL, PostgreSQL, LianjaSQL and other SQL syntax.
I create my VT's from straight tables, updateable views or sometimes I create the VT as the result of a stored procedure.
Q:
In my DB I've a VT, based on a ODBC connection to Fox table: VT_Item (itemid, ...., catmercgam)
then I've a local table Item (itemid,......,foto)
I've create a new VT: VT_Item_Item
ODBC connection = local
Key field = ItemID
Properties = updatefieldlist=*,-catmercgam;quotestring='';basetable=item;translate name=false
SQL command: = select item.*, vt_item.catmercgam from item, vt_item where item.itemid = vt_item.itemid
In the section, I need to update "Item" table..
But if I do:
Code:
select vt_item_item
replace foto with "aaaa"
I get error:
mar mar 15 16:39:23 2016
**** Lianja error ****
replace foto with "aaaa"
To work, I need to do:
Code:
select item
seek vt_item_item.itemid
replace foto with "aaaa"
ca = cursorAdaptor("vt_item_item")
ca.requery("item.itemid = vt_item.itemid")
but but it seems a road a bit twisted ..
A:
I haven't created VT with VFP tables, but have with SQL Server.
How about a regular update statement? does that work for you?
something like
update vt_item_item set foto = "aaaa" where itemid = <value>
My thought being this is probable what the ODBC is looking for.
All topics in [Answers] alphabetically:http://www.lianja.com/community/showthread.php?2717-Answers
Reply With Quote
, different from the application drive (C
Bookmarks