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 4 : DATABASES

KEYWORD : CONNECTDB

:

Definition

- connects databases with MS4,ADO or ODBC data sources

Syntax

 		 
			ConnectDB{Driver}(string DB Name ) ;
 	

Example 1 MS4 DB

 		   
    text : Lcurrentdb :="#DRIVERSYS+\MONDAYSOFTWARE\MS4BI\01-GETTING-STARTED-UNICODE\ms4bilangConso.db" ; 
 
   ConnectDB {Ms4DB} (Lcurrentdb); 
  FormShow : DrawGrid, title :"MS4 DB : ms4LangConsolidation China/Japan : Relation -> ?? = b.city  " 
  use   View  ("[ms4LangConsolidation]")  ;


	

Example 2 ADO DB

 
 ConnectDB{Ado:"xls"}( LFile1Excel);
     display "insert Table Japanese : CONSOLIDATION 1  ";
       Ms4_SQL{Ado:"xls"}(" SELECT  * into " + " %s " + "  from  [中国最大の都市のリスト$] ", Table1SheetJapanese) return x ;
 	 

Remarks

DRIVER :
MS4
ODBC
ADO :string : the ADO connectors must be present in the directory :

driver:\MondaySoftware\MS4BI\zDriversADO

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL

KEYWORD : DBEXIST

:

Definition

- Tests if an MS4 DB, ADO or ODBC database is present.

Syntax

 		 
			DBexist{Driver}(string DB Name )  return integer ;
 	

Example

 		   
    DBexist(Lcurrentdb) return (X) ;
	if (x not_void ) then
 		Display " DB exists   Ok : DELETE !!   " ,Lcurrentdb Column 40;
		....
	ELSE
		  Display "DB   not found   :  ",Lcurrentdb;  
	 
	End_if ;
	  


	

Remarks

integer : returns TRUE (1) if present, otherwise FALSE (0)
not_void = TRUE void = FALSE

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : DELETEDB

:

Definition

- Deletes an MS4 DB database.

Syntax

 		 
			DeleteDB{Driver}(string DB Name ) ;
 	

Example

 		   
    DBexist(Lcurrentdb) return (X) ;
	if (x not_void ) then
 		Display " DB exists   Ok : DELETE !!   " ,Lcurrentdb Column 40;
		DBDelete{MS4DB}(Lcurrentdb);
	ELSE
		  Display "DB   not found   :  ",Lcurrentdb;  
	 
	End_if ;
 
	

Remarks

Only applies to the MS4DB driver
not_void = TRUE void = FALSE

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : DBCREATE

:

Definition

- Creates an MS4 DB database.

Syntax

 		 
			 DBCreate{Driver}(string DB Name ) return integer (optional);
 	

Example

 
   text : Lcurrentdb :="#DRIVERSYS+\MONDAYSOFTWARE\MS4BI\01-GETTING-STARTED-UNICODE\ms4bilangConso.db" ; 
    
    DBexist(Lcurrentdb) return (X) ;
	if (x  void ) then
   		  Display "DB   not found   :  ",Lcurrentdb;  
	  DBCreate{MS4DB}(Lcurrentdb) return (X) ;
	End_if ;
 
	

Remarks

Only applies to the MS4DB driver.
integer : returns TRUE (1) if there is a problem, otherwise FALSE (0)
not_void = TRUE void = FALSE

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : DBCLOSE

:

Definition

- Closes an MS4 DB database.

Syntax

 		 
			 DBClose{Driver}(string DB Name ) return integer (optional);
 	

Example

 
   text : Lcurrentdb :="#DRIVERSYS+\MONDAYSOFTWARE\MS4BI\01-GETTING-STARTED-UNICODE\ms4bilangConso.db" ; 
    
    DBexist(Lcurrentdb) return (X) ;
	if (x  not_void ) then
   		  Display "DB    found   :  ",Lcurrentdb;  
	  DBCLose{MS4DB}(Lcurrentdb) return (X) ;
	End_if ;
 
	

Remarks

Only applies to the MS4DB driver.
integer : returns TRUE (1) if there is a problem, otherwise FALSE (0)

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : TABLEEXIST

:

Definition

- Tests if an MS4 DB table or a file is present.

Syntax

 		 
			TableExist (string file Name ) return integer  ; 
or TableExist{Driver}(string DB Name ) return integer ;

Example 1 FILE

 
 	Concatene  LCurrentDir,"\japaneseExample.xls"     into  Lfile1Excel;
 	Tableexist(Lfile1Excel) return (X) ;
	if (x not_void ) then
              Display " Table Ok : continue  " ,Lfile1Excel Column 40;
	ELSE
               Display "Table not found : database problem !! ",Lfile1Excel;  
	Exit;
	End_if ;
 
	

Example 2 MS4 DB

	
 	BEGIN
		ConnectDB{Ms4db}( LEnvSQLDB);
		Tableexist{Ms4db}("ItabOrder") Return  ( msgsql) ;
		/* 2 return codes : TRUE or FALSE */
 
		if ( msgsql = TRUE) then
		  Ms4_SQL{Ms4db}(  "DROP TABLE  ItabOrder   ");  
			Display " Dropping Table ItabOrder!!";
		else 
 		Display " Table ItabOrder does not exist !!";
		End_if;        
        END;

Remarks

integer : returns TRUE (1) if present, otherwise FALSE (0)
not_void = TRUE void = FALSE

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : VIEWEXIST

:

Definition

- Tests for the presence of an MS4 DB view.

Syntax

 		 
			ViewExist{Driver}(string DB Name ) return integer  ;
 	

Example

	
 BEGIN      ** Connect DB ..    	
              ConnectDB{Ms4DB}(LEnvDemoExcelDB);
		 ViewExist{Ms4DB}("ReportTotalID") Return  (msgsql) ;
		/* 2 return codes : TRUE or FALSE */
  
		if ( msgsql = TRUE) then
			Ms4_SQL{Ms4DB}(  "DROP VIEW  ReportTotalID   "); 
		end_if; /* create a new view */
		Ms4_sql{Ms4DB} (" CREATE  VIEW ReportTotalID  as " +
				" select DISTINCT orderID ,employeeid,LastName ,productID,unitprice," +  
                               "  strftime('%Y',orderDate)  as YEARBUSINESS " +
                                "   from  [ms4EmplConsolidationBusiness]   " +
				"  order by  YEARBUSINESS,employeeID,shipcountry ,orderID "); 
END;        
 

Remarks

Only applies to MS4 DB.
integer : returns TRUE (1) if present, otherwise FALSE (0)
not_void = TRUE void = FALSE

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : EXPORT_TABLE

:

Definition

- Exports all tables and views for an MS4 DB database in EXCEL, CSV, TXT, HTML, XML format.

Syntax

 		 
			Export_table(string DB Name )    ;
 	

Example

	
     BEGIN  
 		DBexist{Ms4DB}(LEnvDemoExcelDB) return (X) ;
		/*   return codes */
		** x = 1 : Ok , x = 0 Not ok */
		 if (x void) then
                  Display "DB not found ", LEnvDemoExcelDB; 
				  enter;
				  Exit;
                 End_if;
     END;
		Display " Ms4 Script------------------------------------>Cube : ";
		Display " Export All Tables  : ",LEnvDemoExcelDB;
		Display " Ms4 Script-------------------------------------------> ";
  		       BEGIN **   
	 			    Export_Schema (LEnvDemoExcelDB);
				END;
		Display " End : Go to   :\MondaySoftware\MS4BI\Ms4BiServer\www\mondaychart ";
    
 

Remarks

Only applies to MS4 DB
- Exports Excel, txt, html, xlm files to: \MondaySoftware\MS4BI\Ms4BiServer\www\mondaychart

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, DESTROYENVSQL



KEYWORD : EXPORT_SCHEMA

:

Definition

- Exports all table schemas from an MS4 DB database in HTML format

Syntax

 		 
			Export_schema(string DB Name )    ;
 	

Example


       BEGIN   
 		DBexist{Ms4DB}(LEnvDemoExcelDB) return (X) ;
		/*   return codes */
		** x = 1 : Ok , x = 0 Not ok */
		 if (x void) then
                  Display "DB  not found ", LEnvDemoExcelDB; 
				  enter;
				  Exit;
                 End_if;
        END;
		Display " Ms4 Script------------------------------------>Cube : ";
		Display " Export All Tables  : ",LEnvDemoExcelDB;
		Display " Ms4 Script-------------------------------------------> ";
  		              BEGIN **   
	 			    Export_Schema(LEnvDemoExcelDB);
				END;
		Display " End : Go to   :\MondaySoftware\MS4BI\Ms4BiServer\www\mondaychart ";
    
 

Remarks

Only applies to MS4 DB
- Exports schemas to an html file in : \MondaySoftware\MS4BI\Ms4BiServer\www\mondaychart

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



KEYWORD : DESTROYENVSQL

:

Definition

- Destroys the connection environment and SQL for MS4 DB, ADO, ODBC databases.

Syntax

 		 
			DestroyEnvSql()    ;
 	

Example


       BEGIN   
 	ConnectDB{Ado:"xls"}(LcurrentDirExcel);
		Ms4_Query: Web {Ado:"xls"}("SELECT * FROM [viewInvoices$]   " );
 
		DestroyEnvSql();
        END;
	 

See also

IMPORT NEW DB, DBEXIST, DBCREATE, DBCONNECT, DBDELETE,DBCLOSE, TABLEEXIST,VIEWEXIST, MS4_SQL, EXPORT_TABLE, EXPORT_SCHEMA, DESTROYENVSQL



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