Modificar el status de una base de datos Interbase / Firebird

Al realizar un backup o un restore de una base de datos necesitamos desconectar a los clientes para garantizar la integridad de la copia de seguridad. Para ello vamos a utilizar un componente llamado tIBConfigService de la paleta Interbase Admin.

El código que utilizaremos será el siguiente:

with IBConfigService1 do

begin

DatabaseName:=Base de datos;

ServerName:=Nombre del servidor;

LoginPrompt:=False;

Params.Clear;

Antes de cerrar la BD se deberá conectar únicamente un usuario

que será el que tomará el control de la BD

Params.Add(user_name=SYSDBA);

Params.Add(password=xxxxx);

try

Active:=True;

ShutdownDatabase(Forced, 0); Cierra la base de datos

except

ShowMessage('No se ha podido cerrar la BD');

end;

Active:=False;

end;

Para activar la BD hay que cambiar la línea "ShutdownDatabase(Forced, 0);" por

"BringDatabaseOnline;"

Los parámetros asociados al procedimiento ShutDownDatabase pueden ser:

Forced: Apaga la base de datos después del número de segundos especificado. Para apagar inmediatamente poner el número de segundos=0;



DenyTransaction: Niega nuevas transacciones para el número de segundos especificado y luego hacer shutdown



DenyAttachment: Niega nuevas conexiones para el número de segundos especificado y luego hacer shutdown.

Libros:

Bases de datos, grado superior

Aprende SQL en un fin de semana

Bases de datos, todo lo que hay que saber!

Sistemas gestores de bases de datos, grado superior

SQL (Manuales imprescindibles)

Create the Interbase tables

Background

Next, create the table structures. This can be achieved in several ways. The easiest way is to take a simple text file, fill in the table structure, and "import" the structure to InterBase using IBConsole, as explained below. There is a separate section covering "Importing database changes using a SQL file" which will be of interest when you need to carry out the Importing process for the second time.

A simple text file has been provided with this guide (Tables.SQL) with illustrative file layouts. Change the layout(s) to the layout(s) you require, and import the structure into InterBase. You can change it easily later. Those who don't feel comfortable with what they are doing might want to see Creating tables - Tips, prior to "doing it".

Do it!

To create the tables:

Open "Tables.SQL" in any text editor (eg. Notepad, Delphi)


Change the file location within the "Connect" statement to the location to which you saved the Database


Change the "user" and "password" to the username and password used to create the database


Change the Table layout to the layout you want. (Want help with Datatypes? See InterBase Datatypes)


Save the file (Latest changes not used by InterBase? See Creating tables - Tips )


In the program IBConsole, click "Query, Load script"


Find the SQL file you saved, and click "Open".


Click "Query, Execute"


If you want the confirmation and/or the error messages to appear in the IBConsole window, click the "No" button. If you prefer them to be saved to disc in a simple text file, click the "Yes" button and give the program a file name and location to which the results will be written.


If successful - you will be told (well done!). (Failure? See Handling SQL script errors)

Web http://www.ibphoenix.com

Opening and closing a Database from Delphi

In the BDE, the Database is closed for you when you exit the program. This is not so with the InterBase Express components.
The solution is to have a IBDatabase1.close statement in the Form.OnClose event (or similar). If you forget to close the Database explicitly, it stays "open". If you try to open a database that is already open, using the TIBDatabase component, you get an error message. The solution is always to check with the database is open before opening it
(eg. of Delphi : if not (IBTransaction1.connected) then IBTransaction1.open; ) and closed before closing it (eg. of Delphi : if (IBTransaction1.connected) then IBTransaction1.close; - Note that when you close a database, the Transaction and any other component "connected" to that Database is also disconnected for you, whether you intended it or not.)

web http://www.ibphoenix.com

Creating indexes on Interbase

Indexes allow InterBase to locate data (dramatically) more quickly. An index has two key components to it - the field(s) that you will want to search on and whether the field(s) are unique (e.g. a Reference number will probably need to be unique, but you may well need to accommodate several people sharing a birth date or a last name).

One particular type of index that is usually needed is an index on the Field(s) which uniquely identify a record within a table (e.g. the unique reference number given to each record, or a Social Security ID, or a post code and House number/name combination within an Address table). This is called the Primary key. Those who don't feel comfortable with what they are doing might want to see Creating indexes - Tips, prior to "doing it".

Do it!
Creating the Primary Key

Open your "table.SQL" file.


Add a line at the bottom of the definition, immediately before the final ")", and add the phrase (with the comma in front)
, PRIMARY KEY (field)
where field is the name of the field(s) you want as the primary key, eg:
, PRIMARY KEY (REF) or another example: , PRIMARY KEY (LASTNAME, FIRSTNAME, POSTCODE)


If you have already created your table, see Creating indexes - Tips
Creating other Indexes

Open the "Indexes.SQL" file


Add a new index with the syntax (don't forget the semi-colon at the end):
CREATE INDEX NAME ON ANIMALS(NAME);
where "Animals" is the name of the table, and "Name" is the field on which to index (sort)

Web: http://www.ibphoenix.com

How to use IBDataset with a DataModule

Steps:
1 .- a Cree First Data Module, which will contain all components DB (querys, tables etc.). Ok
2 .- in the data block put the IBDataBase which will be used solely to be used throughout your application (the only other non habra ok)
3. Module in the same data (DataModule) put the IBQuerys the IBUpdates and IBTransaction .. OK
in IBQuery.SQL there you can put your select * from table (eye code but not under the inspector objects) ok.
4 .- then put the IBUpdateSQL connected to that query ok ..
5 .- then add your form unity Module data you created, so that they can gain access to components put in DataModule ok. (Uses name of the unit)
6 .- as you put the following components:
DataSource which connects to the IBQuery ok, then put a DBEdit this connect to the DataSource and then select the field you want to display in DBEdit (DataSource properties and DataField respectively)
7. There are other components as DBGrid and DBNavigator lso which connect like the DBEdit ok.
Well this is Oriented Programming to objects and thus do not have to do this to me mention:
With IBDataset1 do
Try
DisableControls;
SelectSQL.Add ( 'Select *');
SelectSQL.Add ( 'FROM products');
SelectSQL.Add ( 'where Name =' + Combobox1.Text);
Edit1.Text: = FloatToStr (IBDataset1.Fields.Fields [3]. AsFloat);
Finally
EnableControls;
End;

How to retrieve an InterBase Blob in Delphi using SavetoFile

There are several ways of retrieving InterBase Blob data within Delphi.
This example uses the SaveToFile method.

* Put TDatabase, TQuery, TDatasource and TDBGrid on the form.
* set the Tdatabase's fields as follow:
> Alias name to the BDE Alias.
> DatabaseName to something you wish.
> Connected to true
* Set the TQuery fields as follow:
> SQL to the sql statement to be executed, ie select * from
table1
> DatabaseName to the same name you've named in the
previouse step.

* Set the TDatasource field DataSet to the name
of the Tquery component. Default is query1.
* Set the TDBGrid field DataSource to the name of
the TDataSource component. Defualt is Datasource1.
* Go back to TQuery component and double click.
* Right click on the blank space on the windows said
"form1.query". And choose add fields.
* Select all the fields in the list and added.
* Drop a TButton on the form.
* Double click on the Tbutton.
* Add the following lines between begin and end;

procedure TForm1.Button1Click(Sender: TObject);
begin
query1image.savetofile('c:testimage.jpg');
end;



Note: image is the field name defined as blob.
And c:testimage.jpg is where the location of the
image file to be created.


* Save the project/form/unit. and Run.
* Click on the Button will retrieve a blob entry and create file image.jpg.

Web: http://delphi.about.com

How to insert an InterBase BLOb in Delphi using LoadFromFile

This is one way of inserting BLOB in Delphi. There are several ways
to accomplish this task. This example is just one way of doing it.
This example is using the LoadFromFile method.

Create a table in InterBase with a field that can store non-text Blobs:

create table table1 (images blob sub_type 0);

-------------------------------------------------------------------------------------------------
* Put TQuery and a Button on the form.

* Set the TQuery's properties:
> Alias name to the BDE Alias pointing to the database.
> SQL to the sql statement to be executed,
i.e. select images from table1
> RequestLive to True.
> Active to true.
* Go back to TQuery component and double click which will bring up a window
titled "form1.query".

* Right click on the blank space of form1.query and choose add fields.

* Select images in the list and add.

* Double click on the TButton.

* Add the following lines

procedure TForm1.Button1Click(Sender: TObject);
begin
query1.append;
query1images.loadfromfile('c:testimage.bmp');
query1.post;
end;


Note: images is the field name defined as blob.
And c:testimage.bmp is where the location of the
image file to be inserted.


* Save the project/form/unit and Run.

* Click on the Button will insert a blob entry.


To verify the Blob is inserted:

* Drop a Tdatasource and TDBImage component on the form.

* Set the properties of Tdatasource:
> Dataset to "query1"

* Set the properties of TDBImage:
>Datasource to "datasource1"
>Datafield to images


* Save the project and run. You should see the images displayed.

Note: By default, DBImage will only display bitmaps.

Web: http://delphi.about.com