MS4BI HELP 2019

by MS4script
01-Introduction
Preface & Copyright
Getting started
Demos Simple
02- Declarations
Structure
Type
03- Instructions
Conditions if,do,while..
String functions Str..()
Get + Include functions Get, Include..
Date-Time Day, Time, Year.
Display
Call
Formulas Complex
04- Database
Connector Ado, Odbc, Dsn, SAP, Ms4
Connect DB Create, delete
Import DataSource Import, Consolidation
Administration Admin, User
05- Sql-Query
Sql
Query Form : BTS,SHOW
Grid option
06- Charts
Forms Show, Win, Image, Excel, Morris...
Pie Std, Donut, 3D...
Bar Std, Stack, Line, Area, Plot
Radar
QrCode, Gauge
Step, Stock...
,Google,Leaflet... GeoMap
,Gantt,Timeline,Org.
07- Dashboard
Form tab, no tab
Responsive UI
08- Report
Report Hierarchy
Report Design
09- Filters
Dialogue + Call
10- Menus
Menu Design
Menu Frameset
Menu Special
11-UI : Responsive
Tables Responsive,Frame,position
List Group
Text
Panel Header, body, Footer
Button Button, Progress Bar
Icon & Button
Collapse Group, Panel
Notifications
12- Encryption
Encrypt
Decrypt
13- Windows
Dialogue
Picture, Frame, Button..
MsgBox
Grid
14- Cloud.
Account Open
15- Css, html, Php, js.
Insert Native language Begin_sw..End_Sw
16-Install + tools.
Installation & Configuration This Menu
Generate Native... Php, Html, js..
Code Generator Ms4 Script
MarkDown Ms4 Script
Source Code Menu Help This Menu
written with ms4script
     

BASIC HELP : MS4BI by MS4script 2019 version 1

Chapter 5 : SQL and QUERIES

KEYWORD : MS4_SQL {driver}

Definition

Creates an SQL instruction for managing ADO, ODBC, DSN or MS4 DB databases.

Syntax

 	  	
        Ms4_SQL:  {driver} 
      (  "string or literal = SQL instruction" + "remainder of ..string or literal = SQL instruction",
(optional : variables) ,ivalue, Lvalue, fvalue, dvalue, ) ;
-The variables are defined using the following convention: %i integer, %l long ,%f float ,%d double
MS4DB syntax = SQLITE3

Examples

 	
 ---------------------------------------------------------------------------------  
                                             INTO                           
 ---------------------------------------------------------------------------------   
   
	ConnectDB{Ado:"xls"}( LFile1Excel);
 	display "insert Table Japanese : CONSOLIDATION 1  ";
   	Ms4_SQL{Ado:"xls"}(" SELECT  * into " + " %s " + "  from [中国最大の都市のリスト$]", Table1SheetJapanese) return x ;
 	 display " Σ╕¡σ¢╜μ£Çσñºπü«Θâ╜σ╕éπü«πâ¬πé╣πâê sql return :",x; 

OR

--------------------------------------------------------------------------------- CREATE VIEW --------------------------------------------------------------------------------- Ms4_SQL{Ms4DB} ("CREATE VIEW [ms4EmplTotalBusiness2012] AS " + "SELECT a.LastName, sum(a.unitPrice*a.quantity) AS BUSINESSID2012 ,"+ " count(DISTINCT a.productID) AS TOTALPRODUCTID2012" + " FROM [ms4EmplConsolidationBusiness] AS a " + " WHERE a.orderDate Between DATETIME('2012-01-01') And DATETIME('2012-12-31' ) group by employeeId ");

OR

--------------------------------------------------------------------------------- CREATE TABLE --------------------------------------------------------------------------------- BEGIN Ms4_SQL{Ado:"xls"}("CREATE TABLE [sheet1] (col1 integer,col2 TEXT,age INTEGER,cash FLOAT) "); END; --------------------------------------------------------------------------------- INSERT INTO --------------------------------------------------------------------------------- BEGIN Ms4_SQL{Ado:"xls"}("INSERT INTO [sheet1$] (Col1,Col2,Age) Values ( 1 ,'Information1',3) ",); i:=2; NameVar :="Information2"; wnt := 100000.00; Ms4_SQL{Ado:"xls"}( "Insert Into [sheet1$] (Col1,Col2,cash) Values (%i,'%s',%f) ",i,NameVar,wnt); Ms4_SQL{Ado:"xls"}( "Insert Into [sheet1$] (Col1,Col2,age,cash) Values (3,'Information31', 76 , 4500.00 ) "); Ms4_SQL{Ado:"xls"}( "Insert Into [sheet1$] (Col1,Col2,age,cash) Values (4,'Information32', 76 , 4500.00 ) "); END; --------------------------------------------------------------------------------- DELETE --------------------------------------------------------------------------------- BEGIN Ms4_SQL{Ado:"xls"}( "Delete From [sheet1$] Where Col2 = 'Information31' "); END; BEGIN Ms4_Query: Win{Ado:"xls"} ("SELECT * from [sheet1$] "); END;

OR

ConnectDB {Ms4DB} (LEnvDemoExcelDB); Ms4_SQL{MS4DB} ("ALTER TABLE ms4RefEmployees RENAME TO sqlitestudio_temp_table"); Ms4_SQL{MS4DB} (" CREATE TABLE ms4RefEmployees ( " + " EMPLOYEEID UNIQUE INTEGER," + " LASTNAME TEXT," + " FIRSTNAME TEXT," + " TITLE TEXT," + " TITLEOFCOURTESY TEXT," + " BIRTHDATE TEXT," + " HIREDATE TEXT," + " ADDRESS TEXT," + " CITY TEXT," + " REGION TEXT," + " POSTALCODE INTEGER," + " COUNTRY TEXT," + " HOMEPHONE TEXT," + " EXTENSION INTEGER," + " PHOTO TEXT," + " REPORTSTO FLOAT," + " PHOTOPATH TEXT ) "); Ms4_SQL{MS4DB} ("INSERT INTO ms4RefEmployees ("+ " EMPLOYEEID," + " LASTNAME," + " FIRSTNAME," + " TITLE," + " TITLEOFCOURTESY," + " BIRTHDATE," + " HIREDATE," + " ADDRESS," + " CITY," + " REGION," + " POSTALCODE," + " COUNTRY," + " HOMEPHONE," + " EXTENSION," + " PHOTO," + " REPORTSTO," + " PHOTOPATH" + " )" + " SELECT EMPLOYEEID," + " LASTNAME," + " FIRSTNAME," + " TITLE," + " TITLEOFCOURTESY," + " BIRTHDATE," + " HIREDATE," + " ADDRESS," + " CITY," + " REGION," + " POSTALCODE," + " COUNTRY," + " HOMEPHONE," + " EXTENSION," + " PHOTO, " + " REPORTSTO," + " PHOTOPATH " + " FROM sqlitestudio_temp_table" ); Ms4_SQL{MS4DB} ("DROP TABLE sqlitestudio_temp_table");

Prerequisites

CONNECTDB

Remarks

- The SQL instruction must obey syntax depending on the type of connector
- Max. number of characters 2500 per SQL instruction
- The variables are defined using the following convention: %i integer, %l long ,%f float ,%d double

See Also

MS4_IMPORT:NEW DB, MS4_QUERY : WIN or WEB, DRAWGRID
Examples :
\MondaySoftware\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\99-DATABASE\VersionADO
driver:\MondaySoftware\MS4BI\zConnectors-ADO, ODBC, SQLITE3



KEYWORD : MS4_SQLFETCH{driver}

Definition

Scrolls through ADO, ODBC, DSN or MS4 DB databases

Syntax

 	  	
        Ms4_SQLFetch{driver}("SELECT column 1,column 2.. from ... where... ")
 		 ForEachRow :
			 	var1 	:= GetDataColumn:Number ,FLOAT or INTEGER OR DOUBLE OR CHAR;
				var2	:= GetDataColumn:Number,INTEGER;
	  .....
		End_ForEachRow ; 	
	

example

 		   
Ms4_SQLFetch{ms4db}("SELECT  * FROM  [Sum2013] ")
 ForEachRow :
    zBusinessID 	:= GetDataColumn:1,FLOAT;
    zTotalProduct	:= GetDataColumn:2,INTEGER;
  Display "->[ms4EmplTotalBusiness2013]->sum(businessd2013) : " ,zBusinessID , "Total Product 2013 :", zTotalProduct;
 End_ForEachRow ; 
 	
	

Prerequisites

CONNECTDB

Remarks

- The SQL instruction must obey syntax depending on the type of connector
- Max. number of characters 2500 per SQL instruction

See also

MS4_QUERY : WEB ,MS4_SQL, DRAWGRID, FORMSHOW, FORMMS4
Examples :
driver:\MondaySoftware\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\99-DATABASE\VersionADO
driver:\MondaySoftware\MS4BI\zConnectors-ADO, ODBC, SQLITE3



Interested in our project? contact@monday-software.com
The Monday Software team


Copyright 2019 by Monday Software

the official sites :
www.monday-software.com
www.ms4bi.com
demos.ms4bi.com
getms4bi.com
getms4bifree.com
cloud.ms4bi.com
cloud.testing.ms4bi.com
www.ms4script.com

Our purpose : Keep it simple !


written with ms4script
  • MS4BI v.00.0005.02.2019
  • MS4BIRE v.00.0005.02.2019
  • MS4SCRIPT v.00.0005.02.2019
  • help 02/2019

MS4BI in 5 minutes