Code: Select all
MSQuery1.SQL.Clear;
MSQuery1.SQL.Add(' declare @idtab TT_RawMatlIDs ');
MSQuery1.SQL.Add(' insert into @idtab(RawMatlID) values (1), (2), (3) ');
MSQuery1.SQL.Add(' select * from @idtab ');
try
MSQuery1.Open;
MSQuery1.Close;
ShowMessage('Permissions OK');
except
on E: Exception do
ShowMessage('Error: ' + #13#10 + E.Message);
end;Code: Select all
uses
MSAccess;
procedure TForm1.Button1Click(Sender: TObject);
var
conn: TMSConnection;
q: TMSQuery;
sp: TMSStoredProc;
td: TMSTableData;
i: Integer;
begin
Memo1.Lines.Clear;
conn := TMSConnection.Create(nil);
conn.Server := '.';
conn.Database := 'testdb';
conn.Username := 'sa';
conn.Password := '123';
conn.Connect;
q := TMSQuery.Create(nil);
q.Connection := conn;
q.SQL.Add(' if not exists (select 1 from sys.types where name=''type_table_sdac'' and is_table_type=1) ');
q.SQL.Add(' create type type_table_sdac as table (id int) ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_table_type'') ');
q.SQL.Add(' drop procedure proc_test_table_type ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' create procedure proc_test_table_type @tab type_table_sdac readonly, @text nvarchar(max) output ');
q.SQL.Add(' as ');
q.SQL.Add(' begin ');
q.SQL.Add(' select @text=stuff((select isnull('', ''+convert(varchar(20), id), '''') from @tab for xml path('''')), 1, 2, '''') ');
q.SQL.Add(' end ');
q.ExecSQL;
sp := TMSStoredProc.Create(nil);
sp.Connection := conn;
td := TMSTableData.Create(nil);
td.Connection := conn;
td.TableTypeName := 'type_table_sdac';
td.Open;
for i := 0 to 9 do
begin
td.Insert;
td.FieldByName('id').AsInteger := Random(999) + 1;
td.Post;
end;
sp.StoredProcName := 'proc_test_table_type';
sp.ParamByName('tab').AsTable := td.Table;
sp.Execute;
for i := 0 to sp.Params.Count - 1 do
begin
Memo1.Lines.Add('Param index: ' + IntToStr(i));
Memo1.Lines.Add('Param name: ' + sp.Params[i].Name);
Memo1.Lines.Add('Param value: ' + sp.Params[i].Text);
Memo1.Lines.Add('====================================');
end;
sp.Free;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_table_type'') ');
q.SQL.Add(' drop procedure proc_test_table_type ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.types where name=''type_table_sdac'' and is_table_type=1) ');
q.SQL.Add(' drop type type_table_sdac ');
q.ExecSQL;
q.Free;
conn.Disconnect;
conn.Free;
end; Code: Select all
uses
MSAccess;
procedure TForm1.Button1Click(Sender: TObject);
var
conn: TMSConnection;
q: TMSQuery;
sp: TMSStoredProc;
i: Integer;
begin
Memo1.Lines.Clear;
conn := TMSConnection.Create(nil);
conn.Server := '.';
conn.Database := 'testdb';
conn.Username := 'sa';
conn.Password := '123';
conn.Connect;
q := TMSQuery.Create(nil);
q.Connection := conn;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_max'') ');
q.SQL.Add(' drop procedure proc_test_max ');
q.ExecSQL;
q.SQL.Clear;
q.SQL.Add(' create procedure proc_test_max @repeat int, @text nvarchar(max) output ');
q.SQL.Add(' as ');
q.SQL.Add(' begin ');
q.SQL.Add(' declare @i int=0 ');
q.SQL.Add(' select @text='''' ');
q.SQL.Add(' while @i<@repeat ');
q.SQL.Add(' select @text=@text+''sample text '', @i=@i+1 ');
q.SQL.Add(' end ');
q.ExecSQL;
sp := TMSStoredProc.Create(nil);
sp.Connection := conn;
sp.StoredProcName := 'proc_test_max';
sp.ParamByName('repeat').AsInteger := 500;
sp.Execute;
for i := 0 to sp.Params.Count - 1 do
begin
Memo1.Lines.Add('Param index: ' + IntToStr(i));
Memo1.Lines.Add('Param name: ' + sp.Params[i].Name);
Memo1.Lines.Add('Param value: ' + sp.Params[i].Text);
Memo1.Lines.Add('====================================');
end;
sp.Free;
q.SQL.Clear;
q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_max'') ');
q.SQL.Add(' drop procedure proc_test_max ');
q.ExecSQL;
q.Free;
conn.Disconnect;
conn.Free;
end;Code: Select all
procedure TForm1.Button2Click(Sender: TObject);
var
barr: array of byte;
i: Integer;
begin
SetLength(barr, 20);
for i := 0 to 19 do
barr[i] := 65 + i;
MSStoredProc1.StoredProcName := 'test_proc';
MSStoredProc1.ParamByName('param1').AsString := 'param1';
MSStoredProc1.ParamByName('param2').SetBlobData(@barr[0], Length(barr));
MSStoredProc1.ParamByName('param3').AsString := 'param3';
MSStoredProc1.ParamByName('param4').AsInteger := 0;
MSStoredProc1.ExecProc;
end;
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var
MSQuery1: TMSQuery;
i: Integer;
begin
MSQuery1 := TMSQuery.Create(nil);
MSQuery1.Connection := MSConnection1;
MSQuery1.SQL.Text := ' select * from table order by id ';
MSQuery1.Open;
Memo1.Lines.Add('Records count: ' + IntToStr(MSQuery1.RecordCount));
if not MSQuery1.IsEmpty then
begin
MSQuery1.First;
while not MSQuery1.EOF do
begin
Memo1.Lines.Add('Record no: ' + IntToStr(i));
for i := 0 to MSQuery1.FieldCount - 1 do
Memo1.Lines.Add(MSQuery1.Fields[i].FieldName + ': ' + MSQuery1.Fields[i].AsString); //or: MSQuery1.FieldByName(MSQuery1.Fields[i].FieldName).AsString
MSQuery1.Next;
end;
end;
MSQuery1.Close;
end;Code: Select all
uses
pngimage;
procedure TfrmMain.btn_read_from_DBClick(Sender: TObject);
var
BlobStream : TStream;
png: TPngImage;
begin
if not MSConnection1.Connected then
MSConnection1.Connected := true;
try
png: TPngImage.Create;
MSQuery1.Close;
MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('select * from sign where id = ''' + edt_id.Text + '''');
MSQuery1.Open;
BlobStream := MSQuery1.CreateBlobStream(MSQuery1.FieldByName('image1'),TBlobStreamMode.bmRead);
BlobStream.Position:=0;
png.LoadFromStream(BlobStream); // VCL version
Image2.Picture.Graphic:=png;
png.Free;
BlobStream.Free;
except
on E: Exception do
ShowMessage('Fehler bei lesen blob from DB ' + e.Message);
end;
end;Code: Select all
s:='Provider=TDS;Data Source=localhost;User ID=sa;Password=123;Login Prompt=False';
mscon.ConnectString:=s;
if mscon.LoginPrompt then
showmessage('loginpromt is set to true')
else
showmessage('loginpromt is set to false');
Code: Select all
connection.Database := 'Database1';
query.Connection := connection;
query.SQL.Text := 'SELECT * FROM View';
query.Open;
query.Close;
connection.Database := 'Database2';
query.Fields.Clear;
query.Open; Code: Select all
select s.field1, s.field2, s.field3 from
(SELECT
FIELD1,
FIELD2,
FIELD3
FROM TABLE (PLUS JOINS)
WHERE CONDITIONS1
UNION ALL
SELECT
FIELD1,
FIELD2,
FIELD3
FROM TABLE (PLUS SAME JOINS AS ABOVE)
WHERE CONDITIONS2) s