[Answers] Table

[josipradnik]

Q:
how to copy table
A:
The WHILE clause causes the scope to be REST. COPY TO with no additional clauses will copy all the records. A FOR clause is also supported - this has a scope of ALL.
I'm assuming you just want to copy the structure and that you have the database and the original table repuk open. From the Console Lianja/VFP tab:

copy to repeu while .f.

If you switch back to the Data Workspace, you should click Refresh from the Additional Commands under Tables|Table Names to refresh the list.

---

Logical fields will be displayed as checkboxes automatically in edit mode. There is also the CheckListView gadget (demonstrated in the example_checklistview App) where you can have multiple checkboxes or a set of radio buttons depending on the 'Allow multiple selections' attribute.
Fields with the 'Choices' attribute set will be displayed as comboboxes allowing the defined choices to be selected.

---

Q:
I try to append the records of one cursor to another
A:

open database southwind
use shippers
select 0
select * from shippers into cursor cursor2
insert into shippers select * from cursor2 // or from &(alias(2))

---

Q:
xxtable use again in 0 alias newAlias
the clause "again" does not work
A:
It depends if table already in use has the records you want, rather than just being a subset of the data. If it does, just select the workarea (this is demonstrated in lianjademo quickreport.rsp and in Library report.rsp). If not, use a virtual table.
... Alternatively, you've just run the report more than once without closing the table in between runs, so it does the equivalent of:

use roster in 0 current
use roster in 0 current

If the table wasn't already USED() and you don't need it afterwards, just issue a USE to close it.

---

Q:

select id, id,name from filterTable where id =        0 OR name Like("sfoggia*")

do not work.. the Like is case sensitive..
A:

select ... where id = 0 OR lower(trim(name)) Like("sfoggia")

---

Q:
Can the console handle an sql query with multiple inner joins?
A:
It should be fine - just remember it needs to be a single line as you can't use the ; continuation in the Console.

open database southwind
SELECT customers.customerid, customers.companyname, orders.customerid,  orders.orderid, order_details.orderid, order_details.productid FROM customers INNER JOIN orders ON customers.customerid = orders.customerid, orders INNER JOIN order_details ON orders.orderid = order_details.orderid

---

Q:
Is it possible to move one table and it's records from one database to another?
A:
With the target database open, from the Console, you could do this:

select * from sourceDatabase!sourceTable into table targetTable

Then just delete/drop the original sourceTable when you are ready.

---

list of tables in a database" then atables() can be used to get the tables in a database or SELECT * FROM systables

---

When using parent / child related sections when you add new records to the child Lianja automatically inserts the parent key value into the child key.
in order to be able to do this the parent key can be a composite key i.e. an expression, but the child key has to be a single column which can contain the evaluated parent key expression.
So you need to create a foreign key column in the child table for that purpose.

---

Q:
How would I structure a page to show a list of searchable criteria that could be entered BEFORE the data is retreived?
A:
You need to build a custom query form as a custom canvas then have some buttons that do the retrieval and then change the datasource on a grid (assuming you want to populate a grid).

---

The onbeforeupdare delegate is called just prior to the record being saved when you click on the save icon or click on another row.

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352

[josipradnik]

Q:
I need to use sqlValues() to get field from table.
sqlValues() work fine, but if I need to parse the resuls, if I've a field containing a "," char (for example an address).. I'm not able to parse the results.. (for examle with explode() function).
A:
You can use an alternative separator character with EXPLODE().

---

Note: ZAP physically deletes all records, so

use mytable
zap

is the equivalent of:

use mytable
delete all
pack

or

delete from mytable
pack

Also note that ZAP resets the table SEQNO.

---

Q:
what function should I use for EscapeSQL below where MyVar may contain special chrs:

s = "select * from MyTable where MyCol = '" + EscapeSQL(MyVar) + "'"

A:
Use str_replace() or strtran(), assign it into a variable and substitute it into your literal string using &varname

---

Q:
it seems that the column/table names are not cases sensitive but the data is:

select * from mytable where mYcoLuMn = "ABC" - works
select * from mytable where mycolumn = "ABC" - works
select * from mytable where mycolumn = "abc" - does not work

A:
The Lianja scripting language is case insensitive.
Other scripting languages do not have built-in / integrated SQL support.
The data is case sensitive just as is the case with other SQL databases.
you need to use functions in your query such as upper() or lower(). Alternatively use macros to substitute in the literal string values.

---

Q:
why this query would work, surely building sql this way should fail with an incorrect table-name (but my tests show that it works fine)?

$db->openRecordset("select * from SyscolumNS")

A:
System tables are generated dynamically from the database schema so the name of these tables in case insensitive.

---

you can scatter the old data values and then compare them to the new values.

scatter name newdata
scatter oldvalues name olddata

You can then get an object that contains the changes using diffobj()

scatter name newdata
scatter oldvalues name olddata
m_changes = diffobj(newdata, olddata)

---

Q:
When you edit a form field, you have the options to accept or reject your change.
I'm assuming the data is stored in some Lianja buffer.
How do you access the the buffered data?
Basically I need to track what change was made (for potential audit purposes) so I would need to know what the data originally was and what is was changed to.
A:
Look at OLDVAL(), CURVAL() and OLDVALUES() in the doc wiki.
Also SCATTER and SCATTER OLDVALUES which you can use with DIFFOBJ() to get the changes as an object which you can serialize using JSON_ENCODE() to store an audit trail.
Also study SET TIMELINE ON, LIST TIMELINE etc which handles audit trails automatically albeit not for VTs which are on a remote machine and are being accessed/updated by applications that it does not know about.

Lianja uses row buffering. A record is updated when the buffer is flushed or when you commit or navigate to another record.
Data from the UI does not magically transfer into the buffers of the embedded database until the field loses focus at which point it will be pushed into the current buffer.
When a record is read, it becomes the "Active record".
There are two buffers. The "old data" and the "current data".
Changes in the UI update the "current data".
When a check for updating a record is made there is an internal flag to denote "dirty" and the contents of the "old" and "new" values are compared.
Thats what oldval() and curval() give you.
The "Changed" delegate is called whenever data changes in the UI. There is a delegate for "Formitem", "section" and "page". Lianja looks up the UI hierarchy to determine which one to call.
This means you can have a one for a section and not have to duplicate it for each formitem in the section. Thats where you would use SCATTER etc.

---

enhanced the SCATTER command so that you can scatter the old data values and then compare them to the new values.

scatter name newdata
scatter oldvalues name olddata

You can then get an object that contains the changes using diffobj()

scatter name newdata
scatter oldvalues name olddata
m_changes = diffobj(newdata, olddata)

---

Q:
Is there a way of getting the column name in the json result set?
So instead of this

[[1,"chai",18,39,false]]

I would get this.

{"ProductID":1,"ProductName":"Chai","UnitPrice":18,"UnitsInStock":39,"Discontinued":false}

gnConnHandle = SQLSTRINGCONNECT("driver={SQL server};........")
strw = "exec "+alltrim(m_db)+"..myproc "+m_driverid
= SQLPREPARE(gnConnHandle,strw,'v_master')
= SQLEXEC(gnConnHandle)
select * from v_master into array aresult
m= json_encode(aresult)
echo m

A:
SQL SELECT into an array currently creates a 2 dimensional array of items, hence the use of [[ ... ]]
If I'm understanding you correctly what you are need is...
select * from v_master into objectarray aresult
so that a one dimensional array of objects is generated.

syntax is as follows.

open database southwind
select * from customers into arrayofobjects aobj limit 2
? json_encode(aobj)

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352

[josipradnik]

Q:
I'm try to duplicate manually my database by code.

use allegati                  
copy structure to c:\temp\allegati_stru

then I close the database and open a new database and I write:

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

use allegati                 
copy structure extended to c:\temp\allegati_stru

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:

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

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:

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:

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:

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.

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:

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

ALTER TABLE purchase_order ADD FOREIGN KEY po_idx1 REFERENCES supplier TAG sup_idx1

Try this:

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:

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.

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.

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.

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.

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:

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:

? select()

you will see printed the number of the workarea.

If you issue:

? alias()

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:

USE 'somename'

To move to IFAISS workarea (its number is 2), I must

SELECT 'IFAISS'
//or
SELECT 2

If I now say

USE

(with nothing), it will close my (currently selected) table

You can also

CLOSE n

---

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

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:

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

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:

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.

&(obj.prop)

---

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:

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.

private m_select = select()

and this at the bottom

select &m_select

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

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:

Use &table

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.

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:

**** 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

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:

flush

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:

use supplier

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:

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:

? select()

you will see printed the number of the workarea.

If you issue:

? alias()

you will see the alias assigned to that cursor, which defaults to the table/vt name.

---

Q:

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

[josipradnik]

I see often that developers have a lot confusion about USE and SELECT.
For example, I would never use explicit:

USE ... IN 1

because I do not know if workarea 1 is free, but 0 ;

USE ... alias .... IN 0

and let the system looks for the next available...and then I will reference it by alias.

---

to clarify what we are talking here about (DESCRIPTION and NAME in table's Column attributes), and how to get one or another:

// column NAME

SELECT COLUMN_NAME FROM syscolumns where ...
CUSTOMERID

// or:

open database southwind
use customers in 0

declare column_names[fcount()]
afields(column_names)
?column_names(1,1) && beware of 2-dim array
CUSTOMERID

//?column_names(2,1)
//?column_names(3,1)

// caption, or DESCRIPTION


declare description_caption[fcount()]
adesc(description_caption)
?description_caption(1) && beware of 1-dim array
Customer ID


//?description_caption(2)
//?description_caption(3)

---

A:
Have you tried

SELECT * FROM syscolumnconstraints

to get all such table column names.
I would play with this sql outputing into file:

 SELECT 'alter table "'+alltrim(table_name)+'" drop constraint ('+alltrim(column_name)+' '+alltrim(constraint_name)+')'  FROM syscolumnconstraints

Then execute it, like script in MS SQL Server.

That means you can inside ALTER TABLE...

...ALTER CONSTRAINT (<column> <constraint> [,...])
Change the specified constraint or constraints for the specified column or columns.

...ALTER CONSTRAINT <column> [SET] <column constraints>
Change the specified constraint or constraints for the specified column.

...ALTER CONSTRAINT <column> DROP <column constraints>
Delete the specified constraint or constraints for the specified column.

...DROP CONSTRAINT (<column> <constraint> [,...])
Delete the specified constraints for the specified column or columns.

...DROP <table constraint> [,...]
Delete the specified table constraint or constraints.

...MODIFY (<column> <datatype> [<column constraints>] [,...])
Change the specified column or columns.

...MODIFY COLUMN <column> [SET] <column constraints>
Change the specified constraint or constraints for the specified column.

...MODIFY COLUMN <column> DROP DEFAULT
Delete the DEFAULT constraint for the specified column.

...MODIFY CONSTRAINT (<column> <constraint> [,...])
Change the specified constraint or constraints for the specified column or columns.

...MODIFY CONSTRAINT <column> [SET] <column constraints>
Change the specified constraint or constraints for the specified column.

...MODIFY CONSTRAINT <column> DROP <column constraints>Delete the specified constraint or constraints for the specified column.

---

Q:

select temp_artrans
use
use temp_artrans exclusive
delete from temp_artrans
pack
use temp_artrans

Using this code, it seems that table temp_artrans is still in exclusive use because only one client PC can access it. To the other PC on the network error message 'temp_artrans.dbf not found'.
it worked in one pc but in the other client pc it didn't worked. maybe because the table was used exclusively in one pc so the other pc cannot access the other table.
A:
I will explain how I read your code.

select temp_artrans // cursor is open before, I just set my focus on it
use // I close it
use temp_artrans exclusive // I open it again, but EXCLUSIVELY , so no one can touch it besides me
delete from temp_artrans // I deleted all records
pack // I want physical purge the cursor
use temp_artrans // I try to open already opened cursor, without closing it after (exclusively) packing. Not sure what is the effect, maybe it is ignored.

Maybe you need to reconsider whole thing, Why you are not trying with real temporary table?
If you want individual users to have their own temporary table, then use TMPNAM() or SYS(2015) along with COPY STRUCTURE or COPY STUCTURE EXTENDED + CREATE FROM.

In that case the user who need to, can exclusive use it and pack it. Colliding with no one in multiuser environment.

If it is not a real temporary table (maybe you need it for data bound in a section), then you can add a field to its structure which will contain user's ID and every user will be responsible to delete his own records. And see his own records only by filtering.
This is what I meant with reconsidering whole approach.

---

Q:

private m_select
m_select = select()
use ar_invh in 0
select ar_invh
APPEND BLANK
REPLACE Sninv_No with sninvno 
replace Inv_No with invno
replace form_type with formtype
...
...
use
select (m_select)

A:
The error was referring to the first line of code writing to the table so I thought there was a problem with how I was accessing the table. In actuality it was a data mismatch on a date field further down in my code that I had recently added. After I stored the date as character, everything worked.

---

Q:
I have 3 fields but they have been populated with (Memo) in blue. Is that normal
A:
Any varchar column will be "memo". Any character fields above the 255 limit will be treated as memos.

---

Remove the 'EXCLUSIVE' keyword from the USE command.

You cannot run PACK on a shared table.
If you want individual users to have their own temporary table, then use TMPNAM() or SYS(2015) along withCOPY STRUCTURE or COPY STUCTURE EXTENDED + CREATE FROM.

---

Q:
It looks as though there isn't a dedicated datatype for guids so what would be the best db type for this, varchar(36)?
A:
char(36)

---

Q:
if I run:

Alter table mytable add column mycol varchar(50)

Then the column is created with type 'Memo' (as seen by 'list dictionary'). If I open the column in the app builder's column editor then it shows as 'Varchar' - huh? are varchar and memo the same thing?
The app builder column editor certainly doesn't have as many datatypes as the list of supported types.
A:
Yes, memo and varchar are the same. The column width is ignored on varchar, it is variable length with only a pointer stored in the main table and the contents stored in the dbt file.
Yes, varchar and long varchar are the same as Memo and are all variable length and stored in the dbt. Char is fixed length.

---

Q:
After typing several commands in the console I have ende up in this situation several times:

use purchase_order

list dictionary

output lists purchase_order.dbf info. Then:

use supplier

Gives Error:

Thu Jan 28 14:28:40 2016
**** Lianja error ****
use supplier
^
ALIAS name 'SUPPLIER' already in use

A:
The "ALIAS name 'SUPPLIER' already in use" error is saying that 'supplier' is already open in another workarea. If you

select supplier

this will select the correct workarea.
I probably should have said 'cursor' instead of 'workarea' (old habits die hard).

---

Q:
I've a table with a Autoinc field .
I've created a sample app to update the DB structure on production DB.
After update the table structure, i do a "Append from" the old table.
So, i lost the original autoinc field.... with a new one..
Is possible to disable it?
I can "set" the "default"?
I've not find any ALTER COLUMN <column> SET DEFAULT "seqno()"...
A:
ALTER TABLE

Q2:
retrive a field caption from the table structure..
A2:
FIELDINFO()
ADESC()

---

Q:
Which sql is lianja sql compatible with?
As per an earlier post I am creating tables programmatically through the Lianja ODBC connection and I get errors when using this ansi sql:

ALTER TABLE test2 ADD FOREIGN KEY (TEST_ID) REFERENCES test(ID)

A:
VFP + many extensions.

---

Q:
I am writing a db upgrade routine that loops through json files and creates/updates the lianja db as required. All seems to be going ok (I have only done simple table creation and column create/alter so far) but I always get errors about transactions being in progress as below.
I initially wrapped my whole process in a transaction but then removed it just to keep things simple so the error below is being generated without 'begin transaction' appearing anywhere in my app. Also, this happens randomly ie. it sometimes happens after two tables are created then on the next run it may happen after six.

I am certain that I haven't started the transaction so I wondered if perhaps some internal transaction could be active that is causing the problem?

**** Lianja error ****
columnexists(dbName, tableName, columnName)
^
Command illegal when transaction in progress
Called from procedure - createaltertable at line 250
Called from procedure - dodbupgrade at line 61
Called from procedure - page1_section2_field1_click at line 7

A:
You must have been messing with BEGIN TRANSACTION and had errors in your code. No, there are no internal transactions that would affect this.

RESET TRANSACTION can be used to reset the transactions which are flagged in the headers of the tables.

---

Q:
Is there a way (a method or function) to get a list of column captions from a CSV list of field names?
A:
syscolumnconstraints()
You may also want to look at the fieldinfo() function.

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/showthread.php?2717-Answers

[josipradnik]

Q:
what command I use to see if a table is open and what cursors are in use. I tried used("table"), but it did not appear to work.

A:
Yes, USED("alias-name" | cursor-number), specifying the alias name or the cursor/workarea number - returns .T. or .F.
Also INUSE("alias-name") - returns .T. or .F.
Or SELECT("alias-name") - returns 0 or the cursor/workarea number if open.
Did USED() return an error or just not the result you were expecting?

---

Let's say you want to encrypt a table. In Lianja this uses DES3 encryption. This uses 3 keys which, together, provide quite decent security. To use it securely, and agilely, you will want to avoid putting the actual keys in your code. There are two pieces of information you need to have in mind:
1. each key is a max length of 8 chars
2. to use the keys contained in a .h file and #included you will need to macro-expand them. This is different than VFP, where bracketing them in square brackets [ and ] macro expands them, even inside a string. In Lianja it goes like this (for #define's vars of Key1, Key2, and Key3):
encrypt mytable key "&(key1),&(key2),&(key3)"

The nice part of doing it this way, rather than defining a variable, is that examining memory will give the observer no clue as to the actual keys. Which observer? That's why we concern ourselves with security -- we don't know who that might be.
This particular use is for a table used to store connection strings across multiple applications in one project. The name method of using &() to macro-expand within strings can be used to create the connection string without ever creating a memory variable holding the connection string.
Now, the astute observer will note that the .dbo file does in fact contain the key1, 2 and 3 information at the top of the file (where the #include was). True enough: if the bad guys get on your server, your security is toast. Which is why a) mobile apps can be more secure than desktop apps, especially if b) you configure your network with security in mind (I am no expert on the matter, but the essentials are easy to understand).

---

Q:
On a page with a canvas section with controls (TextBox, etc.) bound to a fields from a record on a table in my database, if the user makes changes to the data and then chooses to revert (not save the changes), what can I do to roll back the changes and not commit them to the table in the database?

A:
needed to call the Cancel() method before leaving the page. Apparently, just leaving the page without calling the Cancel() method automatically commits the data.

A2:
Lianja uses record buffering which means changes will be automatically committed if you move off the current record while editing. Closing the page would move the record pointer and would, therefore, commit any changes.

---

Styling desktop BROWSE
I've had a few folks asking me how to style grids and BROWSE. I've added support for CSS styling of BROWSE in Lianja 3.1.
As stated in the roadmap:

• Added STYLE "text" as a BROWSE keyword. The "text" should be CSS. It may be a reference to a filename.css containing the CSS. This may be prefixed with "app:/filename.css" or "lib:/filename.css" which causes the file contents to be read and applied as CSS.

Grid sections can be styled already. See CSS below.

open database southwind
use example
browse noactionbar ;
            style "* { selection-color:yellow; ;
                          selection-background-color:black; ;
                          gridline-color:green; ;
                          background:black;color:yellow; ;
                        } ;
                        QTableView::item { background:lightgreen; } ;
                        QHeaderView::section { background-color:green;color:white;border:0px; } "

---

Custom VFP Section and use the "Browse" command.
Desktop client only. It's VFP browse embedded in a page.

---

Use SET KEY to limit the range of records you can access in a table. The table must be indexed, and the index key value or values you include must be the same data type as the index expression of the master index file or master tag.
Issue SET KEY TO without any additional arguments to restore access to all records in the table.
SET KEY TO [eExpression1 | RANGE eExpression2 [, eExpression3]] [IN cTableAlias | nWorkArea]

---

---

All topics in [Answers] alphabetically: https://www.lianja.com/community/sho...ll=1#post13748

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.com/category/table/