Jump to content
Sign in to follow this  
nevodj

Query FIREBIRD 2.0 Database

Recommended Posts

Hello,

I can't find anything helpful in the forums about querying a Firebird database.

Does anyone know if this can be done and how?

Thanks

Share this post


Link to post
Share on other sites

Hi nevodj, see

#include <FireBird.au3>
Local $wer
Global $h_fbDll = DllOpen("C:\bird\bin" & "\fbdll4vb20.dll")
Global $servername="192.168.0.5"
Global $sDBName="C:\Program Files\Firebird\Firebird_1_5\bin\Zimkps.gdb"
Local $sUsername = "sysdba"
Local $sPassword = "masterkey"
;_FireBird_Help($h_fbDll)
_FireBird_ConnectDatabase($h_fbDll, $servername, $sDBName, $sUsername, $sPassword)
;_FireBird_About($h_fbDll)
;_FireBird_Help($h_fbDll)
ConsoleWrite( _FireBird_ExecuteStatement($h_fbDll,"select * from tubaina"))
exit

Visit my repository

Share this post


Link to post
Share on other sites

Thanks detefon

I dont really understand dll calls or firebird.. i'm an SQL fan and have never touched one of our systems which is running on firebird... i have the firebird database on a windows server, does this script require firebird to be installed on the client machine before the script will connect?

the filepaths to the dll and the .gdb, are they were the files are located on the server or the client?

Global $h_fbDll = DllOpen("C:\bird\bin" & "\fbdll4vb20.dll")

Global $servername="192.168.0.5"

Global $sDBName="C:\Program Files\Firebird\Firebird_1_5\bin\Zimkps.gdb"

thanks for your help

Share this post


Link to post
Share on other sites

Hi nevodj, now with more information! 8D

Good luck!

#include <FireBird.au3>
Global $h_fbDll = DllOpen("C:\bird\bin\fbdll4vb20.dll")   ; path from 'fbdll4vb20.dll' in client side.
Global $servername = "192.168.0.5"      ; Firebird IP server
 
Global $sDBName="C:\Program Files\Firebird\Firebird_1_5\bin\Zimkps.gdb"
; The line above is the path from 'Zimkps.gdb' in the server
; You must write a script client with a path server from .gdb file
Local $sUsername = "sysdba"
Local $sPassword = "masterkey"
_FireBird_ConnectDatabase($h_fbDll, $servername, $sDBName, $sUsername, $sPassword)
ConsoleWrite( _FireBird_ExecuteStatement($h_fbDll, "select * from tubaina"))
Edited by detefon

Visit my repository

Share this post


Link to post
Share on other sites

thanks champ, couple of things i still dont understand:

1.

Global $h_fbDll = DllOpen("C:\bird\bin\fbdll4vb20.dll") ; path from 'fbdll4vb20.dll' in client side.

does this mean firebird has to be installed on the client?

2.

; You must write a script client with a path server from .gdb file

... could you explain here?

thanks!

Share this post


Link to post
Share on other sites

[Client side]

Global $h_fbDll = DllOpen("C:\bird\bin\fbdll4vb20.dll") ; path from 'fbdll4vb20.dll' in client side.

The AutoIt do not have parts to connnect in firebird, you must have download this dll (fbdll4vb20.dll) and put in someplace, maybe in the same folder like your client's script or another folder... This is only the path's dll.

I make the test with script a long time ago... I cant' remenber now if is necessary install firebird client or only download fbdll4vb20.dll and use that.

I know a little of MySQL, and it need only to connect: IP, username, password... Nothing more.

[server side]

Firebird needed: IP, username, password and the path's DB (or $sDBName).

This path is a place's from .gdb file in the server machine.

After you install a firebird module server in your 'server' and create your database's firebird, you must find the folder's .gdb file.

Something like this...

Global $sDBName="C:\Program Files\Firebird\Firebird_1_5\bin\Zimkps.gdb"

Good Luck (II)!

Edited by detefon

Visit my repository

Share this post


Link to post
Share on other sites

ok i've got this far. when i run it i get this message -

IBPP::Exception inside GDS::Call()***

IBPP Message : Can't find/load FBCLIENT.DLL nor GDS32.DLL

Share this post


Link to post
Share on other sites

hi guy i try to connect whit dll at the db fire bird but autoit tell me

C:\Program Files (x86)\AutoIt3\Include\FireBird.au3 (195) : ==> Subscript used with non-Array variable.:

Return SetError($vRet[0] <> 0, 0, $vRet[0])

Return SetError($vRet^ ERROR

why ?? ;)(((

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      #include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. 
      The code has all the explanations.
      Enjoy  
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
×
×
  • Create New...