---------------------------------------------------------------------------------  
                                             INTO  (only Ado Excel =Microjet !  )              
 ---------------------------------------------------------------------------------   
   text sheetExcel := "$sheet1";
	DBconnect{Ado:"xls"}( LFile1Excel);
 	display "insert Table Japanese : CONSOLIDATION 1  ";
   	Ms4_SQL{Ado:"xls"}("  SELECT  * into " + " %s "  + "  from [中国最大の都市のリスト$ ]  ", sheetExcel) return x  ;
 	 display " return Code sql  :",x; 
	 if(x = MS4_SQL_OK) 
	 then display "Sql ok "; 
	 else display "sql ko" ; 
	      exit; 
	 end_if;
OR
 ---------------------------------------------------------------------------------  
                                             CREATE VIEW                           
 ---------------------------------------------------------------------------------   
  DBconnect {Ms4DB} (LEnvDemoExcelDB);
 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 
 DBconnect{Ado:"xls"}( LFile1Excel);
		Ms4_SQL{Ado:"xls"}("CREATE TABLE [sheet1]
		(col1 integer,col2 TEXT,age INTEGER,cash FLOAT)  ");
END;
 ---------------------------------------------------------------------------------  
 INSERT INTO  
 ---------------------------------------------------------------------------------  
BEGIN
DBconnect{Ado:"xls"}( LFile1Excel);
		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
DBconnect{Ado:"xls"}( LFile1Excel);
	Ms4_SQL{Ado:"xls"}(  "Delete From [sheet1$] Where Col2 = 'Information31'   ");
END;	
BEGIN
DBconnect{Ado:"xls"}( LFile1Excel);
	Ms4_Query: Win{Ado:"xls"} ("SELECT * from  [sheet1$] ");
 END;
OR
 DBconnect {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," +

"    PHOTOPATH       TEXT  ) ");
Ms4_SQL{MS4DB} ("INSERT INTO ms4RefEmployees ("+
                           "     EMPLOYEEID," +
                           "     LASTNAME," +
                           "     FIRSTNAME," +
                           "     TITLE," +
             
                           "     PHOTOPATH" +
                           " )" +
                           " SELECT EMPLOYEEID," +
                           "        LASTNAME," +
                           "        FIRSTNAME," +
                           "        TITLE," +
                            "          PHOTOPATH " +
                         "     FROM sqlitestudio_temp_table" );

Ms4_SQL{MS4DB} ("DROP TABLE sqlitestudio_temp_table");	 
 	

Source Basic Sql
Source & Copy Sql
only MS4DB : MS4DB syntax = SQLITE3

Prerequisites

DBCONNECT

Remarks

-Important message:
An SQL instruction’s syntax can vary from one editor {driver } to another :
Example : Microsoft Jet’s (ADO) « into » SQL clause does not exist in c/SQLITE3 or MS4DB or Oracle
- Max. number of characters 2500 per SQL instruction : split strings using : " +
- The variables are defined using the following convention: %i integer, %l long ,%f float ,%d double
- Return Codes :
header SYSPATHMS4BI+\zInclude\IncludeSysSqlMs4.h
constant_num : MS4_SQL_OK 0, ** Successful result
,MS4_SQL_ERROR 1 , ** Generic error
MS4_SQL_INTERNAL 2 , ** Internal logic error in MS4DB
MS4_SQL_PERM 3 , ** Access permission denied
MS4_SQL_ABORT 4 , ** Callback routine requested an abort
MS4_SQL_BUSY 5 , ** The database file is locked
MS4_SQL_LOCKED 6 , ** A table in the database is locked
MS4_SQL_NOMEM 7 , ** A malloc() failed
MS4_SQL_READONLY 8 , ** Attempt to write a readonly database
MS4_SQL_INTERRUPT 9 , ** Operation terminated by MS4DB _interrupt()
MS4_SQL_IOERR 10 , ** Some kind of disk I/O error occurred
MS4_SQL_CORRUPT 11 , ** The database disk image is malformed
MS4_SQL_NOTFOUND 12 , ** Unknown opcode in MS4DB_file_control()
MS4_SQL_FULL 13 , ** Insertion failed because database is full
MS4_SQL_CANTOPEN 14 , ** Unable to open the database file
MS4_SQL_PROTOCOL 15 , ** Database lock protocol error
MS4_SQL_EMPTY 16 , ** Internal use only
MS4_SQL_SCHEMA 17 , ** The database schema changed
MS4_SQL_TOOBIG 18 , ** String or BLOB exceeds size limit
MS4_SQL_CONSTRAINT 19 , ** Abort due to constraint violation
MS4_SQL_MISMATCH 20 , ** Data type mismatch
MS4_SQL_MISUSE 21 , ** Library used incorrectly
MS4_SQL_NOLFS 22 , ** Uses OS features not supported on host
MS4_SQL_AUTH 23 , ** Authorization denied
MS4_SQL_FORMAT 24 , ** Not used
MS4_SQL_RANGE 25 , ** 2nd parameter to MS4DB _bind out of range
MS4_SQL_NOTADB 26 , ** File opened that is not a database file
MS4_SQL_NOTICE 27 , ** Notifications from MS4DB _log()
MS4_SQL_WARNING 28 , ** Warnings from MS4DB _log()
MS4_SQL_ROW 100 , ** MS4DB _step() has another row ready
MS4_SQL_DONE 101 ; ** MS4DB _step() has finished executing

See Also

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

---

KEYWORD : MS4_SQLFETCH{driver} + Column_GetData

Definition

Scrolls through ADO, ODBC, DSN or MS4 DB databases

Syntax

  	  	
   
 Ms4_SQLFetch{driver}  ("SELECT column 1,column 2.. from ... where... ")
 Row_ForEach : 
			 	var1 	:=  Column_GetData:NumberCol , FLOAT or INTEGER OR DOUBLE OR CHAR;
				var2	:=  Column_GetData:NumberCol,  INTEGER;
	  .....
  End_Row_ForEach ; 	  
	

example 1

  	
  DBconnect {Ms4DB} (LEnvDemoExcelDB);  
Ms4_SQLFetch{ms4db}("SELECT  *  FROM  [Sum2013] ")
 Row_ForEach :
    zBusinessID 	:=   Column_GetData:1  ,FLOAT;
    zTotalProduct	:=   Column_GetData:2  ,INTEGER;
  Display "->[ms4EmplTotalBusiness2013]->sum(businessd2013) : " ,zBusinessID , "Total Product 2013 :", zTotalProduct;
 End_Row_ForEach ; 
 	
	

example 2

  	
 Ms4_sql{ms4db} ( " Create View  [ViewCount] as  SELECT   count(*)   FROM  [%s] ",zTable);
 Ms4_SQLFetch{ms4db}("SELECT   *  FROM  [ViewCount] ") 
 Row_ForEach :
	 	zTotalCount	:=  Column_GetData:1,INTEGER; 
	    Display "->[Table & count ]  : " ,zTable," : ",zTotalCount;
 
End_Row_ForEach ;

Source Basic Ms4_SQLFetch
Source & Copy Ms4_SQLFetch

Query + dynamic Sql

Radial Gauge Peacock

Prerequisites

DBCONNECT

Remarks

- The SQL instruction must obey syntax depending on the type of connector
- Max. number of characters 2500 per SQL instruction : splitting the string by : " +
- Column_GetData = NumberCol : Column Number into the table ( var1 = 1, var2 = 2 ..)
- Column_GetData = NumberCol : type : FLOAT or INTEGER OR DOUBLE OR CHAR

See also

MS4_QUERY : WEB ,MS4_SQL, DRAWGRID, FormPlus, FORMMS4
Examples :
driver:\MandragorePlanete\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\99-DATABASE\VersionADO
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