Trying to access Firebird database

[mjolnirmarkiv]

Hi,

I'm trying to connect to Firebird database via AutoIt. Something that was supposed to be a cakewalk so far ended with no succes, as usual hoping for your help.

I'm using the following Firebird Direct Access for Visual Basic DLL: http://sourceforge.net/projects/fbdll4vb/files/. And the code below comes from Stephen Podhajecki (Eltorro) Firebird UDF (only parts I need at the moment):

Global $fb_dll = DllOpen ("fbdll4vb20.dll")

Firebird_ConnectDatabase ("C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb", "ODCB", "123")
If @error Then MsgBox (48, "Error", "Failed to login!" & @CRLF & "Error code: " & @error)
Firebird_DisconnectDatabase ()

Func Firebird_ConnectDatabase ($p_database, $p_username, $p_password)
  Local $connect_str = "char[%d];char[%d];char[%d]"

  $connect_str = StringFormat ($connect_str, StringLen ($p_database) + 1, StringLen ($p_username) + 1, StringLen ($p_password) + 1)

  Local $fb_struct = DllStructCreate ($connect_str)
  DllStructSetData ($fb_struct, 1, $p_database)
  DllStructSetData ($fb_struct, 2, $p_username)
  DllStructSetData ($fb_struct, 3, $p_password)

  Local $return = DllCall ($fb_dll, "int", "ConnectDatabase", _
                           "ptr", DllStructGetPtr ($fb_struct, 1), _
                           "ptr", DllStructGetPtr ($fb_struct, 2), _
                           "ptr", DllStructGetPtr ($fb_struct, 3))

  $fb_struct = 0

  Return SetError ($return[0] <> 0, 0, $return[0])
EndFunc

Func Firebird_DisconnectDatabase ()
  Local $return = DllCall ($fb_dll, "int", "DisconnectDatabase")
  Return SetError ($return[0] <> 0, 0, $return[0])
EndFunc

The code above gives me this:

The specific kind of server\database I need to connect to is belong to following application if that matters: http://www.projetex.com/, it is using Firebird 2.5. While ODBC\123 comes from following settings in the Server Administrator utility:

I've installed a VM with above mentioned application, server and databse running on it so I have admin rights to access it for testing. Nevertheless when I tried to use a gsec (a command prompt utility that comes with the app) to display a list of users it only gave me this:

gsec -user Admin -password admin -database "C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb" -display
use gsec -? to get help
Your user name and password are not defined. Ask your database administrator to setp up a Firebird login.
unable to open database.

So I changed it to ODBC, and there was some progress:

gsec -user ODBC -password 123 -database "C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb" -display
invalid request BLR at offset 37
table USERS is not defined

I tried to add a user mjolnir\123 and this time there was no progress:

gsec -user ODBC -password 123 -database "C:\Program Files\AIT\Projetex 9\Projetex Server\Database\Projetex.fdb" -add mjolnir -pw 123
An error occured while attempting to add the user.
invalid request BLR at offset 51
table USERS is not defined

It seems I'm not quite understand how the damn thing works. The Server Administrator utility have plenty of users including the admin, all of the have access to read the database and could do this via Projetex client, neither cann't do this nor via gsec, not programmaticlly via AutoIt code above. And then it says smth about "table USERS" which is not defined apparently, looks like database users is not the same thing as server users and I'm doing it wrong.

Edited September 13, 2015 by mjolnirmarkiv

[mjolnirmarkiv]

^

[Skysnake]

Are your %ProgramFiles% permissions restricted?

I have a system that needs to write to that folder and on some machines it can not.  You need Admin level access.

Try placing the .fdb in another folder? Or amend the Windows Security settings for that folder?  

Other RDBMS that store their database files in similar locations run into similar problems - also, while you are at it, check if McAfee is installed/blocking your access? Its notorious for blocking access to the %Temp% folder, which many db tools use to access files.

[mjolnirmarkiv]

Thank you, Skysnake.

 

I have admin rights, so I can read\write from that location, no problem. No McAfee installed. As for the database relocation, it seems I cannot do it, at least legitimately via admin utility.

[Skysnake]

I played with my Firebird db over the weekend.  Do you have FlameRobin (or similar?) installed?  Have you defined users?  

I would suggest first just running a few SQL query tests in FlameRobin to see if it actually works before trying to access it with AutoIt.