PC SOFT

ONLINE HELP
 WINDEV, WEBDEV AND WINDEV MOBILE
  • 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
WINDEVWEBDEV - Server codeReports and QueriesUser code (UMC)AjaxHyper 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
WINDEVWEBDEV - Server codeUser code (UMC)Ajax
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
WINDEVWEBDEV - Server codeUser code (UMC)Ajax
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
Java
SourceName is string = "jdbc:mysql://apollon/test"
ConnectionNum is int = SQLConnect(SourceName, "MH", "HFORM", ...
"test", "" , "com.mysql.jdbc.Driver")
IF ConnectionNum =0 THEN
    // The connection failed: display an error message
    SQLInfo()
    Error("The connection to the data source" + SourceName + ...
      "failed." + CR +...  
      "Error code:" + SQL.Error + CR + ...
      SQL.MesError)
    RETURN
END
 
// Run the query and retrieve the result line by line
IF SQLExec("SELECT CustomerName, Address, Telephone FROM CUSTOMER", "QRY1") = False THEN
    SQLInfo("QRY1")
    Error( "Failure " + CR + "Error code: " + SQL.Error + CR + SQL.MesError)
ELSE
    i is int = 0
WHILE SQLFetch("QRY1") = 0 // There is still another line to read
i ++
// Retrieve the data
sValue is string = SQLGetCol("QRY1", 1)
SQLInfo("QRY1")
IF SQL.MesError <>"" THEN
Trace("Error:" + SQL.MyError)
ELSE
Trace(sValue)
END
sValue = SQLGetCol("QRY1", 2)
SQLInfo("QRY1")
IF SQL.MesError <> "" THEN
Trace("Error: " + SQL.MesError)
ELSE
Trace(sValue)
END
sValue = SQLGetCol("QRY1", 3)
SQLInfo("QRY1")
IF SQL.MesError <> "" THEN
Trace("Error: " + SQL.MesError)
ELSE
Trace(sValue)
END
END
SQLClose("QRY1")
END
SQLDisconnect()
Example 5: Connection via OLE DB
WINDEVWEBDEV - Server codeReports and QueriesOLE 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
WINDEVWEBDEV - 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