homunizam

XML [examples]

Published on 2018-08-292018-07-25 by homunizamLeave a comment

Display object as XML

PRINT_XML

open database southwind
select * from shippers into object shipobj
print_xml(shipobj)

1Speedy Express(503) 55
2United Package(503) 55
3Federal Shipping(503)

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

Specify the default format for XML files created by SELECT…SAVE AS XML

SET_XMLFORMAT

set xmlformat to ADO
SELECT * FROM example;
  SAVE AS XML example
 
// In Visual Basic the file can then be loaded like this:
// Set adoPrimaryRS = New Recordset
// adoPrimaryRS.Open "example.xml"
 
// Excel example
set xmlformat to excel
open database southwind
use customers
copy to mycustomers.xml type xml
Lianja.showDocument("mycustomers.xml")

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

Create a dynamic array (object) from the contents of an XML formatted file

XML_DECODE_FILR

filename = geturl("http://www.myserver.com/getsomepage.rsp?name=smith&month=10", 30, array(), "myfilename.xml")
myobject = xml_decode_file(filename, "customers", "customer")

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

Replace fields in the current record with values from a character string containing XML formatted data

XML_GATHER

open database southwind
use shippers
cVar = xml_scatter()
copy structure to temp
use temp
append blank
xml_gather(cVar)

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

Return a character string containing XML decoded as an object

XML_DECODE

// Proccess the message queue
do while .t.
   // Check for new messages
   do while (mqcurmsgs(m_mqdes) > 0)
       // retrieve message
       m_message = mqreceive(m_mqdes)
           // decode the XML message into an object/dynamic array
           m_objmessage = xml_decode(m_message)
           // process the message
       mq_process_message(m_objmessage, m_message)
   enddo
   sleep 1
enddo

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

Encode an object as a character string containing XML for use with the messaging functions to pass objects between processes

XML_ENCODE

// Open up the queue for read/write access
mqdes=mqcreate("/myqueue2", 2)
if (mqdes < 0)
    messagebox(strerror()+",errno="+alltrim(str(error())))
    return
endif
// send a message to the queue
rc = mqsend(mqdes, xml_encode(tran_obj))
if (rc < 0)
    messagebox(strerror()+",errno="+alltrim(str(error())))
    return
endif
mqclose(mqdes)

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

Encode an object as a character string containing XML for use with the messaging functions to pass objects between processes

XML_DECODE

// Open up the queue for read/write access
mqdes=mqcreate("/myqueue2", 2)
if (mqdes < 0)
    messagebox(strerror()+",errno="+alltrim(str(error())))
    return
endif
// send a message to the queue
rc = mqsend(mqdes, xml_encode(tran_obj))
if (rc < 0)
    messagebox(strerror()+",errno="+alltrim(str(error())))
    return
endif
mqclose(mqdes)

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

Parse XML strings using XPath notation

XQUERY

// Examples using mybooks.xml
cMybooks = filetostr("mybooks.xml")
cBook = xquery(cMybooks,"/bookstore/book[2]")
? cBook
 
  Guy Watson
  Jane Baxter
  2008
  Paperback
 
cMybooks = filetostr("mybooks.xml")
cTitle = xquery(cMybooks,"/bookstore/book[2]/title")
? cTitle
 
Riverford Farm Cook Book

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

Search for an XML node in an XML file previously opened with XQUERY_OPEN() and return the complete node and its attributes

XQUERY_ATTRIBUTES

// Example using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   cTitle = xquery_node("/bookstore/book[&i]/title")
   ? cTitle
   oTitle = xquery_attributes("/bookstore/book[&i]/title")
   ? xquery_decode(oTitle)
endfor
xquery_close()
 
// Results:
 
Pulse
 
Dynarray (refcnt=0)
(
    [title] => Dynarray (refcnt=1)
        (
            [text] => Pulse
            [attributes] => Dynarray (refcnt=1)
                (
                    [lang] => en
                )
        )
)
Riverford Farm Cook Book
 
Dynarray (refcnt=0)
(
    [title] => Dynarray (refcnt=1)
        (
            [text] => Riverford Farm Cook Book
            [attributes] => Dynarray (refcnt=1)
                (
                    [lang] => en
                )
        )
)
The House At Pooh Corner
 
Dynarray (refcnt=0)
(
    [title] => Dynarray (refcnt=1)
        (
            [text] => The House At Pooh Corner
            [attributes] => Dynarray (refcnt=1)
                (
                    [lang] => en
                )
        )
)
Knots and Crosses
 
Dynarray (refcnt=0)
(
    [title] => Dynarray (refcnt=1)
        (
            [text] => Knots and Crosses
            [attributes] => Dynarray (refcnt=1)
                (
                    [lang] => en
                )
        )
)
Le mythe de Sisyphe
 
Dynarray (refcnt=0)
(
    [title] => Dynarray (refcnt=1)
        (
            [text] => Le mythe de Sisyphe
            [attributes] => Dynarray (refcnt=1)
                (
                    [lang] => fr
                )
        )
)

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

Close an XML file previously opened with XQUERY_OPEN()

XQUERY_CLOSE

// Examples using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   cTitle = xquery_find("/bookstore/book[&i]/title")
   ? cTitle
endfor
xquery_close()
 
Pulse
Riverford Farm Cook Book
The House At Pooh Corner
Knots and Crosses
Le mythe de Sisyphe
 
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
	nCountAuthor = xquery_count("/bookstore/book[&i]/author")
	for j = 1 to nCountAuthor
		cAuthor = xquery_find("/bookstore/book[&i]/author[&j]")
		? cAuthor
	endfor
endfor
xquery_close()
 
Jenny Chandler
Guy Watson
Jane Baxter
A. A. Milne
Ian Rankin
Albert Camus

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

Count the occurrences of an XML node in an XML file previously opened with XQUERY_OPEN()

XQUERY_COUNT

// Examples using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   cTitle = xquery_node("/bookstore/book[&i]/title")
   ? cTitle
endfor
xquery_close()
 
Pulse
Riverford Farm Cook Book
The House At Pooh Corner
Knots and Crosses
Le mythe de Sisyphe
 
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
	nCountAuthor = xquery_count("/bookstore/book[&i]/author")
	for j = 1 to nCountAuthor
		cAuthor = xquery_node("/bookstore/book[&i]/author[&j]")
		? cAuthor
	endfor
endfor
xquery_close()
 
Jenny Chandler
Guy Watson
Jane Baxter
A. A. Milne
Ian Rankin
Albert Camus

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

Return an object from an XML string

XQUERY_DECODE

// Example using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   oBook = xquery_node("/bookstore/book[&i]")
   ? xquery_decode(oBook)
endfor
xquery_close()
 
// Results:
Dynarray (refcnt=0)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => Pulse
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => en
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => Jenny Chandler
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 2013
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => Hardback
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => COOKING
                        )
                )
        )
)
 
Dynarray (refcnt=0)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => Riverford Farm Cook Book
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => en
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => Jane Baxter
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 2008
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => Paperback
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => COOKING
                        )
                )
        )
)
 
Dynarray (refcnt=0)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => The House At Pooh Corner
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => en
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => A. A. Milne
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 1928
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => Hardback
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => CHILDREN
                        )
                )
        )
)
 
Dynarray (refcnt=0)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => Knots and Crosses
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => en
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => Ian Rankin
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 2008
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => EPUB
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => CRIME
                        )
                )
        )
)
 
Dynarray (refcnt=0)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => Le mythe de Sisyphe
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => fr
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => Albert Camus
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 1943
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => Paperback
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => PHILOSOPHY
                        )
                )
        )
)

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

Parse an XML file using XPath notation

XQUERY_FILE

// Examples using mybooks.xml
cBook = xquery_file("mybooks.xml","/bookstore/book[2]")
? cBook
 
  Guy Watson
  Jane Baxter
  2008
  Paperback
 
cTitle = xquery_file("mybooks.xml","/bookstore/book[2]/title")
? cTitle
 
Riverford Farm Cook Book
 
nCount = 1
do while .T.
    cTitle = xquery_file("mybooks.xml","/bookstore/book[&nCount]/title")
    if empty(cTitle)
        exit
	endif
    ? cTitle
    ++ nCount 
enddo
 
Pulse
Riverford Farm Cook Book
The House At Pooh Corner
Knots and Crosses
Le mythe de Sisyphe

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

Search for an XML node in an XML file previously opened with XQUERY_OPEN() and return its value

XQUERY_FIND

// Examples using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   cTitle = xquery_find("/bookstore/book[&i]/title")
   ? cTitle
endfor
xquery_close()
 
Pulse
Riverford Farm Cook Book
The House At Pooh Corner
Knots and Crosses
Le mythe de Sisyphe
 
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
	nCountAuthor = xquery_count("/bookstore/book[&i]/author")
	for j = 1 to nCountAuthor
		cAuthor = xquery_find("/bookstore/book[&i]/author[&j]")
		? cAuthor
	endfor
endfor
xquery_close()
 
Jenny Chandler
Guy Watson
Jane Baxter
A. A. Milne
Ian Rankin
Albert Camus

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

Search for an XML node in an XML file previously opened with XQUERY_OPEN() and return its value

XQUERY_NODE

// Examples using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   cTitle = xquery_node("/bookstore/book[&i]/title")
   ? cTitle
endfor
xquery_close()
 
Pulse
Riverford Farm Cook Book
The House At Pooh Corner
Knots and Crosses
Le mythe de Sisyphe
 
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
	nCountAuthor = xquery_count("/bookstore/book[&i]/author")
	for j = 1 to nCountAuthor
		cAuthor = xquery_node("/bookstore/book[&i]/author[&j]")
		? cAuthor
	endfor
endfor
xquery_close()
 
Jenny Chandler
Guy Watson
Jane Baxter
A. A. Milne
Ian Rankin
Albert Camus

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

Open XML files for parsing using XPath notation

XQUERY_OPEN

// Examples using mybooks.xml
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
   cTitle = xquery_node("/bookstore/book[&i]/title")
   ? cTitle
endfor
xquery_close()
 
Pulse
Riverford Farm Cook Book
The House At Pooh Corner
Knots and Crosses
Le mythe de Sisyphe
 
xquery_open("mybooks.xml")
nCount = xquery_count("/bookstore/book")
for i=1 to nCount
	nCountAuthor = xquery_count("/bookstore/book[&i]/author")
	for j = 1 to nCountAuthor
		cAuthor = xquery_node("/bookstore/book[&i]/author[&j]")
		? cAuthor
	endfor
endfor
xquery_close()
 
Jenny Chandler
Guy Watson
Jane Baxter
A. A. Milne
Ian Rankin
Albert Camus

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

Query nodes by attribute and value in an XML file previously opened with XQUERY_OPEN()

XQUERY_SELECT

// Examples using mybooks.xml
xquery_open("mybooks.xml")
xquery_select("book[@category='children']")
obj = xquery_decode()
? obj
xquery_close()
 
// Results:
 
Dynarray (refcnt=1)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => The House At Pooh Corner
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => en
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => A. A. Milne
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 1928
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => Hardback
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => CHILDREN
                        )
                )
        )
)
 
xquery_open("mybooks.xml")
xquery_select("/bookstore/book[year>'2010']")
obj = xquery_decode()
? obj
xquery_close()
 
// Results:
 
Dynarray (refcnt=1)
(
    [book] => Dynarray (refcnt=1)
        (
            [1] => Dynarray (refcnt=1)
                (
                    [title] => Dynarray (refcnt=1)
                        (
                            [text] => Pulse
                            [attributes] => Dynarray (refcnt=1)
                                (
                                    [lang] => en
                                )
                        )
                    [author] => Dynarray (refcnt=1)
                        (
                            [text] => Jenny Chandler
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [year] => Dynarray (refcnt=1)
                        (
                            [text] => 2013
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [format] => Dynarray (refcnt=1)
                        (
                            [text] => Hardback
                            [attributes] => Dynarray (refcnt=1)
                                (
                                )
                        )
                    [attributes] => Dynarray (refcnt=1)
                        (
                            [category] => COOKING
                        )
                )
        )
)

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

Return the contents of a record formatted as XML

XML_SCATTER

open database southwind
use shippers
cVar = xml_scatter()

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

Number of records from an XML file

XMLCOUNT

select company, street, town, state, zip from sales save as xml sales
? xmlcount("sales.xml")
        25

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

Create a Document Type definition file for a particular table

XMLCREATEDTD

use prospect
set xmlformat to recital
? xmlcreatedtd()
.T.

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

Read the first record contained in the specified XML file and return the number of fields in the record

XMLFIRST

number = xmlfirst("sales.xml", target, trans, where, names, data)
? number
        30

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

Read the next record contained in the XML file specified with the XMLFIRST() function and return the number of fields in the record

XMLNEXT

number = xmlnext(trans, where, names, data)
? number
        30

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

Validate an XML file against its DTD file

XMLVALIDATE

? xmlvalidate("sales.xml")
.T.

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

create table customer2;
  from xml cust.xml load

insert into products;
  from xml prices.xml

Export

use example
copy to custxml xml

select employeeid, lastname, firstname;
  from employees into xml emp
select employeeid, lastname, firstname;
  from employees save as xml emp

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

WebView custom Gadget [examples]

Published on 2018-08-292018-07-25 by homunizamLeave a comment

<%@ Language=VFP %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<%
timeofday()
if len(database())=0
	open database southwind
endif
 
// get the column descriptions
use example in 0 current
astore(afieldList, fldlist(), ',')
declare afieldDesc[ fldcount() ]
adesc(afieldDesc)
use
 
// perform the query
tmpfile = sys(2015)	
select * from example order by state save as [&tmpfile]
use [&tmpfile] in 0 current
declare afieldList[ fldcount() ]
 
// declare some special symbols (html uses ampersand so do we)	
amp = chr(38) 
nbsp = amp + "nbsp;"  
 
// generate the html output
? ('<table width="100%" height="100%" cellpadding="5" cellspacing="0" bgcolor="white" border=0px>') 
? ('<tr bgcolor="gray">')
? ('<td align="center" colspan="&(fldcount())">')
? ('</td>')
? ('</tr>')  
 
// display column headings
? ('<tr bgcolor="#eaeaea">')
	for j=1 to fldcount()
		? ('<td halign=center valign=top>')
		? ('<b><font color="gray">' + afieldDesc[j] + '</font></b>')
		? ('</td>')
    next
? ('</tr>') 
? ('<tr bgcolor="darkgray" height="1px">' + replicate('<td></td>', fldcount()) + '</tr>')
 
// group subtotals by the STATE field 
last_state = state 
declare subtotals[3]
subtotals[1] = 0.0  	// limit
subtotals[2] = 0.0	// balance
subtotals[3] = 0.0	// available
m_limit = 0.0
m_balance = 0.0
m_available = 0.0 
 
// scan through the records generating the table rows and columns
goto top
 
// for all records...	   
for i=1 to reccount()+2
	if mod(i,2) = 0
    	rowcolor = "#f9f9f9"
    	altcolor = "#FFFFFF"
	else
		rowcolor = "#FFFFFF"        	
		altcolor = "#f9f9f9"
    endif
 
	// check for subtotal break
	if (state != last_state and i > 1) or (i > reccount())
		? ('<tr bgcolor="lightgray" height="1px">' + replicate('<td></td>', fldcount()) + '</tr>')
 
		// display subtotals
		? ('<tr bgcolor="#f5f5f5" color="gray">')
 
		if i <= reccount()+1
			? ('<td colspan="3" halign=left><b><font color="gray">Sub-total for state: ' + last_state + '</font></b>')
			? (replicate('<td>&(nbsp)</td>', 5))
		elseif i = reccount()+2
			? ('<td color="gray" halign=left><b>Totals:</b>')
			? (replicate('<td>&(nbsp)</td>', 7))
        endif
 
		? ('<td align=right>')
        tmpfld = currency(subtotals[1])
        fld = 'tmpfld'
		? ('<b><font color="gray">' + etos(&fld)+'&(nbsp)&(nbsp)' + '</font><b>')
		? ('</td>')
 
		? ('<td align=right>')
        tmpfld = currency(subtotals[2])
        fld = 'tmpfld'
		? ('<b><font color="gray">' + etos(&fld)+'&(nbsp)&(nbsp)' + '</font><b>')
		? ('</td>')
 
		? ('<td align=right>')
        tmpfld = currency(subtotals[3])
        fld = 'tmpfld'
		? ('<b><font color="gray">' + etos(&fld)+'&(nbsp)&(nbsp)' + '</font><b>')
		? ('</td>') 
		? (replicate('<td>&(nbsp)</td>', 1))
 
		? ('</tr>')
 
		? ('<tr bgcolor="white" height="1px">' + replicate('<td></td>', fldcount()) + '</tr>')
		if i > reccount()+1
			? ('<tr bgcolor="white" height="1px">' + replicate('<td></td>', fldcount()) + '</tr>')
			? ('<tr bgcolor="black" height="1px">' + replicate('<td></td>', fldcount()) + '</tr>')
        endif
 
		? ('<tr colspan="&(fldcount())" bgcolor="&altcolor">')
		? (replicate('<td>&(nbsp)</td>', fldcount()))
		? ('</tr>') 
 
		if i = reccount()+1			
		    subtotals[1] = m_limit  		// limit
		    subtotals[2] = m_balance		// balance
		    subtotals[3] = m_available		// available
        	loop
        endif
 
	    subtotals[1] = 0.0  	// limit
	    subtotals[2] = 0.0		// balance
	    subtotals[3] = 0.0		// available
 
		if i > reccount()+1
        	exit
        endif
    endif
 
	// save subtotal values       	
   	last_state = state 
   	subtotals[1] = subtotals[1] + limit
   	subtotals[2] = subtotals[2] + balance
   	subtotals[3] = subtotals[3] + available 
   	m_limit  = m_limit + limit
   	m_balance = m_balance + balance
   	m_available = m_available + available
 
	// for all columns...
	? ('<tr bgcolor="&rowcolor">')
	for j=1 to fldcount()
		fld = afieldlist(j)
		if (upper(fld) = 'LIMIT' or upper(fld) = 'BALANCE' or upper(fld) = 'AVAILABLE')
        	tmpfld = currency(&fld)
        	fld = 'tmpfld'
			? ('<td valign=top align=right>')
		else            	
			? ('<td valign=top align=left>')
        endif
		? (etos(&fld)+"&(nbsp)&(nbsp)")
		? ('</td>')
    next   
 
	? ('</tr>')
	skip
next  
 
? ('</table>')
? ('** End of report elapsed time '+timeofday(4)+' seconds **')
erase '&tmpfile..dbf'
erase '&tmpfile..dbt'  
%>
</body>
</html>

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

PHP:

// Lianja PHP WebView
//
echo '<html>';
echo '<head>';
echo '<style>';
echo '.tablecaption { background: gray; font-weight: bold; color: white; height:26px; }';
echo '.smallfont { font-size:small; }';
echo 'body { padding: 0px; margin: 0px; border: 1px solid lightgray; border-top: 1px solid white;}';
echo '</style>';
echo '</head>';
echo '<body>'; 
 
// open the southwind database
$db = Lianja::openDatabase("southwind");
 
// Open a recordset
$rs = $db->openRecordSet("select * from example"); 
 
// main table
echo '<table cellpadding="5">'; 
echo '<caption class="tablecaption">Example PHP Report</caption>';
 
// print table headings 
$rs->movefirst(); 
 
echo '<tr bgcolor="lightgray" class="smallfont">';
for ($j=0; $j<$rs->fcount(); ++$j) 
{
	echo '<th><font color="white">' . $rs->fields($j)->name . '</font></th>';
}
echo "</tr>";
 
 
// Traverse the recordset and write the output into the Webview section.
for ($i=0; $i<$rs->reccount(); ++$i)
{
	if (($i%2) == 0)
	{
    	$rowcolor = "#f1f6fe";
    	$altcolor = "#FFFFFF";
	}
	else
	{
		$rowcolor = "#FFFFFF";        	
		$altcolor = "#f1f6fe";
	}
 	echo '<tr bgcolor="' . $rowcolor . '" color="darkgray" class="smallfont" valign="top">';
	for ($j=0; $j<$rs->fcount(); ++$j)
	{
		$name = $rs->fields($j)->name;
		$value = $rs->fields($j)->value;
		if (in_array($name, array('LIMIT', 'AVAILABLE', 'BALANCE')))
		{
			echo '<td align="right">$' . sprintf("%-8.2f", $value) . '</td>';
		}
		else
		{
			echo '<td>' . $value . '</td>';
		}
	}
	echo '</tr>';
	$rs->movenext(); 
}
 
// end of table
echo '</table>';
 
 
// Close the recordset	
$rs->close(); 
 
// end of report
echo '<hr />Report complete at <b>' . date("r", time()) . '</b>'; 
 
// Close off HTML tags
echo '</body>';
echo '</html>';

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

Python:

#
# Lianja Custom Python WebView
#
#
import Lianja
import time
 
# The output of the "print" command will be redirected into the WebView.
print "<html>"
print "<head>"
print "<style>"
print ".tablecaption { background: gray; font-weight: bold; color: white; \
  height:26px; }"
print ".smallfont { font-size:small; }"
print "body { padding: 0px; margin: 0px; border: 1px solid lightgray; \
  border-top: 1px solid white;}"
print "</style>"
print "</head>"
print "<body>"
 
# open a database
db = Lianja.openDatabase("southwind") 
 
# open a recordset
rs = db.openRecordSet("select * from example")
 
# main table
print "<table cellpadding=\"5\">"
print "<caption class=\"tablecaption\">Example Python Report</caption>"
 
# column headings
rs.movefirst()
print "<tr bgcolor=\"lightgray\" class=\"smallfont\">"
for j in range( rs.fcount() ):
	print "<th><font color=\"white\">" + rs.fields(j).name + "</font></th>"
print "</tr>"
 
# Traverse the recordset and write the output into the Webview section.
for i in range( rs.reccount() ):
	if ((i%2) == 0):
		rowcolor = "#f1f6fe"
		altcolor = "#FFFFFF"
	else:
		rowcolor = "#FFFFFF"       	
		altcolor = "#f1f6fe"
 	print "<tr bgcolor=\"" + rowcolor + "\" color=\"darkgray\" class=\"smallfont\" valign=top>"
	for j in range( rs.fcount() ):
		if rs.fields(j).name in [ "LIMIT", "BALANCE", "AVAILABLE" ]:
			print "<td align=right>$%.2f</td>" % rs.fields(j).value
		else:
			print "<td>%s</td>" % rs.fields(j).value
	print "</tr>"
	rs.movenext()
 
# end of table
print "</table>"
 
# Close the RecordSet	
rs.close() 
 
# End of report
print "<hr>Report complete at <b>" + time.asctime() + "</b>" 
 
# Close off HTML tags
print "</body>"
print "</html>"

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

JS:

<%@ Language=JavaScript %>
<html>
<head>
<style>
.tablecaption { background: gray; font-weight: bold; color: white; height:26px; }
.smallfont { font-size:small; }
body { padding: 0px; margin: 0px; border: 1px solid lightgray; border-top: 1px solid white;}
</style>
</head>
<body>
<%
db = Lianja.openDatabase("southwind");
 
// Open a recordset
rs = db.openRecordSet("select * from example");
 
// main table
print("<table cellpadding=\"5\">"); 
print("<caption class=\"tablecaption\">Example JavaScript Report</caption>");
 
// print table headings 
rs.moveFirst(); 
print("<tr bgcolor=\"lightgray\" class=\"smallfont\">");
for (j=0; j < rs.fieldcount; ++j)
{
	print("<th><font color=\"white\">" + rs.fields(j).name + "</font></th>");
}
print("</tr>");
 
// Traverse the recordset and write the output into the Webview section.
for (i=0; i < rs.reccount; ++i)
{
	if ((i%2) == 0)
	{
    	rowcolor = "#f1f6fe";
    	altcolor = "#FFFFFF";
	}
	else
	{
		rowcolor = "#FFFFFF";        	
		altcolor = "#f1f6fe";
	}
 	print("<tr bgcolor=\"" + rowcolor + "\" color=\"darkgray\" class=\"smallfont\" valign=top>");
	for (j=0; j < rs.fieldcount; ++j)
	{
		name = rs.fields(j).name;
		value = rs.fields(j).value;
		if (['LIMIT', 'AVAILABLE', 'BALANCE'].indexOf(name) >= 0)
		{
			print("<td align=right>$" + value.toFixed(2) + "</td>");
		}
		else
		{
			print("<td>" + value + "</td>");
		}
	}
	print("</tr>");
	rs.moveNext(); 
}
 
// end of table
print("</table>");
 
// Close the recordset	
rs.close(); 
 
// end of report
print("<hr>Report complete at <b>" + new Date() + "</b>"); 
%>
</body>
</html>

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

WebView [examples]

Published on 2018-08-282018-07-25 by homunizamLeave a comment

The WebView Section and the WebView Gadget provide the ability to render content generated by any URL and integrate this in with a Lianja App. Use Macros in the URL to fetch contextual data content.

Lianja.showDialogPanel("CUSTOMER LOCATION", 
    "lib:/showdialog_map.rsp?address={customers.address}
                +{customers.city}+{customers.country}", 500);

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

Lianja.showDialogPanel(
	"CUSTOMER LIST",
	"lib:/datalistview.rsp?database=southwind&table=customers&columns=customerid," +
        +"companyname&hyperlink=companyname&sectionid=customers.section1");

Lianja.showDialog(
	"CUSTOMER LIST",
	"lib:/datalistview.rsp?database=southwind&table=customers&columns=customerid," 
        + "companyname&hyperlink=companyname&sectionid=customers.section1", 0, 0, true);

Lianja.showDialogPanel("DOCUMENT VIEW","lib:/documentview.rsp?file=compressed.tracemonkey-pldi-09.pdf")

Lianja.showDialog("DOCUMENT VIEW","lib:/documentview.rsp?file=compressed.tracemonkey-pldi-09.pdf",0,0,true);

Lianja.showDialogPanel("GOOGLE CHART","lib:/googlechart.rsp");

Lianja.showDialogPanel("GOOGLE CHART","lib:/googlechart.rsp", 0, 0, true);

Lianja.showDialogPanel("TREE VIEW","lib:/treeview.rsp");

Lianja.showDialog("TREE VIEW","lib:/treeview.rsp", 0, 0, true);

Lianja.showDialogPanel("CUSTOMER LOCATION","lib:/googlemap.rsp");

Lianja.showDialog("CUSTOMER LOCATION","lib:/googlemap.rsp",0,0,true);

Lianja.showDialog("ORGCHART VIEW","lib:/orgchart.rsp",0,0,true);

Lianja.showDialogPanel("CATALOG VIEW","lib:/catalogview.rsp")

Lianja.showDialog("CATALOG VIEW","lib:/catalogview.rsp",0,0,true);

Lianja.showDialogPanel("REPORT VIEW","lib:/report.rsp", "65%")

Lianja.showDialog("REPORT VIEW","lib:/report.rsp",0,0,true)

Lianja.showDialogPanel("GALLERY VIEW","lib:/galleryview.rsp");

Lianja.showDialog("GALLERY VIEW","lib:/galleryview.rsp",1000,600,true);

Lianja.showDialogPanel("COMMENTS VIEW","lib:/commentsview.rsp", -850)

Lianja.showDialog("COMMENTS VIEW","lib:/commentsview.rsp")

Lianja.showDialogPanel("CALENDAR VIEW","lib:/calendar.rsp", "65%")

Lianja.showDialog("CALENDAR VIEW","lib:/calendar.rsp", 1000,600)

Lianja.showDialogPanel("EDITOR VIEW","lib:/web_editor.rsp", "65%")

Lianja.showDialog("EDITOR VIEW","lib:/web_editor.rsp", 1000, 600, true)

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

Web service – Socket – TCPIP [examples]

Published on 2018-08-282018-07-25 by homunizamLeave a comment

Open a TCP/IP socket

FSOCKOPEN

fp=fsockopen("www.server.com",80)
if fp > 0
    ...
endif
fclose(fp)

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

Read data from an open socket connection

SOCKET_READ

// read the JSON encoded object from the socket
data = socket_read(skt)
 
// assume we are connected to node.js (as an example) 
// and decode the JSON string
myobj = json_decode(data)
 
// now myobj is a VFP object with all the properties 
// (and subobjects/arrays etc)

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

Write data to an open socket connection

SOCKET_WRITE

// assume we are connected to node.js (as an example) then 
// send the data in JSON format
data = json_encode(myobj)
 
// write the JSON encoded object to the socket
m_count = socket_write(skt, data)
if m_count != len(data)
    // error sending data
endif

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

Create a socket server listening on a port

SOCKET_SERVER

proc myhandler(skt as numeric)
    private m_running = .t.
    do while m_running
        if socket_peek(skt) <= 0
            sleep 1
            loop
        endif
        data = socket_read(skt)
        // do what you want to do with the data
    enddo
endproc
 
// create a server listening on port 9000
skt = socket_server(9000, "myhandler")
if skt < 0
    // error
endif

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

Open a socket

SOCKET_OPEN

// connect to a remote server e.g. node.js listening on port 9000
skt = socket_open("mydomain.com", 9000)
if skt < 0
    // error
endif

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

Close an open socket connection

SOCKET_CLOSE

// close the socket connection
m_skt = socket_close(skt)
if m_skt != skt
    // error closing socket
endif

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

Error number of the last socket operation error

SOCKET_LASTERROR

// check the socket operation
if socket_lasterror() != 0
    // remote server connection failed, try reconnecting
endif

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

Check if there is data available on an open socket connection

SOCKET_PEEK

// check the socket connection
if socket_peek(skt) > 0 
    // we have some data to read...
endif

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

Socket_server()

proc myhandler(skt as numeric)
    private m_running = .t.
    do while m_running
        if socket_peek(skt) <= 0
            sleep 1
            loop
        endif
        data = socket_read(skt)
        // do what you want to do with the data
    enddo
endproc

// create a server listening on port 9000
skt = socket_server(9000, "myhandler")
if skt < 0
    // error
endif

Socket_open()

// connect to a remote server e.g. node.js listening on port 9000
skt = socket_open("mydomain.com", 9000)
if skt < 0
    // error
endif

socket_peek()

if socket_peek(skt) > 0 
    // we have some data to read...
endif

socket_read()

// read the JSON encoded object from the socket
data = socket_read(skt)

// assume we are connected to node.js (as an example) 
// and decode the JSON string
myobj = json_decode(data)

// now myobj is a VFP object with all the properties 
// (and subobjects/arrays etc)

socket_write()

// assume we are connected to node.js (as an example) then 
// send the data in JSON format
data = json_encode(myobj)

// write the JSON encoded object to the socket
m_count = socket_write(skt, data)
if m_count != len(data)
    // error sending data
endif

socket_close()

// close the socket connection
m_skt = socket_close(skt)
if m_skt != skt
    // error closing socket
endif
socket_lasterror()	None	
// check the socket operation
if socket_lasterror() != 0
    // remote server connection failed, try reconnecting
endif

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

View [examples]

Published on 2018-08-282018-07-25 by homunizamLeave a comment

Creates a logical view based on one or more tables

CREATE_VIEW

// Create a view based on price being over $10
CREATE VIEW OverTen AS;
SELECT * FROM orders WHERE price > 10
 
// Create a view based on price being over $20
CREATE SQL VIEW OverTwenty AS;
SELECT * FROM orders WHERE price > 20

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

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

Frees up all system resources allocated to a view

DROP_VIEW

DROP VIEW OverTen

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

Virtual table [examples]

Published on 2018-08-272018-07-25 by homunizamLeave a comment

Creates a virtual table

CREATE_VIRTUALTABLE

open database advworks
create virtualtable scurr ;
    alias "scurr" ;
    connstr "awhr" ;
    as select * from sales.currency
 
// Example including account details
open database orahr
create virtualtable vt_locactions ;
    alias "vt_locactions" ;
    connstr "oracle_1;uid=hr;pwd=hr" ;
    as select * from locations
 
// Example including KEYFIELDLIST and PROPERTIES (updatefieldlist)
open database sak
create virtualtable vt_actor ;
    alias "actor" ;
    connstr "sakila" ;
    keyfieldlist "actor_id" ;
    properties "updatefieldlist=*,-actor_id" ;
    as select * from actor

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

Modifies a virtual table

ALTER_VIRTUALTABLE

open database advworks
create virtualtable scurr connstr "awhr" as select * from sales.currency
alter virtualtable scurr connstr "awhr" as select name from sales.currency
 
// Modifying properties example
open datables sakdata
create virtualtable vt_actor connstr "sakila" keyfieldlist "actor_id" as select * from actor
alter virtualtable vt_actor addproperty "updatefieldlist=first_name"
alter virtualtable vt_actor modifyproperty "updatefieldlist=first_name, last_name"
alter virtualtable vt_actor removeproperty "updatefieldlist"
alter virtualtable vt_actor properties "updatefieldlist=last_name;keepalive=1"
alter virtualtable vt_actor properties ""

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

Information about a virtual table

VTINFO

open database southwind
use vt_orders
? vtinfo(vt_orders,"basetable")
use vt_employees in 3
? vtinfo(3,"connstr")

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

Specify a default ‘connstr’ for Virtual Tables

SET_CONNSTR

set connstr to 'local'
? set('connstr')
local

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

//
// Assume we have a grid section which is bound to a VT called vcustomers
// and the vcustomers VT was created with
//
// create virtualtable vcustomers 
//     connstr “local” 
//     properties “pkwhere=1 eq 0“
//     as select * from southwind!customers {pkwhere}
//
mysection = Lianja.get(“page1.section1”)
mysection.setNamedParameter("pkwhere", " where customerid like 'A%'")
// The special named parameter defaultnamedparameters when set to “0” causes
// default named parameters in the VT properties to be ignored
mysection.setNamedParameter("defaultnamedparameters", "0")
mysection.reOpenTable()

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

You can use “local” stored procedures for Virtual Tables.

Virtual Tables provide transparent access to third party SQL backends.

Occasionally you want to fetch data from several backend servers and join the cursors or filter the records before presenting to the user.

You can create a Virtual Table with a “local” connection string and then specify the SQL command as a call to a stored procedure in the database.

create virtualtable vtorders connstr "local" as call sp_getorders()

Here is the stored procedure which is executed locally:

// This is an example of a "local" stored procedure
select * from southwind!orders into cursor localorders
return setResultSet("localorders")

The stored procedure can be quite sophisticated, including joins and filters, and the key to using these “local” stored procedures is to always return back the resultset using the setResultSet(“alias”) function as shown in the example above.

If your virtual table is to be used from the web/mobile clients, the basetable must be set using the TABLE clause of CREATE VIRTUALTABLE or ALTER VIRTUALTABLE:

create virtualtable vtorders connstr "local" table "orders" as call sp_getorders()

create virtualtable vtorders connstr "local" as call sp_getorders()
alter virtualtable vtorders table "orders"

Parameter substitution occurs when {arg} or ?arg is specified:

create virtualtable vtorders connstr "local" table "orders" ;
           as call sp_getorders("{m_arg1}", "{m_arg2}")

create virtualtable vtorders connstr "local" table "orders" ;
          as call sp_getorders(?m_arg1,?m_arg2)

// This is an example of a "local" stored procedure using parameters
select * from southwind!orders where customerid = ?m_arg1 ;
            and employeeid = ?m_arg2 into cursor localorders
return setResultSet("localorders")

In desktop apps, the parameters must be declared as public variables. In OData calls from the Web/Mobile clients, these are specified using the $args argument.

The OData $args argument is set to a comma-separated list of parameters. These are parsed and created as public variables containing the specified values. The first value is assigned to a public variable named m_arg1, the second to m_arg2 and so on.

Here, the public variable m_arg1 will be created and will contain the value “ALFKI” and the public variable m_arg2 will be created and will contain the value 4.

http://localhost:8001/odata/southwind/vtorders?$args=ALFKI,4

So, if your stored procedure is to be used from both desktop and the web/mobile clients, the m_argN naming convention should be used.

If parameters are optional, their existence can be checked in the stored procedure itself, e.g.

create virtualtable vtorders connstr "local" table "orders" as call sp_getorders()

// This is an example of a "local" stored procedure using optional parameters
if isset(m_arg1) and isset(m_arg2)
	select * from southwind!orders where customerid = ?m_arg1 ;
                     and employeeid = ?m_arg2 into cursor localorders
else
	select * from southwind!orders into cursor localorders
endif
return setResultSet("localorders")

https://www.lianja.com/doc/index.php/Virtual_Tables_-_Local_Stored_Procedures

// create virtual table and specify primary key
create virtualtable vt_actors connstr "lianja_mysqltest" alias "vt_actors" primarykey "actor_id";
       properties "keepalive=1;rowid=1" as select * from actor 
// or alter an existing virtual table
alter virtualtable vt_actors primarykey "actor_id"
// keyfieldlist and primarykey are synonymous
alter virtualtable vt_actors keyfieldlist "actor_id"

https://www.lianja.com/doc/index.php/Virtual_Tables_-_Lianja_Cloud_Server

VFP OleDB [examples]

Published on 2018-08-272018-07-25 by homunizamLeave a comment

You can use OleDB with the VFP OleDB driver if you need to work directly with existing VFP data. This works in desktop and cloud Apps.

OleDB is a COM technology so it is Windows-only. An ODBC driver for VFP can be downloaded from https://www.devart.com/odbc/

private oConn, oRS, m_count
oConn = createobject("ADODB.Connection")
oRS = createobject("ADODB.Recordset")
lcConnString = "Provider=vfpoledb;Data Source=C:\Temp\Northwind\northwind.dbc"
oconn.ConnectionString = lcConnString
oconn.Open()
oRS.Open("select * from customers where customerid='BOTTM'", oconn)
m_count = oRs.fields.count
// display the first record selected
for i=0 to m_count
    try
        ? "Name=" + oRS.Fields.item(i).Name + ",value=" + etos(oRS.Fields.item(i).Value)
    catch
        // ignore error from Memo
    endtry
endfor

Notice how you must use the item() method on the Fields property as this is exposed only as a property not a method.

The output produced is as follows:

Name=customerid,value=BOTTM
Name=companyname,value=Bottom-Dollar Markets                   
Name=contactname,value=Elizabeth Lincoln             
Name=contacttitle,value=Accounting Manager            
Name=address,value=23 Tsawassen Blvd.                                          
Name=city,value=Tsawassen      
Name=region,value=BC             
Name=postalcode,value=T2F 8M4   
Name=country,value=Canada         
Name=phone,value=(604) 555-4729          
Name=fax,value=(604) 555-3745

Note: There is a known issue with retrieving memo fields using the VFP OleDB driver which is why TRY/CATCH to ignore the error.

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

Variables [examples]

Published on 2018-08-272018-12-03 by homunizamLeave a comment

Decrement memory variable by one

i=100
do while i > 0
    --i
enddo

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

Increment memory variable by one

i=0
do while i <100
    ++ i
enddo

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

Return scope information about a memory variable or array

VARINFO

function func1
public m_var1
private m_var2
? "m_var1 " + varinfo("m_var1")
? "m_var2 " + varinfo("m_var2")
?
endfunc
 
func1()

m_var1 Public                                                                   
m_var2 Private (FUNC1)

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

Delete memory variables and free the storage that they were occupying

UNSET

if isset(m_var)
  unset(m_var)
endif

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

Check whether a variable exists

ISSET

if isset(m_var)
  // m_var exists
endif

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

Declare a memory variable or array local to the current procedure and optionally assign a data type and value

LOCAL

local cTmpbuf
? cTmpbuf
.F.

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

Declare public memory variable and optionally assign a data type and value

PUBLIC

public i as numeric, j as character, k

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

Delete memory variables and free the storage that they were occupying

RELEASE

release i,j,k
release all like code_*
release all except c?de_*
release all

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

Declare memory variables private to a procedure or program and optionally assign a data type and value

PRIVATE

private i as numeric, j as character, k 
i = 42
? k
.F.

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

Release all memory variables

CLEAR MEMORY

clear memory

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

Save the result of an expression in a memory variable

STORE

store "hello " to string1
store string1 + "world" to string2
? string2
hello world
area = length * width
area = "change to a string"

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

Save the current memory variables to a file

SAVE

save to monday all like mon_*
save to others all except mon_*

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

Restore memory variables and arrays previously saved with the SAVE command

RESTORE

restore from monday additive

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

Initializes memory variables corresponding to the current record of the active table

CLEAR AUTOMEM

use customers
store automem
clear automem

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

Generate memory variables corresponding to the current record

STORE AUTOMEM

set locktype to optimistic
use customer
store automem
@1,1 get m.name
@2,1 get m.address
@3,1 get m.state
read
if not change()
    replace customer.name with m.name,;
    customer.address with m.address,;
    customer.state with m.state
endif

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

Copy the contents of fields to an array or to a series of memory variables

SCATTER

use addresses index add_1
seek "Seymour House"
if found()
    scatter field like add* to aTemp
endif

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

Determines whether dynamically created variables are created as LOCAL

SET LOCAL

function calledfunc
	if isset(cVar1)  // returns .F.
		? cVar1
	endif
	if isset(cVar2)  // returns .T.
		? cVar2
	endif
endfunc
 
set local on  // default
cVar1 = "I'm local"
private cVar2
cVar2 = "I'm private"
calledfunc()

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

Determines whether variables must be pre-declared

SET STRICT

set strict off
m_var = date()
 
set strict on
private m_var
m_var = date()

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

Display the contents of the current memory variables

LIST MEMORY

list memory

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

Display the contents of the current memory variables

DISPLAY MEMORY

display memory

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

procedure if(if)
return if
 
if = "hello"
if if = "hello"
    echo if( if )
endif

There are no reserved words in Lianja. Command names can be used as variables names and database field variables.

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

private x
x = 10    // x already exists
y = 10    // y does not yet exist so it is created
 
set strict on
z = 10   // error is thrown as z does not exist and STRICT is ON

Variables in Lianja do not need to be explicitly declared, although they should be for better code readability and maintainability. When an expression is assigned to a variable, if the variable does not already exist then it will be created implicitly unless SET STRICT ON is in effect.

Simple Variables

Variable names must begin with a letter (A-Z, a-z) or an underscore (-), followed by any combination of letters, digits or underscores. The variable name can be of any length, but only the first 32 characters are significant, so these must be unique. Lianja ignores the case of letters, so m_var, M_VAR, and m_VaR would all be treated as the same memory variable name. The name given to a variable has no bearing on the type of data that is, or can be, stored in it. In fact, the type of data stored in a particular variable can be changed at any time unless SET STRICT is ON, in which case Lianja will type check variables on assigment to them.

m_var = 1234
m_var = 'a character value'
? m_var + 100

Variables can be declared and optionally initialized before used.

private m_var = 1234
m_var = 'a character value'
? m_var + 100

Variables can optionally be declared as specific datatype.

private m_var as numeric = 1234
m_var = 'a character value'    // throws an error

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

Assigment Statements

The assignment statement stores the result of the expression expression into a variable.

variable = expression

If the variable does not exist and STRICT is OFF, then it is created. If the variable already exists, its contents are updated. If the variable does not exist (has not been declared) and STRICT is ON, then an error is thrown. When STRICT is ON, you should pre-declare variables before assigning values to them using the private, public or local commands.

private myvar
 
set strict on
// no error as myvar has already been declared
myvar = "hello world"
 
set strict off
// error as newvar has not been declared
newvar = 10

You can declare and initialize variables in one statement

private myvar = "hello world today is " + cdow( date() )

Lianja automatically performs type conversions for variables. If, for example, an existing variable called name contains a character string, and the command name=10 is executed, the variable will automatically be converted to a numeric variable.

If you explicitly tell Lianja what type of data can be stored in a variable, it will perform data type checking at runtime.

private myvar as character = "hello world today is " + cdow( date() )
 
// an error will be thrown because myvar was declared as a character
myvar = 10

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

private nVar as numeric
// Valid assignment
nVar = 1234
// Throws error
nVar = 'a character value'

public cVar1 as character, nVar1
private cVar2, nVar2 as numeric
local cVar3, nVar3, dVar3 as date

public cVar1 as character 
cVar1 = 'start value'
private cVar1 as character 
cVar1 = 'start value'
local cVar1 as character 
cVar1 = 'start value'
store 'new value' to cVar1
cVar1 = 'newer value'

store 'new value' to cVar1, cVar2

nVar = 1234
echo nVar
echo m.nVar
echo m->nVar

if isset(nVar)
  // nVar exists
endif

if isset(nVar)
  unset(nVar)
endif

private cVar as character
cVar = "Hello World"
? type("cVar")
C
? is_string(cVar)
.T.

private nVar as numeric 
nVar = -12.34
? type("nVar")
N
? is_float(nVar)
.T.
? is_int(nVar)
.T.

private dVar as date 
dVar = date()
? type("dVar")
D

private lVar as logical 
lVar = .T.
? type("lVar")
L

private tVar as datetime 
tVar = datetime()
? type("tVar")
T

private yVar as currency 
yVar = $99.99
? type("yVar")
Y

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

Variables

private x
x = 10    // x already exists
y = 10    // y does not yet exist so it is created
 
set strict on
z = 10   // error is thrown as z does not exist and STRICT is ON

Simple Variables

m_var = 1234
m_var = 'a character value'
? m_var + 100

Variables can be declared and optionally initialized before used.

private m_var = 1234
m_var = 'a character value'
? m_var + 100

Variables can optionally be declared as specific datatype.

private m_var as numeric = 1234
m_var = 'a character value'    // throws an error

Keywords

There are no reserved words in Lianja. Command names can be used as variables names. At first glance this seems strange, but provides for greater flexibility when declaring and referencing memory variables and database field variables, as you do not need to concern yourself about names that may already be used as commands.

As an extreme example, the following code will compile and run. It will output “hello”

procedure if(if)
return if
 
if = "hello"
if if = "hello"
    echo if( if )
endif

Statements

The statement is the basic unit of programming in any programming language. Statements in Lianja are delimited by a newline.

echo "Hello world"

You can extend statements over multiple lines by placing a ‘;’ at the end of the line:

echo "Hello ;
world" + ;
" this is a multi-line statement"

Assigment Statements

The assignment statement stores the result of the expression expression into a variable.

variable = expression

private myvar
 
set strict on
// no error as myvar has already been declared
myvar = "hello world"
 
set strict off
// error as newvar has not been declared
newvar = 10

You can declare and initialize variables in one statement

private myvar = "hello world today is " + cdow( date() )

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

Users and Roles [examples]

Published on 2018-08-262018-07-25 by homunizamLeave a comment

(OS) login name of the current user

USER

? user()
william

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

Login name of the current user as defined in the Lianja Users Workspace

USERNAME

? username()
william

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

Dynamic data mask for the specified column for the current user

USERDATAMASK

open database southwind
? username()
? userdatamask("customers","customerid")

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

Domain/tenancy information for the current or specified user as defined in the Lianja Users Workspace

USERDOMAIN

? userdomain()
lianja.com

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

Domain/tenancy information for the current or specified user as defined in the Lianja Users Workspace

USERTENANCY

? usertenancy()
lianja.com

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

Email address for the current or specified user as defined in the Lianja Users Workspace

USERMAIL

? useremail()
myuser@lianja.com

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

Full name of the current user as defined in the Lianja Users Workspace

USERFULLNAME

? userfullname()
William Colline

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

Roles for the current or specified user as defined in the Lianja Users Workspace

USERROLES

? userroles()
hr,sales

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

Rowfilter condition for the current or specified user for the current or specified table

USERROWFILTER

// 'Harry' has the 'salesuk' role
? userrowfilter("Harry","southwind","customers")  //  --> 'country = "UK"'
 
// 'Sally' has 'salesusa' role
? userrowfilter("Sally","southwind","customers")  //  --> 'country = "USA"'
 
// Logged in as 'Harry'
open database southwind
use customers
? userrowfilter()  //   --> 'country = "UK"'
? userrowfilter("Sally")  //  --> 'country = "USA"'

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

String representing masked data

MASKEDDATA

open database southwind
use customers alias cust in 3
// Display customerid data with 'partial' mask applied
? maskeddata(cust,"customerid","partial")
? maskeddata(3,"customerid","partial")
// Display customerid data with current user's dynamic data mask applied
? maskeddata(cust,"customerid")
? maskeddata(3,"customerid")

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

Enable/disable use of LDAP/ActiveDirectory for user/group authentication

LIANJA_LDAP

set LIANJA_LDAP=ON
set LIANJA_LDAP=OFF

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

Define LDAP/ActiveDirectory Base DN

LIANJA_LDAP_BASEDN

set LIANJA_LDAP_BASEDN=OU=AD LDS Users,O=Lianja,C=UK

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

Define LDAP/ActiveDirectory server

LIANJA_LDAP_SERVER

set LIANJA_LDAP_SERVER=192.168.80.139:389

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

LDAP/ActiveDirectory groups the specified user is a member of

LDAP_USERROLES

? ldap_userroles("Mandy Monroe","192.168.80.139:389","OU=AD LDS Users,O=Lianja,C=UK"))
HR,sales

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

Test an LDAP/ActiveDirectory user login and return the Groups and Roles the user is a member of

LDAP_LOGIN

? ldap_login("Mandy Monroe","CcxkTpjFbfw2ulmv","192.168.80.139:389","OU=AD LDS Users,O=Lianja,C=UK"))
HR,Sales,Readers,Users

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

Grants access privileges for users to tables

GRANT

// Grant update privilege for columns lastname and firstname and insert for the table
GRANT UPDATE (lastname, firstname) INSERT;
  ON customer;
  TO '[20,100]' 
 
// Grant all privileges to all users
GRANT ALL ON test TO PUBLIC

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

Revoke access privileges for users to tables

REVOKE

// Revoke update privilege for columns lastname and firstname and insert on the table
REVOKE UPDATE (lastname, firstname) INSERT;
  ON customer;
  FROM '[20,100]'
 
// Grant all privileges to all users
REVOKE ALL;
  ON test;
  FROM PUBLIC

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

Currently active users on the system

SYSACTIVEUSERS

SELECT user_name FROM sysactiveusers

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

Alert records for watched records

SYSALERTS

SELECT * FROM sysalerts WHERE user = user()

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

Username and email information for alerts for watched records

SYSUSERS

SELECT * FROM sysusers WHERE user = user()

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

User access group

ACCESS

if access() > 100
    dialog box "Sorry, access denied."
endif

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

Group name

GETGRNAM

use accounts
@2,3 get name
@3,3 get rates;
  when getgrnam() = "lianja"
read

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

Group identity

GETID

if getgid() > 100 and getgid() < 200
//...
endif

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

User identity

GETUID

if getuid() > 100 and getuid() < 200
//...
endif

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

Dynamic role information

SYSPERMS

select * from system!sysperms

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

User and role information

SYSROLES

select * from system!sysroles

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

Role based dynamic data masking

SYSDATAMASKS

select * from system!sysdatamasks

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

Role based row level filters

SYSROWFILTERS

select * from system!sysrowfilters

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

UI States [examples]

Published on 2018-08-262018-07-25 by homunizamLeave a comment

To cause all UI components to revert back to their default attributes (no UI state set), use:

showdocument("resetstate")

Lianja.showDocument("resetstate")

The ‘Show Orders’ button changes the UI State to ‘state1’.

?changestate:state1

and the ‘Hide Orders’ button changes the UI state to ‘state2’.

?changestate:state2

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