Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: ODBC getting specific row

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

    ODBC getting specific row

    Hi everybody,

    I'm struggeling with this problem for a week now and it makes me (almost) crazy

    Suppose I have an ODBC database with 1 column ("name") and it's filled with 5 records:
    record 1 = mike
    record 2 = eros
    record 3 = frank
    record 4 = roberto
    record 5 = petr

    Now I make a query with SELECT * FROM ... WHERE name LIKE 'frank'

    The query gives ofcourse 1 result in this case. Now I want to know what the REAL record number (row) is of this result (in this case row 3). How should I do that? I googled a lot, but still no satisfied answers found. I read something about Bookmarks and FetchScroll but I have no idea how to use this in Thinbasic.

    Can anyone help me out here?

    Thanks in advance,

    Martin

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

    Re: ODBC getting specific row

    Hi Martin,

    how big is the database?
    I am not expert on this topic, so I could only solve it in 2 ways:

    #1
    - Use silly statement like "SELECT * FROM Authors"
    - Then something like:
    [code=thinbasic]
    ...
    DIM Row AS LONG
    DIM UserName AS STRING
    DO
    OdbcFetch hStmt
    IF ISTRUE(OdbcError) THEN EXIT DO
    Row += 1
    UserName = OdbcGetDataString(hStmt, 1)
    IF UserName = what I seek THEN Return Row
    LOOP
    [/code]

    ... I guess it would take time for bigger DB.

    #2
    - Inject column with 1,2,3,... before executing the SELECT command

    I am sorry, me and SQL ...


    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

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

    Re: ODBC getting specific row

    Hi Petr,

    Thanks for your reply.
    The database can be very large so solution 1 is too slow.
    Solution 2 does not work good anymore if the columns are sorted.

    In reality i have a big database with 12 columns and i need this for a find next/previous function.

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

    Re: ODBC getting specific row

    Quote Originally Posted by martin
    Solution 2 does not work good anymore if the columns are sorted.
    This should be: Solution 2 does not work good anymore if the columns are sorted descending.

  5. #5

    Re: ODBC getting specific row

    Martin,

    If you need to keep track of 'record numbers' then the best thing to do is create a column with a auto incrementing column type. The problem with this method is you have to ignore this column in updates and inserts as the SQL server maintains it. Create secondary indexes on columns you need to sort by. This speeds up the query and is most noticeable if your doing joins with other tables.

    Good luck with your quest.

    John
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

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

    Re: ODBC getting specific row

    Hi John,

    Thanks! An auto increment column sounds good to me! I didn't know that this was possible.

    I made an auto increment column with CREATE TABLE Phonebook (SID IDENTITY PRIMARY KEY ........ )

    Then I try to add a record:
    cbSID = %SQL_COLUMN_IGNORE
    szAnalyzed = "n":cbAnalyzed = 1
    OdbcAddRecord hStmt

    But no succes, the record will not be added. But if I remove %SQL_COLUMN_IGNORE and give SID a value the record will be added succesfully. So somehow %SQL_COLUMN_IGNORE does not work. Do you have any idea what I am doing wrong?

    Kind regards,

    Martin

  7. #7

    Re: ODBC getting specific row

    Hi Martin,

    Just ignore the auto increment column in your INSERT and UPDATE SQL statements. The SQL server will assign the next sequential number with the next INSERT. If you include the column in a UPDATE you will get an error.

    Only use the auto increment column with a SELECT / WHERE clause.

    Hope this helps.

    John
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  8. #8
    Senior Member Lionheart008's Avatar
    Join Date
    Sep 2008
    Location
    Germany, Bad Sooden-Allendorf
    Age
    48
    Posts
    919
    Rep Power
    104

    Re: ODBC getting specific row

    hi martin, petr, hi john

    I have found this material for odbc

    perhaps martin, you can use it, but it's just a source content for your work or idea... it doesn't run at all.. I have no deeper infos or knowledge about odbc, but some day I will need it too for my liongfx manual or syntax help.

    [code=thinbasic]'-- testscript for martin by lionheart
    '-- not using, as it's not running, it's just a source for your work !!!

    uses "console", "liongfx"


    CONST MySQLHost = <MySQLServer>
    CONST MySQLUser = <Username>
    CONST MySQLPW = <Password>
    CONST MySQLDB = <Datafile>

    dim ServerInfo, u as string = MySQLInfo()
    dim sel, result, a as integer

    do

    liongfx_CLS
    liongfx_print: liongfx_print " MYSQL-example for thinbasic": liongfx_print
    liongfx_print " Serverinfo: "+ ServerInfo
    liongfx_print
    liongfx_print " (1) show matrix"
    liongfx_print " (2) insert data"
    liongfx_print " (0) close, end"
    liongfx_print
    DO: LOOP WHILE liongfx_INKEY <> ""
    liongfx_INPUT " select: ", sel
    Select Case sel
    Case 0 : liongfx_error,2
    Case 1
    If showMatrix() < 0 Then
    liongfx_print " error."
    liongfx_Sleep
    End If
    Case 2
    liongfx_print
    liongfx_INPUT " Username: " + u
    liongfx_INPUT " Age: " + a
    liongfx_print
    result = MySQLInsert(u,a)
    If result < 1 Then
    liongfx_print " error " & result
    ELSE
    liongfx_print " new data: " & result
    END IF
    liongfx_SLEEP
    Case Else
    Beep
    End Select
    LOOP


    FUNCTION MySQLInfo () As String

    dim db as MYSQL ptr ' ?
    Dim ServerInfo as String
    db = mysql_init( NULL )
    if( mysql_real_connect( db, MySQLHost, MySQLUser, MySQLPW, NULL, MYSQL_PORT, NULL, 0 ) = 0 ) then
    liongfx_print "error: no connection to MySQL-Server "
    mysql_close( db )
    liongfx_SLEEP
    end
    end if
    ServerInfo = *mysql_get_server_info( db )
    Mysql_close(db)
    Return ServerInfo
    END FUNCTION

    FUNCTION MySQLInsert (ByVal A As String, ByVal B As Integer) As Integer
    dim db as MYSQL ptr
    Dim Query As String
    Dim ID As Integer
    Dim result As Integer
    Dim Fehler as ZString Ptr
    db = mysql_init( NULL )
    if mysql_real_connect( db, MySQLHost, MySQLUser, MySQLPW, NULL, MYSQL_PORT, NULL, 0 ) = 0 then Return -1
    if mysql_select_db( db, MySQLDB ) then Return -2


    query = "INSERT INTO testtab (`username`,`age`,`time`) VALUES (" & chr(34) & A & chr(34) & "," & chr(34) & B & chr(34) & ",CURRENT_TIMESTAMP())"
    result = mysql_real_query (db,STRPTR(query),LEN(query))
    If result = 0 THEN
    ID = mysql_insert_id(db)
    mysql_close(db)
    Return ID
    ELSE
    Error = mysql_error(db)
    If (*Error)[0] <> 0 Then
    liongfx_print " "+ *mysql_error(db)
    END IF
    mysql_close(db)
    Return -3
    END IF
    END FUNCTION

    FUNCTION showMatrix () AS INTEGER
    dim db as MYSQL ptr '?
    dim row as MYSQL_ROW '?
    dim res as MYSQL_RES ptr '?
    Dim Query as String
    Dim i as integer
    db = mysql_init( NULL )
    if (mysql_real_connect( db, MySQLHost, MySQLUser, MySQLPW, NULL, MYSQL_PORT, NULL, 0 ) = 0) then Return -1
    if (mysql_select_db( db, MySQLDB )) then Return -2
    query = "SELECT * FROM testtab ORDER BY id ASC"
    If mysql_real_query (db,STRPTR(query),LEN(query)) <> 0 THEN
    mysql_close(db)
    Return -3
    END IF
    res = mysql_use_result(db)

    CLS
    liongfx_print: liongfx_print " useful data:": liongfx_print
    liongfx_print " ID | Username | Age | Input-Time"
    liongfx_print " --------------------------------------------------"

    Do
    row = mysql_fetch_row( res )
    if( row = NULL ) then
    Exit Do
    ELSE
    liongfx_print format$ " #### | \ \ | ### | &"+ Val(*row[0]), *row[1], Val(*row[2]), *row[3]
    END IF
    LOOP
    mysql_close(db)

    liongfx_SLEEP
    RETURN 0
    END FUNCTION
    [/code]

    I adept it in some way from an old freebasic example for thinbasic-a-like-using and perhaps you can choose your things you needed for your work... but there is missing a lot of declarations about mysql stuff and much more...

    I have translate it into 'lion english', but you are able to understand the content !!!
    I am thinking this may help...

    best regards, Lionheart
    you can't always get what you want, but if you try sometimes you might find, you get what you need

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

    Re: ODBC getting specific row

    Hi All, thanks for thinking with me!

    I'm still struggeling with creating an IDENTIFY column. I find examples on internet but it doesn't work in Thinbasic. But also I wondering if I really need auto incrementing as John adviced.

    Example:
    If I have these records:
    record 1 = mike
    record 2 = eros
    record 3 = frank
    record 4 = roberto
    record 5 = petr
    and make this query: SELECT * FROM mytable WHERE name LIKE 'frank' the auto increment field should return ROW 3 (from the whole table) and not row 1 (because row 1 is the only result). And for example when I sort the database decending, the auto increment field should return a different record (ROBERTO should be at ROW 1 then).

    In the meantime I think this could be the solution:
    [code=thinbasic]dim r , RowNum as long
    r=SQLGetStmtAttr(hStmt,%SQL_ATTR_ROW_NUMBER,RowNum,%SQL_IS_INTEGER,0)
    printl "Current row is: " & RowNum[/code]
    But the problem is that I get errors about BYVAL and BYREF if I try to use this code in my script.
    Pffff....ODBC is more difficult then I thought. But I will never give up

    Have a nice day!

    Martin


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

    Re: ODBC getting specific row

    Hi Martin,

    I checked the declare of SQLGetStmtAttr:
    DECLARE FUNCTION SQLGetStmtAttr LIB "ODBC32.DLL" ALIAS "SQLGetStmtAttr" ( _
    BYVAL StatementHandle AS DWORD, _
    BYVAL Attribute AS LONG, _
    BYREF Value AS ANY, _
    BYVAL BufferLength AS LONG, _
    BYREF StringLength AS LONG _
    ) AS INTEGER
    So the BYREF is needed for the last one too. BYREF means it wants variable passed by reference, not value. So you can pass dummy variable:
    [code=thinbasic]
    dim r , RowNum as long
    dim length as long = 0

    r=SQLGetStmtAttr(hStmt,%SQL_ATTR_ROW_NUMBER,RowNum,%SQL_IS_INTEGER,length)
    printl "Current row is: " & RowNum

    [/code]

    It should stop complaining at least, cannot say if it works as I do not understand dark magic around ODBC/SQL that much.


    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

Page 1 of 3 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
  •