Jump to content
mjolnirmarkiv

Trying to access Firebird database

Recommended Posts

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:

2dwcaag.jpg

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:

xm8i13.jpg

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 by mjolnirmarkiv

Share this post


Link to post
Share on other sites
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.


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • hugomito
      By hugomito
      Hi all,
      I hope you can help me with this. I need to connect to a Database using Cloudera ODBC Driver for Impala. I installed the driver, created a User DSN (drona2) and tested it successfully (got the message: Successfully connected to data source!) but when I try to connect to the database using a Conneciton String, it simply didn't work.
      I tried connecting to the database using the User DSN, I previously created, that has all parameters needed but got the following error:
           $ProviderDatasource = 'DSN=drona2;'
           $conn_Database = ObjCreate ("ADODB.Connection")
           $conn_Database.ConnectionString = $ProviderDatasource
           $conn_Database.Open
           ==> The requested action with this object has failed.:
           $conn_Database.Open
           $conn_Database.Open^ ERROR
      Also I tried adding all parameters to my connection string as follows but also got the same error:
           $ProviderDatasource = 'Driver=Cloudera ODBC Driver for Impala;Host=MyHost.domain.com;PORT=21050;AuthMech=3;UID=MyUserId@domain.com;PWD=MyPasswordc;Schema=default;SSL=1;AllowSelfSignedCerts=1;AllowHostNameCNMismatch=1;CheckCertRevocation=1;KrbFQDN=_HOTS;KrbServiceName=impala;ServicePrincipalCanonicalization=1'
      I hope someone has used this driver successfully before and can shed some light here.
      If you need additional information, let me know.
      Regards,
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning guys
      I'd like to know if there is a way to convert a PDF in CSV or, eventually, in TXT, in order to read from it, like a database...
      I have a PDF and I think ( I dind't search a lot on the forum ) with AutoIt, but I'd like work with Excel styles...
      Does anyone know a good program which convert PDF to CSV? 
      PS: the PDF file is 5 MB, and it contains 439 pages...
      Thanks everyone for the help
       
    • Queener
      By Queener
      Using the MSSQL.udf
      I asked this question once, but haven't had a chance to reply to my old post so posting a new one instead of bouncing the old post.
      I'm trying to retrieve data from my database in Microsoft SQL Server 2014. When I tried the code below; empty message box return and no error approaches.
      $Connection = _MSSQL_Con($ServerAddress, $ServerUsername, $ServerPassword, $ServerDBName) $Testquery = _MSSQL_Query($Connection, "SELECT User_ID From Banker where Manager like '%Jason%'") msgbox(0, "Test", $Testquery) exit
    • GarnetDagger
      By GarnetDagger
      I would love to have some help or guidance 
      I am able to Query from my database but I am not able to Update or insert etc because I lack the knowledge for it
       
      this is my code for to retrieve some data from and works perfect
       
      Dim $ueberschriften = "" Dim $anzahl = 0 Dim $dsncount = 1 Global $DSN = "MTXXV5" Global $Query = "Select * from VIEWS" Func SSSQL($SSQuery) ;SuperSonicSQL     ConsoleWrite($SSQuery & @LF)     $cmboVal = ""     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open($DSN)     $adoRs = ObjCreate("ADODB.Recordset")     $adoSQL = $SSQuery     $adoRs.CursorType = 2     $adoRs.LockType = 3     $adoRs.Open($adoSQL, $adoCon)     With $adoRs         Global $QueryAnswer = .GetRows     EndWith     Return $QueryAnswer EndFunc   ;==>SSSQL and this is my failed attempt, The Query is correct but I want to able to use it in autoit instead of have to manual update or insert into flamerobin
       
      $SS_SQL_Insert_or_Update = "INSERT INTO DETECTION (DNAME,DTYPE,WINNAME,AREA,COLOR,MOUSE) VALUES ('PopUpTradeInviteYes        s                    ','SEARCH','XXX','175,240,550,240,65,228,82,234','5062478','0') " Func SSSQLIU($SS_SQL_Insert_or_Update) ;SuperSonicSQL :P     $cmboVal = ""     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open($DSN)     $adoRs = ObjCreate("ADODB.Record")     $adoSQL =$SS_SQL_Insert_or_Update ;~     $adoRs.CursorType = 2     $adoRs.LockType = 4     $adoRs.Open($adoSQL, $adoCon) ;~     With $adoRs ;~         Global $QueryAnswer = .GetRows ;~     EndWith ;~     Return $QueryAnswer EndFunc   ;==>SSSQL I am at a total loss and I am just trying this at random but I am also going to sleep now
      I would love to have some minor examples
      "C:\_CC_V10_DetectionTAB.au3" (174) : ==> The requested action with this object has failed.:
      $adoRs.Open($adoSQL, $adoCon)
      $adoRs^ ERROR
    • kcvinu
      By kcvinu
      Hi all,
      This is an UDF for working with Access(2007 above) databases. I would like to say thanks to @spudw2k for his wonderful ADODB udf. That is my inspiration. And i borrowed three functions from him. The difference of this UDF is that it is using SQL statements as parameters. So if you know the SQL, then you can easily work with this UDF.
      Functions in this UDF
      1. _Start_Connection
      2. _Close_Connection
      3. _Create_Table
      4. _Delete_Table
      5. _Alter_Table
      6. _Delete_FromTable
      7. _Insert_Data - You can use this to update or delete data
      8. _Get_Records
      This is an example. This example uses an access database named Test. It is packed with the zip file
      #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.0 Author: kcvinu Date : sep 2015 Script Function: Examples of Access_UDF Template AutoIt script. #ce ---------------------------------------------------------------------------- #include "Access_UDF.au3" #include <Array.au3> ; only for displaying data Example1() Func Example1() Local $MsgBox1 = MsgBox(4, "Access UDF Examples", " Starting Connection example, Ready ?") If $MsgBox1 = 6 Then ; We are starting a connection Local $Connection = _Start_Connection(@ScriptDir & "\Test.accdb;") ConsoleWrite($Connection & " Started" & @CRLF) ; Look for the function status Else Exit EndIf Local $MsgBox2 = MsgBox(4, "Access UDF Examples", " Starting Create Table example, Ready ?") If $MsgBox2 = 6 Then ; We need to create a new table Local $CreateTable = _Create_Table("CREATE TABLE TestTable(Column1 Text, Column2 Text);") ConsoleWrite($CreateTable & " Table Created" & @CRLF) Else Exit EndIf Local $MsgBox3 = MsgBox(4, "Access UDF Examples", " Starting Alter Table example, Ready ?") If $MsgBox3 = 6 Then ; We need to add a column to that table Local $AlterTable = _Alter_Table("ALTER TABLE TestTable ADD Column3 int;") ConsoleWrite($AlterTable & " Table Alterd" & @CRLF) Else Exit EndIf Local $MsgBox4 = MsgBox(4, "Access UDF Examples", " Starting Insert data example, Ready ?") If $MsgBox4 = 6 Then ; Insert some data Local $InsertData = _Insert_Data("INSERT INTO Table1([Col1], [Col2], [Col3]) VALUES ('AutoIt', 'Coding is Fun', 'AutoIt Rocks');") ConsoleWrite($InsertData & " Data inserted" & @CRLF) Else Exit EndIf Local $MsgBox5 = MsgBox(4, "Access UDF Examples", " Starting Update table example, Ready ?") If $MsgBox5 = 6 Then ; Let us update the table Local $UpdateData = _Insert_Data("UPDATE Table1 SET Col1 = 'Autoit Is Great', Col2 = 'Coding is Really Fun' WHERE Col3 = 'AutoIt Rocks';") ConsoleWrite($UpdateData & " Table Updated" & @CRLF) Else Exit EndIf Local $MsgBox6 = MsgBox(4, "Access UDF Examples", " Starting Get records example, Ready ?") If $MsgBox6 = 6 Then ; Now, collect some data Local $GetData = _Get_Records("SELECT [Col1],[Col2] FROM Table1 WHERE Col3 = 'AutoIt Rocks' ;") _ArrayDisplay($GetData) Else Exit EndIf Local $MsgBox7 = MsgBox(4, "Access UDF Examples", " Starting Delete from Table example, Ready ?") If $MsgBox7 = 6 Then ; Let us delete the whole data from that table, but not the table Local $DeleteAll = _Delete_FromTable("DELETE FROM Table1;") ConsoleWrite($DeleteAll & " All data deleted from Table" & @CRLF) Else Exit EndIf Local $MsgBox8 = MsgBox(4, "Access UDF Examples", " Starting Delete the entire table example, Ready ?") If $MsgBox8 = 6 Then ; Now, we are going to delete the entire table Local $DeleteTable = _Delete_Table("DROP TABLE TestTable;") ConsoleWrite($DeleteTable & " Table deleted" & @CRLF) Else Exit EndIf ; Last but not least, close the connection. _Close_Connection() ConsoleWrite("Examples Over...." & @CRLF) EndFunc ;==>Example1 Here is the files. 
      Access UDF.rar
      Access UDF.zip
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.