ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / SQL functions
  • Example 1: Connection to an HFSQL database
  • Example 2: Connection via a native access
  • Example 3: Connection via ODBC
  • Example 4: Connection via JDBC
  • Example 5: Connection via OLE DB
  • Example 6: Connection to a Hive server
SQLConnect (Example)
Example 1: Connection to an HFSQL database
WEBDEV - Server codeAjaxHyper File 5.5
This example is used to handle an HFSQL database via an SQL connection.
ConnectionNum is int
FullWddName is string
WddPassword is string
TestVariable is string
 
// Path of the analysis
FullWddName = fCurrentDir() + "\MyAnalysis.ana\MyAnalysis.WDD"
 
// Password defined in the analysis for the WDD (if a password is defined)
WddPassword = "PASSWDD"
 
// Connection to the HFSQL database
ConnectionNum = SQLConnect(FullWddName, "", WddPassword, "Hyper File")
IF ConnectionNum <> 0 THEN
// The connection was successful
// Use the connection to run SQL queries
TestVariable = "DOE"
IF SQLExec("SELECT * FROM CUSTOMER WHERE NAME='" + TestVariable + "", ...
 "FINDCUSTOMERBYNAME") = True THEN
 // Process the query
ELSE
  // Query failure: display an error message
  SQLInfo("FINDCUSTOMERBYNAME")
  Error("Error" + SQL.Error + "in the query: ", SQL.MesError)
END
ELSE
 // The connection failed: display an error message
SQLInfo()
Error(SQL.MesError)
END
// Mandatory, whether the connection is OK or not
SQLDisconnect()
Example 2: Connection via a native access
WEBDEV - Server codeAjax
This example is used to handle an ACCESS (or ORACLE) database via an SQL connection.
ConnectionNum is int
SourceName is string
UserName is string = ""
UserPassword is string = ""
TestVariable is string
DatabaseType is string
 
// Connection to a specific data source via the native MS ACCESS access
SourceName = "C:\cdm\test.mdb"
DatabaseType = "Access"
ConnectionNum = SQLConnect(SourceName, UserName, UserPassword, "", DatabaseType)
// Or connection to a data source via the native ORACLE access
// SourceName = "ALIASNAME" (name of the alias defined by SQLNet)
// DatabaseType = "ORACLE"
// ConnectionNum = SQLConnect(SourceName, UserName, UserPassword, "", DatabaseType)
IF ConnectionNum <> 0 THEN
// The connection was successful
// Use the connection to run SQL queries
TestVariable = "DOE"
IF SQLExec("SELECT * FROM CUSTOMER WHERE NAME='" + ...
  TestVariable + "", "FINDCUSTOMERBYNAME") = True THEN
// Process the query
ELSE
 // Query failure: display an error message
 SQLInfo("FINDCUSTOMERBYNAME")
 Error("Error" + SQL.Error + "in the query: ", SQL.MesError)
END
ELSE
// The connection failed: display an error message
// Find the types of sources in the list of available sources
IF Position(CR + Upper(SQLListSource("DATABASE")) + CR, ...
CR + Upper(DatabaseType) + CR) = 0 THEN
// Type of source not found
Error("The native" + DatabaseType + "access is not installed properly.", ...
   "Check the presence of the corresponding WINDEV/WEBDEV DLLs" + ...
   "(WDxxxACC.DLL for Access, WDxxxORA.DLL for Oracle," + ...
"WDxxxSQS.DLL for SQL Server)")
 ELSE
 // Type of source found in the list of source types
 SQLInfo()
 // Statement to delete for the native Oracle access
  Error("The type of data source" + DatabaseType + ...
 "was found but the connection failed.", ...
 "Check the name of the database (or the connection string) " + ...
 "and the password.", SQL.MesError)
 END
END
// Mandatory, whether the connection is OK or not
SQLDisconnect()
Example 3: Connection via ODBC
WEBDEV - Server codeAjax
This example is used to handle an ACCESS database via an ODBC connection.
ConnectionNum is int
SourceName is string
TestVariable is string
// Connection to a specific data source via ODBC MS ACCESS
// (note: some drivers open a box for selecting
// files if no file is associated with this source)
SourceName = "MS Access 97 Database"
ConnectionNum = SQLConnect(SourceName, "", "", "", "ODBC")
IF ConnectionNum <> 0 THEN
// The connection was successful
// Use the connection to run SQL queries
TestVariable = "DOE"
IF SQLExec("SELECT * FROM CUSTOMER WHERE NAME='" + ...
TestVariable + "", "FINDCUSTOMERBYNAME") = True THEN
 // Process the query
ELSE
 // Query failure: display an error message
 SQLInfo("FINDCUSTOMERBYNAME")
 Error("Error" + SQL.Error + "in the query: ", SQL.MesError)
END
ELSE
 // The connection failed: display an error message
 // Find the types of sources in the list of available sources
 IF Position(CR + Upper(SQLListSource("DATABASE")) + CR, ...
 CR + Upper(DatabaseType) + CR) = 0 THEN
  // Type of source not found
  Error("The native" + DatabaseType + "access is not installed properly.", ...
"Check the presence of the corresponding WINDEV/WEBDEV DLLs" + ...
"(WDxxxACC.DLL for Access, WDxxxORA.DLL for Oracle," + ...
"WDxxxSQS.DLL for SQL Server)")
 ELSE
// Type of source found in the list of source types
SQLInfo()
// Statement to delete for the native Oracle access
Error("The type of data source" + DatabaseType + ...
"was found but the connection failed.", ...
"Check the name of the database (or the connection string) " + ...
"and the password.", SQL.MesError)
END
END
// Mandatory, whether the connection is OK or not
SQLDisconnect()
Example 4: Connection via JDBC
Example 5: Connection via OLE DB
WEBDEV - Server codeOLE DB
This example is used to handle an SQL Server database via an OLE DB connection.
ConnectionNum is int
ConnectionNum = SQLConnect("127.0.0.1", "sa", "", "master", "OLEDB", hOledbSQLServer)
IF ConnectionNum <> 0 THEN
// The connection was successful
Info("The connection was successful")
ELSE
// The connection failed: display an error message
SQLInfo()
Error("The connection to the data source failed." + CR + ...
"Error code: " + SQL.Error + CR + SQL.MesError)
END
// In any case (connection OK or not)
SQLDisconnect()
Example 6: Connection to a Hive server
WEBDEV - Server codeLinux
This example is used to handle a Hive database.
ConnectionNum is int
ConnectionNum = SQLConnect("master2.hadoop1.myserver.com", "sa", "", "default", "HIVE")
IF ConnectionNum <> 0 THEN
// The connection was successful
// Use the connection to run SQL queries
IF SQLExec("SELECT * FROM TEAM WHERE NAME='HOU'") = True THEN
// Process the query
ELSE
// Query failure: display an error message
SQLInfo("FINDCUSTOMERBYNAME")
Error("Error" + SQL.Error + "in the query: ", SQL.MesError)
END
ELSE
// The connection failed: display an error message
SQLInfo()
Error("The connection to the data source failed." + CR + ...
"Error code: " + SQL.Error + CR + SQL.MesError)
END
// In any case (connection OK or not)
SQLDisconnect()
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help