Author Topic: ODBC in QB64 (Windows)  (Read 4145 times)

0 Members and 1 Guest are viewing this topic.

Offline SpriggsySpriggs

  • Forum Resident
  • Posts: 1145
  • Larger than life
    • View Profile
    • GitHub
ODBC in QB64 (Windows)
« on: September 03, 2021, 11:55:57 am »
I've recently started working with the database engine at my current job. We use ODBC to connect our software to the database. This got me thinking about ODBC and how hard it might be to use in QB64. Turns out, it is surprisingly easy. Easier than the MySQL code from the Wiki. ODBC can be used to connect to any database that can use an ODBC connection. You simply set up the data source on your computer by installing the necessary drivers and adding it to "ODBC Data Sources". For instance, for MySQL, you'd look up "MySQL ODBC connector" on Google. Super simple. I'm not posting full code here just yet as I am still working on trying to make sure the library/wrapper has all the functionality I would expect from a library that communicates with databases.

Below are some snippets to show some of the core functionality:


To open a connection:
Code: QB64 $NOPREFIX: [Select]
  1. DB_Open "SpriggsyWinServer" 'if you already know the name of the DSN or got it from input
  2. 'OR
  3. DB_Open "" 'if you do not have it hard-coded or did not receive input, Windows will open the data source selection box

To query the database:
Code: QB64 $NOPREFIX: [Select]
  1. DB_QUERY "select * from root.table1"

The TYPE being used to store the results:
Code: QB64 $NOPREFIX: [Select]
  1. Type SQL_FIELD
  2.     As Integer type 'identifies what sort of data is stored in that column. VARCHAR, NUMERIC, etc
  3.     As Unsigned Integer size 'the maximum size allowed to be stored in that column
  4.     As Byte isNullable 'whether or not the column allows null values. Not all database engines report this value correctly. Adjust accordingly.
  5.     As Integer decimalDigits 'If the data type is NUMERIC or DECIMAL, this will be the number of digits allowed after the decimal place
  6.     As String columnName, value 'The name of the column and the value in the field
  7.  

I store the TYPE in a two-dimensional array
Code: QB64 $NOPREFIX: [Select]
  1. ReDim Shared As SQL_FIELD DB_Result(1 To 1, 1 To 1)

A simple printing:
Code: QB64 $NOPREFIX: [Select]
  1. Dim As Unsigned Integer row, column
  2. For row = 1 To UBound(DB_Result, 2)
  3.     Print "Row"; row
  4.     For column = 1 To UBound(DB_Result, 1)
  5.         Print "  "; column; GetDataType(DB_Result(column, row).type);
  6.         If DB_Result(column, row).type = SQL_DECIMAL Or DB_Result(column, row).type = SQL_NUMERIC Then
  7.             Print "("; Trim$(Str$(DB_Result(column, row).size)); ","; Trim$(Str$(DB_Result(column, row).decimalDigits)); ") ";
  8.         Else
  9.             Print "("; Trim$(Str$(DB_Result(column, row).size)); ") ";
  10.         End If
  11.  
  12.         If DB_Result(column, row).isNullable = 0 Then
  13.             Print DB_Result(column, row).columnName; " "; DB_Result(column, row).value; " "; "Not nullable"
  14.         Else Print DB_Result(column, row).columnName; " "; DB_Result(column, row).value
  15.         End If
  16.     Next

Screenshot showing the above code in action:
 
Screenshot 2021-09-03 114619.png


To close a connection:
Code: QB64 $NOPREFIX: [Select]
  1. DB_Close
Shuwatch!

Marked as best answer by SpriggsySpriggs on September 05, 2021, 10:16:47 pm

Offline SpriggsySpriggs

  • Forum Resident
  • Posts: 1145
  • Larger than life
    • View Profile
    • GitHub
Re: ODBC in QB64 (Windows)
« Reply #1 on: September 06, 2021, 02:16:41 am »
Here is my test code so far. This will eventually be broken up into a BI & BM style library. The code does work in 64 and 32 bit Windows.

Code: QB64 $NOPREFIX: [Select]
  1. $VersionInfo:Comments=Testing ODBC connections in Windows using QB64
  2. $ExeIcon:'databases.ico'
  3.  
  4. Type SQL_FIELD
  5.     As Unsigned Integer size
  6.     As Byte isNullable
  7.     As Integer decimalDigits
  8.     As String columnName, value
  9.  
  10. Const SQL_SUCCESS = 0
  11.  
  12. Dim Shared As Offset hEnv, hDbc, hStmt
  13. ReDim Shared As SQL_FIELD DB_Result(1 To 1, 1 To 1)
  14. Dim Shared As String ConnectionString
  15. Dim As String datasource: datasource = "SpriggsyWinServer"
  16.  
  17. 'If DB_Open(datasource) Then
  18. DB_Open datasource
  19. Dim As String conTitle: conTitle = "ODBC Test - " + datasource: ConsoleTitle conTitle
  20. If DB_QUERY("SELECT * FROM root.table1") = SQL_SUCCESS Then
  21.     DB_DetailResult
  22. DB_Close
  23. 'Else System
  24. 'End If
  25.  
  26.     Sub SQLAllocHandle (ByVal HandleType As Integer, Byval InputHandle As Offset, Byval OutputHandlePtr As Offset)
  27.     Function SQLGetDiagRec%& (ByVal HandleType As Integer, Byval Handle As Offset, Byval RecNumber As Integer, Byval SQLState As Offset, Byval NativeErrorPtr As Offset, Byval MessageText As Offset, Byval BufferLength As Integer, Byval TextLengthPtr As Offset)
  28.     Sub SQLSetEnvAttr (ByVal EnvironmentHandle As Offset, Byval Attribute As Long, Byval ValuePtr As Offset, Byval StringLength As Long)
  29.     Function SQLDriverConnect%& (ByVal ConnectionHandle As Offset, Byval WindowHandle As Offset, InConnectionString As String, Byval StringLength1 As Integer, Byval OutConnectionString As Offset, Byval BufferLength As Integer, Byval StringLength2Ptr As Offset, Byval DriverCompletion As Unsigned Integer)
  30.     Sub SQLPrepare (ByVal StatementHandle As Offset, StatementText As String, Byval TextLength As Long)
  31.     Sub SQLExecute (ByVal StatementHandle As Offset)
  32.     Function SQLExecute%& (ByVal StatementHandle As Offset)
  33.     Sub SQLNumResultCols (ByVal StatementHandle As Offset, Byval ColumnCountPtr As Offset)
  34.     Sub SQLDescribeCol (ByVal StatementHandle As Offset, Byval ColumnNumber As Unsigned Integer, Byval ColumnName As Offset, Byval BufferLength As Integer, Byval NameLengthPtr As Offset, Byval DataTypePtr As Offset, Byval ColumnSizePtr As Offset, Byval DecimalDigitsPtr As Offset, Byval NullablePtr As Offset)
  35.     Function SQLFetch%& (ByVal StatementHandle As Offset)
  36.     Function SQLGetData%& (ByVal StatementHandle As Offset, Byval ColOrParamNum As Unsigned Integer, Byval TargetType As Integer, Byval TargetValuePtr As Offset, Byval BufferLength As Offset, Byval StrLenOrIndPtr As Offset)
  37.     Function SQLRowCount%& (ByVal StatementHandle As Offset, Byval RowCountPtr As Offset)
  38.     Sub SQLFreeHandle (ByVal HandleType As Integer, Byval Handle As Offset)
  39.     Sub SQLDisconnect (ByVal ConnectionHandle As Offset)
  40.  
  41.     Function GetDesktopWindow%& ()
  42.  
  43. Function DB_Open%% (dsn As String)
  44.     Const SQL_HANDLE_ENV = 1
  45.     Const SQL_HANDLE_DBC = 2
  46.     Const SQL_HANDLE_STMT = 3
  47.     Const SQL_DRIVER_COMPLETE = 1
  48.     Const SQL_NULL_HANDLE = 0
  49.     Const SQL_NTS = -3
  50.     Const SQL_ATTR_ODBC_VERSION = 200
  51.     Const SQL_OV_ODBC3 = 3~&
  52.  
  53.     Dim As Offset ret
  54.     SQLAllocHandle SQL_HANDLE_ENV, SQL_NULL_HANDLE, Offset(hEnv)
  55.     SQLSetEnvAttr hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0
  56.     SQLAllocHandle SQL_HANDLE_DBC, hEnv, Offset(hDbc)
  57.     Dim As String outstr: outstr = Space$(1024)
  58.     Dim As Integer outstrlen
  59.     If dsn = "" Then
  60.         ret = SQLDriverConnect(hDbc, GetDesktopWindow, "", SQL_NTS, Offset(outstr), Len(outstr), Offset(outstrlen), SQL_DRIVER_COMPLETE)
  61.     Else
  62.         ret = SQLDriverConnect(hDbc, 0, "DSN=" + dsn + ";", SQL_NTS, Offset(outstr), Len(outstr), Offset(outstrlen), SQL_DRIVER_COMPLETE)
  63.     End If
  64.     ConnectionString = Mid$(outstr, 1, outstrlen)
  65.     SQLAllocHandle SQL_HANDLE_STMT, hDbc, Offset(hStmt)
  66.     If SQL_SUCCEEDED(ret) Then
  67.         DB_Open = -1
  68.     Else
  69.         DB_Error "DB_Open", hDbc, SQL_HANDLE_DBC
  70.         DB_Open = 0
  71.     End If
  72.  
  73. Sub DB_Open (dsn As String)
  74.     Const SQL_HANDLE_ENV = 1
  75.     Const SQL_HANDLE_DBC = 2
  76.     Const SQL_HANDLE_STMT = 3
  77.     Const SQL_DRIVER_COMPLETE = 1
  78.     Const SQL_NULL_HANDLE = 0
  79.     Const SQL_NTS = -3
  80.     Const SQL_ATTR_ODBC_VERSION = 200
  81.     Const SQL_OV_ODBC3 = 3~&
  82.  
  83.     Dim As Offset ret
  84.     SQLAllocHandle SQL_HANDLE_ENV, SQL_NULL_HANDLE, Offset(hEnv)
  85.     SQLSetEnvAttr hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0
  86.     SQLAllocHandle SQL_HANDLE_DBC, hEnv, Offset(hDbc)
  87.     Dim As String outstr: outstr = Space$(1024 + 1)
  88.     Dim As Integer outstrlen
  89.     If dsn = "" Then
  90.         ret = SQLDriverConnect(hDbc, GetDesktopWindow, "", SQL_NTS, Offset(outstr), Len(outstr), Offset(outstrlen), SQL_DRIVER_COMPLETE)
  91.     Else
  92.         ret = SQLDriverConnect(hDbc, 0, "DSN=" + dsn + ";", SQL_NTS, Offset(outstr), Len(outstr), Offset(outstrlen), SQL_DRIVER_COMPLETE)
  93.     End If
  94.     ConnectionString = Mid$(outstr, 1, outstrlen)
  95.     SQLAllocHandle SQL_HANDLE_STMT, hDbc, Offset(hStmt)
  96.     If Not (SQL_SUCCEEDED(ret)) Then
  97.         DB_Error "DB_Open", hDbc, SQL_HANDLE_DBC
  98.     End If
  99.  
  100. Sub DB_QUERY (sql_command As String)
  101.     Const SQL_CHAR = 1
  102.     Const SQL_C_CHAR = SQL_CHAR
  103.     Const SQL_NO_NULLS = 0
  104.     Const SQL_NULLABLE = 1
  105.     Const SQL_NULLABLE_UNKNOWN = 2
  106.     Const SQL_NULL_DATA = -1
  107.     Const SQL_NTS = -3
  108.  
  109.     Dim As Offset ret, execCode
  110.     SQLPrepare hStmt, sql_command, SQL_NTS
  111.     execCode = SQLExecute(hStmt)
  112.     If SQL_SUCCEEDED(execCode) Then
  113.         Dim As Integer columns
  114.         SQLNumResultCols hStmt, Offset(columns)
  115.         ret = SQLFetch(hStmt)
  116.         Dim As Long row
  117.         While SQL_SUCCEEDED(ret)
  118.             Dim As Unsigned Integer i
  119.             row = row + 1
  120.             For i = 1 To columns
  121.                 Dim As Long indicator
  122.                 Dim As String buf: buf = Space$(4096 + 1)
  123.                 Dim As String columnName: columnName = Space$(128)
  124.                 Dim As Integer colNameLength, dataType, decimalDigits, nullable
  125.                 Dim As Unsigned Integer columnSize
  126.                 ret = SQLGetData(hStmt, i, SQL_C_CHAR, Offset(buf), Len(buf), Offset(indicator))
  127.                 If SQL_SUCCEEDED(ret) Then
  128.                     ReDim Preserve As SQL_FIELD DB_Result(columns, row)
  129.                     buf = Mid$(buf, 1, indicator)
  130.                     If indicator = SQL_NULL_DATA Then buf = "NULL"
  131.                     SQLDescribeCol hStmt, i, Offset(columnName), Len(columnName), Offset(colNameLength), Offset(dataType), Offset(columnSize), Offset(decimalDigits), Offset(nullable)
  132.                     columnName = Mid$(columnName, 1, colNameLength)
  133.                     DB_Result(i, row).type = dataType
  134.                     DB_Result(i, row).size = columnSize
  135.                     DB_Result(i, row).decimalDigits = decimalDigits
  136.                     DB_Result(i, row).columnName = columnName
  137.                     DB_Result(i, row).value = buf
  138.                     Select Case nullable
  139.                         Case SQL_NO_NULLS
  140.                             DB_Result(i, row).isNullable = 0
  141.                         Case SQL_NULLABLE
  142.                             DB_Result(i, row).isNullable = -1
  143.                     End Select
  144.                 End If
  145.             Next
  146.             ret = SQLFetch(hStmt)
  147.         Wend
  148.     Else
  149.         DB_Error "DB_QUERY", hStmt, 3
  150.     End If
  151.  
  152. Function DB_QUERY%& (sql_command As String)
  153.     Const SQL_CHAR = 1
  154.     Const SQL_C_CHAR = SQL_CHAR
  155.     Const SQL_NO_NULLS = 0
  156.     Const SQL_NULLABLE = 1
  157.     Const SQL_NULLABLE_UNKNOWN = 2
  158.     Const SQL_NULL_DATA = -1
  159.     Const SQL_NTS = -3
  160.     Const SQL_SUCCESS = 0
  161.  
  162.     Dim As Offset ret, execCode
  163.     SQLPrepare hStmt, sql_command, SQL_NTS
  164.     execCode = SQLExecute(hStmt)
  165.     If SQL_SUCCEEDED(execCode) Then
  166.         Dim As Integer columns
  167.         SQLNumResultCols hStmt, Offset(columns)
  168.         ret = SQLFetch(hStmt)
  169.         Dim As Long row
  170.         While SQL_SUCCEEDED(ret)
  171.             Dim As Unsigned Integer i
  172.             row = row + 1
  173.             For i = 1 To columns
  174.                 Dim As Long indicator
  175.                 Dim As String buf: buf = Space$(4096 + 1)
  176.                 Dim As String columnName: columnName = Space$(128)
  177.                 Dim As Integer colNameLength, dataType, decimalDigits, nullable
  178.                 Dim As Unsigned Integer columnSize
  179.                 ret = SQLGetData(hStmt, i, SQL_C_CHAR, Offset(buf), Len(buf), Offset(indicator))
  180.                 If SQL_SUCCEEDED(ret) Then
  181.                     ReDim Preserve As SQL_FIELD DB_Result(columns, row)
  182.                     buf = Mid$(buf, 1, indicator)
  183.                     If indicator = SQL_NULL_DATA Then buf = "NULL"
  184.                     SQLDescribeCol hStmt, i, Offset(columnName), Len(columnName), Offset(colNameLength), Offset(dataType), Offset(columnSize), Offset(decimalDigits), Offset(nullable)
  185.                     columnName = Mid$(columnName, 1, colNameLength)
  186.                     DB_Result(i, row).type = dataType
  187.                     DB_Result(i, row).size = columnSize
  188.                     DB_Result(i, row).decimalDigits = decimalDigits
  189.                     DB_Result(i, row).columnName = columnName
  190.                     DB_Result(i, row).value = buf
  191.                     Select Case nullable
  192.                         Case SQL_NO_NULLS
  193.                             DB_Result(i, row).isNullable = 0
  194.                         Case SQL_NULLABLE
  195.                             DB_Result(i, row).isNullable = -1
  196.                     End Select
  197.                 End If
  198.             Next
  199.             ret = SQLFetch(hStmt)
  200.         Wend
  201.     Else
  202.         DB_Error "DB_QUERY", hStmt, 3
  203.     End If
  204.     DB_QUERY = execCode
  205.  
  206. Function DB_Esc$ (columnName As String)
  207.     DB_Esc = "`" + columnName + "`"
  208.  
  209. Function DB_Q$ (value As String)
  210.     DB_Q = "'" + value + "'"
  211.  
  212. Function DB_AffectedRows%&
  213.     Dim As Offset rowCount
  214.     Dim As Offset ret: ret = SQLRowCount(hStmt, Offset(rowCount))
  215.     If SQL_SUCCEEDED(ret) Then DB_AffectedRows = rowCount
  216.  
  217. Sub DB_DetailResult
  218.     Const SQL_DECIMAL = 3
  219.     Const SQL_NUMERIC = 2
  220.     Dim As Unsigned Integer row, column
  221.     Print "Connection: "; ConnectionString
  222.     For row = 1 To UBound(DB_Result, 2)
  223.         Print "Row"; row
  224.         For column = 1 To UBound(DB_Result, 1)
  225.             Print "  "; column; GetDataType(DB_Result(column, row).type);
  226.             If DB_Result(column, row).type = SQL_DECIMAL Or DB_Result(column, row).type = SQL_NUMERIC Then
  227.                 Print "("; Trim$(Str$(DB_Result(column, row).size)); ","; Trim$(Str$(DB_Result(column, row).decimalDigits)); ") ";
  228.             Else
  229.                 Print "("; Trim$(Str$(DB_Result(column, row).size)); ") ";
  230.             End If
  231.  
  232.             If DB_Result(column, row).isNullable = 0 Then
  233.                 Print DB_Result(column, row).columnName; " "; DB_Result(column, row).value; " "; "Not nullable"
  234.             Else Print DB_Result(column, row).columnName; " "; DB_Result(column, row).value
  235.             End If
  236.         Next
  237.     Next
  238.  
  239. Sub DB_Close
  240.     Const SQL_HANDLE_ENV = 1
  241.     Const SQL_HANDLE_DBC = 2
  242.     SQLDisconnect (hDbc)
  243.     SQLFreeHandle SQL_HANDLE_DBC, hDbc
  244.     SQLFreeHandle SQL_HANDLE_ENV, hEnv
  245.  
  246. Function GetDataType$ (dataType As Integer)
  247.     Const SQL_CHAR = 1
  248.     Const SQL_C_CHAR = SQL_CHAR
  249.     Const SQL_VARCHAR = 12
  250.     Const SQL_LONGVARCHAR = -1
  251.     Const SQL_WCHAR = -8
  252.     Const SQL_WVARCHAR = -9
  253.     Const SQL_WLONGVARCHAR = -10
  254.     Const SQL_DECIMAL = 3
  255.     Const SQL_NUMERIC = 2
  256.     Const SQL_SMALLINT = 5
  257.     Const SQL_INTEGER = 4
  258.     Const SQL_REAL = 7
  259.     Const SQL_FLOAT = 6
  260.     Const SQL_DOUBLE = 8
  261.     Const SQL_BIT = -7
  262.     Const SQL_TINYINT = -6
  263.     Const SQL_BIGINT = -5
  264.     Const SQL_BINARY = -2
  265.     Const SQL_VARBINARY = -3
  266.     Const SQL_LONGVARBINARY = -4
  267.     Const SQL_TYPE_DATE = 91
  268.     Const SQL_TYPE_TIME = 92
  269.     Const SQL_TYPE_TIMESTAMP = 93
  270.     Const SQL_INTERVAL_MONTH = -81
  271.     Const SQL_INTERVAL_YEAR = -80
  272.     Const SQL_INTERVAL_YEAR_TO_MONTH = -82
  273.     Const SQL_INTERVAL_DAY = -83
  274.     Const SQL_INTERVAL_HOUR = -84
  275.     Const SQL_INTERVAL_MINUTE = -85
  276.     Const SQL_INTERVAL_SECOND = -86
  277.     Const SQL_INTERVAL_DAY_TO_HOUR = -87
  278.     Const SQL_INTERVAL_DAY_TO_MINUTE = -88
  279.     Const SQL_INTERVAL_DAY_TO_SECOND = -89
  280.     Const SQL_INTERVAL_HOUR_TO_MINUTE = -90
  281.     Const SQL_INTERVAL_HOUR_TO_SECOND = -91
  282.     Const SQL_INTERVAL_MINUTE_TO_SECOND = -92
  283.     Const SQL_GUID = -11
  284.  
  285.     Select Case dataType
  286.         Case SQL_CHAR, SQL_C_CHAR
  287.             GetDataType = "CHAR"
  288.         Case SQL_VARCHAR
  289.             GetDataType = "VARCHAR"
  290.         Case SQL_LONGVARCHAR
  291.             GetDataType = "LONG VARCHAR"
  292.         Case SQL_WCHAR
  293.             GetDataType = "WCHAR"
  294.         Case SQL_WVARCHAR
  295.             GetDataType = "VARWCHAR"
  296.         Case SQL_WLONGVARCHAR
  297.             GetDataType = "LONGWVARCHAR"
  298.         Case SQL_DECIMAL
  299.             GetDataType = "DECIMAL"
  300.         Case SQL_NUMERIC
  301.             GetDataType = "NUMERIC"
  302.         Case SQL_SMALLINT
  303.             GetDataType = "SMALLINT"
  304.         Case SQL_INTEGER
  305.             GetDataType = "INTEGER"
  306.         Case SQL_REAL
  307.             GetDataType = "REAL"
  308.         Case SQL_FLOAT
  309.             GetDataType = "FLOAT"
  310.         Case SQL_DOUBLE
  311.             GetDataType = "DOUBLE PRECISION"
  312.         Case SQL_BIT
  313.             GetDataType = "BIT"
  314.         Case SQL_TINYINT
  315.             GetDataType = "TINYINT"
  316.         Case SQL_BIGINT
  317.             GetDataType = "BIGINT"
  318.         Case SQL_BINARY
  319.             GetDataType = "BINARY"
  320.         Case SQL_VARBINARY
  321.             GetDataType = "VARBINARY"
  322.         Case SQL_LONGVARBINARY
  323.             GetDataType = "LONG VARBINARY"
  324.         Case SQL_TYPE_DATE
  325.             GetDataType = "DATE"
  326.         Case SQL_TYPE_TIME
  327.             GetDataType = "TIME"
  328.         Case SQL_TYPE_TIMESTAMP
  329.             GetDataType = "TIMESTAMP"
  330.         Case SQL_INTERVAL_MONTH
  331.             GetDataType = "INTERVAL MONTH"
  332.         Case SQL_INTERVAL_YEAR
  333.             GetDataType = "INTERVAL YEAR"
  334.         Case SQL_INTERVAL_YEAR_TO_MONTH
  335.             GetDataType = "INTERVAL YEAR TO MONTH"
  336.         Case SQL_INTERVAL_DAY
  337.             GetDataType = "INTERVAL DAY"
  338.         Case SQL_INTERVAL_HOUR
  339.             GetDataType = "INTERVAL HOUR"
  340.         Case SQL_INTERVAL_MINUTE
  341.             GetDataType = "INTERVAL MINUTE"
  342.         Case SQL_INTERVAL_SECOND
  343.             GetDataType = "INTERVAL SECOND"
  344.         Case SQL_INTERVAL_DAY_TO_HOUR
  345.             GetDataType = "INTERVAL DAY TO HOUR"
  346.         Case SQL_INTERVAL_DAY_TO_MINUTE
  347.             GetDataType = "INTERVAL DAY TO MINUTE"
  348.         Case SQL_INTERVAL_DAY_TO_SECOND
  349.             GetDataType = "INTERVAL DAY TO SECOND"
  350.         Case SQL_INTERVAL_HOUR_TO_MINUTE
  351.             GetDataType = "INTERVAL HOUR TO MINUTE"
  352.         Case SQL_INTERVAL_HOUR_TO_SECOND
  353.             GetDataType = "INTERVAL HOUR TO SECOND"
  354.         Case SQL_INTERVAL_MINUTE_TO_SECOND
  355.             GetDataType = "INTERVAL MINUTE TO SECOND"
  356.         Case SQL_GUID
  357.             GetDataType = "GUID"
  358.     End Select
  359.  
  360. Sub DB_Error (__fn As String, handle As Offset, __type As Integer)
  361.     Const SQL_SUCCESS = 0
  362.     Const MB_OK = 0 'OK button only
  363.     Const MB_ICONEXCLAMATION = 48
  364.     Dim As Long i, NativeError
  365.     Dim As String SQLState: SQLState = Space$(5 + 1)
  366.     Dim As String MessageText: MessageText = Space$(256 + 1)
  367.     Dim As Integer TextLength
  368.     Dim As Offset ret
  369.  
  370.     Do
  371.         i = i + 1
  372.         ret = SQLGetDiagRec(__type, handle, i, Offset(SQLState), Offset(NativeError), Offset(MessageText), Len(MessageText), Offset(TextLength))
  373.         If SQL_SUCCEEDED(ret) Then
  374.             MessageBox 0, "Error reported in " + __fn + ":" + Chr$(10) + Mid$(SQLState, 1, InStr(SQLState, Chr$(0)) - 1) + ":" + Trim$(Str$(i)) + ":" + Trim$(Str$(NativeError)) + ":" + Mid$(MessageText, 1, TextLength) + Chr$(0), "ODBC Error" + Chr$(0), MB_OK Or MB_ICONEXCLAMATION
  375.         End If
  376.     Loop While ret = SQL_SUCCESS
  377.  
  378. Function SQL_SUCCEEDED& (rc As Offset)
  379.     SQL_SUCCEEDED = (((rc) And (Not 1)) = 0)
  380.  
  381. $If MESSAGEBOX = UNDEFINED Then
  382.         Function MessageBox& (ByVal hWnd As _Offset, message As String, title As String, Byval uType As _Unsigned Long)
  383.         Sub MessageBox (ByVal hWnd As _Offset, message As String, title As String, Byval uType As _Unsigned Long)
  384.     End Declare
  385.     $Let MESSAGEBOX = TRUE

DB_DetailResult is just a way for me to quickly print a bunch of detailed output from the SELECT query. It won't be part of the final product.

Screenshot showing successful query:
 
Screenshot 2021-09-06 020817.png


Screenshot showing unsuccessful query:
 
Screenshot 2021-09-06 020846.png


Screenshot showing ODBC data source selection window when a data source is not specified in DB_Open:
 
Screenshot 2021-09-06 021108.png

* databases.ico (Filesize: 29.22 KB, Dimensions: 16x16, Views: 520)
« Last Edit: September 06, 2021, 02:20:46 am by SpriggsySpriggs »
Shuwatch!

Offline KingRat

  • Newbie
  • Posts: 16
    • View Profile
Re: ODBC in QB64 (Windows)
« Reply #2 on: October 01, 2021, 04:29:50 pm »
That´s a nice approach!

Some time ago I was studying a way to work with sqlite3, I think it would be a nice feature for QB...
Btw I´ll follow this thread!






Offline Pete

  • Forum Resident
  • Posts: 2361
  • Cuz I sez so, varmint!
    • View Profile
Re: ODBC in QB64 (Windows)
« Reply #3 on: October 01, 2021, 04:40:26 pm »
For my office, I created my own random access databases. For websites, I stick with HTML / CSS and use apps to generate and upload page changes, rather than updating a mySQL file and PHP to to show pages. That said, what do you see as the most common usage for pairing QB64 programs with a database system like mySQL or ODBC?

Pete
Want to learn how to write code on cave walls? https://www.tapatalk.com/groups/qbasic/qbasic-f1/

Offline SpriggsySpriggs

  • Forum Resident
  • Posts: 1145
  • Larger than life
    • View Profile
    • GitHub
Re: ODBC in QB64 (Windows)
« Reply #4 on: October 01, 2021, 05:01:56 pm »
That´s a nice approach!

Some time ago I was studying a way to work with sqlite3, I think it would be a nice feature for QB...
Btw I´ll follow this thread!

I've worked with it before on Windows in QB64. I've got a small library going that I've put on the back burner
Shuwatch!