Database bug

[Num3]

When using ODBC and calling GetDatabaseString() for the same column more than once only the 1st time it is called is displayed.

Generic sample:

Code: Select all

UseODBCDatabase()
If OpenDatabase(#db,"mydatabse","username","password",#PB_Database_ODBC )
  If DatabaseQuery(#db,"select name, surname from database_table limit 1;")
    
    If NextDatabaseRow(#db)

      GetDatabaseString(#db,1) ; Returns value surname
      GetDatabaseString(#db,1) ; Returns nothing
      
      GetDatabaseString(#db,0) ; Returns value name
      GetDatabaseString(#db,0) ; Returns nothing
      
      GetDatabaseString(#db,1) ; Returns surname
      GetDatabaseString(#db,1) ; Returns nothing
      GetDatabaseString(#db,0) ; Returns value name
      
    EndIf
    
  EndIf
EndIf

[USCode]

http://www.purebasic.com/documentation/ ... tring.html

"Note: This function can be called only once for each column. Therefore if this value needs to be used more than once, the data has to be stored in a variable, since all subsequent calls will return the wrong value. This is an ODBC limitation."

[Num3]

RTFM...

Strange enough it works if the calls are not successive... LOL

Code: Select all

GetDatabaseString(#db,0)
GetDatabaseString(#db,1)
GetDatabaseString(#db,0)

So I want to make a feature request now
Please buffer the last return data in ODBC ...

[bamsagla]

Hey Num3,

I understand your feature request and just wanted to add a simple code example to submit an (as I think) easy and cross-platform solution to handle multiple actions for your database query. IMHO it is also more effective to "buffer" the values and free the database access for other users (what is only relevant if you plan multi-user access, but I have those in most of my apps).

Bye, Harry.

Code: Select all

EnableExplicit

Define.i Getback

Structure Test : row1.i : row2.s : row3.s : row4.i : row5.s : EndStructure
NewList Values.Test()

UsePostgreSQLDatabase()

If OpenDatabase(0, "host=localhost port=5432", "postgres", "postgres")
	
	Debug "Connected to PostgreSQL"
	
	Getback = DatabaseQuery(0, "SELECT id, name, adress, zipcode, town FROM customers ORDER BY zipcode ASC, name ASC;")
	If Not Getback = 0
		While NextDatabaseRow(0)
			AddElement(Values())
			Values()\row1 = GetDatabaseLong(0, 0)
			Values()\row2 = GetDatabaseString(1, 0)
			Values()\row3 = GetDatabaseString(2, 0)
			Values()\row4 = GetDatabaseLong(3, 0)
			Values()\row5 = GetDatabaseString(4, 0)
		Wend
		FinishDatabaseQuery(0)
		
		; Here we could do many operations with the "buffered" values of the database table.
		; Now just parse the contents of Values() and you can use them as often as you want.
		; Of course we could do a "SELECT COUNT(*) FROM customers;" and generate a structured array instead of a linked list...
		
	EndIf
Else
	Debug "Connection error: " + DatabaseError()
EndIf

FreeList(Values())

End

[spikey]

IMHO it is also more effective to "buffer" the values and free the database access for other users

Definitely in the short term, ODBC is an extra layer of software which takes time to process the data, if the database is hosted remotely on a network then this adds another time delay too - probably not noticeable on a single user/locally hosted database but if you scale it up to a many user/network hosted database you are definitely going to end up taking a performance hit sooner or later...

The only thing you need to look out for would be "multi user interference" issues - where two or more users might alter data in their "cached" copies in parallel and the changes of one user overwrite the changes of the other when committed to the database, leading to lost changes, support calls, bug reports, jumping up and down, throwing toys out of the pram...

[jassing]

there is no (technical) reason why GetDatabase..() couldn't cache the result internally. Just because its a windows odbc issue, doesn't mean it needs to be propagated to a purebasic problem.
that said; I wrapped all of them in my toolbox library to do just that.