Q:
I'm try to duplicate manually my database by code.
Code:
use allegati
copy structure to c:\temp\allegati_stru
then I close the database and open a new database and I write:
Code:
sele 0
create allegati from c:\temp\allegati_stru
I get the error:
create allegati from c:\temp\allegati_stru
^
Illegal data format in FROM file
A:
if I want to duplicate a database in code (as opposed to a table) I would use the command:
copy database
Code:
use allegati
copy structure extended to c:\temp\allegati_stru
Code:
CREATE FROM a STRUCTURE EXTENDED file not a STRUCTURE file.
Q:
What is the difference between seqno and auto-incriment
A:
They both provide similar functionality. Seqno() existed before autoinc was added for VFP compatibility.
Autoinc happens when you insert a new record on the server. In desktop apps the database engine is embedded. So yes it is handled in the database engine. So therefore it is implemented in the cloud server. It is not client UI functionality.
If you are adding a record and you want an auto generated unique id for a record you would be better off having a server side procedure as the default expression. That server side procedure can use seqno() or guid().
There is is a functional difference between desktop and web/mobile as the latter are client/server.
Developing for client/server is mainly done for you if you follow best practices.
In the case of autoinc you would not include the autoinc field on the form, make it read only, or generate it via a default as I previously described.
Unlike many other databases Lianja records have an implied unique rowid also.
When following best practices and visually constructing pages out of related sections adding child records automatically maintains the parent child relationships by evaluating the parent key and inserting it into the child section. That's what you use the relationship builder for.
I did already advise you to use a server side proc as your default expression. This will need to open the database, the table, then return the seqno().
Using autoinc and/or seqno() on the web/mobile client makes no sense whatsoever as the database engine is not running on the client it is running on the server so there is no database/table context to get a seqno().
I'm puzzled by what you aim to achieve. In any SQL database the autoinc is stored in the column at the time of the SQL insert not when you switch into add mode in a UI form.
In another question (ticket or forum I can't remember) you asked how to exclude a column from a form section. Just remove it from the form. It will still be populated in the record at SQL insert time.
for that use case I would remove the default from the table and specify a default expression of a server side stored proc. This way it will be displayed when you switch into add mode in the UI.
Q:
I queried syscolumns through ODBC and that returned quite a bit of column info, I am battling with the two issues below:
1. COLUMN_DEF - (I assume this is the column default value) this seems to be empty ie. I'd expect to see seqno() for a column where that is defined
2. The table definitions 'Auto-Inc' property doesn't appear to be in this table, where can I find this?
A:
From the console:
Code:
open database yourdb
use yourtable
list dictionary
if you want to use ODBC rather than the app builder itself then write a stored procedure to create the resultset for all columns in a given table.
Q:
I want to delete all records in a table and set exclusive to on
Code:
select artrans
set exclusive on
delete from artrans
pack
But every time I run the project an error prompt says
Exclusive use of table is required.
A:
Code:
set exclusive on
use artrans
zap
Added a new SQL command TRUNCATE TABLE tablename [IF EXISTS]. This is the equivalent of USE tablename followed by ZAP.
Q:
In command window,
I have a table with 50.000 addresses, I open it with: use address, set order to tag name (index on name tag name).
Then; Brow for name="A" , this gives a correct browse with all records which start with the "A" in it.
Brow for startswith(name,"A") , correct results to. same.
seek "A" gives eof(), not found() ????????
locate for name="A", gives also eof() ??????
A:
SET STRCOMPARE ON is now on by default in the App Builder and the runtime App Center. This is now consistent across Lianja desktop Apps, Lianja Cloud Server, and Lianja SQL Server.
Look SET STRCOMPARE up in the documentation wiki.
When ON all string comparisons are case insensitive and are trimmed. This provides EXACT matching.
This is the way mySQL, MSSQL and other SQL databases operate and it was requested that we turn this ON for consistency.
If you don't want that functionality then set it OFF but you will need to rebuild your indexes as they will not be case insensitive.
Q:
Is there any way to view the final sql that is generated by an execute command? When I do this:
Code:
sql = 'alter table ? add column ? ? '
prepare stmt from :sql
execute stmt using :tableName, :columnDef.name, :colDefSql
I get this:
**** Lianja error ****
execute stmt using :tableName, :columnDef.name, :colDefSql
^
Data type was expected
but I have double-checked my variable and they seem to be correct, the final sql I expect is:
[CODE]
alter table TEMPLATE add column ID varchar(36) not null
[/CODE}
A:
Code:
tablename = "TEMPLATE"
columnName = "ID"
colDefSql = columnName + " char(36) not null"
sql = 'alter table ? add column ? '
prepare stmt from :sql
execute stmt using :tableName, :colDefSql
trying to create an alter table statement which really just needs created using simple macro substitution rather than PREPARE and EXECUTE which is not really suitable for that purpose.
parameter substitution for numerics, dates etc is handled differently. The square brackets are actually alternate string delimiters.
You don't need to use prepare and execute as you have full runtime command line macro substitution. This provides powerful dynamic scripting.
Code:
cmd = "select * from customers"
&cmd
A useful function to use for this type of thing is sprintf()
http://www.lianja.com/doc/index.php/SPRINTF()
A2:
in similar situations I used textmerge() for short SQL statements and text...endtext with the "merge" and "noshow" parameters for longer sql statements.
In both cases I am setting a variable, e.g., lcSQL, which I can then view, store, etc.
Q:
FK's need to work on indexes (where I am accustomed to adding them to columns). This example may look awkward but it shows what I want to achieve:
Code:
CREATE TABLE supplier (SuppId i PRIMARY KEY, SuppRef c(10) UNIQUE, SuppName c(40) UNIQUE)
CREATE TABLE purchase_order (POId i PRIMARY KEY, PO_SuppRef c(10), POtotal n(10,2))
CREATE INDEX sup_idx1 ON Supplier (SuppRef)
CREATE INDEX po_idx1 ON purchase_order (PO_SuppRef)
ALTER TABLE purchase_order ADD FOREIGN KEY po_idx1 TAG po_fk1 REFERENCES supplier TAG sup_idx1
But this gives an error about 'TAG' being an unrecognized phrase. Please help with the code to join
purchase_order.PO_SuppRef -> supplier.SuppRef
A:
I don't believe you need the tag name of the parent table.
Try this
Code:
ALTER TABLE purchase_order ADD FOREIGN KEY po_idx1 REFERENCES supplier TAG sup_idx1
Try this:
Code:
CREATE TABLE supplier;
(SuppId int PRIMARY KEY, SuppRef char(10) UNIQUE, SuppName char(40) UNIQUE)
CREATE TABLE purchase_order;
(POId int PRIMARY KEY,;
PO_SuppRef char(10) FOREIGN KEY REFERENCES supplier TAG SuppRef,;
POtotal num(10,2))
Note: UNIQUE creates a Tag index with the same name as the field.
Also, using in-built Lianja UI functionality such as the Relationship Builder, Instant Search, Instant Selections and Grid sorting indexes are generated and selected automatically.
Can you try this in the meantime:
Code:
ALTER TABLE purchase_order;
alter column PO_SuppRef REFERENCES supplier TAG SuppRefcreate index PO_SuppRef on purchase_order (PO_SuppRef)
A2:
Due to the way the SQL was made compatible with VFP the clauses specified may not create the desired effect as with MySQL and MSSQL.
I have tidied this up in v2.1Beta4.
When only the FOREIGN KEY cause is specified creates a foreign (non-primary) index for the table.
Code:
ALTER TABLE purchase_order ADD FOREIGN KEY (po_suppref) [TAG name]
Specifies the parent table to which a persistent relationship is established on a particular column. The column is that which is specified for the FOREIGN KEY clause.
Code:
ALTER TABLE purchase_order ADD FOREIGN KEY (po_suppref) REFERENCES supplier TAG sup_idx1
Specifies the parent table to which a persistent relationship is established on a particular column. The column is that which is specified by the ALTER COLUMN clause.
Code:
ALTER TABLE purchase_order ALTER COLUMN po_suppref REFERENCES supplier TAG sup_idx1
I trust this simplifies the use of foreign key constraints.
Now saying all this and to not scare off any less technical developers I want to point out that relationships between sections (containing tables), referential integrity and cascading deletes is all handled automatically for you without having to code anything like this.
If all you want to do is specify REFERENCES then use the ALTER COLUMN syntax.
Q:
what is a workarea?
A:
a workarea tracks which table is currently open and selected.
Here is some code I've used so that it avoids this error.
Code:
if inuse("supplier") = .T.
SELECT supplier
else
select 0
use supplier
endif
I probably should have said 'cursor' instead of 'workarea'
A workarea is a numbered item in set of available slots for opening cursors. The size of that set varies with development platform. We don't usually see the numbers (something that dates back to the dBase days, at least). But when we issue the following:
Code:
use suppliers in 0 current
we are really telling the system: "Hey Joe, find an empty workarea for me to open a cursor, and then make it the current one I'm using."
If after that command you issue:
you will see printed the number of the workarea.
If you issue:
you will see the alias assigned to that cursor, which defaults to the table/vt name.
select() can be used to store the current workarea so it can be returned to after code has gone somewhere else and done something else:
lnSelect = select(0) && without the 0 works the same way -- that's a habit from the early version Fox days when it made a difference)
... go do your workselect (lnSelect)
USE is for initial opening (one-time for every table).
SELECT is for moving among workareas (to set only one of them as 'current' or active).
Some commands work on 'current' workarea, without mentioning name or number of it.
USE without parameters closes currently selected one.
Everyone was once initially open with:
To move to IFAISS workarea (its number is 2), I must
Code:
SELECT 'IFAISS'
//or
SELECT 2
If I now say
(with nothing), it will close my (currently selected) table
You can also
Q:
I am looping and creating many tables using 'create table' and 'alter table add column', nowhere in my code do I use 'use' but after several tables have been created I get the error 'No more work areas available'.
A:
Yes that is compatible with VFP. Close it if you don't want it open after the create table.
Q:
If you USE table1 and then USE table2 which is a structural copy of it, calling
Code:
INSERT INTO table1 SELECT * FROM table2
Will stop you from being able to call USE table1 AGAIN.
You can reproduce this in the Lianja/VFP console using the Southwind datbase with the following:
Code:
OPEN DATABASE southwind
USE productsbyname
COPY STRUCTURE TO productsbyname_copy
INSERT INTO productsbyname_copy values(111,"zyx",22,33)
CLOSE ALL
CLOSE DATABASE
OPEN DATABASE southwind
USE productsbyname
USE productsbyname_copy
INSERT INTO productsbyname SELECT * FROM productsbyname_copy
USE productsbyname AGAIN
The first half is only to create an exact copy. That works fine. If you already have a structural copy of a table sitting somewhere else, you can use that instead and see that it's the second half of the above code that causes problems.
The error output is:
Fri Feb 5 10:56:30 2016
**** Lianja error ****
USE productsbyname AGAIN
^
File 'productsbyname.dbf' does not exist
A:
You need to
Code:
USE table name IN 0
to open tables up in their own cursor.
What you are doing is using one cursor. The AGAIN should be being ignored if its the same cursor
Q:
When I use the following code:
Code:
Create Cursor MyCursor (EmpName c(40))
Append Blank
x="EmpName"
Select MyCursor
Replace Next 1 (x) with "John Smith"
The error "Field variable was expected" is generated at the first parenthesis of the Replace statement.
I assume Lianja doesn't support VFP name expressions for field names in the Replace command. What are the suggested workarounds for this?
A:
Just use a macro &x
Note that macros do not work in the console only in scripts.
Q2:
In the case where the named expression is a field on an object (like oData.MyField), since macro substitution produces an error in that case, is there a better workaround than just assigning the value of oData.MyField to a new variable and then doing macro substitution with the new variable?
A2:
In Lianja macros can be expressions too.
Q:
I am receiving an error at the end of a procedure appending a new record and writing data from several memory variables. I have included a section of code below and searched through the app and cannot find this statement. Any thoughts on what might be causing it or the most logical place to look to find it.
**** Lianja error ****
LOWER(AD_ADDR.ID)+SYNCNUM
^
Variable was expected
Called from procedure - transferheader at line 318
Called from procedure - generate_header_continue_click at line 298
Procedure code:
Code:
proc transferheader()
save datasession
use ar_invh in 0
// go bottom
store inv_no to invlno
sninvno = 0
APPEND BLANK
REPLACE sninv_no with sninvno //remove at some point
REPLACE inv_no WITH invlno
REPLACE us_gov WITH usgov
...
use
restore datasession
endproc
A:
Remove the save and restore datasession. You don't need them as you are opening a new table appending a record then closing it.
In this case you can put this at the top.
Code:
private m_select = select()
and this at the bottom
or alternatively use a one line SQL insert command.
You are just saving the currently selected cursor and restoring it as the currently active cursor. It's good practice when opening new tables like that in a delegate as the cursor context may change.
Q:
I just have a simple program creating adding record in a table.
I used a canvas sections with text boxes and checkbox.
The database is opened on the load() event of the form and the table is opened when the addbutton is cliked.
when the two instructions are executed
Code:
select 1
use tache shared (table)
append blank
I get an error message on the name of the table (tache) "field variable was expected"
Even if I moved the code in an other section, I'm getting the same error.
A:
Look in the \lianja\error\error.mem file for more information.
Also, your database will be opened when the app loads you do not need to manually open it. This seems like unnecessary coding.
in the app builder tables are opened exclusive and in the app center runtime they are opened shared automatically.
In your case you may need to put the shared keyword after the table name.
Are you aware that Lianja will handle data binding and automatically open the database and tables for you? You do not need to do this manually.
What is in the variable "table"? Try changing the name of that variable too.
It sounds like you have several instances of the app builder running. Look in task manager.
Perhaos you have an index on sonething that does not exist.
run these command {below) from the VFP console with your database already opened, The answer might just pop out at you.
Code:
select * from sysfieldinfo where table_cat = "gesavoc" and table_name = "tache"
select * from sysindexinfo where table_cat = "gesavoc" and table_name = "tache"
select * from syscolumnconstraints where table_cat = "gesavoc" and table_name = "tache"
select * from systableconstraints where table_cat = "gesavoc" and table_name = "tache"
select * from sysversioncolumns where table_cat = "gesavoc" and table_name = "tache"
select * from sysprimarykeys where table_cat = "gesavoc" and table_name = "tache"
Q2:
I received errors for the 1st and 4th commands:
Code:
**** Lianja error ****
select * from systableconstraints where table_cat = "gesavoc" and table_name = "tache"
^
Fatal I/O error reading record 1 from table - errno 2
A2:
systableconstraints error suggests something wrong with your table triggers. Copy, and then erase, all the prgs in the gesavoc database directory (dbo's also). The Rebuild the database. Then see if you have an error.
SysVersionColumns inexplicably (to me -- there may be a cogent explanation) has no table_cat field.
The reason I gave you the list of commands is because, if it was not your index being the problem, these are the other places you would look. It appears that something in the table triggers many be an issue.
Which suggests the base database directory has been altered.
Q:
I have read all the form material but so far havent found the way to properly replace grid fields with new grid data
My grid is sourced to a data file. I do the updates in the data file and then a skip 0 and refresh of the grid.
The specific code is as follows
Code:
Testlu1() &&testlkup8b() && &&LKUPL2('','L') &&DO LKUP2
debugout MSTOCK
debugout invdet.STOCK
ENDI
SELE (DETAIL2)
REPLA stock with INV.stock,;
invdet.unit with iif(invdet.unit=' ',iif(INV.unit=' ','EA',INV.unit),invdet.unit),;
invdet.act with iif(EMPT(INV.act),MATCD,INV.act),invdet.COST WITH INV.COST
SKIP 0
GRID1.REFRESH
DEBUGOUT INV.COST
DEBUGOUT INV.RETAIL
DEBUGOUT INV.UNIT
debugout 'REPLACE'
RETU
The result is that the stock field which is the current grid field in focus when my valid is called gets updated but none of the other fields chg.
A:
Your replace is attempting to replace values in two different cursors (detail2 and invdet). Keep to one cursor target per replace statement.
you are not doing a skip 0 in the invdet cursor so the buffer is not being flushed.
skip 0 only works in the active cursor not all...
The following command will force write all dirty buffers:
instead of skip 0
do a FLUSH afterwards to flush all work areas.
System tables like sysindexinfo operate on databases, not directories. Ask your Centos system administrators if they can create a symbolic link to your directory from the /opt/lianja/server/data/ directory, e.g.
ln -s /home/cory/test/testdata /opt/lianja/server/data/testdata
That way, you will be able to use sysindexinfo, but will not need to change your DSN.
Q:
Gives Error:
Thu Jan 28 14:28:40 2016
**** Lianja error ****
use supplier
^
ALIAS name 'SUPPLIER' already in use
A:
A workarea is a numbered item in set of available slots for opening cursors. The size of that set varies with development platform. We don't usually see the numbers (something that dates back to the dBase days, at least). But when we issue the following:
Code:
use suppliers in 0 current
we are really telling the system: "Hey Joe, find an empty workarea for me to open a cursor, and then make it the current one I'm using."
If after that command you issue:
you will see printed the number of the workarea.
If you issue:
you will see the alias assigned to that cursor, which defaults to the table/vt name.
Q:
Code:
95 // Transfers line items ofinvoices from ar_invl to il_all, the complete invoice history file
96 if .not. used('il_all')
97 use il_all in 0
98 endif
99 select il_all
100 goto bottom
101 APPEND FROM ar_invl
102 use
A:
Your command that fails is onb line 102. The reason is tails is that on line 101, you are attempting to APPEND FROM a table that is open. That error is closing the current workarea: this seems to a common action when a table-related error occurs.
From the APPEND FROM Wiki article:
"If the FROM file is another Lianja table, it cannot be open and active at the time of the APPEND."
Remember: USE only once, SELECT many times.
OPEN is reserved for DATABASE, so when you meet a table for the first time, you "open" it with USE (you can not use OPEN syntax)
After that you can switch from one to another with SELECT.
There is no need to "get back" to previous one: you point to next cursor you want.
SELECT order is freely, but be sure that you are pointing to correct cursor.
If you CLOSE currently activated cursor, you will be left with no activated cursor.
All topics in [Answers] alphabetically:http://www.lianja.com/community/showthread.php?2717-Answers
Bookmarks