Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: ODBC getting specific row

  1. #11

    Re: 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

    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  2. #12
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    49
    Posts
    248
    Rep Power
    37

    Re: ODBC getting specific row

    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

  3. #13
    thinBasic MVPs
    Join Date
    Sep 2006
    Posts
    3,289
    Blog Entries
    2
    Rep Power
    343

    Re: ODBC getting specific row

    Hi martin,

    which ODBC driver do you use?

    Michael

  4. #14
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,083
    Blog Entries
    5
    Rep Power
    725

    Re: ODBC getting specific row

    Hi Martin,

    I like your spirit to keep fighting the problem!

    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
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

  5. #15
    thinBasic MVPs
    Join Date
    Sep 2006
    Posts
    3,289
    Blog Entries
    2
    Rep Power
    343

    Re: ODBC getting specific row

    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

  6. #16
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,083
    Blog Entries
    5
    Rep Power
    725

    Re: ODBC getting specific row

    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]
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

  7. #17

    Re: ODBC getting specific row

    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.

    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  8. #18

    Re: ODBC getting specific row

    I noticed that my CGI / ODBC example on the www.scriptbasic.org site was returning a error. It worked fine on the old server. 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

    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  9. #19
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    49
    Posts
    248
    Rep Power
    37

    Re: ODBC getting specific row

    Good morning Mike, Petr & John

    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

  10. #20

    Re: ODBC getting specific row

    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.

    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?
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

Page 2 of 3 FirstFirst 123 LastLast

Similar Threads

  1. A little more help with ODBC
    By marcuslee in forum Fixed or cleared errors in help material
    Replies: 8
    Last Post: 04-09-2008, 19:32

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •