MySQL DB via TCPIP using GFA? Yes!

[(X)]

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:

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...

You get the jist.

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$  = "[e-mail] "
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


[scalion]

Wow , est-ce que tu saurais créer un programme avec une petite base de données ?

Par exemple avec 2 petites tables :

Une table client avec des champs nom, adresse, tel
Une table facture avec numéro, date, montant, clientindex

et une fonction pour interroger la base et retourner , je ne sais pas, un tableau de réponse sql ou quelquechose dans le genre ?
Ca serait top , ca m'intéresse.

En ce qui concerne ton site web, si tu en as la possibilité, essaie d'investir dans un petit serveur web perso (un pc allumé en permanence) et tu te fais ton propre dns et serveur style apache, c'est pas vraiment simple mais ca vaut vraiment le coup. Tu n'as pas plus qu'à enregistrer un nom de domaine (tu paies environs 10 euros par ans, c'est gérable).

Il y a des tas de tuto la dessus (moi personnellement c'est un ami vraiment très fort sur ce sujet qui me l'a fait, je suis pas assez calé pour le faire moi-même, disons que ca m'aurait pris beaucoup de temps), il m'a installé Joomla pour gérer ma page web (https://www.gfabasic32codes.info/) , c'est plutot top (mais pas du tout obligatoire, il y a pas mal de possibilités selon ce que tu souhaites), l'idéal serait que tu puisses prendre conseil auprès de quelqu'un dans ton entourage proche qui l'a déjà fait, faut avoir de la chance et se lancer. En bref laisse tomber les hébergeur, soit gratuit et pas top soit très cher...

[(X)]

Bons conseils!

Ce sont parfois les petits projets qui nous renseignent le plus.

Alors, je m'y suis lancé pour voir jusqu'où j'allais me rendre.

J'ai imaginé mon Raspberry Pi qui ramasse de la poussière en ce rôle de serveur 24/7.
J'pourrai toujours utiliser l'address IP en attendant qu'un nom de 'Domain' me vient à l'idée.

En bref, si j'arrive a faire marcher ça sous GFA je publierai.

---

Good advice!

Sometimes it's the small projects that tell us the most.

So, I jumped in to see how far I could take it.

I imagined my Raspberry Pi collecting dust in this 24/7 server role.
I could always use the IP address until a domain name comes to mind.

In short, if I can get this to work in GFA I'll publish

[(X)]

Ok, j'y suis arrivé!
J'ai réussi d'accéder à une petite base de données existant sur "sql9.freesqldatabase.com"

En bref, à l'aide de ChatGPT.  Pourquoi le délai?  La première méthode SQLConnect() pour VB6 proposé flanchait toujours.  Après m'avoir aperçu d'une autre aproche: SQLDriverConnect() pour VBNet, c'est celle-là qui a débouché.  Je ne sais pas encore si la première methode devrait fonctionner ou pas.  Je vais réduire mon demo au plus simple et l'ajouter ici.

---

Ok, I did it!
I managed to access a small database existing on "sql9.freesqldatabase.com"
In short, with a little help from ChatGPT. Why the delay? The first method proposed SQLConnect() for VB6 was always failing.  I tried another approach that worked: SQLDriverConnect() suggested for VBNet. I don't know yet if the first method should work or not. I'll reduce my demo to the simplest and add it here.

[(X)]

I am trying to put as much as I can in an ODBC32 library LG32 file.

[scalion]

Mar 20, 2023 17:36:21 GMT 1 (X) said:

I am trying to put as much as I can in an ODBC32 library LG32 file.

Trop fort !

[(X)]

I am checking out the ADO route... Apparently this is high level compared to SQL commands which are low level. Thanks for the suggestion ChatGPT!

I got ADO to work inserting a row of data to a table in a couple hours!

Now, I need to display the data and I am looking at using GFA's ListView control.

[(X)]

I am just starting to get my head around the 4 or 5 pillars of managing even a small database over the internet.

I did get a nice little "keypad" working to enter a passcode:

I saw how I could use the 16 balls to directly enter hex code.

I even added sound effects! Wow!!!

Anyways, this is yet another project that seemed easy enough at first, but, is slowly wearing me down.

[rogercabo]

Cool.. In the past I tried to create a Billard game as well.

But then it becomes difficultly to calculate the balls running over the table and collide.
The reason for this is easy: The calculation should not depend on the frame rate, because this will cause the balls to intersecting or overrun while fast movements. Then it's a mess to calculate each collide and recalculate the test of the balls no not contact.

The solution is simple: When the user hits with his CUE the first ball, then pre calculate the complete ball scenery with all conditions and save each movement and collide. Then run the complete scenery pre-calculated.

---------------

Perhaps should ask ChatGPT to establish a online connection to a server to save and load data in a secure SFTP way with gb32

Does anyone use GPT Model 4 ?

[(X)]

Mar 25, 2023 15:42:23 GMT 1 rogercabo said:

Does anyone use GPT Model 4 ?

Not yet.

Apparently, it is better... more "bells and whistles", but, version 4 is not free.  Last I checked: ~20$/mo.

I am still enjoying V3 so far.  It's like having your own mentor. 

I am getting to know the ins and outs of HTML, PHP, CSS, JS and online database SQL for starters. 

ChatGPT has helped A LOT! 

[rogercabo]

You can cancel the subscription each month. I think I will do this for one month and have a look what possible with model 4.0
One time 20 euro its okay. And if it's great, then I have no idea if I should continue..
Perhaps I should cancel the subscription, before using.. 

[(X)]

I have been working on the esthetics of the "Billiard Statistics Registry" web interface...

I am having to get my head around a lot of HTML quirks just to get a simple clean interface.

[(X)]

I've been at this since March 3rd 2023. What a ride.

I am getting collisions to work at the moment.  The "3D-ness" is due to JavaScript "Three.js".  It is bare bones (which is the way I like it) so no fancy textures.