XML [examples]

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


 

Advertisements

WebView custom Gadget [examples]

<%@ 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>';

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>

WebView [examples]

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]

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]

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]

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

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")

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()

or

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}")

or

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]

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