ODBC getting specific row

Hi everybody,

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

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

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

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.

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.

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

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

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

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 !!! :D
I am thinking this may help... 8)

best regards, Lionheart

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. :oops: But I will never give up :lol:

Have a nice day!

Martin

Hi Martin,

I checked the declare of SQLGetStmtAttr:

Quote:

---

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