/packages/fcl-db/tests/testfieldtypes.pas
https://github.com/slibre/freepascal · Pascal · 2286 lines · 1929 code · 274 blank · 83 comment · 69 complexity · a80a47ecf165eafd751103b400d33a60 MD5 · raw file
Large files are truncated click here to view the full file
- unit TestFieldTypes;
- {$mode objfpc}{$H+}
- {$modeswitch nestedprocvars}
- interface
- uses
- Classes, SysUtils, fpcunit, testutils, testregistry,
- db;
- type
- TParamProc = procedure(AParam:TParam; i : integer);
- TFieldProc = procedure(AField:TField; i : integer);
- TGetSQLTextProc = function(const i: integer) : string; { is nested;}
- TCheckFieldValueProc = procedure(AField:TField; i : integer) is nested;
- { TTestFieldTypes }
- TTestFieldTypes= class(TTestCase)
- private
- procedure CreateTableWithFieldType(ADatatype : TFieldType; ASQLTypeDecl : string);
- procedure TestFieldDeclaration(ADatatype: TFieldType; ADataSize: integer);
- procedure TestSQLFieldType(ADatatype: TFieldType; ASQLTypeDecl: string;
- ADataSize: integer; AGetSQLTextProc: TGetSQLTextProc;
- ACheckFieldValueProc: TCheckFieldValueProc);
- procedure TestXXParamQuery(ADatatype : TFieldType; ASQLTypeDecl : string; testValuescount : integer; Cross : boolean = false);
- procedure TestSetBlobAsParam(asWhat : integer);
- protected
- procedure SetUp; override;
- procedure TearDown; override;
- procedure RunTest; override;
- published
- procedure TestEmptyUpdateQuery; // bug 13654
- procedure TestParseJoins; // bug 10148
- procedure TestDoubleFieldNames; // bug 8457
- procedure TestParseUnion; // bug 8442
- procedure TestInsertLargeStrFields; // bug 9600
- procedure TestNumericNames; // Bug9661
- procedure TestApplyUpdFieldnames; // Bug 12275;
- procedure TestServerFilter; // bug 15456
- procedure Test11Params;
- procedure TestRowsAffected; // bug 9758
- procedure TestLocateNull;
- procedure TestLocateOnMoreRecords;
- procedure TestStringsReplace;
- procedure TestCircularParams;
- procedure TestBug9744;
- procedure TestCrossStringDateParam;
- procedure TestGetFieldNames;
- procedure TestUpdateIndexDefs;
- procedure TestMultipleFieldPKIndexDefs;
- procedure TestGetIndexDefs;
- procedure TestSetBlobAsMemoParam;
- procedure TestSetBlobAsBlobParam;
- procedure TestSetBlobAsStringParam;
- procedure TestNonNullableParams;
- procedure TestDblQuoteEscComments;
- procedure TestpfInUpdateFlag; // bug 7565
- procedure TestScript;
- procedure TestInsertReturningQuery;
- procedure TestOpenStoredProc;
- procedure TestOpenSpecialStatements;
- procedure TestTemporaryTable;
- procedure TestRefresh;
- procedure TestParametersAndDates;
- procedure TestExceptOnsecClose;
- procedure TestErrorOnEmptyStatement;
- procedure TestBlob;
- procedure TestChangeBlob;
- procedure TestBlobGetText;
- procedure TestBlobSize;
- procedure TestLargeRecordSize;
- procedure TestInt;
- procedure TestNumeric;
- procedure TestFloat;
- procedure TestDate;
- procedure TestDateTime; // bug 6925
- procedure TestString;
- procedure TestUnlVarChar;
- procedure TestNullValues;
- procedure TestParamQuery;
- procedure TestStringParamQuery;
- procedure TestFixedStringParamQuery;
- procedure TestDateParamQuery;
- procedure TestSmallIntParamQuery;
- procedure TestIntParamQuery;
- procedure TestLargeIntParamQuery;
- procedure TestTimeParamQuery;
- procedure TestDateTimeParamQuery;
- procedure TestFmtBCDParamQuery;
- procedure TestFloatParamQuery;
- procedure TestBCDParamQuery;
- procedure TestBytesParamQuery;
- procedure TestVarBytesParamQuery;
- procedure TestBooleanParamQuery;
- procedure TestAggregates;
- procedure TestStringLargerThen8192;
- procedure TestQueryAfterReconnect; // bug 16438
- // SchemaType tests
- procedure TestTableNames;
- procedure TestFieldNames;
- procedure TestClearUpdateableStatus;
- procedure TestReadOnlyParseSQL; // bug 9254
- procedure TestGetTables;
- // Test SQL-field type recognition
- procedure TestSQLClob;
- procedure TestSQLLargeint;
- procedure TestSQLInterval;
- procedure TestSQLIdentity;
- procedure TestSQLReal;
- end;
- implementation
- uses sqldbtoolsunit,toolsunit, variants, sqldb, bufdataset, strutils, dbconst, FmtBCD;
- Type HackedDataset = class(TDataset);
- const
- testFloatValuesCount = 21;
- testFloatValues : Array[0..testFloatValuesCount-1] of double = (-maxSmallint-1,-maxSmallint,-256,-255,-128,-127,-1,0,1,127,128,255,256,maxSmallint,maxSmallint+1,0.123456,-0.123456,4.35,12.434E7,9.876e-5,123.45678);
- testBCDValuesCount = 10;
- testBCDValues : Array[0..testBCDValuesCount-1] of currency = (-100,54.53,1.2345,123.5345,0,1,-1,0,1.42,1324.4324);
- testIntValuesCount = 17;
- testIntValues : Array[0..testIntValuesCount-1] of integer = (-maxInt,-maxSmallint-1,-maxSmallint,-256,-255,-128,-127,-1,0,1,127,128,255,256,maxSmallint,maxSmallint+1,MaxInt);
- testDateValuesCount = 18;
- testDateValues : Array[0..testDateValuesCount-1] of string = (
- '2000-01-01',
- '1999-12-31',
- '2004-02-29',
- '2004-03-01',
- '1991-02-28',
- '1991-03-01',
- '2040-10-16',
- '1977-09-29',
- '1899-12-29',
- '1899-12-30',
- '1899-12-31',
- '1900-01-01',
- '1800-03-30',
- '1754-06-04',
- '1650-05-10',
- '0904-04-12',
- '0199-07-09',
- '0001-01-01'
- );
- testBytesValuesCount = 5;
- testVarBytesValuesCount = 8;
- testBytesValues : Array[0..testVarBytesValuesCount-1] of shortstring = (
- #1#0#1#0#1, #0#0#1#0#1, #0''''#13#0#1, '\'#0'"\'#13, #13#13#0#10#10,
- '', #0, #0#1#2#3#4#5#6#7#8#9
- );
- STestNotApplicable = 'This test does not apply to this sqldb-connection type';
- procedure TTestFieldTypes.TestpfInUpdateFlag;
- var ds : TCustomBufDataset;
- AFld1, AFld2, AFld3 : Tfield;
- begin
- ds := (DBConnector.GetNDataset(True,5) as TCustomBufDataset);
- with ds do
- begin
- AFld1 := TIntegerField.Create(ds);
- AFld1.FieldName := 'ID';
- AFld1.DataSet := ds;
- AFld1.ProviderFlags := AFld1.ProviderFlags + [pfInKey];
- AFld2 := TStringField.Create(ds);
- AFld2.FieldName := 'NAME';
- AFld2.DataSet := ds;
- AFld3 := TIntegerField.Create(ds);
- AFld3.FieldName := 'CALCFLD';
- AFld3.DataSet := ds;
- Afld3.FieldKind := fkCalculated;
- AFld3.ProviderFlags := []; // do not include calculated fields into generated sql insert/update
- Open;
- Edit;
- FieldByName('ID').AsInteger := 254;
- Post;
- ApplyUpdates;
- Append;
- FieldByName('ID').AsInteger := 255;
- Post;
- ApplyUpdates;
- Close;
- AFld1.Free;
- AFld2.Free;
- AFld3.Free;
- end;
- end;
- procedure TTestFieldTypes.TestScript;
- var Ascript : TSQLScript;
- begin
- Ascript := tsqlscript.create(nil);
- try
- with Ascript do
- begin
- DataBase := TSQLDBConnector(DBConnector).Connection;
- transaction := TSQLDBConnector(DBConnector).Transaction;
- script.clear;
- script.append('create table a (id int);');
- script.append('create table b (id int);');
- ExecuteScript;
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- end;
- finally
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('drop table a');
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('drop table b');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- end;
- end;
- procedure TTestFieldTypes.TestLargeRecordSize;
- begin
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('create table FPDEV2 (plant varchar(8192),sampling_type varchar(8192),area varchar(8192), area_description varchar(8192), batch varchar(8192), sampling_datetime timestamp, status varchar(8192), batch_commentary varchar(8192))');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.clear;
- sql.append('insert into FPDEV2 (plant,sampling_type,batch,sampling_datetime,status,batch_commentary) values (''ZUBNE PASTE'',''OTISCI POVRINA'',''000037756'',''2005-07-01'',''NE ODGOVARA'',''Ovdje se upisuje komentar o kontrolnom broju..............'')');
- ExecSQL;
- sql.clear;
- sql.append('select * from FPDEV2');
- open;
- AssertEquals('ZUBNE PASTE',FieldByName('plant').AsString);
- AssertEquals(EncodeDate(2005,07,01),FieldByName('sampling_datetime').AsDateTime);
- close;
- end;
- end;
- procedure TTestFieldTypes.CreateTableWithFieldType(ADatatype: TFieldType;
- ASQLTypeDecl: string);
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- Connection.ExecuteDirect('create table FPDEV2 (FT ' +ASQLTypeDecl+ ')');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- CommitDDL;
- end;
- end;
- procedure TTestFieldTypes.TestFieldDeclaration(ADatatype: TFieldType;
- ADataSize: integer);
- begin
- with TSQLDBConnector(DBConnector).Query do
- begin
- SQL.Clear;
- SQL.Add('select * from FPDEV2');
- Open;
- AssertEquals(1,FieldCount);
- AssertTrue(CompareText('FT',fields[0].FieldName)=0);
- AssertEquals('DataSize', ADataSize, Fields[0].DataSize);
- AssertEquals('DataType', ord(ADatatype), ord(Fields[0].DataType));
- Close;
- end;
- end;
- procedure TTestFieldTypes.TestInt;
- var
- i : byte;
- begin
- CreateTableWithFieldType(ftInteger,'INT');
- TestFieldDeclaration(ftInteger,4);
- for i := 0 to testIntValuesCount-1 do
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (' + inttostr(testIntValues[i]) + ')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- for i := 0 to testIntValuesCount-1 do
- begin
- AssertEquals(testIntValues[i],fields[0].AsInteger);
- Next;
- end;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestNumeric;
- const
- testValuesCount = 13;
- testValues4 : Array[0..testValuesCount-1] of currency = (-99.99,-12.34,-10.2,-10,-0.5,-0.01,0,0.01,0.5,10,10.2,12.34,99.99);
- testValues9 : Array[0..testValuesCount-1] of currency = (-123456.789,-10000,-1875.25,-10,-0.5,-0.001,0,0.001,0.5,10,1875.25,10000,123456.789);
- FieldTypes: array [0..7] of TFieldType = (ftBCD, ftBCD, ftBCD, ftFmtBCD, ftLargeInt, ftFmtBCD, ftFmtBCD, ftFmtBCD);
- FieldSizes: array [0..7] of integer = (4,2,3,5,0,3,5,0); //scale
- var
- i,d : integer;
- s,s4,s9 : string;
- t : TFieldType;
- begin
- with TSQLDBConnector(DBConnector) do begin
- if SQLConnType = INTERBASE then
- begin
- //Interbase internal storage of exact numeric data types based on precision:
- // 1-4 (smallint), 5-9 (integer), 10-18 (int64)
- s := ''; //Interbase supports precision up to 18 only
- FieldTypes[5] := ftBCD; //ATM TIBConnection incorrectly maps NUMERIC(18,3) to ftBCD
- end
- else
- s := ', N19_0 NUMERIC(19,0)';
- Connection.ExecuteDirect('create table FPDEV2 (FT NUMERIC(18,4), N4_2 NUMERIC(4,2), N9_3 NUMERIC(9,3), N9_5 NUMERIC(9,5), N18_0 NUMERIC(18,0), N18_3 NUMERIC(18,3), N18_5 NUMERIC(18,5)' + s + ')');
- CommitDDL;
- with Query do
- begin
- SQL.Text := 'select * from FPDEV2';
- Open;
- for i := 0 to FieldCount-1 do
- begin
- case Fields[i].DataType of
- ftBCD: d := sizeof(Currency);
- ftFmtBCD: d := sizeof(TBCD);
- ftLargeInt: d := sizeof(int64);
- else d := 0;
- end;
- t := FieldTypes[i];
- if t = ftLargeInt then t := ftFmtBCD; //acceptable alternative
- AssertEquals(Fields[i].DataSize, d);
- AssertTrue(Fields[i].DataType in [FieldTypes[i], t]);
- AssertEquals(Fields[i].Size, FieldSizes[i]);
- end;
- Close;
- end;
- for i := 0 to testValuesCount-1 do
- begin
- s4 := CurrToStrF(testValues4[i],ffFixed,2,DBConnector.FormatSettings);
- s9 := CurrToStrF(testValues9[i],ffFixed,3,DBConnector.FormatSettings);
- Connection.ExecuteDirect(format('insert into FPDEV2 (N4_2,N9_5,FT,N9_3,N18_3,N18_5) values (%s,%s,%s,%s,%s,%s)', [s4,s4,s9,s9,s9,s9]));
- end;
- with Query do
- begin
- Open;
- for i := 0 to testValuesCount-1 do
- begin
- AssertEquals(testValues4[i], Fields[1].AsCurrency);
- AssertEquals(testValues4[i], Fields[3].AsCurrency);
- AssertEquals(testValues9[i], Fields[0].AsCurrency);
- AssertEquals(testValues9[i], Fields[2].AsCurrency);
- AssertEquals(testValues9[i], Fields[5].AsCurrency);
- AssertEquals(testValues9[i], Fields[6].AsCurrency);
- Next;
- end;
- Close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestFloat;
- const
- testValuesCount = 21;
- testValues : Array[0..testValuesCount-1] of double = (-maxSmallint-1,-maxSmallint,-256,-255,-128,-127,-1,0,1,127,128,255,256,maxSmallint,maxSmallint+1,0.123456,-0.123456,4.35,12.434E7,9.876e-5,123.45678);
- var
- i : byte;
- begin
- CreateTableWithFieldType(ftFloat,'FLOAT');
- TestFieldDeclaration(ftFloat,sizeof(double));
- for i := 0 to testValuesCount-1 do
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (' + floattostr(testValues[i],DBConnector.FormatSettings) + ')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- for i := 0 to testValuesCount-1 do
- begin
- AssertEquals(testValues[i],fields[0].AsFloat);
- Next;
- end;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestString;
- const
- testValuesCount = 19;
- testValues : Array[0..testValuesCount-1] of string = (
- '',
- 'a',
- 'ab',
- 'abc',
- 'abcd',
- 'abcde',
- 'abcdef',
- 'abcdefg',
- 'abcdefgh',
- 'abcdefghi',
- 'abcdefghij',
- 'lMnOpQrStU',
- '1234567890',
- '_!@#$%^&*(',
- ')-;:/?.<>',
- '~`|{}- =', // note that there's no \ (backslash) since some db's uses that as escape-character
- ' WRaP ',
- 'wRaP ',
- ' wRAP'
- );
- var
- i : byte;
- begin
- CreateTableWithFieldType(ftString,'VARCHAR(10)');
- TestFieldDeclaration(ftString,11);
- for i := 0 to testValuesCount-1 do
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''' + testValues[i] + ''')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- for i := 0 to testValuesCount-1 do
- begin
- AssertEquals(testValues[i], Fields[0].AsString);
- Next;
- end;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestUnlVarChar;
- const
- testValuesCount = 21;
- testValues : Array[0..testValuesCount-1] of string = (
- '',
- 'a',
- 'ab',
- 'abc',
- 'abcd',
- 'abcde',
- 'abcdef',
- 'abcdefg',
- 'abcdefgh',
- 'abcdefghi',
- 'abcdefghij',
- 'lMnOpQrStU',
- '1234567890',
- '_!@#$%^&*(',
- ')-;:/?.<>',
- '~`|{}- =',
- ' WRaP ',
- 'wRaP ',
- ' wRAP',
- '0123456789',
- 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 !@#$%^&*()_+-=][|}{;:,./<>?abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 !@#$%^&*()_+-=][|}{;:,./<>?' + 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 !@#$%^&*()_+-=][|}{;:,./<>?'
- + 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 !@#$%^&*()_+-=][|}{;:,./<>?abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 !@#$%^&*()_+-=][|}{;:,./<>?' + 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 !@#$%^&*()_+-=][|}{;:,./<>?'
- );
- var
- i : byte;
- begin
- if SQLConnType<>postgresql then Ignore('This test does only apply to Postgres, since others don''t support varchars without length given');
- CreateTableWithFieldType(ftString,'VARCHAR');
- TestFieldDeclaration(ftString,dsMaxStringSize+1);
- for i := 0 to testValuesCount-1 do
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''' + testValues[i] + ''')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- for i := 0 to testValuesCount-1 do
- begin
- AssertEquals(testValues[i],fields[0].AsString);
- Next;
- end;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestDate;
- var
- i : byte;
- begin
- CreateTableWithFieldType(ftDate,'DATE');
- TestFieldDeclaration(ftDate,8);
- for i := 0 to testDateValuesCount-1 do
- if SQLConnType=oracle then
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (to_date (''' + testDateValues[i] + ''',''YYYY-MM-DD''))')
- else
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''' + testDateValues[i] + ''')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- for i := 0 to testDateValuesCount-1 do
- begin
- AssertEquals(testDateValues[i],FormatDateTime('yyyy/mm/dd', fields[0].AsDateTime, DBConnector.FormatSettings));
- Next;
- end;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestDateTime;
- const
- testValuesCount = 31;
- testValues : Array[0..testValuesCount-1] of string = (
- '2000-01-01',
- '1999-12-31',
- '2004-02-29',
- '2004-03-01',
- '1991-02-28',
- '1991-03-01',
- '1977-09-29',
- '2000-01-01 10:00:00',
- '2000-01-01 23:59:59',
- '1994-03-06 11:54:30',
- '2040-10-16', // MySQL 4.0 doesn't support datetimes before 1970 or after 2038
- '2100-01-01 01:01:01',
- '1903-04-02 01:04:02',
- '1900-01-01',
- '1899-12-31',
- '1899-12-30',
- '1899-12-29',
- '1899-12-30 18:00:51',
- '1899-12-30 04:00:51',
- '1899-12-29 04:00:51',
- '1899-12-29 18:00:51',
- '1815-09-24 03:47:22',
- '1800-03-30',
- '1754-06-04',
- '1650-05-10', // MS SQL 2005 doesn't support datetimes before 1753
- '1400-02-03 12:21:53',
- '1333-02-03 21:44:21',
- '0904-04-12',
- '0354-11-20 21:25:15',
- '0199-07-09',
- '0001-01-01'
- );
- var
- i : byte;
- begin
- CreateTableWithFieldType(ftDateTime,FieldtypeDefinitions[ftDateTime]);
- TestFieldDeclaration(ftDateTime,8);
- for i := 0 to testValuesCount-1 do
- if SQLConnType=oracle then
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (to_date (''' + testValues[i] + ''',''YYYY-MM-DD HH24:MI:SS''))')
- else
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''' + testValues[i] + ''')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- for i := 0 to testValuesCount-1 do
- begin
- if length(testValues[i]) < 12 then
- AssertEquals(testValues[i],FormatDateTime('yyyy/mm/dd', fields[0].AsDateTime, DBConnector.FormatSettings))
- else
- AssertEquals(testValues[i],FormatDateTime('yyyy/mm/dd hh:mm:ss', fields[0].AsDateTime, DBConnector.FormatSettings));
- Next;
- end;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestChangeBlob;
- var s : string;
- begin
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('create table FPDEV2 (ID int,FT '+FieldtypeDefinitions[ftblob]+')');
- TSQLDBConnector(DBConnector).CommitDDL;
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (ID,FT) values (1,''Test deze blob'')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.clear;
- sql.add('select * from FPDEV2');
- Open;
- fields[1].ProviderFlags := [pfInUpdate]; // blob niet in de where
- UpdateMode := upWhereAll;
- AssertEquals('Test deze blob',fields[1].AsString);
- edit;
- // Dat werkt niet lekker, omdat de stream vernield wordt...
- // fields[0].asstring := 'Deze blob is gewijzigd!';
- With Createblobstream(fields[1],bmwrite) do
- begin
- s := 'Deze blob is gewijzigd!';
- WriteBuffer(Pointer(s)^,Length(s));
- post;
- free;
- end;
- AssertEquals('Deze blob is gewijzigd!',fields[1].AsString);
- ApplyUpdates(0);
- TSQLDBConnector(DBConnector).Transaction.CommitRetaining; // For debug-purposes
- close;
- open;
- AssertEquals('Deze blob is gewijzigd!',fields[1].AsString);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestBlobGetText;
- begin
- CreateTableWithFieldType(ftBlob,FieldtypeDefinitions[ftBlob]);
- TestFieldDeclaration(ftBlob,0);
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''Test deze blob'')');
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (Null)');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- AssertFalse(fields[0].IsNull);
- AssertEquals('(BLOB)',fields[0].DisplayText);
- AssertEquals('Test deze blob',fields[0].AsString);
- Next;
- AssertTrue(fields[0].IsNull);
- AssertEquals('(blob)',fields[0].Text);
- AssertEquals('',fields[0].AsString);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestBlobSize;
- begin
- CreateTableWithFieldType(ftBlob,FieldtypeDefinitions[ftBlob]);
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''Test deze blob'')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.text := 'select * from FPDEV2';
- Open;
- AssertEquals(14,TBlobField(fields[0]).BlobSize);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestBlob;
- begin
- CreateTableWithFieldType(ftBlob,FieldtypeDefinitions[ftBlob]);
- TestFieldDeclaration(ftBlob,0);
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''Test deze blob'')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- AssertEquals('Test deze blob',fields[0].AsString);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestNullValues;
- begin
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('create table FPDEV2 (FIELD1 INT, FIELD2 INT)');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FIELD1) values (1)');
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.clear;
- sql.append('select * from FPDEV2');
- open;
- AssertEquals(1,FieldByName('FIELD1').AsInteger);
- AssertTrue('Null-values test failed',FieldByName('FIELD2').IsNull);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestParamQuery;
- // Tests running insert queries using parameters
- const
- DecoyFieldData1='decoytest';
- DecoyFieldData2=':decoy ::test $decoy2 $$2';
- begin
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('create table FPDEV2 (FIELD1 INT, FIELD2 INT, FIELD3 INT, DECOY VARCHAR(30))');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.clear;
- sql.append('insert into FPDEV2 (field1) values (:field1)');
- Params.ParamByName('field1').AsInteger := 1;
- ExecSQL;
- sql.clear;
- sql.append('insert into FPDEV2 (field1,field2,decoy) values (:field1,:field2,'''+DecoyFieldData1+''')');
- Params.ParamByName('field1').AsInteger := 2;
- Params.ParamByName('field2').DataType := ftInteger;
- Params.ParamByName('field2').Value := Null;
- ExecSQL;
- sql.clear;
- sql.append('insert into FPDEV2 (field1,field2,field3) values (:field1,:field2,:field3)');
- Params.ParamByName('field1').AsInteger := 3;
- Params.ParamByName('field2').AsInteger := 2;
- Params.ParamByName('field3').AsInteger := 3;
- ExecSQL;
- sql.clear;
- sql.append('insert into FPDEV2 (field1,field2,field3,decoy) values (:field1,:field2,:field3,'''+DecoyFieldData2+''')');
- Params.ParamByName('field1').AsInteger := 4;
- Params.ParamByName('field2').AsInteger := 2;
- Params.ParamByName('field3').AsInteger := 3;
- ExecSQL;
- sql.clear;
- sql.append('insert into FPDEV2 (field1,field2,field3) values (:field1,:field2,:field1)');
- Params.ParamByName('field1').AsInteger := 5;
- Params.ParamByName('field2').AsInteger := 2;
- ExecSQL;
- sql.clear;
- sql.append('select * from FPDEV2 order by FIELD1');
- open;
- AssertEquals(1,FieldByName('FIELD1').asinteger);
- AssertTrue(FieldByName('FIELD2').IsNull);
- AssertTrue(FieldByName('FIELD3').IsNull);
- AssertTrue(FieldByName('DECOY').IsNull);
- next;
- AssertEquals(2,FieldByName('FIELD1').asinteger);
- AssertTrue(FieldByName('FIELD2').IsNull);
- AssertTrue(FieldByName('FIELD3').IsNull);
- AssertEquals(DecoyFieldData1,FieldByName('DECOY').AsString);
- next;
- AssertEquals(3,FieldByName('FIELD1').asinteger);
- AssertEquals(2,FieldByName('FIELD2').asinteger);
- AssertEquals(3,FieldByName('FIELD3').asinteger);
- AssertTrue(FieldByName('DECOY').IsNull);
- next;
- AssertEquals(4,FieldByName('FIELD1').asinteger);
- AssertEquals(2,FieldByName('FIELD2').asinteger);
- AssertEquals(3,FieldByName('FIELD3').asinteger);
- AssertEquals(DecoyFieldData2,FieldByName('DECOY').AsString);
- next;
- AssertEquals(5,FieldByName('FIELD1').asinteger);
- AssertEquals(2,FieldByName('FIELD2').asinteger);
- AssertEquals(5,FieldByName('FIELD3').asinteger);
- AssertTrue(FieldByName('DECOY').IsNull);
- close;
- end;
- TSQLDBConnector(DBConnector).Transaction.CommitRetaining;
- end;
- procedure TTestFieldTypes.TestSmallIntParamQuery;
- begin
- TestXXParamQuery(ftSmallInt,FieldtypeDefinitions[ftSmallInt],testValuesCount);
- end;
- procedure TTestFieldTypes.TestIntParamQuery;
- begin
- TestXXParamQuery(ftInteger,'INT',testIntValuesCount);
- end;
- procedure TTestFieldTypes.TestLargeIntParamQuery;
- begin
- TestXXParamQuery(ftLargeInt,FieldtypeDefinitions[ftLargeInt],testValuesCount);
- end;
- procedure TTestFieldTypes.TestFmtBCDParamQuery;
- begin
- TestXXParamQuery(ftFMTBcd,FieldtypeDefinitions[ftFMTBcd],testValuesCount);
- end;
- procedure TTestFieldTypes.TestDateParamQuery;
- begin
- TestXXParamQuery(ftDate,FieldtypeDefinitions[ftDate],testDateValuesCount);
- end;
- procedure TTestFieldTypes.TestCrossStringDateParam;
- begin
- TestXXParamQuery(ftDate,FieldtypeDefinitions[ftDate],testDateValuesCount,True);
- end;
- procedure TTestFieldTypes.TestTimeParamQuery;
- begin
- TestXXParamQuery(ftTime,FieldtypeDefinitions[ftTime],testValuesCount);
- end;
- procedure TTestFieldTypes.TestDateTimeParamQuery;
- begin
- TestXXParamQuery(ftDateTime,FieldtypeDefinitions[ftDateTime],testValuesCount);
- end;
- procedure TTestFieldTypes.TestFloatParamQuery;
- begin
- TestXXParamQuery(ftFloat,FieldtypeDefinitions[ftFloat],testFloatValuesCount);
- end;
- procedure TTestFieldTypes.TestBCDParamQuery;
- begin
- TestXXParamQuery(ftBCD,'NUMERIC(10,4)',testBCDValuesCount);
- end;
- procedure TTestFieldTypes.TestBytesParamQuery;
- begin
- TestXXParamQuery(ftBytes, FieldtypeDefinitions[ftBytes], testBytesValuesCount, true);
- end;
- procedure TTestFieldTypes.TestVarBytesParamQuery;
- begin
- TestXXParamQuery(ftVarBytes, FieldtypeDefinitions[ftVarBytes], testVarBytesValuesCount, not(SQLServerType in [ssMSSQL, ssSybase]));
- end;
- procedure TTestFieldTypes.TestBooleanParamQuery;
- begin
- TestXXParamQuery(ftBoolean, FieldtypeDefinitions[ftBoolean], testValuesCount);
- end;
- procedure TTestFieldTypes.TestStringParamQuery;
- begin
- TestXXParamQuery(ftString,'VARCHAR(10)',testValuesCount);
- end;
- procedure TTestFieldTypes.TestFixedStringParamQuery;
- begin
- TestXXParamQuery(ftFixedChar,'CHAR(10)',testValuesCount);
- end;
- procedure TTestFieldTypes.TestXXParamQuery(ADatatype : TFieldType; ASQLTypeDecl : string; testValuesCount : integer; Cross : boolean = false);
- var i : integer;
- begin
- if ASQLTypeDecl = '' then
- Ignore('Fields of the type ' + FieldTypeNames[ADatatype] + ' are not supported by this sqldb-connection type');
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('create table FPDEV2 (ID INT, FIELD1 '+ASQLTypeDecl+')');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.clear;
- sql.append('insert into FPDEV2 (ID,FIELD1) values (:id,:field1)');
- // There is no Param.AsFixedChar, so the datatype has to be set manually
- if ADatatype=ftFixedChar then
- Params.ParamByName('field1').DataType := ftFixedChar;
- for i := 0 to testValuesCount -1 do
- begin
- Params.ParamByName('id').AsInteger := i;
- case ADataType of
- ftSmallInt: Params.ParamByName('field1').AsSmallInt := testSmallIntValues[i];
- ftInteger: Params.ParamByName('field1').AsInteger := testIntValues[i];
- ftLargeInt: Params.ParamByName('field1').AsLargeInt := testLargeIntValues[i];
- ftBoolean: Params.ParamByName('field1').AsBoolean := testBooleanValues[i];
- ftFloat : Params.ParamByName('field1').AsFloat := testFloatValues[i];
- ftBCD : Params.ParamByName('field1').AsCurrency:= testBCDValues[i];
- ftFixedChar,
- ftString : Params.ParamByName('field1').AsString := testValues[ADataType,i];
- ftTime : Params.ParamByName('field1').AsTime := TimeStringToDateTime(testTimeValues[i]);
- ftDate : if cross then
- Params.ParamByName('field1').AsString:= testDateValues[i]
- else
- Params.ParamByName('field1').AsDate := StrToDate(testDateValues[i],'yyyy/mm/dd','-');
- ftDateTime:Params.ParamByName('field1').AsDateTime := StrToDateTime(testValues[ADataType,i], DBConnector.FormatSettings);
- ftFMTBcd : Params.ParamByName('field1').AsFMTBCD := StrToBCD(testFmtBCDValues[i],DBConnector.FormatSettings);
- ftBytes : if cross then
- Params.ParamByName('field1').Value := StringToByteArray(testBytesValues[i])
- else
- Params.ParamByName('field1').AsBlob := testBytesValues[i];
- ftVarBytes:if cross then
- Params.ParamByName('field1').AsString := testBytesValues[i]
- else
- Params.ParamByName('field1').AsBlob := testBytesValues[i];
- else
- AssertTrue('no test for paramtype available',False);
- end;
- ExecSQL;
- end;
- TSQLDBConnector(DBConnector).Transaction.CommitRetaining;
- sql.clear;
- sql.append('select * from FPDEV2 order by ID');
- open;
- for i := 0 to testValuesCount -1 do
- begin
- AssertEquals(i,FieldByName('ID').AsInteger);
- case ADataType of
- ftSmallInt: AssertEquals(testSmallIntValues[i],FieldByName('FIELD1').AsInteger);
- ftInteger: AssertEquals(testIntValues[i],FieldByName('FIELD1').AsInteger);
- ftLargeInt: AssertEquals(testLargeIntValues[i],FieldByName('FIELD1').AsLargeInt);
- ftBoolean: AssertEquals(testBooleanValues[i],FieldByName('FIELD1').AsBoolean);
- ftFloat : AssertEquals(testFloatValues[i],FieldByName('FIELD1').AsFloat);
- ftBCD : AssertEquals(testBCDValues[i],FieldByName('FIELD1').AsCurrency);
- ftFixedChar : AssertEquals(PadRight(testStringValues[i],10),FieldByName('FIELD1').AsString);
- ftString : AssertEquals(testStringValues[i],FieldByName('FIELD1').AsString);
- ftTime : AssertEquals(testTimeValues[i],DateTimeToTimeString(FieldByName('FIELD1').AsDateTime));
- ftDate : AssertEquals(testDateValues[i],DateTimeToStr(FieldByName('FIELD1').AsDateTime, DBConnector.FormatSettings));
- ftDateTime : AssertEquals(testValues[ADataType,i], DateTimeToStr(FieldByName('FIELD1').AsDateTime, DBConnector.FormatSettings));
- ftFMTBcd : AssertEquals(testFmtBCDValues[i], BCDToStr(FieldByName('FIELD1').AsBCD, DBConnector.FormatSettings));
- ftVarBytes,
- ftBytes : AssertEquals(testBytesValues[i], shortstring(FieldByName('FIELD1').AsString));
- else
- AssertTrue('no test for paramtype available',False);
- end;
- Next;
- end;
- close;
- end;
- TSQLDBConnector(DBConnector).Transaction.CommitRetaining;
- end;
- procedure TTestFieldTypes.TestSetBlobAsParam(asWhat: integer);
- const
- TestValue='Test deze BLob';
- var
- ASQL : TSQLQuery;
- begin
- CreateTableWithFieldType(ftBlob,FieldtypeDefinitions[ftBlob]);
- TestFieldDeclaration(ftBlob,0);
- ASQL := DBConnector.GetNDataset(True,1) as tsqlquery;
- with ASql do
- begin
- sql.Text := 'insert into FPDEV2 (FT) values (:BlobParam)';
- case asWhat of
- 0: Params.ParamByName('blobParam').AsMemo := TestValue;
- 1: Params.ParamByName('blobParam').AsString := TestValue;
- 2: Params.ParamByName('blobParam').AsBlob := TestValue;
- end;
- ExecSQL;
- end;
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- if not eof then
- AssertEquals(TestValue, Fields[0].AsString);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestSetBlobAsMemoParam;
- begin
- // Firebird/Interbase ODBC driver : if parameter of ValueType=SQL_C_CHAR is bind to BLOB column
- // driver interprets character data as hexadecimal string and performs conversion ('FF10'=#255#16)
- TestSetBlobAsParam(0);
- end;
- procedure TTestFieldTypes.TestSetBlobAsStringParam;
- begin
- TestSetBlobAsParam(1);
- end;
- procedure TTestFieldTypes.TestSetBlobAsBlobParam;
- begin
- TestSetBlobAsParam(2);
- end;
- procedure TTestFieldTypes.TestAggregates;
- begin
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('create table FPDEV2 (FIELD1 INT, FIELD2 INT)');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 values (1,1)');
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 values (2,3)');
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 values (3,4)');
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 values (4,4)');
- TSQLDBConnector(DBConnector).Transaction.CommitRetaining;
- with TSQLDBConnector(DBConnector).Query do
- begin
- sql.clear;
- sql.append('select count(*) from FPDEV2');
- open;
- AssertEquals(4,Fields[0].AsInteger);
- close;
- sql.clear;
- sql.append('select sum(FIELD1) from FPDEV2');
- open;
- AssertEquals(10,Fields[0].AsInteger);
- close;
- sql.clear;
- sql.append('select avg(FIELD2) from FPDEV2');
- open;
- AssertEquals(3,Fields[0].AsInteger);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestQueryAfterReconnect;
- var DS: TDataset;
- begin
- ds := DBConnector.GetNDataset(true,5);
- with ds do
- begin
- open;
- close;
- TSQLDBConnector(DBConnector).Connection.Close;
- TSQLDBConnector(DBConnector).Connection.Open;
- open;
- close;
- end;
- end;
- procedure TTestFieldTypes.TestLocateNull;
- var DS: TCustomBufDataset;
- begin
- ds := TSQLDBConnector(DBConnector).GetNDataset(true,5) as TCustomBufDataset;
- with ds do
- begin
- open;
- edit;
- fieldbyname('name').Clear;
- post;
- next;
- AssertFalse(Locate('name',VarArrayOf(['TestName1']),[]));
- AssertTrue(Locate('name',VarArrayOf([Null]),[]));
- AssertEquals(1,fieldbyname('ID').AsInteger);
- end;
- end;
- procedure TTestFieldTypes.TestLocateOnMoreRecords;
- var DS: TCustomBufDataset;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- ds := GetNDataset(true,30) as TCustomBufDataset;
- with query do
- begin
- SQL.Text:='update FPDEV set NAME = null where ID<11;';
- ExecSQL;
- SQL.Text:='update FPDEV set NAME = null where (ID>11) and (ID<23);';
- ExecSQL;
- end;
- with ds do
- begin
- Open;
- // Must be exactly 11 to trigger bug/test
- AssertTrue(Locate('name',VarArrayOf(['TestName11']),[]));
- AssertEquals(11,fieldbyname('ID').AsInteger);
- // Must be exactly 23 to trigger bug/test
- AssertTrue(Locate('name',VarArrayOf(['TestName23']),[]));
- AssertEquals(23,fieldbyname('ID').AsInteger);
- end;
- end;
- end;
- procedure TTestFieldTypes.TestRefresh;
- var ADataset: TDataset;
- i: integer;
- AFldID, AFldName: TField;
- begin
- ADataset := TSQLDBConnector(DBConnector).GetNDataset(true,5);
- Adataset.Open;
- AFldId:=Adataset.Fields[0];
- AFldName:=Adataset.Fields[1];
- for i := 1 to 5 do
- begin
- AssertEquals(i,AFldID.asinteger);
- AssertEquals('TestName'+inttostr(i),AFldName.asstring);
- ADataset.Next;
- end;
- ADataset.Next;
- AssertTrue(ADataset.EOF);
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('update FPDEV set NAME=''test'' where ID=2');
- ADataset.Refresh;
- ADataset.First;
- for i := 1 to 5 do
- begin
- AssertEquals(i,AFldID.AsInteger);
- if i = 2 then
- AssertEquals('test',AFldName.AsString)
- else
- AssertEquals('TestName'+inttostr(i),AFldName.AsString);
- ADataset.Next;
- end;
- ADataset.Next;
- AssertTrue(ADataset.EOF);
- end;
- procedure TTestFieldTypes.TestEmptyUpdateQuery;
- begin
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('update FPDEV set name=''nothing'' where (1=0)');
- end;
- procedure TTestFieldTypes.TestNonNullableParams;
- var ASQLQuery : TSQLQuery;
- Passed: Boolean;
- begin
- // Check for an exception when a null value is stored into a non-nullable
- // field using a parameter
- // There was a bug in IBConnection so that in this case the last used value
- // for the parameter was used.
- // To make sure that any changes are cancelled in the case the test fails
- TSQLDBConnector(DBConnector).GetNDataset(true,5);
- ASQLQuery := TSQLDBConnector(DBConnector).Query;
- ASQLQuery.SQL.text := 'update fpdev set ID=:ID1 where id = :ID2';
- ASQLQuery.Params[0].Clear;
- ASQLQuery.Params[1].AsInteger := 1;
- AssertTrue(ASQLQuery.Params[0].IsNull);
- Passed:=False;
- try
- @ASQLQuery.ExecSQL;
- except
- on E: Exception do
- if E.ClassType.InheritsFrom(EDatabaseError) then
- Passed := true;
- end;
- AssertTrue(Passed);
- end;
- procedure TTestFieldTypes.TestStringLargerThen8192;
- // See also: TestInsertLargeStrFields
- var
- s : string;
- i : integer;
- begin
- CreateTableWithFieldType(ftString,'VARCHAR(9000)');
- TestFieldDeclaration(ftString,9001);
- setlength(s,9000);
- for i := 1 to 9000 do
- s[i]:=chr((i mod 10)+ord('a'));
- TSQLDBConnector(DBConnector).Connection.ExecuteDirect('insert into FPDEV2 (FT) values (''' + s + ''')');
- with TSQLDBConnector(DBConnector).Query do
- begin
- Open;
- AssertEquals(s,fields[0].AsString);
- close;
- end;
- end;
- procedure TTestFieldTypes.TestTableNames;
- var TableList : TStringList;
- i : integer;
- begin
- TableList := TStringList.Create;
- try
- TSQLDBConnector(DBConnector).Connection.GetTableNames(TableList);
- AssertTrue(TableList.Find('fpdev',i));
- finally
- TableList.Free;
- end;
- end;
- procedure TTestFieldTypes.TestFieldNames;
- var FieldList : TStringList;
- i : integer;
- begin
- FieldList := TStringList.Create;
- try
- TSQLDBConnector(DBConnector).Connection.GetFieldNames('fpdev',FieldList);
- AssertTrue(FieldList.Find('id',i));
- finally
- FieldList.Free;
- end;
- end;
- procedure TTestFieldTypes.TestInsertReturningQuery;
- begin
- if not(SQLServerType in [ssFirebird, ssOracle, ssPostgreSQL]) then Ignore(STestNotApplicable);
- with TSQLDBConnector(DBConnector) do
- begin
- // This only works with databases that supports 'insert into .. returning'
- // for example: PostgreSQL, Oracle, Firebird version 2.0 and up
- CreateTableWithFieldType(ftInteger,'int');
- Query.SQL.Text:='insert into FPDEV2 values(154) returning FT';
- Query.Open;
- AssertTrue(CompareText('FT',Query.Fields[0].FieldName)=0);
- AssertEquals(154,Query.fields[0].AsInteger);
- Query.Close;
- end;
- end;
- procedure TTestFieldTypes.TestOpenStoredProc;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- case SQLServerType of
- ssMySQL:
- begin
- Connection.ExecuteDirect('create procedure FPDEV_PROC() select 1 union select 2;');
- Query.SQL.Text:='call FPDEV_PROC';
- end;
- ssFirebird, ssInterbase:
- begin
- Connection.ExecuteDirect('create procedure FPDEV_PROC returns (r integer) as begin r=1; end');
- Query.SQL.Text:='execute procedure FPDEV_PROC';
- end;
- ssMSSQL, ssSybase:
- begin
- Connection.ExecuteDirect('create procedure FPDEV_PROC as select 1 union select 2;');
- Query.SQL.Text:='execute FPDEV_PROC';
- end;
- else
- begin
- Ignore('This test does not apply to this sqldb-connection type, since it does not support selectable stored procedures.');
- Exit;
- end;
- end;
- Transaction.CommitRetaining;
- try
- Query.Open;
- AssertEquals(1, Query.Fields[0].AsInteger);
- Query.Next;
- if not(SQLServerType in [ssFirebird, ssInterbase]) then
- begin
- AssertFalse('Eof after 1st row', Query.Eof);
- AssertEquals(2, Query.Fields[0].AsInteger);
- Query.Next;
- end;
- AssertTrue('No Eof after last row', Query.Eof);
- Query.Close;
- finally
- Connection.ExecuteDirect('drop procedure FPDEV_PROC');
- Transaction.CommitRetaining;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestOpenSpecialStatements;
- const CTE_SELECT = 'WITH a AS (SELECT * FROM FPDEV) SELECT * FROM a';
- type TTestStatements = array of string;
- var statements: TTestStatements;
- s: string;
- begin
- // tests non-select statements (other than "SELECT ..."), which return result-set
- // at least one row must be returned
- with TSQLDBConnector(DBConnector) do
- begin
- case SQLServerType of
- ssSQLite:
- statements := TTestStatements.Create('pragma table_info(FPDEV)');
- ssFirebird:
- statements := TTestStatements.Create(
- CTE_SELECT (*FB 2.1*),
- 'EXECUTE BLOCK RETURNS (U VARCHAR(255)) AS BEGIN SELECT rdb$get_context(''SYSTEM'',''CURRENT_USER'') FROM rdb$database INTO U; SUSPEND; END' (*FB 2.0*)
- );
- ssPostgreSQL:
- statements := TTestStatements.Create(CTE_SELECT, 'EXPLAIN '+CTE_SELECT);
- ssMSSQL:
- statements := TTestStatements.Create(CTE_SELECT (*MS SQL 2005*));
- ssMySQL:
- statements := TTestStatements.Create(
- 'check table FPDEV', // bug 14519
- 'show tables from '+Connection.DatabaseName // bug 16842
- )
- else
- Ignore(STestNotApplicable);
- end;
- for s in statements do
- begin
- Query.SQL.Text := s;
- Query.Open;
- AssertTrue(Query.FieldCount>0);
- AssertFalse('Eof after open', Query.Eof);
- Query.Next;
- Query.Close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestClearUpdateableStatus;
- // Test if CanModify is correctly disabled in case of a select query without
- // a from-statement.
- begin
- if not (SQLServerType in [ssMySQL]) then Ignore('This test does only apply to MySQL because the used SQL-statement is MySQL only.');
- with TSQLDBConnector(DBConnector) do
- begin
- with (GetNDataset(false,5) as TSQLQuery) do
- begin
- Open;
- AssertEquals(True,CanModify);
- Close;
- SQL.Text:='select last_insert_id();';
- Open;
- AssertEquals(False,CanModify);
- close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestReadOnlyParseSQL;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- GetFieldDataset(True);
- with query do
- begin
- AssertFalse(ReadOnly);
- AssertTrue(ParseSQL);
- // If ParseSQL is false, and no update-queries are given, the query
- // shouldn't be updateable after open.
- ParseSQL := False;
- AssertFalse(ParseSQL);
- AssertFalse(ReadOnly);
- SQL.Text := 'select * from FPDEV;';
- open;
- AssertFalse(ParseSQL);
- AssertFalse(ReadOnly);
- AssertFalse(CanModify);
- close;
- // If ParseSQL is true, the query should be updateable after open.
- ReadOnly := False;
- ParseSQL := True;
- AssertTrue(ParseSQL);
- AssertFalse(ReadOnly);
- SQL.Text := 'select * from FPDEV';
- open;
- AssertTrue(ParseSQL);
- AssertFalse(ReadOnly);
- AssertTrue(CanModify);
- edit;
- FieldByName('ID').AsInteger:=321;
- post;
- Applyupdates;
- close;
- // If ParseSQL is true, but the supplied query isn't updateable, then
- // the query shouldn't be updateable after open.
- ReadOnly := False;
- SQL.Text:='select ID,NAME from FPDEV where ID<5';
- sql.Add('union');
- sql.Add('select ID,NAME from FPDEV where ID>5');
- AssertTrue(ParseSQL);
- AssertFalse(ReadOnly);
- open;
- AssertTrue(ParseSQL);
- AssertFalse(ReadOnly);
- AssertFalse(CanModify);
- close;
- // As above, but now with an update-query, so that the query should
- // be updateable again.
- ReadOnly := False;
- AssertTrue(ParseSQL);
- AssertFalse(ReadOnly);
- UpdateSQL.Text:='update FPDEV set ID=:ID where ID=:OLD_ID';
- open;
- AssertTrue(ParseSQL);
- AssertFalse(ReadOnly);
- AssertTrue(CanModify);
- edit;
- post;
- Applyupdates;
- close;
- // Also if ParseSQL is False, the query should be updateable if a update-
- // query is given.
- ReadOnly := False;
- ParseSQL := False;
- AssertFalse(ParseSQL);
- AssertFalse(ReadOnly);
- open;
- AssertFalse(ParseSQL);
- AssertFalse(ReadOnly);
- AssertTrue(CanModify);
- edit;
- FieldByName('ID').AsInteger:=1;
- post;
- Applyupdates;
- close;
- // But if ReadOnly is true, then CanModify should always be false
- ReadOnly := True;
- ParseSQL := False;
- AssertFalse(ParseSQL);
- AssertTrue(ReadOnly);
- open;
- AssertFalse(ParseSQL);
- AssertTrue(ReadOnly);
- AssertFalse(CanModify);
- close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestParseJoins;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- with query do
- begin
- SQL.Text:='select TT.NAME from FPDEV left join FPDEV TT on TT.ID=FPDEV.ID';
- Open;
- AssertFalse(CanModify);
- Close;
- SQL.Text:='select T1.NAME from FPDEV T1,FPDEV T2 where T1.ID=T2.ID';
- Open;
- AssertFalse(CanModify);
- Close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestDoubleFieldNames;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- with query do
- begin
- SQL.Text:='select FPDEV.*,TT.* from FPDEV left join FPDEV TT on TT.ID=FPDEV.ID';
- Open;
- AssertTrue(assigned(FindField('ID')));
- AssertTrue (assigned(FindField('ID_1')));
- AssertTrue(assigned(FindField('NAME')));
- AssertTrue(assigned(FindField('NAME_1')));
- AssertEquals(1,fieldbyname('ID').AsInteger);
- AssertEquals(1,fieldbyname('ID_1').AsInteger);
- AssertEquals('TestName1',fieldbyname('NAME').AsString);
- AssertEquals('TestName1',fieldbyname('NAME_1').AsString);
- close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestParseUnion;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- with query do
- begin
- SQL.Text:='select NAME from FPDEV where ID<5';
- sql.Add('union');
- sql.Add('select NAME from FPDEV where ID>5');
- Open;
- close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestInsertLargeStrFields;
- // See also: TestStringLargerThen8192
- const
- FieldValue1='test1';
- var
- FieldValue2: string;
- begin
- FieldValue2:=StringOfChar('t', 16000);
- with TSQLDBConnector(DBConnector) do
- begin
- Connection.ExecuteDirect('create table FPDEV2 ( ' +
- ' ID INT NOT NULL , ' +
- ' NAME VARCHAR(16000),' +
- ' PRIMARY KEY (ID) ' +
- ') ');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- query.sql.Text:='select * from FPDEV2';
- Query.Open;
- Query.InsertRecord([1,FieldValue1]); // string length <= 8192 (dsMaxStringSize)
- Query.InsertRecord([2,FieldValue2]); // string length > 8192 (dsMaxStringSize)
- Query.ApplyUpdates;
- Query.Close;
- Query.Open;
- AssertEquals(FieldValue1, Query.FieldByName('NAME').AsString);
- Query.Next;
- AssertEquals(length(FieldValue2), length(Query.FieldByName('NAME').AsString));
- AssertEquals(FieldValue2, Query.FieldByName('NAME').AsString);
- Query.Close;
- end;
- end;
- procedure TTestFieldTypes.TestNumericNames;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- Connection.ExecuteDirect('create table FPDEV2 (' +
- ' '+connection.FieldNameQuoteChars[0]+'2ID'+connection.FieldNameQuoteChars[1]+' INT NOT NULL,' +
- ' '+connection.FieldNameQuoteChars[0]+'3TEST'+connection.FieldNameQuoteChars[1]+' VARCHAR(10),' +
- ' PRIMARY KEY ('+connection.FieldNameQuoteChars[0]+'2ID'+connection.FieldNameQuoteChars[0]+') ' +
- ') ');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- with query do
- begin
- SQL.Text:='select * from FPDEV2';
- Open;
- Edit;
- fieldbyname('2ID').AsInteger:=1;
- fieldbyname('3TEST').AsString:='3test';
- Post;
- ApplyUpdates(0);
- close;
- open;
- AssertEquals('3test',FieldByName('3TEST').AsString);
- Edit;
- fieldbyname('3TEST').AsString:='test3';
- Post;
- ApplyUpdates(0);
- open;
- AssertEquals('test3',FieldByName('3TEST').AsString);
- close;
- end;
- end;
- end;
- procedure TTestFieldTypes.TestApplyUpdFieldnames;
- begin
- with TSQLDBConnector(DBConnector) do
- begin
- AssertEquals(-1,query.RowsAffected);
- Connection.ExecuteDirect('create table FPDEV2 (' +
- ' ID INT NOT NULL, ' +
- ' '+Connection.FieldNameQuoteChars[0]+'NAME-TEST'+Connection.FieldNameQuoteChars[1]+' VARCHAR(250), ' +
- ' PRIMARY KEY (ID) ' +
- ') ');
- // Firebird/Interbase need a commit after a DDL statement. Not necessary for the other connections
- TSQLDBConnector(DBConnector).CommitDDL;
- Connection.ExecuteDirect('insert into FPDEV2(ID,'+Connection.FieldNameQuoteChars[0]+'NAME-TEST'+Connection.FieldNameQuoteChars[1]+') values…