PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
  • Example 1: Connection to a 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 a HFSQL database
WINDEVWEBDEV - Server codeReports and QueriesUser code (UMC)AjaxHyper File 5.5
This example is used to handle a HFSQL database via a 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 a SQL connection.
ConnectionNum is int
SourceName is string
UserName is string = ""
UserPass 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, UserPass, "", 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(DataSourceName, 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 a 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