Post by (X) on Mar 13, 2023 16:18:33 GMT 1
I had a brainstorm to create a little 'online' website to host a simple Billiard Statistics Registry to track billiard games amoung friends. Just for fun. Well, a week later, I am finding out about all the gotchas related to "free" web-hosting:
You get the jist.
It's free but you can't use relational database feature, you have to pay for that...
It's free but you have to extend your database account by signing in every week or we delete the account...
It's free but you have to extend your database account by signing in every week or we delete the account...
I learned a lot in a short time thanks to ChatGPT.
I tried accessing the database from LibreOffice. I tried with: InfinityFreeApp.com, but it does not permit outside access.
The other: FreeSQLdatabase, lets me in, but, this will get deleted after a week of disuse.
I tried logging in via GFA and made some progress, but "no cigar".
This code is still a "dog's breakfast" but I feel I came close to logging in.
See next post for a solution.
$Library "gfawinx"
$Library "UpdateRT"
UpdateRuntime ' Patches GfaWin23.Ocx
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' These constants are used to specify the type of ODBC handle that is being
' allocated or operated on. SQL_HANDLE_ENV is used to allocate an environment
' handle, which is used to set attributes that affect the behavior of the ODBC
' driver manager. SQL_HANDLE_DBC is used to allocate a database connection
' handle, which is used to connect to a database server and execute SQL
' statements.
'
Public Const SQL_HANDLE_ENV = 1 // indicates an environment handle
Public Const SQL_HANDLE_DBC = 2 // indicates a connection handle
Public Const SQL_HANDLE_STMT = 3 // indicates a statement handle
Public Const SQL_HANDLE_DESC = 4 // indicates a descriptor handle
' SQL_ATTR_ACCESS_MODE
Public Const SQL_MODE_READ_WRITE = 0
Public Const SQL_MODE_READ_ONLY = 1
' SQL_ATTR_ASYNC_ENABLE
Public Const SQL_ASYNC_ENABLE_OFF = 0
Public Const SQL_ASYNC_ENABLE_ON = 1
' SQL_ATTR_AUTOCOMMIT
Public Const SQL_AUTOCOMMIT_OFF = 0
Public Const SQL_AUTOCOMMIT_ON = 1
' SQL_ATTR_ODBC_CURSORS
Public Const SQL_CUR_USE_IF_NEEDED = 0
Public Const SQL_CUR_USE_ODBC = 1
Public Const SQL_CUR_USE_DRIVER = 2
' SQL_ATTR_QUIET_MODE
Public Const SQL_FALSE = 0
Public Const SQL_TRUE = 1
' SQL_ATTR_TRACE
Public Const SQL_OPT_TRACE_OFF = 0
Public Const SQL_OPT_TRACE_ON = 1
Public Const SQL_OPT_TRACE_FILE = 2
Public Const SQL_OPT_TRACE_DEBUG = 3
' SQL_ATTR_TXN_ISOLATION
Public Const SQL_TXN_READ_UNCOMMITTED = 0
Public Const SQL_TXN_READ_COMMITTED = 1
Public Const SQL_TXN_REPEATABLE_READ = 2
Public Const SQL_TXN_SERIALIZABLE = 3
' SQL_ATTR_USE_TRUSTED_CONNECTION
Public Const SQL_ATTR_USE_TRUSTED_CONNECTION_NO = 0
Public Const SQL_ATTR_USE_TRUSTED_CONNECTION_YES = 1
' SQL_ATTR_METADATA_ID
Public Const SQL_FALSE = 0
Public Const SQL_TRUE = 1
Public Const SQL_CP_STRICT_MATCH = 0
Public Const SQL_CP_RELAXED_MATCH = 1
Public Const SQL_ATTR_CONNECTION_POOLING As Int16 = 200
' SQL_CP_OFF: Connection pooling is disabled.
Public Const SQL_CP_OFF As Int16 = 0
' SQL_CP_ONE_PER_DRIVER: Each driver maintains a separate pool of connections.
Public Const SQL_CP_ONE_PER_DRIVER As Int16 = 1
' SQL_CP_ONE_PER_HENV: Each environment handle maintains a separate pool of connections.
Public Const SQL_CP_ONE_PER_HENV As Int16 = 2
Declare Function SQLAllocHandle Lib "odbc32.dll" _
(ByVal HandleType As Int16, _
ByVal InputHandle As Long, _
ByRef OutputHandlePtr As Long) As Int16
Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
(ByVal EnvironmentHandle As Long, _
ByVal Attribute As Int16, _
ByRef ValuePtr As Any, _
ByVal StringLength As Int16) As Int16
°Declare Function SQLFreeHandle Lib "odbc32.dll" _
(ByVal HandleType As Int16, _
ByVal Handle As Long) As Int16
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' The SQLFreeHandle function that you have specified is a generic function that
' can be used to free any ODBC handle, regardless of its type. It takes two
' parameters:
'
' HandleType is an Int16 value that specifies the type of the handle to be
' freed. This can be one of the following constants:
'
' SQL_HANDLE_ENV (1): indicates an environment handle
' SQL_HANDLE_DBC (2): indicates a connection handle
' SQL_HANDLE_STMT (3): indicates a statement handle
' SQL_HANDLE_DESC (4): indicates a descriptor handle
'
' Handle is a Long value that specifies the handle to be freed.
'
Declare Function SQLFreeHandle Lib "odbc32.dll" _
(ByVal HandleType%, _
ByVal Handle%) As Int16
' SQLFreeEnv function
Declare Function SQLFreeEnv Lib "odbc32.dll" ( _
ByVal hEnv As Long) As Integer
' SQLFreeConnect function
Declare Function SQLFreeConnect Lib "odbc32.dll" ( _
ByVal hdbc As Long) As Integer
' SQLFreeStmt function
Declare Function SQLFreeStmt Lib "odbc32.dll" ( _
ByVal hStmt As Long, _
ByVal fOption As Integer) As Integer
Declare Function SQLConnect Lib "odbc32.dll" _
(ByVal ConnectionHandle As Long, _
ByVal ServerName As String, _
ByVal NameLength1 As Int16, _
ByVal UserName As String, _
ByVal NameLength2 As Int16, _
ByVal Authentication As String, _
ByVal NameLength3 As Int16) As Int16
Declare Function SQLGetDiagRec Lib "odbc32.dll" _
(ByVal HandleType As Int16, _
ByVal Handle As Long, _
ByVal RecNumber As Int16, _
ByVal SqlState As String, _
ByRef NativeError As Long, _
ByVal MessageText As String, _
ByVal BufferLength As Int16, _
ByRef TextLength As Int16) As Int16
°Declare Function SQLGetDiagRec Lib "odbc32.dll" _
(ByVal HandleType%, _
ByVal Handle%, _
ByVal RecNumber%, _
ByVal SqlState$, _
ByRef NativeError%, _
ByVal MessageText$, _
ByVal BufferLength%, _
ByRef TextLength%) As Int16
°Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc As Long) As Integer
Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc As Long) As Int16
Global henv%, hdbc%, hstmt%, fOption%, ret&
Debug "SQLAllocHandle"
ret = SQLAllocHandle(SQL_HANDLE_ENV, 0, henv)
Debug "henv:", "0x" & Hex(henv)
Debug ADODB_Return_Code_Str(ret)
Debug
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' The Attribute parameter specifies the attribute to be set, and the Value
' parameter contains the value to be set for the attribute. The StringLength
' parameter is used when setting attributes that require a character string
' value, and should be set to the length of the string (including the null
' terminator).
' Here are some of the commonly used attributes that can be set with
' SQLSetEnvAttr:
' SQL_ATTR_ODBC_VERSION: sets the version of ODBC that the driver manager
' should use. Valid values are SQL_OV_ODBC2 or
' SQL_OV_ODBC3.
'
' SQL_ATTR_CONNECTION_POOLING: enables or disables connection pooling for the
' driver manager. Valid values are SQL_CP_OFF,
' SQL_CP_ONE_PER_DRIVER, SQL_CP_ONE_PER_HENV,
' and SQL_CP_DEFAULT.
'
' SQL_ATTR_CP_MATCH: sets the criteria for matching connection
' strings in the connection pool. Valid
' values are SQL_CP_STRICT_MATCH and
' SQL_CP_RELAXED_MATCH.
'
' SQL_ATTR_TXN_ISOLATION: sets the default transaction isolation level
' for connections. Valid values are
' SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED,
' SQL_TXN_REPEATABLE_READ, and SQL_TXN_SERIALIZABLE.
'
' SQL_ATTR_PACKET_SIZE: sets the maximum packet size for TCP/IP
' connections. Valid values depend on the
' specific ODBC driver being used.
' Note that not all ODBC drivers support all attributes. You can use the
' SQLGetEnvAttr function to retrieve the current value of an attribute.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SQL_ATTR_CONNECTION_POOLING is an ODBC environment attribute with a constant
' value of 200. This attribute is used to control connection pooling behavior
' for an ODBC environment.
'
' Connection pooling is a technique used to improve the performance of database
' applications that repeatedly open and close database connections. When
' connection pooling is enabled, a pool of database connections is created and
' maintained by the driver manager, so that connections can be reused rather
' than being closed and reopened for each database transaction.
'
' The SQL_ATTR_CONNECTION_POOLING attribute can be set to one of three values
' to control connection pooling behavior:
'
' SQL_CP_OFF (0): Connection pooling is disabled.
'
' SQL_CP_ONE_PER_DRIVER (1): Each driver loaded by the application maintains
' a separate pool of connections.
'
' SQL_CP_ONE_PER_HENV (2): Each environment handle maintains a separate pool
' of connections.
'
' Here is an example of how to use SQLSetEnvAttr to enable connection pooling
' for an ODBC environment:
'
° Dim envHandle As Long
° Dim ret As Integer
°
° ' Allocate an environment handle
° ret = SQLAllocHandle(SQL_HANDLE_ENV, 0, envHandle)
°
° ' Enable connection pooling with one connection per environment handle
° Dim pooling As Integer
° pooling = SQL_CP_ONE_PER_HENV
° ret = SQLSetEnvAttr(envHandle, SQL_ATTR_CONNECTION_POOLING, pooling, 0)
'
' In this example, SQLSetEnvAttr is used to set the SQL_ATTR_CONNECTION_POOLING
' attribute to SQL_CP_ONE_PER_HENV for the specified environment handle. This
' will enable connection pooling with one connection per environment handle.
'
Debug "SQLSetEnvAttr"
Dim pooling As Int16
pooling = SQL_CP_ONE_PER_HENV
ret = SQLSetEnvAttr(henv, SQL_ATTR_CONNECTION_POOLING, pooling, 0)
Debug "V:pooling:", "0x" & Hex(V:pooling)
Debug "pooling:", pooling
Debug ADODB_Return_Code_Str(ret)
Debug
Debug "SQLAllocHandle"
Dim lp_hdbc%
ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc)
Debug "hdbc:", "0x" & Hex(hdbc)
Debug ADODB_Return_Code_Str(ret)
Debug
'Dim svr_addr$ = "http://sql9.freesqldatabase.com/sql9605065"
'Dim svr_addr$ = "sql9.freesqldatabase.com/sql9605065"
'Dim svr_addr$ = "52.5.226.201:3306"
Dim svr_addr$ = "52.5.226.201"
Dim user_name$ = "sql9605065"
'Dim user_name$ = "sql9605065@ec2-52-8-112-233.us-west-1.compute.amazonaws.com "
Dim pw$ = "*******"
Debug "svr_addr$:",svr_addr$,Len(svr_addr$)
Debug "user_name$:",user_name$,Len(user_name$)
Debug "pw$:",pw$, Len(pw$)
Debug "SQLConnect"
ret = SQLConnect(hdbc, _
svr_addr$, _
Len(svr_addr$) + 1, _
user_name$, _
Len(user_name$) + 1, _
pw$, _
Len(pw$) + 1)
Debug ADODB_Return_Code_Str(ret)
Debug
OpenW 1
Do
Sleep
Until Me Is Nothing
P_Disconnect()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Free SQL handles...
'
If (hstmt <> 0) Debug "Free Stmt", Iif(SQLFreeStmt(hstmt, fOption) == 0, "OK", "ERROR")
If (hdbc <> 0) Debug "Free Conn", Iif(SQLFreeConnect(hdbc) == 0, "OK", "ERROR")
If (henv <> 0) Debug "Free Env", Iif(SQLFreeEnv(henv) == 0, "OK", "ERROR")
CloseW 1
Proc P_Disconnect()
Debug
Debug "Disconnecting..."
Debug "hdbc:", "0x" & Hex(hdbc, 8)
ret = SQLDisconnect(hdbc)
Debug ADODB_Return_Code_Str(ret)
EndProc
Function ADODB_Return_Code_Str(ret As Long ) As String
Const SQL_INVALID_HANDLE = -2
Const SQL_ERROR = -1
Const SQL_SUCCESS = 0
Const SQL_SUCCESS_WITH_INFO = 1
Const SQL_STILL_EXECUTING = 2
Const SQL_NO_DATA = 100
Dim buffer As String*255
Dim buflen As Short
Dim errorcode As Long
Debug "Ret value:", ret
Select ret
Case SQL_INVALID_HANDLE
Return "SQL_INVALID_HANDLE"
Case SQL_ERROR
ret = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, "", errorcode, buffer, Len(buffer), buflen)
Debug "Error Code:", errorcode
Debug "buflen:", buflen
Debug "len(buffer):",Len(buffer)
Return "Connection message: " & Left(buffer, buflen)
Case SQL_SUCCESS
Return "SQL_SUCCESS"
Case SQL_SUCCESS_WITH_INFO
Return "SQL_SUCCESS_WITH_INFO"
Case SQL_STILL_EXECUTING
Return "SQL_STILL_EXECUTING"
Case SQL_NO_DATA
Return "SQL_NO_DATA"
EndSelect
EndFunc