ODBC getting specific row

Martin,

I didn't realize that accessing the ODBC interface in thinBASIC was at the API level.

With the ScriptBasic ODBC/unixODBC extension module interface it only takes a few lines of code to access your data.

[code=basic]
dbh = ODBC::RealConnect("DSN","user","password")

ODBC::query(dbh,"SELECT * FROM contact")

ODBC::FetchHash(dbh,column)

PRINT column{"ID"},"\n"
PRINT column{"NAME"},"\n"
PRINT column{"ADDRESS"},"\n"
PRINT column{"CITY"},"\n"
PRINT column{"STATE"},"\n"
PRINT column{"ZIP"},"\n"
PRINT column{"PHONE"},"\n"
PRINT column{"EMAIL"},"\n"
PRINT column{"URL"},"\n"
[/code]

In the above example I'm using the associative array feature of the ODBC::FetchHash() function. The quoted names are the column names for the table.

You can use a standard array to return your results with the ODBC:FetchArray() function. I use this function when the results of my query may be from a join of multiple tables.

I thought someone here was working on a thinBASIC database module for MySQL or SQLite. I think an easy to use DB interface is imperative if thinBASIC is going to be used for general utilities or business applications.

John

Hello Petr,

I must confess that I never exactly understood BYVAL and BYREF, I never used it. But you are right, your code is fine --> no errors anymore! Great! But unfortunately RowNum always returns value 0. Maybe this function is not 100% developed in ThinBasic yet. Maybe Eros can tell us...by the way where is our master? Very busy I guess?


To John:

hmmm maybe I must step over to Scriptbasic :-) Just kidding, my thinbasic script is too long now. And Thinbasic satisfy me at most points I need. But Scriptbasic language is also very interesting, I will certainly study it in the future (the same for TBGL, I really would learn that too someday!).
In the past I created an ADO database in Visual Basic. That was VERY easy to code. I saw an example on this forum for ADO in Thinbasic but I can't find the needed include-file. I guess Eros deleted it because Thinbasic can handle ODBC now.

Greetings,

Martin

Hi martin,

which ODBC driver do you use?

Michael

Hi Martin,

I like your spirit to keep fighting the problem! :occasion:

Well, ODBC header comes from José Roca - what Yoda is to mastering of Force, José Roca is to Win32 programming.
So to learn from source you can pay a visit to Dagobah his forum and learn from his examples.

They are in PowerBASIC, but PB is very close to ThinBASIC, so the learning process should be very smooth (ODBC examples use same API as we in ThinBASIC).
I see databases are very hot topic, I will do my best to prepare some resources on this topic for next ThinBASIC Journal.

BYVAL, BYREF ... this might seem complicated, but it is not.

When parameter is passed BYVAL, it means BY VALUE. So you can pass number/string, function returning value or variable.

BYREF is slightly different beast. It means BY REFERENCE. It needs variable, and it is interesting way allowing to manipulate external variables from inside of FUNCTION/SUB.

Regarding the Master :) - according to my last information Eros should be finishing some work by now and appear back here quite soon. I also miss his presence here, but I respect his work.

So if you have some time, please check out that Josés resource. If it does not answer your question, I will be happy to seek solution with you. José also provides some newer headers, I will check them and let you know.


Petr

Yes martin,

thumbs up for your spirit. I like that. Maybe there is a bug in the header file. I hope to find time to rebuild what you do so I can seek for a solution myself.

Michael

Hi,

I think SQLGetStmtAttr is ODBC 3.x+, but I am not able to create context for it on my PC (old ODBC driver?)
This is translated example from José Roca forum, including handy function to "describe error".

[code=ThinBasic]
uses "Console"

#INCLUDE "%APP_INCLUDEPATH%\ODBC352.INC"

GLOBAL hEnv AS DWORD
GLOBAL hDbc AS DWORD
GLOBAL hStmt AS DWORD
' ========================================================================================
' Main
' ========================================================================================

FUNCTION TBMAIN()

LOCAL r AS INTEGER
LOCAL szBuf AS ASCIIZ * 256
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025

' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
PRINTL "Allocating handle..."
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION

' Tells to the driver manager that is an application that uses the ODBC driver 3.x
LOCAL ByvalValue AS LONG = %SQL_OV_ODBC3
LOCAL ByvalValue2 AS LONG
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, ByvalValue, %SQL_IS_INTEGER)
PRINTL "Trying to set ODBC version to 3.x..."
IF ISFALSE SQL_SUCCEEDED(r) THEN
printl SQLGetErrorInfo(%SQL_HANDLE_ENV, hEnv)
Terminate()
END IF

' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN Terminate()
PRINTL "Connection handle allocated..."

' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
ByvalValue = 0
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
ByvalValue, _
%SQL_DRIVER_COMPLETE)

PRINTL "Connected..."

' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
Terminate
END IF

' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN Terminate
' Cursor type
ByvalValue = %SQL_CURSOR_KEYSET_DRIVEN
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, ByvalValue, %SQL_IS_UINTEGER)
' Optimistic concurrency
ByvalValue = %SQL_CONCUR_VALUES
ByvalValue2 = %SQL_IS_UINTEGER
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, ByvalValue, ByvalValue2)

' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Titles ORDER BY Title", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
PRINTL SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
Terminate
END IF

' Get the current setting or values of the descriptor record
' for the 9th field ("Price")

LOCAL hIpd AS DWORD
LOCAL szName AS ASCIIZ * 256
LOCAL cbLength AS LONG
LOCAL Precision AS INTEGER
LOCAL nLength AS LONG

ByvalValue = 0
r = SQLGetstmtAttr(hStmt, %SQL_ATTR_IMP_ROW_DESC, hIpd, %SQL_IS_UINTEGER, ByvalValue)
IF ISFALSE hIpd THEN Terminate
r = SQLGetDescField(hIpd, 9, %SQL_DESC_NAME, szName, SIZEOF(szName), cbLength)
r = SQLGetDescField(hIpd, 9, %SQL_DESC_PRECISION, Precision, %SQL_IS_SMALLINT, cbLength)
r = SQLGetDescField(hIpd, 9, %SQL_DESC_OCTET_LENGTH, nLength, %SQL_IS_INTEGER, cbLength)

printl szName
printl Precision
printl nLength

Terminate()
END FUNCTION
' ========================================================================================

SUB Terminate()
PRINTL "Terminating"
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
waitkey
STOP
END SUB

' ========================================================================================
' SQLGetErrorInfo uses SQLGetDiagRec to retrieve an error description.
' HandleType must be one of the following:
' %SQL_HANDLE_ENV
' %SQL_HANDLE_DBC
' %SQL_HANDLE_STMT
' %SQL_HANDLE_DESC
' hndl is the handle of the environment, connection, statement or descriptor.
' ========================================================================================
FUNCTION SQLGetErrorInfo (BYVAL HandleType AS INTEGER, BYVAL hndl AS DWORD, OPTIONAL BYVAL iErrorCode AS INTEGER) AS STRING

LOCAL iResult AS INTEGER
LOCAL szSqlState AS ASCIIZ * 6
LOCAL lNativeError AS DWORD
LOCAL szErrMsg AS ASCIIZ * %SQL_MAX_MESSAGE_LENGTH + 1
LOCAL strErrMsg AS STRING
LOCAL cbbytes AS INTEGER
LOCAL ErrorCount AS LONG
LOCAL i AS LONG

iResult = SQLGetDiagField(HandleType, hndl, 0, %SQL_DIAG_NUMBER, ErrorCount, %SQL_IS_INTEGER, cbbytes)

IF ErrorCount THEN
FOR i = 1 TO ErrorCount
iResult = SQLGetDiagRec(HandleType, hndl, 1, szSqlState, lNativeError, szErrMsg, SIZEOF(szErrMsg), cbbytes)
IF iResult <> %SQL_SUCCESS AND iResult <> %SQL_SUCCESS_WITH_INFO THEN EXIT FOR
strErrMsg = strErrMsg & "SqlState: " & szSqlState & $CRLF & _
"Native error: " & FORMAT$(lNativeError) & $CRLF & szErrMsg
NEXT
ELSEIF iErrorCode THEN
SELECT CASE iErrorCode
CASE -1 : strErrMsg = "SQL error" ' "SQL_ERROR"
CASE 2 : strErrMsg = "Still executing" ' "SQL_STILL_EXECUTING"
CASE -2 : strErrMsg = "Invalid handle" '"SQL_INVALID_HANDLE"
CASE 99 : strErrMsg = "Need data" ' "SQL_NEED_DATA"
CASE 100 : strErrMsg = "No data" '"SQL_NO_DATA"
CASE ELSE
strErrMsg = "Error " & FORMAT$(iErrorCode)
END SELECT
END IF

FUNCTION = $CRLF+"Error occurred:"+$CRLF+strErrMsg+$CRLF

END FUNCTION
' ========================================================================================
FUNCTION SQL_SUCCEEDED(byval param as long) AS LONG
if param = %SQL_SUCCESS or param = %SQL_SUCCESS_WITH_INFO THEN Return 1
Return 0
END FUNCTION
[/code]

Quote:

---

hmmm maybe I must step over to Scriptbasic :-) Just kidding, my thinbasic script is too long now. And Thinbasic satisfy me at most points I need. But Scriptbasic language is also very interesting,

---

My goal with ScriptBasic is to provide a easy to use general purpose scripting language based on a traditional Basic syntax that runs on everything. I think there is a need for a language tool to solve one off tasks that doesn't require a development budget to get the job done.

ScriptBasic is fast with a < 500KB foot print that's was designed from the ground up to be embeddable. The console mode interpreter and multi-threaded HTTPD server are variation examples of language API.

Give it a try and I'm sure it will find a place in your development toolbox.

I noticed that my CGI / ODBC example on the www.scriptbasic.org site was returning a error. It worked fine on the old server. :read: The problem ended up being a permission issue for the .odbc.ini file in the site home directory. (ODBC trace to the rescue)

http://www.scriptbasic.org/forum/ind...topic,4.0.html

Good morning Mike, Petr & John :arms:

I think the spirit to keep fighting a problem should be the spirit of every programmer. If I can't solve this problem, I will remove all ODBC code and try something else.
Ofcourse I already noticed José Roca's website and forum as it is mentioned in the ThinBasic Helpfile. I did a lot of research there and also googled a lot but as I said no succes. But maybe I should post a message at that forum, that might help.

I use version 2.5 of ODBC so that could be the problem indeed. So Petr I can't run your translated example as well. And I don't want to install another driver because I want that potential users of my script can run it without installing of any drivers.

To John: I will give ScriptBasic a try for sure. But first I want to finish my thinbasic script.

All the best,

Martin

Quote:

---

To John: I will give ScriptBasic a try for sure. But first I want to finish my thinbasic script.

---

Problem solving is life's best teacher. :violent:

I would like to see the thinBASIC community take a serious look at creating a ODBC extension module that is easy to use. Sooner or later you will need to move beyond spiffy games.

After a quick search of the thinBASIC site I found this link in the modules help section.

ODBC Include

Have you tried this include file Martin? Is this what your trying to get working?