WebSVN – indexer_suite – Diff – /trunk/AdoConnHelper.pas


unit AdoConnHelper;
 
(*
 * Class helper for TAdoConnection
 * by Daniel Marschall, ViaThinkSoft <www.viathinksoft.com>
 * Revision: 14 June 2021
 *)
 
interface
 
uses
  DB, ADODB, Variants, Classes, SysUtils;
 
type
  TAdoConnectionHelper = class helper for TADOConnection
  private
    function GetConnectionID: TGUID;
  public
    // Attention: Some of the functions here (e.g. ConnectionID) require SQL Server
    function GetTable(SQL: string; ATimeout: integer=-1): TADODataSet;
    function GetScalar(SQL: string; ATimeout: integer=-1): Variant;
    procedure ExecSQL(SQL: string; ATimeout: integer=-1);
    procedure ExecSQLList(List: TStrings; ATimeout: integer=-1);
    procedure Connect(AConnectionString: string; ATimeout: integer=-1);
    procedure Disconnect;
    class function SQLStringEscape(str: string): string; static;
    class function SQLObjectNameEscape(str: string): string; static;
    procedure GetPrimaryKeyNames(TableName: string; outsl: TStrings);
    property ConnectionID: TGUID read GetConnectionID;
    function InsertAndReturnID(query: string): integer;
    procedure DropTable(aTableName: string);
    function FieldCount(aTableName: string): integer;
    function IndexCount(aTableName: string): integer;
    function TableExist(aTableName: string): boolean;
    function ViewExist(aViewName: string): boolean;
    function ColumnExists(aTableName, aColumnName: string): boolean;
    procedure GetTableNames(List: TStrings; SystemTables: Boolean=false);
    //property TransactionLevel: integer read FTransactionLevel;
  end;
 
implementation
 
{ TAdoConnectionHelper }
 
function TAdoConnectionHelper.GetConnectionID: TGUID;
var
  s : string;
begin
  s := GetScalar('select connection_id from sys.dm_exec_connections ec ' +
                 'left join sys.dm_exec_sessions se on ec.session_id = se.session_id ' +
                 'where se.session_id = @@SPID');
  result := StringToGUID(s);
end;
 
procedure TAdoConnectionHelper.GetPrimaryKeyNames(TableName: string; outsl: TStrings);
var
  ds: TADODataSet;
begin
  ds := TADODataSet.Create(nil);
  try
    OpenSchema(siPrimaryKeys, Unassigned, EmptyParam, ds);
    while not ds.Eof do
    begin
      if ds.FieldByName('TABLE_NAME').AsString = TableName then
      begin
        outsl.Add(ds.FieldByName('COLUMN_NAME').AsString);
      end;
      ds.Next;
    end;
    ds.Close;
  finally
    ds.Free;
  end;
end;
 
function TAdoConnectionHelper.GetScalar(SQL: string; ATimeout: integer=-1): Variant;
var
  ds: TADODataSet;
begin
  ds := GetTable(SQL, ATimeout);
  result := ds.Fields[0].AsVariant;
  ds.Free;
end;
 
function TAdoConnectionHelper.GetTable(SQL: string; ATimeout: integer=-1): TADODataSet;
begin
  result := TADODataSet.Create(nil);
  result.Connection := Self;
  result.EnableBCD := false;
  result.ParamCheck := false;
  result.CommandType := cmdText;
  result.CommandText := SQL;
  result.DisableControls;
  if ATimeout <> -1 then
    result.CommandTimeout := ATimeout
  else
    result.CommandTimeout := self.CommandTimeout;
  result.Active := true;
end;
 
function TAdoConnectionHelper.ColumnExists(aTableName, aColumnName: string): boolean;
begin
  result := GetScalar('select count (*) from sys.columns where Name = '+SQLStringEscape(aColumnName)+' and Object_ID = Object_ID('+SQLStringEscape(aTableName)+')') > 0;
end;
 
procedure TAdoConnectionHelper.GetTableNames(List: TStrings;
  SystemTables: Boolean);
begin
 
end;
 
function TAdoConnectionHelper.IndexCount(aTableName: string): integer;
begin
  result := GetScalar('select max (ik.indid) from sysindexkeys ik left join sysindexes ind on ind.id = ik.id and ind.indid = ik.indid where ik.id = (select id from sysobjects ' +
                      'where name = ''' + aTableName + ''') and ind.status < 10000000');
end;
 
function TAdoConnectionHelper.InsertAndReturnID(query: string): integer;
resourcestring
  LNG_NO_AUTOINC = 'Cannot find AutoIncrement value';
var
  q1: TADODataSet;
begin
  BeginTrans;
  try
    ExecSql(query); // Execute(query);
 
    // Hinweis: Das geht nur lokal, nicht über linked servers
    // TODO: Man sollte lieber SCOPE_IDENTITY() verwenden
    q1 := GetTable('select @@IDENTITY as INSERT_ID;');
    try
      if (q1.RecordCount = 0) or (q1.{FieldByName('INSERT_ID')}Fields[0].AsInteger = 0) then
      begin
        //result := -1;
        raise EADOError.Create(LNG_NO_AUTOINC);
      end;
 
      result := q1.{FieldByName('INSERT_ID')}Fields[0].AsInteger;
    finally
      FreeAndNil(q1);
    end;
  finally
    CommitTrans;
  end;
end;
 
procedure TAdoConnectionHelper.Connect(AConnectionString: string; ATimeout: integer=-1);
begin
  Disconnect;
  ConnectionString := AConnectionString;
  if ATimeout <> -1 then ConnectionTimeout := ATimeout;
  Connected := true;
end;
 
procedure TAdoConnectionHelper.Disconnect;
begin
  Connected := false;
end;
 
procedure TAdoConnectionHelper.DropTable(aTableName: string);
begin
  if ViewExist(aTableName) then
  begin
    ExecSql('drop view ' + SQLObjectNameEscape(aTableName));
  end;
  if TableExist(aTableName) then
  begin
    ExecSql('drop table ' + SQLObjectNameEscape(aTableName));
  end;
end;
 
procedure TAdoConnectionHelper.ExecSQL(SQL: string; ATimeout: integer);
var
  cmd: TADOCommand;
begin
  cmd := TADOCommand.Create(nil);
  try
    cmd.Connection := Self;
    cmd.ParamCheck := false;
    cmd.CommandType := cmdText;
    cmd.CommandText := SQL;
    if ATimeOut <> -1 then
      cmd.CommandTimeout := ATimeout
    else
      cmd.CommandTimeout := self.CommandTimeout;
    cmd.Execute;
  finally
    cmd.Free;
  end;
end;
 
procedure TAdoConnectionHelper.ExecSQLList(List: TStrings; ATimeout: integer);
var
  s: string;
begin
  for s in List do
  begin
    ExecSQL(s, ATimeout);
  end;
end;
 
function TAdoConnectionHelper.FieldCount(aTableName: string): integer;
begin
  result := GetScalar('select count (*) from syscolumns where id = (select id from sysobjects where name = ''' + aTableName + ''') ');
end;
 
class function TAdoConnectionHelper.SQLStringEscape(str: string): string;
begin
  result := str;
 
  // Escape SQL-Argument
  (*
  result := StringReplace(result, '\', '\\', [rfReplaceAll]);
  result := StringReplace(result, '_', '\_', [rfReplaceAll]);
  result := StringReplace(result, '%', '\%', [rfReplaceAll]);
  result := StringReplace(result, '[', '\[', [rfReplaceAll]);
  result := StringReplace(result, '''', '\''', [rfReplaceAll]);
  *)
 
  // DM 29.02.2016 Irgendwie versteh ich das nicht...
  // 'xxx\'xxx' ist erlaubt, aber 'xxx\'xxx\'xxx' nicht
  // aber 'xxx''xxx''xxx' geht.
  result := StringReplace(result, '''', '''''', [rfReplaceAll]);
 
  // Verhindern, dass SQL Server denkt, es sei ein Parameterobjekt
  // Brauchen wir nur, wenn die abfrage ParamCheck=true hat.
  // Wir haben aber in hl_Datenbank.pas das immer auf false.
  // result := StringReplace(result, ':', '::', [rfReplaceAll]);
 
  {$IFDEF UNICODE}
  result := 'N''' + result + '''';
  {$ELSE}
  result := '''' + result + '''';
  {$ENDIF}
end;
 
function TAdoConnectionHelper.TableExist(aTableName: string): boolean;
begin
  if Copy(aTableName, 1, 1) = '#' then
  begin
    // TempTable
    result := GetScalar('select case when OBJECT_ID(''tempdb..'+aTableName+''') is not null then ''1'' else ''0'' end') > 0;
  end
  else
  begin
    // Physikalische Tabelle (in Schema dbo)
    // result := GetScalar('select count (*) from sysobjects where name = ' + aTableName.toSQLString) > 0;
    result := GetScalar('SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = N'''+DefaultDatabase+''' AND TABLE_SCHEMA = N''dbo'' AND TABLE_NAME = N'''+aTableName+'''') > 0;
  end;
end;
 
function TAdoConnectionHelper.ViewExist(aViewName: string): boolean;
begin
  result := GetScalar('select count(*) FROM sys.views where name = '+SQLStringEscape(aViewName)) > 0;
end;
 
class function TAdoConnectionHelper.SQLObjectNameEscape(str: string): string;
begin
  result := '[dbo].[' + str + ']';
end;
 
end.
 

Rev 7	Rev 8
1	unit AdoConnHelper;	1	unit AdoConnHelper;
2		2
3	(*	3	(*
4	* Class helper for TAdoConnection	4	* Class helper for TAdoConnection
5	* by Daniel Marschall, ViaThinkSoft <www.viathinksoft.com>	5	* by Daniel Marschall, ViaThinkSoft <www.viathinksoft.com>
6	* Revision: 28 Aug 2018	6	* Revision: 14 June 2021
7	*)	7	*)
8		8
9	interface	9	interface
10		10
11	uses	11	uses
12	DB, ADODB, Variants, Classes, SysUtils;	12	DB, ADODB, Variants, Classes, SysUtils;
13		13
14	type	14	type
15	TAdoConnectionHelper = class helper for TADOConnection	15	TAdoConnectionHelper = class helper for TADOConnection
16	private	16	private
17	function GetConnectionID: TGUID;	17	function GetConnectionID: TGUID;
18	public	18	public
19	// Attention: Some of the functions here (e.g. ConnectionID) require SQL Server	19	// Attention: Some of the functions here (e.g. ConnectionID) require SQL Server
20	function GetTable(SQL: string; ATimeout: integer=-1): TADODataSet;	20	function GetTable(SQL: string; ATimeout: integer=-1): TADODataSet;
21	function GetScalar(SQL: string; ATimeout: integer=-1): Variant;	21	function GetScalar(SQL: string; ATimeout: integer=-1): Variant;
22	procedure ExecSQL(SQL: string; ATimeout: integer=-1);	22	procedure ExecSQL(SQL: string; ATimeout: integer=-1);
23	procedure ExecSQLList(List: TStrings; ATimeout: integer=-1);	23	procedure ExecSQLList(List: TStrings; ATimeout: integer=-1);
24	procedure Connect(AConnectionString: string; ATimeout: integer=-1);	24	procedure Connect(AConnectionString: string; ATimeout: integer=-1);
25	procedure Disconnect;	25	procedure Disconnect;
26	class function SQLStringEscape(str: string): string; static;	26	class function SQLStringEscape(str: string): string; static;
27	class function SQLObjectNameEscape(str: string): string; static;	27	class function SQLObjectNameEscape(str: string): string; static;
28	procedure GetPrimaryKeyNames(TableName: string; outsl: TStrings);	28	procedure GetPrimaryKeyNames(TableName: string; outsl: TStrings);
29	property ConnectionID: TGUID read GetConnectionID;	29	property ConnectionID: TGUID read GetConnectionID;
30	function InsertAndReturnID(query: string): integer;	30	function InsertAndReturnID(query: string): integer;
31	procedure DropTable(aTableName: string);	31	procedure DropTable(aTableName: string);
32	function FieldCount(aTableName: string): integer;	32	function FieldCount(aTableName: string): integer;
33	function IndexCount(aTableName: string): integer;	33	function IndexCount(aTableName: string): integer;
34	function TableExist(aTableName: string): boolean;	34	function TableExist(aTableName: string): boolean;
35	function ViewExist(aViewName: string): boolean;	35	function ViewExist(aViewName: string): boolean;
36	function ColumnExists(aTableName, aColumnName: string): boolean;	36	function ColumnExists(aTableName, aColumnName: string): boolean;
37	procedure GetTableNames(List: TStrings; SystemTables: Boolean=false);	37	procedure GetTableNames(List: TStrings; SystemTables: Boolean=false);
38	//property TransactionLevel: integer read FTransactionLevel;	38	//property TransactionLevel: integer read FTransactionLevel;
39	end;	39	end;
40		40
41	implementation	41	implementation
42		42
43	{ TAdoConnectionHelper }	43	{ TAdoConnectionHelper }
44		44
45	function TAdoConnectionHelper.GetConnectionID: TGUID;	45	function TAdoConnectionHelper.GetConnectionID: TGUID;
46	var	46	var
47	s : string;	47	s : string;
48	begin	48	begin
49	s := GetScalar('select connection_id from sys.dm_exec_connections ec ' +	49	s := GetScalar('select connection_id from sys.dm_exec_connections ec ' +
50	'left join sys.dm_exec_sessions se on ec.session_id = se.session_id ' +	50	'left join sys.dm_exec_sessions se on ec.session_id = se.session_id ' +
51	'where se.session_id = @@SPID');	51	'where se.session_id = @@SPID');
52	result := StringToGUID(s);	52	result := StringToGUID(s);
53	end;	53	end;
54		54
55	procedure TAdoConnectionHelper.GetPrimaryKeyNames(TableName: string; outsl: TStrings);	55	procedure TAdoConnectionHelper.GetPrimaryKeyNames(TableName: string; outsl: TStrings);
56	var	56	var
57	ds: TADODataSet;	57	ds: TADODataSet;
58	begin	58	begin
59	ds := TADODataSet.Create(nil);	59	ds := TADODataSet.Create(nil);
60	try	60	try
61	OpenSchema(siPrimaryKeys, Unassigned, EmptyParam, ds);	61	OpenSchema(siPrimaryKeys, Unassigned, EmptyParam, ds);
62	while not ds.Eof do	62	while not ds.Eof do
63	begin	63	begin
64	if ds.FieldByName('TABLE_NAME').AsString = TableName then	64	if ds.FieldByName('TABLE_NAME').AsString = TableName then
65	begin	65	begin
66	outsl.Add(ds.FieldByName('COLUMN_NAME').AsString);	66	outsl.Add(ds.FieldByName('COLUMN_NAME').AsString);
67	end;	67	end;
68	ds.Next;	68	ds.Next;
69	end;	69	end;
70	ds.Close;	70	ds.Close;
71	finally	71	finally
72	ds.Free;	72	ds.Free;
73	end;	73	end;
74	end;	74	end;
75		75
76	function TAdoConnectionHelper.GetScalar(SQL: string; ATimeout: integer=-1): Variant;	76	function TAdoConnectionHelper.GetScalar(SQL: string; ATimeout: integer=-1): Variant;
77	var	77	var
78	ds: TADODataSet;	78	ds: TADODataSet;
79	begin	79	begin
80	ds := GetTable(SQL, ATimeout);	80	ds := GetTable(SQL, ATimeout);
81	result := ds.Fields[0].AsVariant;	81	result := ds.Fields[0].AsVariant;
82	ds.Free;	82	ds.Free;
83	end;	83	end;
84		84
85	function TAdoConnectionHelper.GetTable(SQL: string; ATimeout: integer=-1): TADODataSet;	85	function TAdoConnectionHelper.GetTable(SQL: string; ATimeout: integer=-1): TADODataSet;
86	begin	86	begin
87	result := TADODataSet.Create(nil);	87	result := TADODataSet.Create(nil);
88	result.Connection := Self;	88	result.Connection := Self;
89	result.EnableBCD := false;	89	result.EnableBCD := false;
90	result.ParamCheck := false;	90	result.ParamCheck := false;
91	result.CommandType := cmdText;	91	result.CommandType := cmdText;
92	result.CommandText := SQL;	92	result.CommandText := SQL;
93	result.DisableControls;	93	result.DisableControls;
-		94	if ATimeout <> -1 then
94	if ATimeout <> -1 then result.CommandTimeout := ATimeout;	95	result.CommandTimeout := ATimeout
-		96	else
-		97	result.CommandTimeout := self.CommandTimeout;
95	result.Active := true;	98	result.Active := true;
96	end;	99	end;
97		100
98	function TAdoConnectionHelper.ColumnExists(aTableName, aColumnName: string): boolean;	101	function TAdoConnectionHelper.ColumnExists(aTableName, aColumnName: string): boolean;
99	begin	102	begin
100	result := GetScalar('select count (*) from sys.columns where Name = '+SQLStringEscape(aColumnName)+' and Object_ID = Object_ID('+SQLStringEscape(aTableName)+')') > 0;	103	result := GetScalar('select count (*) from sys.columns where Name = '+SQLStringEscape(aColumnName)+' and Object_ID = Object_ID('+SQLStringEscape(aTableName)+')') > 0;
101	end;	104	end;
102		105
103	procedure TAdoConnectionHelper.GetTableNames(List: TStrings;	106	procedure TAdoConnectionHelper.GetTableNames(List: TStrings;
104	SystemTables: Boolean);	107	SystemTables: Boolean);
105	begin	108	begin
106		109
107	end;	110	end;
108		111
109	function TAdoConnectionHelper.IndexCount(aTableName: string): integer;	112	function TAdoConnectionHelper.IndexCount(aTableName: string): integer;
110	begin	113	begin
111	result := GetScalar('select max (ik.indid) from sysindexkeys ik left join sysindexes ind on ind.id = ik.id and ind.indid = ik.indid where ik.id = (select id from sysobjects ' +	114	result := GetScalar('select max (ik.indid) from sysindexkeys ik left join sysindexes ind on ind.id = ik.id and ind.indid = ik.indid where ik.id = (select id from sysobjects ' +
112	'where name = ''' + aTableName + ''') and ind.status < 10000000');	115	'where name = ''' + aTableName + ''') and ind.status < 10000000');
113	end;	116	end;
114		117
115	function TAdoConnectionHelper.InsertAndReturnID(query: string): integer;	118	function TAdoConnectionHelper.InsertAndReturnID(query: string): integer;
116	resourcestring	119	resourcestring
117	LNG_NO_AUTOINC = 'Cannot find AutoIncrement value';	120	LNG_NO_AUTOINC = 'Cannot find AutoIncrement value';
118	var	121	var
119	q1: TADODataSet;	122	q1: TADODataSet;
120	begin	123	begin
121	BeginTrans;	124	BeginTrans;
122	try	125	try
123	ExecSql(query); // Execute(query);	126	ExecSql(query); // Execute(query);
124		127
125	// Hinweis: Das geht nur lokal, nicht über linked servers	128	// Hinweis: Das geht nur lokal, nicht über linked servers
126	// TODO: Man sollte lieber SCOPE_IDENTITY() verwenden	129	// TODO: Man sollte lieber SCOPE_IDENTITY() verwenden
127	q1 := GetTable('select @@IDENTITY as INSERT_ID;');	130	q1 := GetTable('select @@IDENTITY as INSERT_ID;');
128	try	131	try
129	if (q1.RecordCount = 0) or (q1.{FieldByName('INSERT_ID')}Fields[0].AsInteger = 0) then	132	if (q1.RecordCount = 0) or (q1.{FieldByName('INSERT_ID')}Fields[0].AsInteger = 0) then
130	begin	133	begin
131	//result := -1;	134	//result := -1;
132	raise EADOError.Create(LNG_NO_AUTOINC);	135	raise EADOError.Create(LNG_NO_AUTOINC);
133	end;	136	end;
134		137
135	result := q1.{FieldByName('INSERT_ID')}Fields[0].AsInteger;	138	result := q1.{FieldByName('INSERT_ID')}Fields[0].AsInteger;
136	finally	139	finally
137	FreeAndNil(q1);	140	FreeAndNil(q1);
138	end;	141	end;
139	finally	142	finally
140	CommitTrans;	143	CommitTrans;
141	end;	144	end;
142	end;	145	end;
143		146
144	procedure TAdoConnectionHelper.Connect(AConnectionString: string; ATimeout: integer=-1);	147	procedure TAdoConnectionHelper.Connect(AConnectionString: string; ATimeout: integer=-1);
145	begin	148	begin
146	Disconnect;	149	Disconnect;
147	ConnectionString := AConnectionString;	150	ConnectionString := AConnectionString;
148	if ATimeout <> -1 then ConnectionTimeout := ATimeout;	151	if ATimeout <> -1 then ConnectionTimeout := ATimeout;
149	Connected := true;	152	Connected := true;
150	end;	153	end;
151		154
152	procedure TAdoConnectionHelper.Disconnect;	155	procedure TAdoConnectionHelper.Disconnect;
153	begin	156	begin
154	Connected := false;	157	Connected := false;
155	end;	158	end;
156		159
157	procedure TAdoConnectionHelper.DropTable(aTableName: string);	160	procedure TAdoConnectionHelper.DropTable(aTableName: string);
158	begin	161	begin
159	if ViewExist(aTableName) then	162	if ViewExist(aTableName) then
160	begin	163	begin
161	ExecSql('drop view ' + SQLObjectNameEscape(aTableName));	164	ExecSql('drop view ' + SQLObjectNameEscape(aTableName));
162	end;	165	end;
163	if TableExist(aTableName) then	166	if TableExist(aTableName) then
164	begin	167	begin
165	ExecSql('drop table ' + SQLObjectNameEscape(aTableName));	168	ExecSql('drop table ' + SQLObjectNameEscape(aTableName));
166	end;	169	end;
167	end;	170	end;
168		171
169	procedure TAdoConnectionHelper.ExecSQL(SQL: string; ATimeout: integer);	172	procedure TAdoConnectionHelper.ExecSQL(SQL: string; ATimeout: integer);
170	var	173	var
171	cmd: TADOCommand;	174	cmd: TADOCommand;
172	begin	175	begin
173	cmd := TADOCommand.Create(nil);	176	cmd := TADOCommand.Create(nil);
174	try	177	try
175	cmd.Connection := Self;	178	cmd.Connection := Self;
176	cmd.ParamCheck := false;	179	cmd.ParamCheck := false;
177	cmd.CommandType := cmdText;	180	cmd.CommandType := cmdText;
178	cmd.CommandText := SQL;	181	cmd.CommandText := SQL;
-		182	if ATimeOut <> -1 then
179	if ATimeOut <> -1 then cmd.CommandTimeout := ATimeout;	183	cmd.CommandTimeout := ATimeout
-		184	else
-		185	cmd.CommandTimeout := self.CommandTimeout;
180	cmd.Execute;	186	cmd.Execute;
181	finally	187	finally
182	cmd.Free;	188	cmd.Free;
183	end;	189	end;
184	end;	190	end;
185		191
186	procedure TAdoConnectionHelper.ExecSQLList(List: TStrings; ATimeout: integer);	192	procedure TAdoConnectionHelper.ExecSQLList(List: TStrings; ATimeout: integer);
187	var	193	var
188	s: string;	194	s: string;
189	begin	195	begin
190	for s in List do	196	for s in List do
191	begin	197	begin
192	ExecSQL(s);	198	ExecSQL(s, ATimeout);
193	end;	199	end;
194	end;	200	end;
195		201
196	function TAdoConnectionHelper.FieldCount(aTableName: string): integer;	202	function TAdoConnectionHelper.FieldCount(aTableName: string): integer;
197	begin	203	begin
198	result := GetScalar('select count (*) from syscolumns where id = (select id from sysobjects where name = ''' + aTableName + ''') ');	204	result := GetScalar('select count (*) from syscolumns where id = (select id from sysobjects where name = ''' + aTableName + ''') ');
199	end;	205	end;
200		206
201	class function TAdoConnectionHelper.SQLStringEscape(str: string): string;	207	class function TAdoConnectionHelper.SQLStringEscape(str: string): string;
202	begin	208	begin
203	result := str;	209	result := str;
204		210
205	// Escape SQL-Argument	211	// Escape SQL-Argument
206	(*	212	(*
207	result := StringReplace(result, '\', '\\', [rfReplaceAll]);	213	result := StringReplace(result, '\', '\\', [rfReplaceAll]);
208	result := StringReplace(result, '_', '\_', [rfReplaceAll]);	214	result := StringReplace(result, '_', '\_', [rfReplaceAll]);
209	result := StringReplace(result, '%', '\%', [rfReplaceAll]);	215	result := StringReplace(result, '%', '\%', [rfReplaceAll]);
210	result := StringReplace(result, '[', '\[', [rfReplaceAll]);	216	result := StringReplace(result, '[', '\[', [rfReplaceAll]);
211	result := StringReplace(result, '''', '\''', [rfReplaceAll]);	217	result := StringReplace(result, '''', '\''', [rfReplaceAll]);
212	*)	218	*)
213		219
214	// DM 29.02.2016 Irgendwie versteh ich das nicht...	220	// DM 29.02.2016 Irgendwie versteh ich das nicht...
215	// 'xxx\'xxx' ist erlaubt, aber 'xxx\'xxx\'xxx' nicht	221	// 'xxx\'xxx' ist erlaubt, aber 'xxx\'xxx\'xxx' nicht
216	// aber 'xxx''xxx''xxx' geht.	222	// aber 'xxx''xxx''xxx' geht.
217	result := StringReplace(result, '''', '''''', [rfReplaceAll]);	223	result := StringReplace(result, '''', '''''', [rfReplaceAll]);
218		224
219	// Verhindern, dass SQL Server denkt, es sei ein Parameterobjekt	225	// Verhindern, dass SQL Server denkt, es sei ein Parameterobjekt
220	// Brauchen wir nur, wenn die abfrage ParamCheck=true hat.	226	// Brauchen wir nur, wenn die abfrage ParamCheck=true hat.
221	// Wir haben aber in hl_Datenbank.pas das immer auf false.	227	// Wir haben aber in hl_Datenbank.pas das immer auf false.
222	// result := StringReplace(result, ':', '::', [rfReplaceAll]);	228	// result := StringReplace(result, ':', '::', [rfReplaceAll]);
223		229
224	{$IFDEF UNICODE}	230	{$IFDEF UNICODE}
225	result := 'N''' + result + '''';	231	result := 'N''' + result + '''';
226	{$ELSE}	232	{$ELSE}
227	result := '''' + result + '''';	233	result := '''' + result + '''';
228	{$ENDIF}	234	{$ENDIF}
229	end;	235	end;
230		236
231	function TAdoConnectionHelper.TableExist(aTableName: string): boolean;	237	function TAdoConnectionHelper.TableExist(aTableName: string): boolean;
232	begin	238	begin
233	if Copy(aTableName, 1, 1) = '#' then	239	if Copy(aTableName, 1, 1) = '#' then
234	begin	240	begin
235	// TempTable	241	// TempTable
236	result := GetScalar('select case when OBJECT_ID(''tempdb..'+aTableName+''') is not null then ''1'' else ''0'' end') > 0;	242	result := GetScalar('select case when OBJECT_ID(''tempdb..'+aTableName+''') is not null then ''1'' else ''0'' end') > 0;
237	end	243	end
238	else	244	else
239	begin	245	begin
240	// Physikalische Tabelle (in Schema dbo)	246	// Physikalische Tabelle (in Schema dbo)
241	// result := GetScalar('select count (*) from sysobjects where name = ' + aTableName.toSQLString) > 0;	247	// result := GetScalar('select count (*) from sysobjects where name = ' + aTableName.toSQLString) > 0;
242	result := GetScalar('SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = N'''+DefaultDatabase+''' AND TABLE_SCHEMA = N''dbo'' AND TABLE_NAME = N'''+aTableName+'''') > 0;	248	result := GetScalar('SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = N'''+DefaultDatabase+''' AND TABLE_SCHEMA = N''dbo'' AND TABLE_NAME = N'''+aTableName+'''') > 0;
243	end;	249	end;
244	end;	250	end;
245		251
246	function TAdoConnectionHelper.ViewExist(aViewName: string): boolean;	252	function TAdoConnectionHelper.ViewExist(aViewName: string): boolean;
247	begin	253	begin
248	result := GetScalar('select count(*) FROM sys.views where name = '+SQLStringEscape(aViewName)) > 0;	254	result := GetScalar('select count(*) FROM sys.views where name = '+SQLStringEscape(aViewName)) > 0;
249	end;	255	end;
250		256
251	class function TAdoConnectionHelper.SQLObjectNameEscape(str: string): string;	257	class function TAdoConnectionHelper.SQLObjectNameEscape(str: string): string;
252	begin	258	begin
253	result := '[dbo].[' + str + ']';	259	result := '[dbo].[' + str + ']';
254	end;	260	end;
255		261
256	end.	262	end.
257		263

Subversion Repositories indexer_suite

indexer_suite/trunk/AdoConnHelper.pas – Rev 7 → 8