Lianja SQL Server

Lianja SQL now supports aggregate functions in GROUP BY … HAVING and also ORDER BY.


 

Advertisements

Lianja files

Q:

in App Builder this work..
but in App Center, this command:

Code:
select 0
SELECT * FROM syscolumnconstraints where lower(table_schem) = lower(dbname) into cursor cur_columnconstraints

 

not work..

error message: File ‘syscolumnconstraints.dbf’ does not exist.

I think this is not a real table..

A:

Correct: it is not a table. It is effectively (don’t know what it really is) a stored procedure that reads the information for you. You can’t write to it

System tables do not exist in the system database, they are pseudo tables that are created dynamically.



Editing the .lianja file for an app is discouraged and unsupported.


Q:

while reviewing the example_jqueryui app.

I opened the app and went to the Page:Navigation Panelata Source and it showed lib:/treeview.rsp?ondblclick=page1_navtreeselection&key =address%2Bcity%2Bcountry.

I clicked on the ellipse and it brought up treeview.rsp in the script editor but it was blank. On clsing the script and returning to the page work space Data Source was showing

app:/lib:/treeview.rsp?ondblclick=page1_navtreeselection&key =address%2Bcity%2Bcountry

and the treeview wasn’t working. Removing the app:/ prefix got everything working again but the editor still brings up a blank script and adds the app:/ prefix. This occurs from the app inspector as well as from the page attributes panel. In the end, in order to view/edit the treeview script I have to open it from the library work space.

A:

yes, that’s an editor infelicity. Same thing happens when debugging and you get the “Do you want to fix” dialog: the debugger thinks the file is always in the app directory. Click on “Yes” (you want to edit) and it creates a blank one in the app directory.


 

JSON and OData

Q:

The app has to be an executable, so electron looks perfect.
It is a disconnected app that has no database and needs to be installed all over the globe as just an executable and some support files.
All of the data is coming from already existing web services. So I am decoding json data with getUrl and json_decode.

I will try a temp table/cursor on init

A:

until we get disconnected data (it’s in the roadmap, currently for 4.1), that will not work. The cursor never exists on the mobile client: JSON record(s) are sent from the server, and updates are sent back by JSON.


Q:

In a canvas section, there are input textbox, combobox and checkbox. These input items table fields (character/numeric/logical) have data source bind to them.

When I use getJson() to pass all the item values to backend.prg, all the data types decode from the Json parameter seems to be character or logical .f. if nothing enter into the textbox.

Is this the correct behaviour?

A:

JSON format is text only

When exchanging data between a browser and a server, the data can only be text.

Logical .f. is not correct JSON format and you should provide some value if nothing is entered in textbox.

A2:

I would also recommend that you use the base64_encode() function to encode the JSON if you are passing it as parameter. In your server side proc you would then use use base64_decode() to decode it.

This prevents any issues with special characters being transferred.


Lianja Cloud Server supports OData-compatible data access.

The Server handles ODBC connections as well as HTTP requests using OData URIs. In the following article I will show you how to use Lianja Cloud Server with OData URIs that will allow you to perform CRUD (Create, Read, Update and Delete) operations on your data.

Making OData requests from jQuery and/or the Lianja HTML5 Client in a Web App is extremely straightforward and simple.

Unlike other OData implementations, Lianja Cloud Server OData services does not require any server-side configuration of web services.

http://www.lianja.com/resources/blog…-cloud-server-

You can use Lianja cloud server odata calls to perform CRUD operations on virtual tables too so this functionality is not limited to native Lianja data; MSSQL, MySQL, PostgreSQL, Oracle and others are all supported.

Let me just emphasize again. Unlike other OData implementations, Lianja Cloud Server OData services does not require any server-side configuration of web services.


Using OData to query MSSQL is working fine for me.

Name:  Screen Shot 2017-01-31 at 9.07.51 AM.jpg Views: 45 Size:  170.7 KB

The example shows how to use it with Lianja SQL Server.

wwwroot should be set to C:\lianja\cloudserver\tenants\public\wwwroot\

Remember also that you have the

odata_create(),

odata_read(),

odata_update() and

odata_delete()

functions that can be used in both programs and rsp pages. These functions provide an OData abstraction above the target database SQL syntax. e.g. you can fetch MSSQL data using $limit (see below).

Name:  Screen Shot 2017-01-31 at 10.01.45 AM.jpg Views: 46 Size:  92.5 KB


$format=img, if you select an image field from a table then it will be output as an IMG tag with embedded data encoding so that you can insert this into the DOM to fetch and display an image.

Name:  Screen Shot 2017-01-31 at 10.13.25 AM.jpg Views: 45 Size:  112.4 KB

By specifying memo fields you can also perform CRUD operations on JSON data.


Q:

Is it also possible to use the Lianja oData interface to call a server side function which then returns JSON data?

to allow our non-Lianja clients access to our server side functions.

by including and utilising the LianjaCloudDataServceis.js in our non-lianja clients

A:

Yes. You have several choices.

1. OData calls
2. result = Lianja.evaluate(‘your_proc( args )’); calls on the client with your_proc residing on the server. ( you can also map client-side JavaScript functions by describing them in exports,conf )
3. Retrieve results of a dynamic .rsp page located in the server.


You can also write full rsp pages that return json results fast super fast.


Q:

If I have a stored procedure named sp_testfunction.prg in my SouthWind database, which returns the customers table in json format, how would I call the function using OData?

A:

Normally you would use Lianja.evaluate() from an open app, but to evaluate code from the OData URI with no app open you need to create an app that will be used to contain all of your stored procs for fetching JSON encoded data and execute like this.

Code:
http://localhost:8001/odata?$eval=fetchcustomer()&$app=yourapp

In order to call server side stored procedures in a database I have now recognized databasename! when using $eval in the 3.3 final release (next build):

Code:
// sp_fetchcustomer.prg stored procedure in southwind database
//
// usage: 
//
//    http://localhost:8001/odata?$eval=southwind!sp_fetchcustomer()
//    or
//    http://localhost:8001/odata?$eval=sp_fetchcustomer()&$database=southwind
//
parameter p_custid
if parameters() = 0
    // fetch all customer records
    select * from southwind!customers into cursor mycur
else
    // fetch a specfic customer record
    select * from southwind!customers where customerid = "&p_custid" into cursor mycur
endif
copy to arrayofobjects myobj
// return JSON encoded data to the client
return json_encode(myobj)

Remember that you can test all your stored procs in the “Console” workspace:

Code:
odata_read("/odata?$eval=southwind!sp_fetchcustomer('ALFKI')")

If your data handling procs are in a library you can also make calls like this:

Code:
odata_read("/odata?$eval=mylibrary::fetchcustomer('ALFKI')")

Q:

I created myLibrary.prg containing your fetchCustomer procedure.

When I run odata_read(“/odata?$eval=mylibrary::fetchcustomer(‘ALFKI’)”) from console it works as expected.

However, if I send


http://localhost:8001/odata?$eval=myLibrary::fetchCustomer(%27ALFKI%27)

from a browser I get a 404 error:
The requested URL /odata?$eval=myLibrary::fetchCustomer(%27ALFKI%27) was not found on this server.
A:
Any odata API calls from the browser will reference a deployed library as opposed to the development version. You need to deploy it first.
For performance reasons, the library is opened up only once and kept open which is why you are seeing this behavior. On a heavily loaded system with many concurrent requests occurring this is required so that the library is not opened and parsed for each odata API call.

JS

Q:

I created a file called javascriptfuntions.js.

In that file, I copied the example from the road map.

Code:
function page1_myDialog()
    var cont = createObject("myContainer", "container");
    cont.backcolor = "white";
    cont.addObject("myTextBox", "textbox");
return cont;

In the console – I tried to loadlibrary(‘javascriptfunctions.js’) – but it threw a parse error.

So I added the curly brackets around the javascript function as you would normally do, but still no luck.

A:

Yes, it should have the curly brackets – I have corrected the example in the roadmap.

Using a JavaScript generated container in a Lianja.showDialog() or Lianja.showDialogPanel() is supported in the web client. Your post title mentions VFP



Q:

add a blank grid section to an application.

In a JavaScript application, I am trying to add a blank grid and then populate it later from an array using additems().

A:

Here’s what I did in on app:

1) created a table with the required fields and also had a cSession field of C(26).
2) in the UI, I added a property to a public var oApp of cSessionID
3) this property was, on the INIT of the UI app, set to guid() (this was in VFP, but should work here)
4) I then set a filter on the grid of: cSessionID = oapp.cSessionID — which meant the grid was showing empty
5) Then I could “create” records from anywhere, making certain to use oapp.cSessionID as the cSessionID of the table.

The same could be done with a VT and a parameter for cSessionID of course.

In the close of the database (assuming you don’t close it in the UI), you can delete for cSessionID = oapp.cSessionID.


all objects in a JavaScript canvas must be named uniquely, throughout the application. Thus, from any page you are on, the lbltext object will be available.

This is due to the global nature JavaScript: either a var is scoped within the function, or is global everywhere.


Q:

For an RSP page – I can use the following code

HTML Code:
private m_embedded = getParameter("embedded", "false")
private m_libpath
set macros on
set fixed on 
if isServer()
	m_libpath = "../../library/"
else
	m_libpath = "lib:/"
endif
if m_embedded = "false"
	? '<html>'
	? '<head>'
	? '<link rel="stylesheet" href="&m_libpath/bootstrap-3.3.4/css/bootstrap.min.css">'
	? 'http://&m_libpath/jquery-1.10.2/jquery-1.10.2.min.js'
	? 'http://&m_libpath/bootstrap-3.3.4/js/bootstrap.min.js'
	? '</head>'
	
endif
%>

How do I accomplish the same thing for a .JSSP?

A:

Instead of using ? <expr> to output text you use print(<expr>) pr(<expr>) or echo(<expr>) in JavaScript.

The following associative arrays are available to you in JavaScript on each page request.

$_REQUEST
$_SERVER
$_COOKIE
$_ARGS
$_GET
$_POST
$_FILES
$_SESSION

So for example you can reference $_ARGS[“embedded”]

As per your requirements I have added:

Lianja.getUrl( “url…” ) to Lianja 3.4.1 which returns a string which is returned from the remote server.

This is available in desktop and web framework.

Note that you can also use this on the client.

Lianja.OData_Read(url, callback);

e.g.

Lianja.OData_Read(“http://xxxx…&#8221;, function(result)
{
});

This function returns JSON and can handle any URL.

You can use Lianja.evaluate( “vfp_expression” ) to evaluate any function not available in jssp pages.


Q:

I tried adding a “hello word” into a commandbutton.click delegate, but it keeps returning a “unterminated string” error”
Name:  untermstr.jpg Views: 28 Size:  62.8 KB

A:

You are writing JavaScript in a .prg library file.

You should write JavaScript in a .js library file.


Lianja provides many built-in Javascript functions. In terms of using Javascript libraries the CommonJS module standard is handled in desktop, web and mobile.

The CommonJS group defined a module format to solve JavaScript scope issues by making sure each module is executed in its own namespace.
This is achieved by forcing modules to explicitly export those variables it wants to expose to the “universe”, and also by defining those other modules required to properly work.

To achieve this CommonJS gives you two tools:

  1. the require() function, which allows to import a given module into the current scope.
  2. the module object, which allows you to export something from the current scope.


Further details of the Lianja/VFP functions available in JavaScript/TypeScript can be found at:
https://www.lianja.com/doc/index.php…/VFP_functions

The documentation on JavaScript Server Pages (.jssp) includes an example for require() with module.exports.
https://www.lianja.com/doc/index.php…t_Server_Pages


 

Categories JS

Intellisense and CodeAssistant

I have now handled stepping over a character if the character under the cursor is that character e.g. ” ‘ ] or ). This makes it easier to type over some of the automatic completions that occur.

Typing commands is now a lot more powerful with full CodeAssist as you type, not just Intellisense.

Parameter hints for functions can take a default value, be [optional] or require a value. After typing press Return moves to the next parameter of if no more inserts a newline.

Also, in this build. Code folding and Snippet manager.

Alt+S for snippet manager
Alt+F to fold all code
Alt+U to unfold all code
Alt+C to toggle code folding on and off
Alt+I to toggle intellisense on and off

Many classes now have their intellisense in place e.g. Lianja, Form, TextBox… Others being added before final release.

Code Folding and Intellisense is now all done in a background worker thread to optimize UI performance.

Also note…

When typing a command e.g list you will see the pick list for all list commands such as

list status
list structure
list files

The Code Assistant now handles command abbreviations now, so if i type:

lif<Return>

list files will be placed in the command line replacing lif.

Similarly code snippets are all part of Intellisense now. So if i type:

dow<Return>

The code snippet for “do while” will be inserted and the cursor positioned on the first code insertion mark. Press Ctrl+Return to move to the next code insertion mark as you type.

You can now create collapsible blocks of code by placing the code in a “region”.

#region some description
#endregion

For anyone interested, the code snippets are in the lianja\help directory with a .snippet file extension.

Snippet files can be empty in which case the name of the file is inserted with _ replaced by a space. e.g. list_structure.snippet which cause “list structure” to be added to the pick list and if selected it will be inserted into the command line.

Snippets can contain code insertion marks.

${name}

or with a default value if <Return> is pressed when it is highlighted.

${name:something}

or for optional input

${name:}

${} is an invisible code insertion mark which causes the cursor to move to that position.

You can create your own code snippets and have them added to the intellisense database using the Snippet Manager ( Alt+S ).


 

e.g.

Code:
local tb as textbox

Name: Screen Shot 2017-04-10 at 2.34.16 PM.jpg Views: 92 Size: 41.4 KB

Then when editing if you type tb. this is what you see.

Name: Screen Shot 2017-04-10 at 2.34.37 PM.jpg Views: 91 Size: 44.1 KB

As you type the pick list is filtered by the characters you have typed. You can then press Tab to select that item and stay on the command line

or Return to select it and move to the next line. As you backspace the list changes.

Name: Screen Shot 2017-04-10 at 2.47.37 PM.jpg Views: 89 Size: 42.1 KB

Here is the list of classes we will provide intellisense for (you can add your own quite easily).

grid
cursoradaptor
actionbar
activex
application
camera
column
combobox
commandbutton
commandgroup
container
control
cursor
database
dataenvironment
datetextbox
datetimetextbox
editbox
editor
empty
field
pageframe
pictureflow
popupmenu
checkbox
collection
form
fields
formitem
formset
gadget
header
httpserver
hyperlink
image
label
labelengine
lcdnumber
line
listbox
menu
menubar
menuitem
networkmanager
optionbutton
optiongroup
page
pagebuilder
progressbar
recordset
reportengine
richeditbox
section
separator
session
shape
slider
spinner
splitter
systemsemaphore
systemtrayicon
textbox
timer
toolbar
toolbox
tree
treeitem
videoplayer
lianja
webview

My goal is to make you more productive so as you type pressing Tab on an incomplete command word or keyword will select that and place on the command line. Don’t worry if you don’t know all the LIanja/VFP commands as these are filtered as you type and pressing Return will select the currently highlighted command.

Name: Screen Shot 2017-04-10 at 2.53.32 PM.jpg Views: 89 Size: 43.8 KB

Name: Screen Shot 2017-04-10 at 2.53.46 PM.jpg Views: 89 Size: 41.0 KB

At any time you can press F1 and the help page for that command you are typing will be retrieved and displayed in a browser windows alongside the App Builder.

Name: Screen Shot 2017-04-10 at 2.57.01 PM.jpg Views: 89 Size: 84.2 KB

As you type if you press Return and the command requires keywords it will be underlined and when you hover the mouse over it, it will tell you what is wrong.

Name: Screen Shot 2017-04-10 at 2.59.42 PM.jpg Views: 90 Size: 41.7 KB


Q:

while we type this, plainly in an editor line?

Code:
page1.section1.

Intellisense to show us all UI elements of THAT standard section (at that hierarchy level), after typing the point?

fldAttribute
fldLabel1
fldMyField2

Of course, properties and methods of selected field, too.


Q:

I do not see the way to declare (temporary) anything similar to

local page1 as page

what will instruct Intellisense to give mi picklist of all sections in page1 of Lianja demo app example_gridfilter.

A:

You need to declare the variable type when using code such as page1.section1 as the type cannot be determined at editing time.

local page1 as pagebuilder

Not page.

i provided to you.

mypage_obj = Lianja.get(“page1”)
mysection_obj = Lianja.get(“page1.section1”)
myfield_obj = Lianja.get(“page1.section1.field1”)

Then intellisense kicks in.

Specifically on a section…

local sec as section

sec = Lianja.get(“page1.section1”)
sec.grid.filter = “”
sec.webview.html = “”
sec.tabview.enabled = .t.
sec.videoplayer.xxx = “”
etc

So if you do this:

local myobj as formitem
myobj = Lianja.get(“page1.section1.field3”)

then you type:

myobj.

You will see the properties/methods of the command button.


1. Hovering over a variable displays the value of that variable as a tooltip.

2. Hovering over a function displays a tooltip with contextual help.

3. Hovering over a command displays a tooltip with contextual help.

You toggle this functionality off and on by pressing Ctrl+/

4. As you type a command and press a space, contextual intellisense for the command is displayed. Certain commands e.g. OPEN DATABASE and USE display a popup database pick list and a popup table list respectively. You can navigate with the cursor up/down keys, double click an item, press spacebar, press Return or press the first letter of the item you want to select.

5. As you type a function followed by a bracket contextual intellisense for the function is displayed.

You toggle this functionality on and off by pressing Ctrl+Space

I will be adding object reflection of properties and methods in a forthcoming version.

And one last thing…

Intellisense is active in the console workspace, command window and the Lianja/VFP script editor.


in Lianja 3.4 we have a new CodeAssistant that will help you develop and edit Lianja/VFP scripts faster than ever before.

The Lianja CodeAssistant consists of:

  • Intellisense
  • IntelliTips
  • Statement completion
  • Context sensitive statement assistance while typing
  • Code suggestions
  • Function and method parameter hints
  • If you press Ctrl and hover over a command word “Quick Info” for the command will appear as a tooltip
  • If you press Ctrl and hover over a variable the “Quick Info” value of the variable will appear as a tooltip
  • Variable/word hilighting while typing
  • Code snippets
  • Code folding
  • Auto indenting
  • Code beautifier
  • Integrated with the Documentation wiki
  • + much more

Intellisense

Lianja now provides Intellisense for commands, functions, object variables and cursors. Intellisense is implemented in a background thread so that it is kept up-to-date in real time.

Command Intellisense 

Typing a command followed by a space pops up a context sensitive picklist of keywords and clauses for the command being typed.

list<space>

Name: Screen Shot 2017-03-09 at 4.54.29 PM.jpg Views: 123 Size: 25.9 KB

Selecting a clause from the picklist e.g for <lExp> will guide you as you type in the statement. At any time you can type Ctrl+Space to enable/disable the command picklist.

Name: Screen Shot 2017-03-09 at 4.55.50 PM.jpg Views: 123 Size: 21.5 KB

Special command Intellisense 

Some commands have special picklists depending on context.

use<space>

This will popup a picklist containing table names.

Name: Screen Shot 2017-03-09 at 4.57.11 PM.jpg Views: 125 Size: 29.8 KB

open database<space>

This will popup a picklist containing database names.

Name: Screen Shot 2017-03-09 at 4.58.12 PM.jpg Views: 123 Size: 21.9 KB

modify command<space>
or
ed<space>
or
mc<space>

This will popup a picklist containing program script filenames.

Function Intellisense 

Typing a function name followed by an open bracket pops up an intellitip for the function.

myvar = str(

Name: Screen Shot 2017-03-09 at 4.59.22 PM.jpg Views: 123 Size: 29.3 KB

After typing in the code for the hilited argument, press Tab or Return to move on to the next argument.

Nested intellitips are stacked and unstacked when a ) is typed.

myvar = str( source, at(

Name: Screen Shot 2017-03-09 at 5.00.13 PM.jpg Views: 121 Size: 29.9 KB

Object variable Intellisense.

Typing an object variable name followed by a . pops up a picklist of properties and methods for the object variable. Hovering the mouse over items in the picklist displays a tooltip with a short description of the item.

Object variable Intellisense requires any of the following to be present in the file being edited.

local|private|public|parameter|lparameter name as classname

or

name = createObject(“classname”)

or

obj.addObject(“name”,”classname”)

or

name = Lianja.getElementByID(“Id”)
name = Lianja.get(“id”)

In the latter case, the specified ‘id’ is introspected to identify the class based on the pages, sections and formitems in the currently open App.

The “Lianja” system object is known to the script editor so now typing:

lianja<period>

Pops up the intellisense for it.

Name: Screen Shot 2017-03-09 at 5.02.01 PM.jpg Views: 124 Size: 27.6 KB

Cursor Intellisense 

Tables that are open during editing are known to the editor so when for example you have the customers table open and you type:

customers.

The columns in the customers table are displayed as a picklist.

Name: Screen Shot 2017-03-09 at 5.09.14 PM.jpg Views: 121 Size: 24.5 KB

Hovering the mouse over a column name will display a tooltip containing useful information regarding the column e.g. data type, width, decimals

You can toggle Intellisense on and off by pressing Ctrl+Space.

Integrated with the Documentation Wiki

Pressing F1 when in the command window or the Lianja/VFP code editor will now fetch and display the help page from the Documentation Wiki for the command or function being typed.

IntelliTips

Moving the mouse cursor over a command while pressing the control key will popup the IntelliTip for the command.

Moving the mouse cursor over a function name followed by a ( while pressing the control key will popup the IntelliTip for the function.

Moving the mouse cursor over a variable name or an objectname.propertyname or a cursorname.columnname while pressing the control key will popup a tooltip displaying the current value.

You can toggle IntelliTips on and off by pressing Ctrl+/

Statement completion

Pressing the return key on an empty line while typing in a statement block will close the statement block off for you and move the cursor onto the next line at the previous block indentation.

Auto indenting

When you press the return key while typing commands the cursor will move onto the next line and auto indent for you.

Code Snippets

Code snippets are a productivity aid when coding. As you type a command any code snippets that match it are displayed in a pick list. Press Return to insert the snippet.

You can edit your own snippets in the Snippet Manager by pressing Alt+S

When a snippet is inserted it contains parameter insertion points e.g. Here is the snippet called ife for an if/else/endif statement.

 

Code:
for ${var:i} = ${start:1} to ${end}    
    ${insert your code here}
endfor

 

If the parameter insertion point contains a : colon) pressing Ctrl+Return will insert the text following the :.

You type in a parameter then press Ctrl+Return to move onto the next one.

Code beautifier

While editing you can press Ctrl+B to beautify your code with statement block indentation.

Code folding

Code blocks are automatically indicated in the left margin with a small + or – icon. Click the icon to fold / unfold the code block for better code readability.

While editing you can press Alt+F and Alt+U to fold and unfold your code respectively..

Extending Lianja with your own Intellisense definitions

If you have existing libraries of classes or functions you can place your own intellisense files in the lianja\help directory. If you look at the existing files in that directory you will see that they are just text files that are preloaded at startup. e.g.

intellitips_vfp_yourcompany.properties contains function definitions
intellisense_vfp_yourcompany.properties contains class definitions

Alternatively you can create a file called references_vfp.config and place it in your app directory. This file can contain function and global variable type definitions that intellisense will use as hints.

The file should contain triple-slash comments like this:

/// <function=”funcname(name as type, name2 as type ,[name3 as type]) returns type // description” />
/// <public=”name as type” />

type can be any Lianja classname or Any, Character, Numeric, Logical, Date, Datetime, Currency, Array, Object

Any of the files you edit can also include type definitions by adding:

/// <reference path=”filename” />

where filename should exist in the lianja help directory or alternatively prefix the name with lib:/ or app:/ to reference definition files in an App or in the library.

My intention is to scan all files in an app in the background worker thread and recognize procedure and function statements with correctly defined arguments that use the “as” notation.

e.g.

proc myproc(arg1 as character, arg2 as numeric)
endproc


https://www.lianja.com/doc/index.php/Script_Editor#The_Lianja_CodeAssistant_for_Lianja.2FVFP


When typing a variable name followed by a . (dot), if the variable is untyped and it begins with “page” it is implied that it is a “pagebuilder” class and the picklist for that will be displayed.

Similarly, if the variable begins with “section” it is implied that it is a “section”,

and if the variable begins with “field” it is implied that it is a “formitem”.

If you follow the suggested format for naming object variables as described on MSDN then Lianja will use heuristics to determine the class of untyped object variables.

 

 

Installing

(After a problem with installation) have a look in lianja\log\log.txt

That will provide information regarding the installation and directories.


Q:

A couple of weeks ago I logged out of the forum. Now, every time I log in and check Remember Me, it doesn’t.
I have to login again if I close the browser.

A:

If you go to the main site www.lianja.com then sign in (if you are not yet signed in) then click “Forums” it works correctly. We use a single sign on for the two sites and occasionally the cookies expire.