Ms4_Query: web{driver} 
   (optional)Option : BEGIN   Attributes  END
      (  "string or literal = SQL " + "rest 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 ,%s char or text

	

  		   
DBconnect{Ado:"xls"}("#DRIVERSYS+:\MandragorePlanete\MS4BI\02-DEMO....\2-QUERIES\EXCEL\..\viewInvoices.xls");
Ms4_Query: web{Ado:"xls"}("SELECT * FROM [viewInvoices$]   " );

OR

 Ms4_Query:  web{Ado:"xls"} 
Option  : 
  BEGIN 
   Style   :$BLue ,
   height  :550,     
   pagesize:100,
   EXPORTEXCEL : yes   , 
   Pageable :yes,
   Resizable :yes, 
   Sortable :yes,
   ViewResult:yes,
   filterable:yes
  END  
 ("SELECT *  FROM [Sheet1$]   " );	

or

 FormPlus : Ms4_Query  {Ado:"xls"} 
	BEGIN
      SetUniCode(True);
         DBconnect{Ado:"xls"}(LcurrentDirExcel);
        Ms4_Query: Web{Ado:"xls"}
         Option  : 
         BEGIN 
              Style :$METRO  
          END 
          ("SELECT * FROM [中国最大の都市のリスト$]     " );
       END ;
	
	

  	
   Visualise Example 1   
 	
   Source Basic Query   	
 
    Source & Copy Basic Query     	
 
 
	

  	 
  Visualise ALL EXAMPLES : Queries      

  Visualise ALL   EXAMPLES    
	
	

  	
   Setting :
	Chart :
	Backgroundcolor : $color_bkms4bi 
	ToolBarExport  : yes  ;
  
      DBconnect{Ms4DB}(LEnvDemoExcelDB); 
       Ms4_query:web{ms4db}   
        ( " SELECT OrderID ,CustomerID,	EmployeeID,	" + 
           "        OrderDate,	RequiredDate,	ShippedDate "+
           " from   [ms4EmplConsolidationBusiness]  order by EmployeeID desc limit 2 " ) ; 
                   ** TOP 2 employeeid
	

  
BEGIN 
  TEXT  Stable1 := "ms4EmplTotalBusiness2013"; 
  integer IretSql;
   DBconnect{Ms4DB}(LEnvDemoExcelDB);   
   Ms4_Query: Web{ms4db}
   Option  : /* optional */
		BEGIN 
            style :$BLACK ,
		
 		END  
("SELECT  A.lastname as myname, printf('%.02f',b.BUSINESSID2011) as YEAR2011,printf('%.02f',A.BUSINESSID2013 ) AS YEAR2013  "+
" FROM   %s  as A  join  ms4EmplTotalBusiness2011  as B "+
" where  a.lastname = b.lastname", Stable1 ) return IretSql;/* dynamic sql : possible : %s = Stable1 */
  END ;	

  	 
  Visualise ALL EXAMPLES : Queries      

  Visualise ALL   EXAMPLES    
	
	

Prerequisites

DBCONNECT

Remarks

- The SQL instruction must respect 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 ,%s char or text -Option :
BEGIN
style : $MOONLIGHT or $BLACK or $WHITE or $BLUE or $METRO or $SILVER ,
height : xxx (numeric) ,
EXPORTEXCEL : yes or no ,
pagesize : xx (numeric),
popEdit : yes or no ,
ColumMenu : yes or no ,
Pageable : yes or no ,
Resizable : yes or no ,
Sortable : yes or no ,
ExpandFirst : yes or no ,
filterable : yes or no
END
NB :
yes=1
no =0

See also

MS4_QUERY : WIN ,MS4_SQL, DRAWGRID, FormPlus, FORMMS4
Examples :
driver:\MandragorePlanete\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\2-QUERIES
driver:\MandragorePlanete\MS4BI\zConnectors-ADO, ODBC, SQLITE3

---

KEYWORD : MS4_QUERY :WIN{driver}

Definition

Creates a WINDOWS query to interrogate ADO, ODBC, DSN or MS4 DB databases

Syntax

  	  	
        Ms4_Query:  win{driver} 
       (  "string or literal = SQL instruction" + "rest of string or literal = SQL instruction",
 
	  (optional : the variables) ,ivalue, Lvalue, fvalue, dvalue    ) ;

 
 -  The variables are defined using the following convention:  %i  integer, %l  long ,%f  float ,%d double  ,%s char or text

 	
	

Example 1 ADO EXCEL

  		   
DBconnect{Ado:"xls"}("#DRIVERSYS+:\MandragorePlanete\MS4BI\02-DEMO....\2-QUERIES\EXCEL\..\viewInvoices.xls");
 tabname := "[viewInvoices$]"; varname := "SAVEA"; 
Ms4_Query: web{Ado:"xls"}("SELECT * FROM   %s  WHERE  CUSTOMERID =  '%s'  ORDER BY 1  DESC ", tabname,varname  );
 
 	
	

Example 2 SQLITE3 or MS4 DB

  	
DBconnect{sqlite3}( LEnvSQLDB);

Display "SELECT  * FROM  all tables of DB SAMPLE DB ";

 Ms4_Query: Win{sqlite3}("SELECT * FROM     sqlite_master   ORDER BY type  ");

	

Prerequisites

DBCONNECT

Remarks

- The SQL instruction must respect syntax depending on the type of connector
- Max. number of characters 2500 per SQL instruction
- WIN : Use on a grid with small amount of data, as limited memory space (64k max)

See also

MS4_QUERY : WEB ,MS4_SQL, DRAWGRID, FormPlus, FORMMS4
Examples :
driver:\MandragorePlanete\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\2-QUERIES
driver:\MandragorePlanete\MS4BI\zConnectors-ADO, ODBC, SQLITE3

---

Interested in joining us? contact@mgplanete.com
The Mg Planete team

---

Copyright 2022 by Mandragore Planete

the official sites :
mgplanete.com
getms4bi.com
demos.ms4bi.com
help.ms4bi.com
ms4script.com

---

Download

official site :
getms4bi.com

---

Our purpose : Keep it simple !

written with ms4script

• help 01.010.2022.8

MS4BI in 5 minutes