unit DB_UTL;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables;

 
type
  TFM_DB_UTL = class(TForm)
    Query1: TQuery;
//    procedure Comm1ReceiveData(Sender: TObject; Buffer: Pointer;      BufferLength: Word);
  private
    { Private declarations }
  public
    { Public declarations }
  end;


// ODBC 類 =====================================================================
FUNCTION DB_SET_ODBC_ACCESS(DATASOURCE_NAME ,PATHNAME:STRING):BOOLEAN;
FUNCTION DB_SET_ODBC_INI   (INIFILENAME, DATASOURCE_NAME ,PATHNAME:STRING):BOOLEAN;
// ODBC 類 =====================================================================





// 數(shù)據(jù)庫類 ====================================================================
FUNCTION DB_QUERY_TABLELIST(DATABASE_NAME:STRING):STRING; //列出 TABLE
FUNCTION DB_QUERY_FIELDLIST(TABLE_NAME:STRING):STRING; //列出 FIELD
FUNCTION DB_QUERY_FIND_VALUE(TNAME1, FNAME1, VALUE1, FNAME2:STRING):STRING;
FUNCTION DB_QUERY_FIND_VALUETEXT(TNAME1, FTEXT, FNAME2:STRING):STRING;
FUNCTION DB_QUERY_FIND_MAXDATE(TNAME1, FNAME1, FKEY1, VKEY1:STRING):STRING;
FUNCTION DB_QUERY_DEL_REC(TNAME1, FNAME1, VALUE1:STRING):INTEGER;
FUNCTION DB_QUERY_UPDATE_VALUE_REAL    (TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:REAL):BOOLEAN;
FUNCTION DB_QUERY_UPDATE_VALUE_REAL_ACC(TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:REAL):BOOLEAN;
FUNCTION DB_QUERY_UPDATE_VALUE_STRING  (TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:STRING):BOOLEAN;
FUNCTION DB_QUERY_UPDATE_VALUE_DATE    (TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:TDATE):BOOLEAN;
// 數(shù)據(jù)庫類 ====================================================================









// 找出該TABLE最大值 ===========================================================
FUNCTION TABLE_MAX_RECCOUNT(TNAME1:STRING):INTEGER;
FUNCTION   TABLEFINDMAXCNT(TNAME1, FNAME1:STRING;NO_LENGTH, NO_STEP:INTEGER):STRING;
FUNCTION   TABLEFINDMAXCNT2(TNAME1, FNAME1, FTITLE:STRING;NO_LENGTH, NO_STEP:INTEGER):STRING;
FUNCTION DBTABLEFINDMAXCNT(TDBNAME,TNAME1, FNAME1:STRING;NO_LENGTH, NO_STEP:INTEGER):STRING;
FUNCTION   TABLEFINDMAXITM(TNAME1, FNAME1,VALUE1,FNAME2:STRING;NO_LENGTH, NO_STEP:INTEGER):STRING;
FUNCTION DBTABLEFINDMAXITM(TDBNAME, TNAME1, FNAME1,VALUE1,FNAME2:STRING;NO_LENGTH, NO_STEP:INTEGER):STRING;
FUNCTION   TABLEFINDMAXENO(TNAME1, FNAME1:STRING):STRING;
FUNCTION   TABLEFINDMAXENO2(TNAME1, FNAME1:STRING;NO_LENGTH, NO_STEP:INTEGER):STRING;
// 找出該TABLE最大值 ===========================================================



//檢查重復123 (含''號) =========================================================
FUNCTION TABLECHECK_RE1(TNAME1, FNAME1, WNAME1:STRING):INTEGER;
FUNCTION TABLECHECK_RE2(TNAME1, FNAME1, FNAME2, WNAME1, WNAME2:STRING):INTEGER;
FUNCTION TABLECHECK_RE3(TNAME1, FNAME1, FNAME2, FNAME3, WNAME1, WNAME2, WNAME3:STRING):INTEGER;
//檢查重復123 (不含''號)
FUNCTION TABLECHECK_RED1(TNAME1, FNAME1, WNAME1:STRING):INTEGER;
FUNCTION TABLECHECK_RED2(TNAME1, FNAME1, FNAME2, WNAME1, WNAME2:STRING):INTEGER;
FUNCTION TABLECHECK_RED3(TNAME1, FNAME1, FNAME2, FNAME3, WNAME1, WNAME2, WNAME3:STRING):INTEGER;
// =============================================================================







//調(diào)用系統(tǒng)設置文件 ===============================================================
FUNCTION UNSETREAD    (UNNAME, ID :STRING):STRING;
FUNCTION UNSETWRITE   (UNNAME, ID, NAMEVALUE :STRING):BOOLEAN;
FUNCTION UNSETWRITEMRK(UNNAME, ID, NAMEVALUE, NAMEMARK :STRING):BOOLEAN;
FUNCTION UNSETREADMRK (UNNAME, ID :STRING):STRING;
//調(diào)用系統(tǒng)設置文件 ===============================================================




//調(diào)用系統(tǒng)設置文件2 ==============================================================
FUNCTION UNSET_READ_SST  (LSTENO :STRING):STRING;
FUNCTION UNSET_READ_SIN  (LSTENO :STRING):INTEGER;
FUNCTION UNSET_READ_SBL  (LSTENO :STRING):BOOLEAN;
FUNCTION UNSET_READ_SME  (LSTENO :STRING):STRING;
FUNCTION UNSET_WRITE_SST (LSTENO :STRING; VALUE :STRING ):BOOLEAN;
FUNCTION UNSET_WRITE_SIN (LSTENO :STRING; VALUE :INTEGER):BOOLEAN;
FUNCTION UNSET_WRITE_SBL (LSTENO :STRING; VALUE :BOOLEAN):BOOLEAN;
FUNCTION UNSET_WRITE_SME (LSTENO :STRING; VALUE :STRING ):BOOLEAN;
//調(diào)用系統(tǒng)設置文件2 ==============================================================





{FUNCTION COMM_OPENPORT(T_PORT:STRING):BOOLEAN;
FUNCTION COMM_CLOSEPORT:BOOLEAN;
FUNCTION COMM_DATACLEAR:BOOLEAN;
}

var
  FM_DB_UTL: TFM_DB_UTL;

   //系統(tǒng)特殊值
   _DBKIND    : STRING;      // SERVER KIND (ACCESS和SQL SEVER替換)

   _DT        : STRING;      // 日期值(ACCESS和SQL SEVER替換)
   _SUMF      : ARRAY[0..9] OF STRING;      // SUM FIELD EXPR OR COLUM
   _TRUE      : STRING;      //TRUE值(ACCESS和SQL SEVER替換)
   _FALSE     : STRING;      //FALSE值(ACCESS和SQL SEVER替換)

  //硬件變量
  _BCP_DATA : STRING;

implementation

USES inifiles, Registry,
     UN_UTL;//, sysini;

{$R *.DFM}














FUNCTION DB_SET_ODBC_ACCESS(DATASOURCE_NAME ,PATHNAME:STRING):BOOLEAN;
var REG: TRegistry;
    D  : STRING;
BEGIN
  D   := DATASOURCE_NAME;
  REG := TRegistry.Create;
  try
    Reg.RootKey := HKEY_CURRENT_USER;
    IF Reg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources',True) then
       Reg.WriteString(D,'Microsoft Access Driver (*.mdb)');
    IF Reg.OpenKey('\Software\ODBC\ODBC.INI\'+D,True) then
       BEGIN
       Reg.WriteString ('Driver','C:\WINDOWS\SYSTEM\odbcjt32.dll');
       Reg.WriteString ('DBQ',PATHNAME);
       Reg.WriteInteger('DriverId',64);
       Reg.WriteString ('FIL','MS Access;');
       Reg.WriteInteger('SafeTransactions',0);
       Reg.WriteString ('UID','');
       END;
    IF Reg.OpenKey('\Software\ODBC\ODBC.INI\'+D+'\Engines\Jet',True) then
       BEGIN
       Reg.WriteString ('ImplicitCommitSync','');
       Reg.WriteInteger('MaxBufferSize',32768);
       Reg.WriteInteger('PageTimeout',5);
       Reg.WriteInteger('Threads',3);
       Reg.WriteString ('UserCommitSync','Yes');
       END;
    RESULT := TRUE;
  FINALLY
    Reg.CloseKey;
    Reg.Free;
  END;
END;



FUNCTION DB_SET_ODBC_INI   (INIFILENAME, DATASOURCE_NAME ,PATHNAME:STRING):BOOLEAN;
VAR T : Tinifile;     // LOG FILE
    SERVER_KIND : STRING;
BEGIN
  IF FileExists(INIFILENAME) = FALSE  THEN
     BEGIN
     FILE_CREATE(INIFILENAME);
     DB_SET_ODBC_ACCESS(DATASOURCE_NAME , PATHNAME);
  
     T := Tinifile.Create(INIFILENAME);
     T.WRITEString('ODBC','SERVER','ACCESS');
     T.WRITEString('ODBC','DATASOURCE_NAME',DATASOURCE_NAME);
     T.WRITEString('ODBC','PATH_NAME'      ,PATHNAME);
     RESULT := TRUE;
     END ELSE BEGIN
     T := Tinifile.Create(INIFILENAME);
     SERVER_KIND := T.READString('ODBC','SERVER' ,'ACCESS');
  
     // MS ACCESS DRIVER
     IF SERVER_KIND = 'ACCESS' THEN
        BEGIN
        DATASOURCE_NAME  := T.READString('ODBC','DATASOURCE_NAME' ,DATASOURCE_NAME);
        PATHNAME        := T.READString('ODBC','PATH_NAME'       ,PATHNAME);
        DB_SET_ODBC_ACCESS(DATASOURCE_NAME ,PATHNAME);
        END;
  
     // MS SQL SERVER DRIVER
     IF SERVER_KIND = 'SQLSERVER' THEN
        BEGIN
        END;
     RESULT := TRUE;
     END;
END;







//數(shù)據(jù)庫類
FUNCTION DB_QUERY_TABLELIST(DATABASE_NAME:STRING):STRING; //列出 TABLE
BEGIN
{
  TRY


   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('SELECT * FROM '+TABLE_NAME+'');
   QDBCHECK.Close;
   QDBCHECK.Open;

   try
   IF QDBCHECK.Eof = FALSE THEN  RESULT := QDBCHECK.FieldList.Text; //FOUND
   EXCEPT
      RESULT := '';
   END;

  FINALLY
  END;
  }
END;

FUNCTION DB_QUERY_FIELDLIST(TABLE_NAME:STRING):STRING;
VAR QDBCHECK : TQUERY;
BEGIN
  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('SELECT * FROM '+TABLE_NAME+'');
   QDBCHECK.Close;
   QDBCHECK.Open;
   try
   IF QDBCHECK.Eof = FALSE THEN  RESULT := QDBCHECK.FieldList.Text; //FOUND
   EXCEPT
      RESULT := '';
   END;

  FINALLY
  QDBCHECK.Free;
  END;
END;


FUNCTION DB_QUERY_FIND_VALUE(TNAME1, FNAME1, VALUE1, FNAME2:STRING):STRING;
VAR QDBCHECK : TQUERY;
BEGIN
  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('SELECT * FROM ' +TNAME1);
   QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.Close;
   QDBCHECK.Open;
   RESULT := QDBCHECK.FieldByName(FNAME2).AsString;
  FINALLY
  QDBCHECK.Free;
  END;
END;

FUNCTION DB_QUERY_FIND_VALUETEXT(TNAME1, FTEXT, FNAME2:STRING):STRING;
VAR QDBCHECK : TQUERY;
BEGIN
  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('SELECT * FROM ' +TNAME1);
   QDBCHECK.SQL.Add(FTEXT);
   QDBCHECK.Close;
   QDBCHECK.Open;
   RESULT := QDBCHECK.FieldByName(FNAME2).AsString;
  FINALLY
  QDBCHECK.Free;
  END;
END;

FUNCTION DB_QUERY_FIND_MAXDATE(TNAME1, FNAME1, FKEY1, VKEY1:STRING):STRING;
VAR QDBCHECK : TQUERY;
BEGIN
  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('SELECT MAX('+FNAME1+') FROM ' +TNAME1);
   IF FKEY1 <> '' THEN QDBCHECK.SQL.Add('WHERE '+FKEY1+'='''+VKEY1+'''');
   QDBCHECK.Close;
   QDBCHECK.Open;
   RESULT := QDBCHECK.Fields[0].AsSTRING;
  FINALLY
  QDBCHECK.Free;
  END;
END;

FUNCTION DB_QUERY_DEL_REC(TNAME1, FNAME1, VALUE1:STRING):INTEGER;
VAR DELETE_CNT : INTEGER;
    QDBCHECK : TQUERY;
BEGIN

  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   IF FNAME1 =  '' THEN QDBCHECK.SQL.Add('SELECT COUNT(*) FROM ' +TNAME1);
   IF FNAME1 <> '' THEN QDBCHECK.SQL.Add('SELECT COUNT('+FNAME1+') FROM ' +TNAME1);
   IF FNAME1 <> '' THEN QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.Close;
   QDBCHECK.Open;
   DELETE_CNT := QDBCHECK.Fields[0].AsINTEGER;

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('DELETE FROM ' +TNAME1);
   IF FNAME1 <> '' THEN QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.ExecSQL;
  FINALLY
  QDBCHECK.Free;
  END;

RESULT := DELETE_CNT;
END;


FUNCTION DB_QUERY_UPDATE_VALUE_REAL(TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:REAL):BOOLEAN;
VAR QDBCHECK : TQUERY;
BEGIN

  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('UPDATE ' +TNAME1);
   QDBCHECK.SQL.Add('SET');
   QDBCHECK.SQL.Add(FNAME2 +' = ' + FLOATTOSTR(VALUE2));
   QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.ExecSQL;
   RESULT := TRUE;
  FINALLY
  QDBCHECK.Free;
  END;

END;

FUNCTION DB_QUERY_UPDATE_VALUE_REAL_ACC(TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:REAL):BOOLEAN;
VAR QDBCHECK : TQUERY;
BEGIN

  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('UPDATE ' +TNAME1);
   QDBCHECK.SQL.Add('SET');
   QDBCHECK.SQL.Add(FNAME2 +' = '+FNAME2+'+'+ FLOATTOSTR(VALUE2));
   QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.ExecSQL;
   RESULT := TRUE;
  FINALLY
  QDBCHECK.Free;
  END;

END;

FUNCTION DB_QUERY_UPDATE_VALUE_STRING(TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:STRING):BOOLEAN;
VAR QDBCHECK : TQUERY;
BEGIN

  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('UPDATE ' +TNAME1);
   QDBCHECK.SQL.Add('SET');
   QDBCHECK.SQL.Add(FNAME2 +' = ''' + VALUE2 + '''');
   QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.ExecSQL;
   RESULT := TRUE;
  FINALLY
  QDBCHECK.Free;
  END;

END;

FUNCTION DB_QUERY_UPDATE_VALUE_DATE(TNAME1, FNAME1, VALUE1, FNAME2:STRING;VALUE2:TDATE):BOOLEAN;
VAR QDBCHECK : TQUERY;
BEGIN
  TRY
   QDBCHECK := TQUERY.Create(APPLICATION.Owner);
   QDBCHECK.DatabaseName := 'MAIN';

   QDBCHECK.SQL.Clear;
   QDBCHECK.SQL.Add('UPDATE ' +TNAME1);
   QDBCHECK.SQL.Add('SET');
   QDBCHECK.SQL.Add(FNAME2 +' = '+_DT + DATETOSTR(VALUE2)+_DT);
   QDBCHECK.SQL.Add('WHERE '+FNAME1+'='''+VALUE1+'''');
   QDBCHECK.ExecSQL;
   RESULT := TRUE;
  FINALLY
  QDBCHECK.Free;
  END;
END;