Interbase & Firebird : Wrapper Problem

[the.weavster]

Code: Select all

#FB_Client = 1
Global sLibrary.s
Global EndOfLine.s
CompilerSelect #PB_Compiler_OS
  CompilerCase #PB_OS_Windows
    sLibrary.s = "fbclient.dll"
    EndOfLine = Chr(13) + Chr(10)
  CompilerCase #PB_OS_Linux
    sLibrary.s = "libfbclient.so"
    EndOfLine = Chr(10) ;need to check this out
  CompilerCase #PB_OS_MacOS
    sLibrary.s = "Firebird.framework"
    EndOfLine = Chr(10) ;need to check this out
CompilerEndSelect
lresult = OpenLibrary(#FB_Client,sLibrary)
If Not lresult
  MessageRequester("Error","Library not found",0)
  End
EndIf

Prototype.i isc_attach_database(*StatusVector,dbNameLength.w,*dbName,*dbHandle,DPBSize.w,*DPB)
Global fb_attach.isc_attach_database = GetFunction(#FB_Client,"isc_attach_database")

Prototype.i isc_detach_database(*StatusVector,*dbHandle)
Global fb_detach.isc_detach_database = GetFunction(#FB_Client,"isc_detach_database")

PrototypeC.i isc_start_transaction(*StatusVector,*TransactionHandle,Count.w,*dbHandle,TPBLength.w,*TPB)
Global fb_start.isc_start_transaction = GetFunction(#FB_Client,"isc_start_transaction")

Prototype.i isc_rollback_transaction(*StatusVector,*TransactionHandle)
Global fb_rollback.isc_rollback_transaction = GetFunction(#FB_Client,"isc_rollback_transaction")

Prototype.i isc_commit_transaction(*StatusVector,*TransactionHandle)
Global fb_commit.isc_commit_transaction = GetFunction(#FB_Client,"isc_commit_transaction")

Prototype.i isc_dsql_alloc_statement2(*StatusVector,*dbHandle,*QueryHandle)
Global fb_allocate.isc_dsql_alloc_statement2 = GetFunction(#FB_Client,"isc_dsql_alloc_statement2")

Prototype.i isc_dsql_prepare(*StatusVector,*TransactionHandle,*QueryHandle,SQLlength.w,*SQLstring,Dialect.w,*XSQLDA)
Global fb_prepare.isc_dsql_prepare = GetFunction(#FB_Client,"isc_dsql_prepare")

Prototype.i isc_dsql_describe(*StatusVector,*QueryHandle,da_version.w,*XSQLDA)
Global fb_describe.isc_dsql_describe = GetFunction(#FB_Client,"isc_dsql_describe")

Prototype.i fb_interpret(*Buffer,SizeOfBuffer.i,*PtrToStatusVector)
Global fb_error.fb_interpret = GetFunction(#FB_Client,"fb_interpret")

Prototype.i isc_sqlcode(*StatusVector)
Global fb_sqlcode.isc_sqlcode = GetFunction(#FB_Client,"isc_sqlcode")

Prototype.i isc_sql_interpret(SQLCode.w,*Buffer,SizeOfBuffer.w)
Global fb_sql_error.isc_sql_interpret = GetFunction(#FB_Client,"isc_sql_interprete")

Prototype.i isc_dsql_fetch(*StatusVector,*QueryHandle,da_version.w,*XSQLDA)
Global fb_fetch.isc_dsql_fetch = GetFunction(#FB_Client,"isc_dsql_fetch")

Prototype.i isc_dsql_free_statement(*StatusVector,*QueryHandle,free_option.w)
Global fb_free.isc_dsql_free_statement = GetFunction(#FB_Client,"isc_dsql_free_statement")

Prototype.i isc_dsql_execute_immediate(*StatusVector,*dbHandle,*TransactionHandle,SQL_length.w,*SQLstring,Dialect.w,*XSQLDA)
Global fb_execute_immediate.isc_dsql_execute_immediate = GetFunction(#FB_Client,"isc_dsql_execute_immediate")

Prototype.i isc_dsql_execute(*StatusVector,*TransactionHandle,*QueryHandle,da_version.w,*XSQLDA)
Global fb_execute.isc_dsql_execute = GetFunction(#FB_Client,"isc_dsql_execute")

Global fb_LastErrorMessage.s

;data type constants
#SQL_ARRAY = 540
#SQL_BLOB = 520
#SQL_DATE = 510
#SQL_DOUBLE = 480
#SQL_D_FLOAT = 530
#SQL_FLOAT = 482
#SQL_INT64 = 580
#SQL_LONG = 496
#SQL_QUAD = 550
#SQL_SHORT = 500
#SQL_TEXT = 452
#SQL_TIMESTAMP = 510
#SQL_TYPE_DATE = 570
#SQL_TYPE_TIME = 560
#SQL_VARYING = 448

;some other required constants
;database parameter buffer constants
#isc_dpb_version1 = 1
#isc_dpb_user_name = 28
#isc_dpb_password = 29
#isc_dpb_sql_dialect = 63
;transaction parameter buffer constants
#isc_tpb_concurrency = 2
#isc_tpb_version3 = 3
#isc_tpb_wait = 6
#isc_tpb_write = 9

#DSQL_CLOSE = 1
#DSQL_DROP = 2
#SQLDA_VERSION1 = 1
#SQL_DIALECT_V5 = 1
#SQL_DIALECT_V6 = 3

Structure ISC_STATUS ;used for retrieveing error messages
  vector.i[20]
EndStructure

Procedure fb_InterpretDatabaseError(*sv)
  fb_LastErrorMessage = ""
  lSQLCode.w = fb_sqlcode(*sv)
  If lSQLCode <> 0
    fb_LastErrorMessage = "SQL Code: " + Str(lSQLCode)
    *Mem = AllocateMemory(1000)
    fb_sql_error(lSQLCode,*Mem,1000)

    fb_LastErrorMessage = fb_LastErrorMessage + EndOfLine + PeekS(*Mem,1000,#PB_UTF8)
    FreeMemory(*Mem)
  EndIf
  If fb_LastErrorMessage <> "":fb_LastErrorMessage = fb_LastErrorMessage + EndOfLine + EndOfLine:EndIf
  lresult = 1
  While lresult <> 0
    *Mem = AllocateMemory(1000)
    lresult = fb_error(*Mem,1000,@*sv)
    fb_LastErrorMessage = fb_LastErrorMessage + PeekS(*Mem,1000,#PB_UTF8)
    FreeMemory(*Mem)
  Wend
EndProcedure

Procedure.i fb_OpenDatabase(DatabaseAlias.s,Username.s,Password.s)
  lAlias.w = StringByteLength(DatabaseAlias,#PB_UTF8)
  *dbAlias = AllocateMemory(lAlias)
  PokeS(*dbAlias,DatabaseAlias,lAlias,#PB_UTF8)
  sParam.s = Chr(#isc_dpb_version1)
  lUN.w = StringByteLength(Username,#PB_UTF8)
  If lUN > 0:sParam = sParam + Chr(#isc_dpb_user_name) + Chr(lUN) + Username:EndIf
  lPWD.w = StringByteLength(Password,#PB_UTF8)
  If lPWD > 0:sParam = sParam + Chr(#isc_dpb_password) + Chr(lPWD) + Password:EndIf
  lUN = StringByteLength(sParam,#PB_UTF8)
  *dbParam = AllocateMemory(lUN)
  PokeS(*dbParam,sParam,lUN,#PB_UTF8)
  lHandle = #Null
  lresult = fb_attach(@sv.ISC_STATUS,lAlias,*dbAlias,@lHandle,lUN,*dbParam)
  FreeMemory(*dbAlias)
  FreeMemory(*dbParam)
  If lresult = 0
    ProcedureReturn lHandle
  Else
    fb_InterpretDatabaseError(@sv)
    ProcedureReturn -1
  EndIf
EndProcedure

Procedure.i fb_CloseDatabase(dbHandle)
  lresult = fb_detach(@sv.ISC_STATUS,@dbHandle)
  If lresult = 0
    ProcedureReturn lresult
  Else
    fb_InterpretDatabaseError(@sv)
    ProcedureReturn -1
  EndIf
EndProcedure

Procedure.i fb_StartTransaction(dbHandle)
  *TPB = AllocateMemory(4)
  PokeB(*TPB,#isc_tpb_version3)
  PokeB(*TPB + 1,#isc_tpb_write)
  PokeB(*TPB + 2,#isc_tpb_concurrency)
  PokeB(*TPB + 3,#isc_tpb_wait)
  TransactionID = #Null
  lResult = fb_start(@sv.ISC_STATUS,@TransactionID,1,@dbHandle,4,*TPB)
  FreeMemory(*TPB)
  If lResult = 0
    ProcedureReturn TransactionID
  Else
    fb_InterpretDatabaseError(@sv)
    ProcedureReturn -1   
  EndIf
EndProcedure

Procedure.i fb_CommitTransaction(TransactionID)
  lresult = fb_commit(@sv.ISC_STATUS,@TransactionID)
  If lresult = 0
    ProcedureReturn lresult
  Else
    fb_InterpretDatabaseError(@sv)
    ProcedureReturn -1
  EndIf
EndProcedure

Procedure.i fb_RollbackTransaction(TransactionID)
  lresult = fb_rollback(@sv.ISC_STATUS,@TransactionID)
  If lresult = 0
    ProcedureReturn lresult
  Else
    fb_InterpretDatabaseError(@sv)
    ProcedureReturn -1
  EndIf
EndProcedure

Procedure.i fb_SQLExecute(dbHandle,TransactionID,SQLString.s)
  lSQL = StringByteLength(SQLString,#PB_UTF8)
  *SQL = AllocateMemory(lSQL)
  PokeS(*SQL,SQLString,lSQL,#PB_UTF8)
  lResult = fb_execute_immediate(@sv.ISC_STATUS,@dbHandle,@TransactionID,lSQL,*SQL,#SQL_DIALECT_V6,#Null)
  FreeMemory(*SQL)
  If lResult = 0
    ProcedureReturn #True
  Else
    fb_InterpretDatabaseError(@sv)
    ProcedureReturn #False
  EndIf
EndProcedure

;start the test
;Employees is an alias I've set up for the Employees example database that installs with Firebird

db = fb_OpenDatabase("Employees","SYSDBA","masterkey")
If db = -1
  MessageRequester("Error",fb_LastErrormessage,0)
  End
Else
  MessageRequester("Cool #1!","Connected - handle: " + Str(db),0)
  lTransID = fb_StartTransaction(db)
  If lTransID = -1
    MessageRequester("Error",fb_LastErrormessage,0)
  Else
    MessageRequester("Cool #2!","Started a transaction - handle: " + Str(lTransID),0)
    If fb_SQLExecute(db,lTransID,"UPDATE COUNTRY SET CURRENCY = 'Euros' WHERE COUNTRY = 'France';")
      fb_CommitTransaction(lTransID)
      MessageRequester("Cool #3!","Record updated",0)
    Else
      fb_RollbackTransaction(lTransID)
      MessageRequester("Error",fb_LastErrormessage,0)
    EndIf
  EndIf
  fb_CloseDatabase(dbHandle)
EndIf

CloseLibrary(#FB_Client)

I'll add more flesh to the bones when I get more time.

She-who-must-be-obeyed has just summoned me to do DIY

[thanos]

@the.weavster
Thank you very much for the code.
I will try it later.
What means DIY?
Regards

Thanos

[the.weavster]

What means DIY?
Regards

Thanos

DIY is an abbreviation for 'do it yourself', it's usually a term applied to doing repairs around the house.

I'm on coffee break now so I've quickly added a SQL update to the code:

Code: Select all

...

[thanos]

DIY is an abbreviation for 'do it yourself', it's usually a term applied to doing repairs around the house.

I understand

I have run the code. When i tried to open the database

Code: Select all

lresult.l = fb_attach(@sv.ISC_STATUS,lAlias,@DatabaseAlias,@lHandle,lUN,@sParam)

the lresult.l was different than 0 and called the procedure

Code: Select all

  fb_InterpretDatabaseError(@sv)

To the above procedure, the line

Code: Select all

lresult.l = fb_error(lMem,512,@*sv)

returned an fatal error Invalid memory access. (read error at address 0) and the program crashed.
I am using the PureBasic 4.30 beta 4 and the FieBird 2.1 client
Do you have an idea?
Regards.

Thanos

[thanos]

DIY is an abbreviation for 'do it yourself', it's usually a term applied to doing repairs around the house.

I understand...

I have run the code. When i tried to open the database

Code: Select all

lresult.l = fb_attach(@sv.ISC_STATUS,lAlias,@DatabaseAlias,@lHandle,lUN,@sParam)

the lresult.l was different than 0 and called the procedure

Code: Select all

  fb_InterpretDatabaseError(@sv)

To the above procedure, the line

Code: Select all

lresult.l = fb_error(lMem,512,@*sv)

returned an fatal error Invalid memory access. (read error at address 0) and the program crashed.
I am using the PureBasic 4.30 beta 4 and the FieBird 2.1 client
Do you have an idea?
Regards.

Thanos

[thanos]

DIY is an abbreviation for 'do it yourself', it's usually a term applied to doing repairs around the house.

I understand...

I have run the code. When i tried to open the database

Code: Select all

lresult.l = fb_attach(@sv.ISC_STATUS,lAlias,@DatabaseAlias,@lHandle,lUN,@sParam)

the

Code: Select all

lresult.l

was different than 0 and called the procedure

Code: Select all

  fb_InterpretDatabaseError(@sv)

To the above procedure, the line

Code: Select all

lresult.l = fb_error(lMem,512,@*sv)

returned an fatal error Invalid memory access. (read error at address 0) and the program crashed.
I am using the PureBasic 4.30 beta 4 and the FieBird 2.1 client
Do you have an idea?
Regards.

Thanos

[the.weavster]

I'm using Firebird 2.1 and PB 4.20 and it's working fine. Even if I deliberately put in the wrong log-in data I get an error message telling me so.

Have you got fbclient.dll in the same directory as you've saved the code to? Maybe I'm not correctly checking if the library was loaded correctly, try this slightly changed version:

Code: Select all

...

[the.weavster]

@thanos

My new laptop is running Vista so I dug out my old computer which runs XP SP3 Pro to see if that was OK.

If I rename fbclient.dll I correctly get the error message saying the library wasn't found (with both versions of the code).

If I enter incorrect log-in info I get an error message telling me to contact the Firebird administrator.

Everything works exactly as expected.

I'm not sure what's going wrong for you, maybe it's an issue with the PB beta you're using?

***Edit***
I've put the updated code in my first posting and removed it from all the others as there was getting a lot of repetition, I've also added some encoding stuff in case that was the issue.

I also changed .l to .i as I've just read the PB team Blog.

Have another try and let me know what happens.

[thanos]

@thanos

My new laptop is running Vista so I dug out my old computer which runs XP SP3 Pro to see if that was OK.

If I rename fbclient.dll I correctly get the error message saying the library wasn't found (with both versions of the code).

If I enter incorrect log-in info I get an error message telling me to contact the Firebird administrator.

Everything works exactly as expected.

I'm not sure what's going wrong for you, maybe it's an issue with the PB beta you're using?

***Edit***
I've put the updated code in my first posting and removed it from all the others as there was getting a lot of repetition, I've also added some encoding stuff in case that was the issue.

I also changed .l to .i as I've just read the PB team Blog.

Have another try and let me know what happens.

Thank you very much for the response and your effort!
1].The library is in the same directory with the app and it is correctly loaded.
2]. I have the DB_FIREBIRD.FDB into the same folder with the app.
3]. When i tried to open the database file via

Code: Select all

db.l = fb_OpenDatabase("DB_FIREBIRD.FDB", "sysdba", "masterkey")

the attach is not successful

Code: Select all

  lHandle.l = #Null
  lresult.l = fb_attach(@sv.ISC_STATUS,lAlias,*dbAlias,@lHandle,lUN,*dbParam)

the lHandle.l becomes 0 and the lresult.l becomes 335544375 and the procedure calls the

Code: Select all

    fb_InterpretDatabaseError(@sv)

procedure.
The fatal error happened at the line

Code: Select all

   lresult.l = fb_error(*Mem,1000,@*sv)

Please mention that the database file is ok, i can open and edit it with Flame Robin or with my program in VB.
I will download and install the PureBasic 4.20 to check the code.
I will inform you later.
Best regards.

Thanos

[the.weavster]

Hi thanos,
That's not the latest version of the code I posted as I changed all the integer variables from .l to .i

Could you set up an alias for your database and then try again?
Is your server on localhost?
I'm thinking maybe the client library is whistling in the wind and no communication with the server is actually taking place.

[thanos]

Hi thanos,
That's not the latest version of the code I posted as I changed all the integer variables from .l to .i

Could you set up an alias for your database and then try again?
Is your server on localhost?
I'm thinking maybe the client library is whistling in the wind and no communication with the server is actually taking place.

I changed all the .l with .i and than i run the code. The results was the same.
Yes, my server is on localhost.
How can i set up an alias for the database?
Regards

Thanos

[the.weavster]

You set an alias just by adding a line in aliases.conf, something like:

Code: Select all

Employees=c:\Program Files\Firebird\Firebird_2_1\examples\empbuild\employee.fdb

If your server is on localhost this might not be the answer either though, because if I pass the name of a database that doesn't exist to this function I correctly get an error message from the server stating just that.

I think I'll just leave this alone until the full release of PB 4.3 and then see if it does or doesn't work.

[the.weavster]

I just went into control panel and shut down the Firebird server and then ran the code again. Even then I receive an appropriate error message saying communication could not be established.

I don't really want to install the PB beta, so I think I will just wait for 4.3 and see what unfolds.

[thanos]

I just went into control panel and shut down the Firebird server and then ran the code again. Even then I receive an appropriate error message saying communication could not be established.

I don't really want to install the PB beta, so I think I will just wait for 4.3 and see what unfolds.

I tried all, but unfortunately still not working
Tomorrow i will try it on other two computers and i will inform you.
I think that we must waiting for the official release of 4.3
Sorry for the torture and thank you again.
Regards.

Thanos

[the.weavster]

Are you on x64 thanos?

My curiosity got the better of me so I installed the PB 4.30 beta and the code still works correctly for me.