Multiuser [examples]

Determines whether or not table and index files are opened shareable

set exclusive off
open database southwind
use customers

https://www.lianja.com/doc/index.php/SET_EXCLUSIVE


Determines whether or not table and index files are opened shareable

set share on
open database southwind
use customers

https://www.lianja.com/doc/index.php/SET_SHARE


Release active table and record locks

set exclusive off
use patrons index events, dates, names
seek "PHANTOM"
do while not rlock()
    sleep 2
enddo
// ...
unlock

https://www.lianja.com/doc/index.php/UNLOCK


Locks a table to control access by other processes

LOCK TABLE staff IN SHARE MODE

https://www.lianja.com/doc/index.php/LOCK_TABLE


Lock file
do while not flock()
    sleep 2
enddo

https://www.lianja.com/doc/index.php/FLOCK()


Lock record

do while not lock()
    sleep 2
enddo

https://www.lianja.com/doc/index.php/LOCK()


Lock record

do while not rlock()
    sleep 2
enddo

https://www.lianja.com/doc/index.php/RLOCK()


Release all file and record locks

clear locks

https://www.lianja.com/doc/index.php/CLEAR_LOCKS


Test if current record is locked

? islocked()
.T.

https://www.lianja.com/doc/index.php/ISLOCKED()


Test if current or specified record is locked by the current process

? isrlocked(13,2)
.T.

https://www.lianja.com/doc/index.php/ISRLOCKED()


Lock status of current record

?lksys(0)
11:30:20

https://www.lianja.com/doc/index.php/LKSYS()


Lock information about a specified file

? lockinfo("/opt/lianja/data/southwind/example.dbf")
root(8053) /dev/pts/3 shared
? lockinfo("/opt/lianja/data/southwind/example.dbx")
root(8053) /dev/pts/3 shared

https://www.lianja.com/doc/index.php/LOCKINFO()


Access status for a table

if not exclusive()
    dialog box "Command not available."
endif

https://www.lianja.com/doc/index.php/EXCLUSIVE()


Lock a named resource

<%@ Language=VFP %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<body>
<%
unlockResource("LianjaCloudServerRequestLoop")
lockResource("LianjaCloudServerRequestLoop", "exclusive")
 
// Once the exclusive lock has been granted carry out required operations
// ...
// Then release the lock to unblock other requests
unlockResource("LianjaCloudServerRequestLoop")
%>
</body>
</html>
 
// Custom named resource:
lockResource("myresource", "exclusive")
 
// Resources can be unlocked individually by name:
unlockResource("myresource")
 
// Or all previously locked resources can be unlocked:
unlockresourceall()

https://www.lianja.com/doc/index.php/LOCKRESOURCE()


Unlock a named resource previously locked

<%@ Language=VFP %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<body>
<%
unlockResource("LianjaCloudServerRequestLoop")
lockResource("LianjaCloudServerRequestLoop", "exclusive")
 
// Once the exclusive lock has been granted carry out required operations
// ...
// Then release the lock to unblock other requests
unlockResource("LianjaCloudServerRequestLoop")
%>
</body>
</html>
 
// Custom named resource:
lockResource("myresource", "exclusive")
 
// Resources can be unlocked individually by name:
unlockResource("myresource")
 
// Or all previously locked resources can be unlocked:
unlockresourceall()

https://www.lianja.com/doc/index.php/UNLOCKRESOURCE()


Unlock all resources previously locked 

<%@ Language=VFP %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<body>
<%
unlockResource("LianjaCloudServerRequestLoop")
lockResource("LianjaCloudServerRequestLoop", "exclusive")
 
// Once the exclusive lock has been granted carry out required operations
// ...
// Then release the lock to unblock other requests
unlockResource("LianjaCloudServerRequestLoop")
%>
</body>
</html>
 
// Custom named resource:
lockResource("myresource", "exclusive")
 
// Resources can be unlocked individually by name:
unlockResource("myresource")
 
// Or all previously locked resources can be unlocked:
unlockresourceall()

https://www.lianja.com/doc/index.php/UNLOCKRESOURCEALL()


 

Advertisements

CursorAdapter [examples]

Return a reference to the internal CursorAdapter object for the current or specified ODBC data source in ‘use’ as a table
CURSORADAPTER
CURSORADAPTOR

 

use sales.currency connstr "awhr"
oCa = cursoradapter()
? oCa.alias  // CURRENCY
? oCa.datasource  // awhr
? oCa.datasourcetype  // ODBC

https://www.lianja.com/doc/index.php/CURSORADAPTER()

https://www.lianja.com/doc/index.php/CURSORADAPTOR()


Cursor [examples]

Select a cursor
SELECT
select a
use patrons index events, dates alias pat
select b
use addresses index addr_names alias add
select pat
? add.state

https://www.lianja.com/doc/index.php/SELECT


Frees up all system resources allocated to a cursor
DROP CURSOR
DROP CURSOR accounts

https://www.lianja.com/doc/index.php/DROP_CURSOR


Closes a cursor
CLOSE
CLOSE accounts

https://www.lianja.com/doc/index.php/SQL_CLOSE


Fetches rows returned by the SELECT statement defined in a CURSOR
FETCH
// Declare the cursor to retrieve records from the accounts table
DECLARE accounts;
  CURSOR FOR;
  SELECT name, address, ord_value, balance;
  FROM accounts;
  ORDER BY name 
 
// Open the cursor and establish a temporary set of records
OPEN accounts
 
// Retrieve each row from open cursor
DO WHILE sqlcode = 0
   FETCH NEXT accounts;
   INTO m_name, m_address, m_ord_value, m_balance
ENDDO 
 
// Close the cursor
CLOSE accounts
 
// Free up any resources used for the cursor
DROP CURSOR accounts

https://www.lianja.com/doc/index.php/FETCH


Opens a pointer to a logical table
OPEN
OPEN accounts

https://www.lianja.com/doc/index.php/OPEN


Declares a pointer to a logical table
DECLARE CURSOR
// Declare the cursor to select records from the accounts table
DECLARE accounts;
  CURSOR FOR;
  SELECT name, address, ord_value, balance;
  FROM accounts;
  ORDER BY name

https://www.lianja.com/doc/index.php/DECLARE_CURSOR


Creates a temporary table
CREATE CURSOR
CREATE CURSOR tempstaff;
  (staff_no CHAR(6) NOT NULL,;
  lastname CHAR(15) NOT NULL,;
  firstname CHAR(10),;
  hiredate DATE,;
  location CHAR(15),;
  supervisor CHAR(6),;
  salary DECIMAL(6,0),;
  picture VARBINARY,;
  history LONG VARCHAR,;
  commission DECIMAL(4,1))

https://www.lianja.com/doc/index.php/CREATE_CURSOR


Closes a cursor
CLOSE
CLOSE accounts

https://www.lianja.com/doc/index.php/CLOSE


Current or specified cursor/workarea number
SELECT
close all
? select()
         1
use prefixes in 3
? select ("prefixes")
         3
? select(0)
         3
? select(1)
       100
 
// Another Example
// to access each open workarea
m_count=0
select 1
do while not empty(alias())
  m_count = m->m_count + reccount()
  select select() + 1
enddo
dialog box "Total records in all tables is &m_count"

https://www.lianja.com/doc/index.php/SELECT()


The lowest available workarea number
WORKAREA
close databases
select 1
use state.rdb
? workarea()
         2
? select()
         1

https://www.lianja.com/doc/index.php/WORKAREA()


To determine if a table is open
USED
? select()
         1
? used()
.T.
? used("products")
.T.
? used(5)
.F.

https://www.lianja.com/doc/index.php/USED()


Load the alias name and cursor/workarea number of all open tables into an array
AUSED
? aused(alianjademo)
         7
? alianjademo
Array (refcnt=1)
(
    [1,1] = CUSTOMERS
    [1,2] = 1
    [2,1] = ORDERS
    [2,2] = 2
    [3,1] = ORDER_DETAILS
    [3,2] = 3
    [4,1] = EMPLOYEES
    [4,2] = 4
    [5,1] = ATTACHMENTS
    [5,2] = 5
    [6,1] = EMPLOYEES_CALENDAR
    [6,2] = 6
    [7,1] = EXAMPLE
    [7,2] = 7
)

https://www.lianja.com/doc/index.php/AUSED()


Return field values directly from disk
CURVAL
close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
?

https://www.lianja.com/doc/index.php/CURVAL()


Position record pointer in the specified work area
GOTO
Name = IIF(GOTO(supplier, RECNO()), suppliers->name, "??????")

https://www.lianja.com/doc/index.php/GOTO()


Return original values for fields that may have been modified but not updated
OLDVAL
close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
?

https://www.lianja.com/doc/index.php/OLDVAL()


Discard changes and restore OLDVAL() or currect disk data during buffered processing
TABLEREVERT
close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
?

https://www.lianja.com/doc/index.php/TABLEREVERT()


Commit buffered changes
TABLEUPDATE
close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
?

https://www.lianja.com/doc/index.php/TABLEUPDATE()


Assign a field modification value to a field or record
SETFLDSTATE

 

close databases
clear
set exclusive off
open database southwind
use customers
// Set to known value before start
replace customerid with "ALFKI"
 
cursorsetprop("Buffering", 5, "customers")
messagebox("Buffering set to " +etos(cursorgetprop("Buffering")))
 
? "Original customerid value: " + customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate at start: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
 
// Alter in another session
// update southwind!customers set customerid = 'MULTI' where recno() = 1
? "Someone else just updated the record!"
? "New customerid value: " +  customerid 
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace: " + getfldstate("customerid")
tablerevert(.T.)
 
? "Reverted customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after revert: " + getfldstate("customerid")
 
replace customerid WITH "LIANJ"
? "New customerid value: " +  customerid 
tableupdate(.T.)
? "Updated customerid value: " +  customerid
? "Curval(): " + curval("customerid")
? "Oldval(): " + oldval("customerid")
? "Fieldstate after replace and update: " + getfldstate("customerid")
 
setfldstate("customerid",2)
? "Fieldstate after set: " + getfldstate("customerid")
?

https://www.lianja.com/doc/index.php/SETFLDSTATE()


Using a Cursor to Select, Update and Delete Records

A cursor is a temporary selection of rows that can be fetched one at a time and processed further. The update and delete statements can both operate on the most recently fetched row from a cursor.

// Declare the cursor to select records from the orders table
declare cursor1;
  cursor for;
  select orderid, customerid, orderdate;
  from orders;
  where requireddate < date()
 
// Open the cursor
open cursor1
 
// Fetch records one at a time from the cursor and update them
fetch cursor1;
  into m_order, m_customer, m_orderdate
  do while sqlcode = 0
    if not empty(m_order) and empty(shippeddate)
        update orders;
          set shippeddate = date();
          where current of cursor1
    endif
    fetch cursor1;
      into m_order, m_customer, m_orderdate
enddo
 
// Close the cursor and free up any resources used for the cursor
close cursor1
drop cursor cursor1

https://www.lianja.com/doc/index.php/Using_Recital_SQL


Create

select * from customers into cursor cust

Or alternatively just open the customers table with noSQL:

use customers

To position on the first record in a cursor:

goto top

When you are positioned on a particular record in a cursor you can extract data just by referencing the field name:

? customers.amount

To lookup a customer by their id and scan through the data selecting only those records that satisfy a certain condition:

use customers order tag id
seek "12345"
scan rest while amount > 0
   ? "Amount is ", customers.amount
endscan

When opening a table in noSQL mode, you can lookup records by keys.

use customers order tag id
seek "12345"
if not found()
   // key was not found.
endif

You can add new blank records to a recordset:

use customers
append blank
replace id with "34567", amount with 0

Or alternatively:

insert into customers (id, amount) values("34567", 0)

After executing append blank the record is not written until the you move the record pointer (Lianja supports record buffering by default). This allows you to update the fields of the blank record prior to it being committed into the table.

You can update records in a cursor:

use customers
set order to tag id
seek "12345"
if found() 
   replace amount with amount+1000
endif

Or alternatively:

update customers set amount = amount+1000  where id = "12345"

You can delete records in a cursor:

use customers
set order to tag id
seek "12345"
if found() 
   delete
endif

Or alternatively:

delete from customers  where id = "12345"

You close a cursor:

select customers
use

https://www.lianja.com/doc/index.php/Working_with_data_in_Visual_FoxPro


Table

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.

Name:  Screen Shot 2016-10-10 at 10.24.15 AM.jpg Views: 44 Size:  108.7 KB

Grid sections can be styled already. See CSS below.

Code:
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]