Delphi 6 Database connection length mode (SQL Server)

  Delphi 6 Database connection length mode (SQL Server)Tags: delphi Database2015-08-12 20:59 351 people read Comments (0) favorite reports Classification: Delphi(3)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

1 DelphiThe length of the connection to SQL Server Short Connection

We connect to the database usually have long connection and short connection mode , in Delphi often use Tadoconnection to establish database connection, Then use Tadoquery to perform crud operations. Tadoconnection can set the connection timeout, Keepconnection keeps the database connected until the program stops running. If you actively invoke the bottom 2 lines of a single comment, there is no action left or right inside the 1~2min, and the connection is automatically disconnected.

Adoconnection1. ConnectionString: = ' provider=sqloledb.1; password=1; Persist Security info=true; User id=sa;initial catalog=databasename;data source=127.0.0.1 ';  Adoquery1.connection: = ADOConnection1;  Adoconnection1.open;  ADOQUERY1.SQL.ADD (' select * from table1 ');  Adoquery1.open;  adoquery1.eof do  begin    {get value}    adoquery1.next;  End;  Qry1. Connection:=nil;  

Considering that the * * long connection is most likely due to long time no data access, the firewall connection recycling mechanism is recycled. 　Due to the low frequency of access to the database, we can use each access to the database to establish a connection first, run out of release. You can directly connect to the database with Adoquery by dynamically creating the adoquery and then setting its ConnectionString property.

var mqry:tadoquery  begin    mqry:= tadoquery.create (nil);    Mqry. Close;    Mqry. Sql. Clear;    Mqry. ConnectionString: = ' provider=sqloledb.1; password=123; Persist Security info=true;  User id=sa;initial catalog=databasename;data source=127.0.0.1 ';    Mqry. Sql. ADD (str_sql);    Mqry. Open;    While not mqry. Eof do    begin      {get value}      mqry. Next;     End;    Sleep (3000); {View effects}     If Assigned (mqry) then Freeandnil (mqry);//Release Object  

2 How to re-connect a database DelphiTadoconnnection and tadoquery do not have broken nets. automatic re-connectFunction.

Procedure Tform1.formcreate (sender:tobject); begin  ADOcon: = Tadoconnection.create (nil);  ADOcon: = sconnect_sting; Database connection string End;procedure Tform1.btnqueryclick (sender:tobject); Begin  Try    if not adocon. Connected then      ADOcon. Open;    Adoqry. Connection: = ADOcon;    Adoqry. Connection: = ADOcon;    Adoqry.commandtext: = PSQL;    Adoqry.commandtimeout: = itimeout;    {1. Manually shut down the database service,    each call to open, will generate a connection failure error, even if the DB service at this time, or will be connected error, it can be seen that adoqry will not re-connect}    adoqry. Open;  Except on E:exception do    begin       {if the DB service is disconnected, if you regenerate the ADOcon object connection database, error < general network errors. Please check the network document, do not generate connection failure error <description= connection failed; nativeerror=0; Sqlstate=08s01;>}       showmessage (e.message);    End  End;end;

3 How to solve

Generally use a timer, if the connection failed , timed to re-connect several times (usually three times), here I only show how to reconnect, see the code:

Adoconn is initialized at the time of your main class create.    Re-connected DB function doreconnect (itimeout:integer = 0): Boolean;begin result:=true;      Try if Assigned (adoconn) then Freeandnil (adoconn);      adoconn:= tadoconnection.create (application);      adoconn.connectionstring:=sconnstring;      Adoconn.connectiontimeout:=itimeout;    Adoconn.open;      except Result:=false;    If Assigned (adoconn) then Freeandnil (adoconn);  End;end;function Opensql (Psql:pchar; adodataset:tadodataset; itimeout:integer = 0): Boolean;var error_set:errors;  Error_obj:error;  Icount:integer;        Error_str:string;begin try//whether to re-connect the database if reconnect then BEGIN if Doreconnect (itimeout) THEN BEGIN      Reconnect: = false;//next come in does not need to re-connect error_str: = ' re-connected database success ';    End else raise Exception.create (' re-connected database failed, possibly the database service is not open! ');    End    If not adoconn.connected then Adoconn.open; Adoqry.    Connection: = Adoconn;    Adoqry.commandtext: = PSQL;    Adoqry.commandtimeout: = Itimeout; Adoqry.  Open;      Except on E:exception do begin error_str: = ' opensql execution [' + string (PSQL) + '] Error: '; If Assigned (adoqry. Connection) THEN begin error_set: = Adoqry.        Connection.errors; For iCount: = 0 to Error_set. Count-1 do begin error_obj: = Error_set.          Item[icount]; ERROR_STR: = error_str + ' description= ' + error_obj. Description + '; Nativeerror= ' + inttostr (error_obj. NativeError) + '; Sqlstate= ' + error_obj.          SQLState + '; '; If Error_obj. SQLState = ' 08s01 ' then//if connection problem reconnect: = True;      If a re-connect flag is required, the next call requires a re-connect end;    End else Error_str: = error_str + e.message + chr (0);  End End End for try if Error_str <> "then ShowMessage" (ERROR_STR); end;//End for Begin

Summarize

Tadoquery can connect directly to the database, not active release will remain connected (for low database frequency), but Tadoconnnection has some detailed properties, such as ConnectionTimeout connection timeout seconds, Keepconnection maintains the connection, the controllable ability is stronger, when the connection error, will return the detailed error information set Errorset;

Delphi 6 Database connection length mode (SQL Server)