Windows Database Handle

Xombie · Post by **Xombie** » Thu Oct 04, 2007 10:41 pm

I've been using this trick (from ABBKlaus) to get the handle to a database...

HandleDB = PeekL(IsDatabase(iDatabase) + 4)

...but now my SQLGetDiagRec() function is returning a -2 which (I think) means #SQL_INVALID_HANDLE.

Did anything change with the latest beta to invalidate this method?

Post by **Fred** » Fri Oct 05, 2007 9:17 am

You are messing with internals...

Yes we changed that, try to peek between 0 and 16 (+0, +4, +8, +16) it should be somewhere here.

ABBKlaus · Post by **ABBKlaus** » Fri Oct 05, 2007 12:07 pm

try this :

Code: Select all

  If OpenDatabase(1,"DB-Test","Admin","")
    ID=IsDatabase(1)

    Structure PB_StructureDataBase
	    Connection.l   ; OS ODBC Connection identifier  (SQLHDBC)
	    Statement.l    ; OS ODBC Statement identifier  (SQLHSTMT)
    EndStructure

    *Buffer.PB_StructureDataBase=ID
    
    Handle=PeekL(*Buffer\Statement)
    
    res.w=SQLAllocHandle_(3,Handle,@stmthandle)
    If res = #SQL_SUCCESS
      Debug "stmthandle="+Str(stmthandle)
    EndIf
    
    res.w=SQLTables_(stmthandle,0,0,0,0,0,0,0,0)
    If res=#SQL_INVALID_HANDLE
      Debug "#SQL_INVALID_HANDLE"
    EndIf
    
    If res = #SQL_SUCCESS Or res = #SQL_SUCCESS_WITH_INFO 
      Debug "#SQL_SUCCESS" 
      strlen=128+1 
      *Buffer1=AllocateMemory(strlen):strlen1.l=0 
      *Buffer2=AllocateMemory(strlen):strlen2.l=0 
      *Buffer3=AllocateMemory(strlen):strlen3.l=0 
      *Buffer4=AllocateMemory(strlen):strlen4.l=0 
      *Buffer5=AllocateMemory(strlen):strlen5.l=0 
      
      #SQL_C_CHAR=1
      SQLBindCol_(stmthandle,1,#SQL_C_CHAR,*Buffer1,strlen,@strlen1) ; Database
      ;SQLBindCol_(stmthandle,2,#SQL_C_CHAR,*Buffer2,strlen,@strlen2) ; ?
      SQLBindCol_(stmthandle,3,#SQL_C_CHAR,*Buffer3,strlen,@strlen3) ; Tablename
      SQLBindCol_(stmthandle,4,#SQL_C_CHAR,*Buffer4,strlen,@strlen4) ; Table-Type
      
      While SQLFetch_(stmthandle)&$FFFF=0 
        Debug PeekS(*Buffer1,-1,#PB_Ascii)
        Debug PeekS(*Buffer3,-1,#PB_Ascii)
        Debug PeekS(*Buffer4,-1,#PB_Ascii)
        Debug "-----------------------------------------------------------------"
      Wend 
    EndIf    
    
    If SQLFreeHandle_(3,stmthandle)=#SQL_SUCCESS
      stmthandle=0
    EndIf
  EndIf

Regards Klaus

Xombie · Post by **Xombie** » Fri Oct 05, 2007 5:40 pm

Now, fred. You know you're supposed to get my permission before changing PB internal stuff

So this is not a bug (no surprise). Feel free to move this to general or coding questions where everyone can shake their head at my ineptitude

@ABBKlaus - I tried running your code but I get a "Cannot Execute ......" error statement. The code does not compile or execute.

I also tried adapting the bits to fit my need. I went all the way to...

Code: Select all

res.w=SQLAllocHandle_(3,Handle,@stmthandle)

... to get the handle and passed that to my SQLGetDiagRec() function and now I'm just getting a result of 100 which should be #SQL_NO_DATA.

The function I'm using is a badly homegrown...

Code: Select all

Procedure.s GetSQLMessages(HandleDB.l) 
   ;
   Protected lResult.l
   ;
   Protected SQLState.s = Space(6)
   ;
   Protected NativeErrorPtr.l
   ;
   Protected MessageText.s = Space(10000)
   ;
   Protected BufferLength.l = 10000
   ;
   Protected TextLengthPtr.l
   ;
   Protected DiagInfoPtr.l = -1
   ;
   Repeat
      ;
      lResult = SQLGetDiagRec(#SQL_HANDLE_STMT, HandleDB, 1, @SQLState, @NativeErrorPtr, @MessageText, BufferLength, @TextLengthPtr)
      ;
      Debug lResult
      ;
      If lResult = #SQL_NO_DATA : Break : EndIf
      ;
      Debug Str(TextLengthPtr)+" : "+SQLState+" : "+Left(MessageText, 20)+" : "+Str(lResult)
      ;
      SQLMoreResults(HandleDB)
      ;
   ForEver
   ;
   ProcedureReturn ""
   ;
EndProcedure

And did work in the past. I tested using this bit from Wikipedia.

Code: Select all

DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
   PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
   SET @Counter = @Counter - 1
END

And in the past it worked. However, ever since fred cruelly changed how PB worked (without asking!) I get nothing returned.

In this most recent test I'm passing stmthandle to the GetSQLMessages function.

Help?

ABBKlaus · Post by **ABBKlaus** » Sat Oct 06, 2007 1:25 am

@xombie, the structure i used was from PB4.02 and is not valid anymore for PB4.10B3.

could you try this :

Code: Select all

    ID=IsDatabase(1)
    Debug "ID="+Str(ID)
    
    Debug "SQL_HANDLE_ENV="+Hex(PeekL(PeekL(ID)-4))
    Debug "SQL_HANDLE_STMT="+Hex(PeekL(PeekL(ID+4)+4))
    Debug "SQL_HANDLE_DBC="+Hex(PeekL(PeekL(ID+4)))

i uploaded a test program to demonstrate : DSN-Test

Regards Klaus

Xombie · Post by **Xombie** » Tue Oct 09, 2007 5:16 pm

That worked like a charm, thanks! And as a side bonus, I got the three different handles

I was wondering how I might get those but didn't need them yet.

Thanks again!

ABBKlaus · Post by **ABBKlaus** » Tue Oct 09, 2007 5:33 pm

but be warned, they change from beta to beta

(just tested Beta4 and it worked)

Regards Klaus

fsw · Post by **fsw** » Mon Dec 03, 2007 8:37 pm

Fred wrote:You are messing with internals... Yes we changed that, try to peek between 0 and 16 (+0, +4, +8, +16) it should be somewhere here.

Just got burned because Fantaisie software changed the internals.

Why are you guys making it hard to get the handle?

Yes, PureBasic is soo powerful, you can even use Windows API with ease.
You want to use Windows DataBase API? No problem

Oh, it doesn't work anymore?
We are sorry, but we changed some internal stuff.
It's our compiler and our libraries, so we are allowed to do that.
Sorry, we don't care enough to create a GetDataBaseHandle command

Rook Zimbabwe · Post by **Rook Zimbabwe** » Mon Dec 03, 2007 11:17 pm

You could always request that command...

byo · Post by **byo** » Mon Dec 03, 2007 11:19 pm

fsw: Please, refrain from posting if the only purpose of it is to whine. Thank you.

Hopefully one can learn a lot from posts like this. Even the makers of the software so why the silly sarcasm?

Dare · Post by **Dare** » Mon Dec 03, 2007 11:25 pm

Xombie wrote:Now, fred. You know you're supposed to get my permission before changing PB internal stuff

I just love it that so many people here have a great sense of humour!

netmaestro · Post by **netmaestro** » Mon Dec 03, 2007 11:26 pm

Sorry, we don't care enough to create a GetDataBaseHandle command

You never even posted a request for one... who was it again who doesn't care enough?

fsw · Post by **fsw** » Tue Dec 04, 2007 1:52 am

Hey guys,
Fred's response is from October 5th, so I thought he would think by itself of it, but he didn't.
He would have had enough time to implement this in 5 minutes before releasing 4.1

Would have been common sense, would it not?

Also if you would have read my post you would see I found out about it today.
But you guys are right, I could put in a request.

fsw

BTW: @byo
Maybe you don't know, but I'm with PB since spring 2001 and have contributed a lot to PB, specially when PB only had a handful of users.
You joined 01 Apr 2007

, so what was your request again?

byo · Post by **byo** » Tue Dec 04, 2007 2:21 am

BTW: @byo
Maybe you don't know, but I'm with PB since spring 2001 and have contributed a lot to PB, specially when PB only had a handful of users.
You joined 01 Apr 2007 , so what was your request again?

OK, let's end it here before this topic turns into childish better-than-you arguments. All I say is help the guy who posted the question in a Coding Questions' topic or just don't. And then make a new thread on the Offtopic section on how it's easy to program something in assembly in 5 minutes.

I'm sorry to have reacted like I did but the kind of sarcastic posts like the one you posted really get on my nerves. And I'm sure I don't speak only for myself.

Peace, man.

fsw · Post by **fsw** » Tue Dec 04, 2007 4:29 am

byo wrote:OK, let's end it here before this topic turns into childish better-than-you arguments.

You are right, and of top of that I overreacted myself, sorry.

Maybe next time when I discover something obvious to me (like useful customer workarounds don't work anymore, lets make a official function out of it... what about UBound?) and also a very important function, I will sit on my fingers and let some hours pass before I write something.

Peace