Feb 28 2007
DbFit: Test-driven database development
DbFit is a set of FIT fixtures which enables FIT/FitNesse tests to execute directly against a database. This enables developers to manipulate database objects in a relational tabular form, making database testing and management much easier then with xUnit-style tools. The library is free to use, released under GNU GPL.
Features | Download | Reference | Source code | Related resources
Why DBFit | Project goals | Q & A
- Regression testing for queries and SQL statements
- Functional testing for stored procedures and functions
- Automatic transaction control and various short-cuts to make writing test scripts easier and more efficient
- Support for Oracle (java and .net versions), MySql 5 (java) and SQLServer 2005 (.net version)
For more details, see DbFit pages on fitnesse.info.
After introducing FitNesse into a big .NET project, I found that we were still lacking test-coverage for a lot of DB (legacy) code, and that DB developers have a long feedback loop, so they were not really benefiting from TDD. FitNesse was a good solution because it enabled DB developers to write functional tests much easier than in pl/sql based unit testing libraries, and without getting Java developers involved to write those as JUnit tests. Although FitNesse is typically used for acceptance tests, the combination of FitNesse+DbFit filled the testing gap for our DB code with excellent results. In fact, as the natural DB interface is relational, FitNesse tables allow writing database tests much more efficiently than using function-centered unit testing libraries. For a more detailed view into the background of DbFit, see Fighting the Monster.
- Enable managing database state from FitNesse as an utility for integration tests involving databases, by .Net and Java developers
- Enable the use of FitNesse for effective database acceptance and unit testing by DB developers (with no .Net/Java knowledge)
The first group mainly needs reusable fixtures which they can use, either directly on the page or embedded in their project-specific fixtures, and will not mind digging into a bit of Java/.Net code. But the other group must not be made to wait for Java/.Net developers to do effective TDD. So the project is taking a dual approach – developing test fixtures which will be reusable and extendable, but also developing one arch-test fixture which can control the connection, transactions and test flow in a fitlibrary flow mode. So Java/.Net developers can utilise the fixtures directly, and DB developers will be able to utilise the test fixtures through the over-arching fixture control.
See other FitNesse resources on this site
are you planning for a java version ?
yes, that’s planned for the future. the first thing in the roadmap is SqlServer support and probably expanding the supported set of data types, then I plan to do a java version.
[...] In related news - Gojko Adzic has also released DbFit, an extension of the Fit testing framework which allows database developers to write and automate functional and acceptance tests easily. [...]
Way cool!
Just out of curiosity, how soon will support for SQLServer be available….
–Thanks,
–Mike Ramsey
Sometime next week.
This looks great.
I’ve just finished building my own test framework for SQLServer stored procedures using batch scripts, in large part because I was not comfortable extending FitNesse to handle the kinds of tests I wanted to build. Now, as I prepare to roll out my framework to my users, I find that you have done the heavy lifting.
I’ve got a meeting tomorrow to discuss our next steps, so I am very interested in how the SQLServer port is coming along.
Mark
Is the DBFit for SqlServer available? Please let me know
Thanks.
The project has been moved to sourceforge (see http://sourceforge.net/projects/dbfit) and the version with full SQL server 2005 support can be downloaded from there. Meanwhile, I laid out the roadmap for 1.0 release. I’m still cleaning it up, did not have too much time over the last month, but I expect to release 1.0 for .Net soon.
How does DbFit relate to DbUnit? I’m aware that DbUnit has no Fit/Fitnesse integration but it has great support for importing/exporting data and asserting db state, just wondering =)
DBFit does not suffer from the object/functional-relational mismatch. It allows you to express tests in a tabular format, which comes much more natural for database objects. main aim of dbunit is to enable putting a database in a “known” state, but dbfit is aimed at full db regression testing (makes it very easy to test stored procs, for example)
Have you released DBFit for .Net 2.0 or 3.0?
I just went to SourceForge using the url in 8. above. The project is there, but it indicates that no file release packages have been defined. Is there a technical problem or has the project been disabled or…?
binary releases for .NET and Java can be downloaded from http://sourceforge.net/project/showfiles.php?group_id=191053
Do you plan to add support for binary data columns?
I generally add stuff when someone requests it, so why not. Nobody asked for binary data support yet - let me know exactly what you need and i’ll put it in.
I use binary data columns to store long hex strings like 0×865878C1DED639BC. When I use .Net apps to get records, these columns come back with a DataType of System.Byte[].
When my FitNesse tables have these kind of hex strings as the expected result, it currently cause dbfit tests to throw exceptions:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.FormatException: Input string was not in a correct format.
FitNesse expects arrays to be specified as comma-separated lists, so you may try putting the value in in CSV format. Leave the field empty and re-run the test to check what fitnesse thinks it got in that cell. I can extend the parsing mechanism to read 0x… as a binary array if CSV is not an option for you. Send me a test case if you want me to do that.
The value that fitnesse thinks it has when it selects a binary value is “System.Byte[]“
How difficult is it to set up dbfit to use other databases? I am thinking especially of hsqldb (in-memory database, http://hsqldb.org/)
Is it more to it than implementing the interface DBEnvironment?
If you publish some rough guidelines, I can try myself : )
It’s just implementing a DBEnvironment and a new DatabaseTest implementation.
See MySqlTest.java
and
MySqlEnvironment.java
. to support standalone fixtures, also add a piece of code into
DatabaseEnvironment.java
to initialise the environment. That’s it for a rough guideline - don’t hesitate to yell for help
Hi Gojko,
I found that the only way to execute no-param no-return Oracle Stored Procedures was with an Execute statement, such as the following
|Execute| Begin Sproc; End;|
Although you mention to have only one row for the special case where no parameters are passed to the procedure / function, I found that approach doesn’t work and wanted to share my workaround for the time being.
Cheers,
Jim
Andy,
I’ve changed the SQLServer DbFit implementation to support binary and varbinary (it was SQLServer, was it?), and i’ve also built in support for parsing 0x formatted hex strings into byte arrays. I’ll do a proper release soon, but the current build is on the following URL:
dbfit-dotnet-current.zip
you will need to load the cell handler manually to support the new parsing. See the example on the bottom of
DotNet.AcceptanceTests.SqlServerAcceptanceTests.DataTypes
Hi I have very simple SQL server 2005 stored procedue and when I ran the test code as shown below its giving me errors…Am I missing something here. Thanks for the help.
–sql stored procedure
create procedure [dbo].[get_ConnectString1]
@atlasServer varchar(20),
@connectStringOut varchar(200) output
as
set @connectStringOut = ‘test’
select @connectStringOut ‘connectstring’
– wiki test code
!|Execute Procedure|get_ConnectString1|
|AtlasServer|connectStringOut|
|CS3SMDSEI32R||
–errors
Execute Procedure
——————————————————————————–
System.NotSupportedException: Type DESC15 is not supported
at dbfit.SqlServerEnvironment.GetDBType(String dataType) in W:\work\dbfit\dbfit\impl\dotnet\src\environment\SqlServerEnvironment.cs:line 136
at dbfit.SqlServerEnvironment.ReadIntoParams(String objname, String query) in W:\work\dbfit\dbfit\impl\dotnet\src\environment\SqlServerEnvironment.cs:line 91
at dbfit.SqlServerEnvironment.GetAllProcedureParameters(String procName) in W:\work\dbfit\dbfit\impl\dotnet\src\environment\SqlServerEnvironment.cs:line 45
at dbfit.fixture.ExecuteProcedure.InitParameters(Parse headerCells) in W:\work\dbfit\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 116
at dbfit.fixture.ExecuteProcedure.DoRows(Parse rows) in W:\work\dbfit\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 43
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows) get_ConnectString1
AtlasServer connectStringOut
CS3SMDSEI32R
Hi Srini,
it complains about an unsupported type definition. Don’t really know why, but if you could run the following query and send me the results, I will be able to fix it.
select p.[name], t.[name] as [Type], p.max_length, p.is_output, p.is_cursor_ref from sys.parameters p inner join sys.types t on t.system_type_id = p.system_type_id
where p.object_id = OBJECT_ID(@objname)
and t.[name] <> ’sysname’
objname parameter should be get_ConnectString1
is it possible that you have another procedure by the same name, or some other db object called get_ConnectString1?
I have two questions about DbFit with Oracle:
1. In general our packages are stored in a central schema, but when developers are working, they bring the packages into their own schemas and rely on public synonyms to “see” the central packages. For example, all packages will be in schema DEV, but the package I’m working on will also be in my SHARON schema. But DbFit can’t resolve any package names that are in the DEV schema unless I explicitly call it by DEV.package_name.proc_name. I’d like the database tests to be usable by developers while working and as a full-blown regression suite, so I’d really like to have the transparency of synonyms.
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at dbfit.fixture.ExecuteProcedure.InitParameters(Parse headerCells) in W:\work\dbfit\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 121
at dbfit.fixture.ExecuteProcedure.DoRows(Parse rows) in W:\work\dbfit\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 43
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
2. Some of our procedures take arrays as input parameters. DbFit errors saying that the pl/sql type is not supported. Any plans for array support?
Thanks!
–Sharon
OK…so thank you for all of your info about FitNesse - our QA team has implemented FitNesse thanks to the information found on your site (which is the best we have found so far).
I am eager to get DBFit working - I can connect to our SQL DB but I keep getting the following dbfit error:
System.MissingMethodException: Method not found: ‘fit.ICellHandler fit.CellOperation.GetHandler(fit.Parse, fit.Accessor)’.
at dbfit.AbstractDataTableFixture.IsMatch(Parse row, DataRow d)
at dbfit.AbstractDataTableFixture.FindMatchingTableRow(Parse row, DataTable table)
at dbfit.AbstractDataTableFixture.DoRows(Parse rows)
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
Can’t wait for the fix/help!
Marisa,
it seems that you are using an incompatible version of FIT.NET. Can you confirm that you are using the latest binary version (1.3)? Maybe try to use the one from Dbfit dbfit-complete package from sourceforge.
I have a SQL Server 2005 procedure that returns 40 or so rows.
I would like to test the stored proc logic. It has 5 input parms but 0 output parms
I have predefined data for it so I expect the same results every time
What dbfit fixture can I use? Here is example
!|Execute Procedure|srd_Stock_Stats|
|@ReportDate|@PKPPRD_GroupingTypes|@PKPPRD_Groupings|
@PKPPRD_Levels|@PKSCC_StockTypes|@PKPPRD_EarnTypes|@TopXDivision|@TopXStock|
|3/31/2006|2|2|6|1|0|0|0|
|GroupingName|Stock|Week1|Week2|Week3|Week4|||
|OIL|NULL|-/-|83%/92%|100%/100%|100%/81%|||
|OIL|Shell|-/-|83%/92%|100%/100%|100%/81%|||
|CONSUMER|NULL|-/-|100%/53%|71%/71%|100%/86%|||
|CONSUMER|Target|-/-|100%/53%|71%/71%|100%/86%|||
Hi Denis,
How does the procedure return the results? does it open a cursor? If so, that functionality is not yet supported for SQL Server, but if you are willing to help with testing, I can write the support. Another way to do it would be to write a new utility test proc that stores the results into a temp table, and then query that temp table after procedure is executed.
btw, you don’t need the @ before param name.
Hi Gojko
The sql stored procedure is something like this
It declares a temp variable table x and stores info into from another stored proc.
Through alot of SQL, joins and other complex logic the data in that temp variable table is
selected in the stored proc like so
select GroupingName,Stock,Week1,Week2,Week3,Week4 FROM x
So it is just a standard resultset from sql server. I know Oracle is that different in that
you create output array variables but the SELECT above is standard in SQL Server
I basically want to do what your Query fixture does but call a stored proc instead with some input parms
so that I can test it’s logic
Denis
I forget to mention that when I was looking at fitnesse by itself earlier I had hoped there were great
new fixtures that would let me check my db code easily and insert test data beforehand.
I think the dbfit insert fixture is great. It looks like it inserts test data
and does an automatic rollback afterwards. Query is good too but I really want to
test stored procs that I create/manage.
Denis,
I can modify the execute procedure fixture to have an implicit output argument for this result set, then you would be able to use it with the query. it would be something like this:
|Execute procedure|myproc1|
|param a|param b|$output1|
|input1|input2|>>myvar|
|Query|<<myvar|
|f1|f2|
…
I don’t have access to a SQL server for testing, so if you can do that for me, I’ll be happy to modify the code and get this done for you.
Hi I just tried something like the following and it worked!
|Query|exec myproc1 parm1,parm2,parm3|
|col1|col2|col3|
|data|data|data|
|more Data|more..|more..|
Thank you! You already had the correct fixtures and I didn’t know it.
I also inserted test data using your insert fixture!
|Insert|dbo.tablex|
|colx|coly|colz|
|test1|test2|test3|
|test4|test5|test6|
It puts test data in table(s) and when the dbfit test is finished it rolls back!!
Excellent! I can test without worrying about affecting others or getting affected by them.
dbfit truly is a great addition to fitnesse!!
Sorry to have wasted your time!
P.S. working at 2am!?? I hope you usually have work-life balance!
Hi Gojko,
Thanks for the help with my previous problem. I’ve gotten the latest FIT for .NET binaries and was able to run a FIT test for a function in our database. However, I’m having a problem trying to test a stored procedure. I’m getting this error in the Execute Procedure cell:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at dbfit.fixture.ExecuteProcedure.InitParameters(Parse headerCells)
at dbfit.fixture.ExecuteProcedure.DoRows(Parse rows)
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
I’ve tried referencing the stored procedure name in the following ways but still get the error above:
DBName.dbo.procName
DBName.procName
dbo.procName
procName
The only thing I can come up with is that there could be some kind of permissions issue with respect to the user account I’m using to connect to the DB…but then I’m not sure why the function test would have executed (same connection string). THANK YOU for your help!
Hi Marisa,
the error message typically means that the procedure or one of specified parameters cannot be found. if you are certain that the proc name is correct, check the parameter list for misspellings. I’m working on better error reporting, that should be included in the next version. if it still does not work, I can give you a partial build with better error report to tell you what’s wrong.
Hi Gojko,
I will wait until the next version is available - I’ve tried to isolate whether a specific input or output parameter name is causing the problem but I seem to get the error regardless.
Looking forward to the next version…which I hope will include support for guids as well?
Thanks.
I have used FitNesse for .net applications and have just downloaded the DBFit package. I have not been successful at connecting to our database. We are using SQL Server 2005. We usually have our databases secured using Windows Authentication, but set up a test database that did not use it. However, I keep getting the error:
Standard Error:
java.lang.NoClassDefFoundError: fit/FitServer
Exception in thread “main”
——————————————————————————–
Internal Exception:
java.lang.Exception: FitClient: external process terminated before a connection could be established.
fitnesse.components.CommandRunningFitClient$
EarlyTerminationRunnable.run(CommandRunningFitClient.java:149)
java.lang.Thread.run(Unknown Source)
Unfortunately, I can’t give out the information I am using to connect to the database, which may make it difficult or impossible to assist me with this problem.
first line is:
!|dbfit.SqlServerTest|
the next line is:
|Connect|host machine|username|password|table name|
Any advice??
thanks,
Michael
Hi Michael,
the error report suggests a couple of setup issues.
It seems that you are running the java version of Fit test runner. (java.lang.NoClassDefFoundError: fit/FitServer). For SQL Server, you need to execute the .net version of the runner. If you have downloaded the “-complete” package, then everything is there and you just need to put these lines into the page header:
You can put that into the main suite page, or a suite set up, to make it available for all your tests.
If you have downloaded just the dbfit binaries, you will need the .net version and you will also need the fit.net distribution. Then adjust those setup lines according to the location of those packages relative to your main fitnesse folder.
Hi Gojko,
Thanks for the updated version today! It looks like the problem I’m having with that one proc has to do with the parameters, so I’ll look into that a little more.
I was curious - do you have plans to add support for guids? Unfortunately this is preventing me from successfully implementing most of what I want to with DBFit because our DB is heavy in guids…
Thanks again!
Hi Marisa,
Generally I add support for stuff when people ask for it - so I can add GUIDs if that is important for you. Please send me a test case (fitnesse tables and db code if required) and i’ll make it work.
I’m refering to Marisa’s problem above with the wrong fitnesse version. I double checked, so probably I’m overlooking something, but it seems to me that dbFit (9.2) is NOT using the most recent fitnessedotnet version (1.3). I come to this conclusion by looking at the code base in SVN.
Here’s the extract of dbfit.AbstractDataTableFixture (trunk):
private bool IsMatch(Parse row, DataRow d)
{
…
foreach (DataColumnAccessor accessor in accessors)
{
ICellHandler cellHandler = CellOperation.GetHandler(cell, accessor);
… }
return true;
}
And here’s the SVN extract from fit.CellOperation (tags/20070615)
public static ICellHandler GetHandler(Fixture theFixture, Parse cell, Accessor accessor)
{
return GetHandler(theFixture, cell, accessor.TypeAdapter.type);
}
So it seems to me that dbFit is not using the latest version but some older one.
Hi Johannes,
You are completely right - sorry for not spotting that earlier and great catch. I’ve updated the source and re-compiled everything to work with Fitnesse.Net 1.3. See the download pages on Sourceforge and get dbfit-dotnet-binaries-20071127-fdn13.zip and dbfit-dotnet-binaries-and-deps-20071126-fdn13.zip.
Gojko,
You ROCK!
This is just a simplified example that is similar to something I’m trying to work with in DBFit.
———–
Assumptions
Table Name: Addresses
Two Columns: AddressID (uniqueidentifier – primary key), Address (nvarchar 255)
One Row: AddressID=Empty Guid, Address=123 Test Lane
Procedure Name: proc_Addresses_Retrieve expects one parameter @AddressID – code as follows
SELECT Address
FROM Addresses
WHERE AddressID = @AddressID
———–
FitNesse Table
!|dbfit.SQLServerTest|
|Connect|My connection string here…|
!|Execute Procedure|proc_Addresses_Retrieve|
|AddressID|Address?|
|00000000-0000-0000-0000-000000000000|123 Test Lane|
———–
I would also like to be able to execute the INSERT and UPDATE procs via DBFit — if you want more example proc code and FitNesse tables for those just let me know.
I can’t say “thank you” enough — once I’m able to work with our procs that depend on guids (which is about 98% of the procs), our test development will be so much faster (it will significantly decrease the fixture code the developers are writing right now).
Marisa, thanks for the praise and all your support. all fixture types use the same sql type base, so when the guid support is added, everything will start working straight away. I’m not really good with sql server stuff, please send me both the create table and create procedure scripts for what you want and I’ll add the support.
Addind to what Marisa says above, we have a similar problem using SQLServerTest. Consider the following to tables:
!|Execute|!-
CREATE PROCEDURE TEST_PROC
AS
SELECT * FROM MITARBEITER
RETURN
-!|
!|Execute|!-
CREATE PROCEDURE GetMitarbeiter ( @name varchar(10) )
AS
SELECT * FROM MITARBEITER WHERE NAME = @name
RETURN
-!|
Whereas the first one works fine, the second one produces an exception:
System.Data.SqlClient.SqlException: Wrong Syntax next to ‘PROCEDURE’ keyword.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at dbfit.fixture.Execute.DoRows(Parse rows) in D:\work\dbfit\impl\dotnet\src\fixture\Execute.cs:line 29
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
Seems to be a problem with SQL Server’s parameter handling?
Johannes,
Because DbFit tries to use fitnesse symbols as bound variables for “Execute”, it will actually try to map @name from the “create procedure” to a bound variable called name. That is why sql server throws an error, I guess. I don’t think that creating procedures on the fly is the best usage of a test tool anyway. From my experience, if you need some procedures for test-harness, that is best kept with the rest of database code, possibly in a separate schema.
Gojko,
What we are trying to do is to create some kind of “mock stored procedures”, i.e. sprocs that are called by our sproc under test. Those can be different for every test case. Hm, maybe we need something like a “create mock procedure”-fixture which allows to “override” a certain sproc and restores it afterwards. Take this as a feature request
When are the tests rollbacked?
At the end of each fitnesse test page, or at the end of a test suite, or..?
Hi Jay,
if you use a DatabaseTest instance (i.e. test starts with dbfit.OracleTest/SqlServerTest/MySqlTest), then the transaction is automatically rolled back on the end of the page. You can commit if you want using the Commit table. If you use standalone fixtures or DatabaseEnvironment to connect, then you must roll back/commit manually.
I’m currently trying to extend DbFit by a few project specific fixtures. One of the things that IMO somewhat lowers the library’s extensibility is the visibility of DatabaseTest.environment. Changing it to “protected” would make the explicit use of DatabaseEnvironment fixture unnecessary since I could subclass from SqlServerTest and pass the environment to my home-made fixtures.
Johaness, Marissa
I’ve added the support for GUIDs and changed DatabaseTest.environment to protected field. Please get dbfit-dotnet-20071130.zip from the dist folder and test. If you are happy with it, it will become part of the next regular release. Btw, to use guid support, load the guid handler in test setup:
Gojko,
The add-in is working perfectly for me so far. It’s such a huge help for us - thank you!
Gojko,
Well, it’s me again. I’ve read through this page and the DatabaseTextFixture page, as well as your easy-to-read PDF. I couldn’t find reference to something I’m wanting to automate using DBFit - and that is comparing two result sets.
For example, we have a stored procedure that returns two columns of data as follows:
|OrganizationName|OrganizationState|
|Acme Products|California|
|etc.|etc.|
Well, I’d like to be able to write a query manually via DBFit, store the results of the query in a variable on the page, and compare the contents of the stored variable to the results returned by the proc. So basically comparing one table of records to another.
Example
!|Query|SELECT OrganizationName, OrganizationState FROM Organization WHERE (my clauses…)|
|>>myStoredResults|
!|Execute Procedure|RetrieveOrganizationsStates param1 param2|
|<<myStoredResults|
I saw your instructions for creating a data regression test, but because many teams use the same central test database, I’d need to be able to re-generate the data with each test pass.
If there’s something I’ve missed in your documentation that explains how to do this, or if you can think of a way to do this using the commands you’ve already provided in DBFit, I’d appreciate your help. Thanks very much.
Hi Marisa,
there is no fixture to compare two queries stored into variables. I’ll add that to the list of features for the next release.
Hi,
I’m new to fitnesse and dbfit and am trying to do an update on a table in our Oracle database using the below:-
!|Update|QUOTE_VALUE|
|Product_NAME|DATE|QUOTE_TYPE|QUOTE_SET_NAME|BID=|ASK=|
|reference product|date_entered|Future|QS_EOD|price1|price2|
My understanding is that with the above, i should be able to reference the product and enter the BID and ASK price into the table. I’m not sure if i’m executing the above correctly as i’m getting the following error:-
Load data into Bid/Ask
Update
——————————————————————————–
Missing method: public TypeOfResult update(Type1 arg1) { } in class dbfit.OracleTest QUOTE_VALUE
QUOTE_NAME
——————————————————————————–
Missing method: public TypeOfResult qUOTE_NAME(Type1 arg1, Type2 arg2, Type3 arg3, Type4 arg4, Type5 arg5) { } in class dbfit.OracleTest QUOTE_DATE BID= ASK= QUOTE_TYPE QUOTE_SET_NAME
Future.AUD.SFE.THREE MONTH AUD IR FUT FO.DEC.07
Could someone help me please?
Cheers,
Peter
Hi peter,
the error below would suggest that you are either using an older version of DbFit (update was introduced in 0.91), or that the page does not start with a DbFit test table.
Can you please send me the whole test page source. Also, are you using the .NET or Java type of DbFit, which database (and version) are you connecting to, and can you verify that you are using the latest version of DbFit?
Hi,Can i use DBfit to execute Join SQL Statements? For Example, select threedresponse as threedresponse from payrecs p, accounts a where p.accountid = id and a.username = ‘xxxxx’?
At the moment, its giving java.sql.SQLException: ORA-00918: column ambiguously defined error.
my script looks like this
|Query| select threedresponse as threedresponse from payrecs p, accounts a where p.accountid = id and a.username = ‘xxxxx’|
|THREEDRESPONSE|
|4100|
Gojko,
Making DatabaseTest.environment protected works for us. There are more things, though, that seem to get into the way of extending (and reusing) the framework. For example, DbParameterAccessor hides its properties “Position” and “DbParameter” which we would like to access for generating certain kinds of sql commands.
The general question hidden here is: Do you want to open DbFit for extension or keep it closed to make maintenance easier? If opening is your future strategy than it would probably make sense in the long run to differentiate between a public and an internal namespace to show extenders on what they can rely and what is implementation detail.
As for now I’d be very happy with “DbParameterAccessor.Position” and “DbParameterAccessor.DbParameter” being public.
Sachin,
DbFit does no special SQL statement processing, it sends whatever you specify to the database. I can only guess that you have “id” in both tables (accounts and payrecs), so that in
p.accountid=id
oracle does not know which ID you are talking about. try to execute that same query in sqlplus or toad… if that does not help, please send me the create table statements for both those tables and I’ll try it out.
Hi Johannes,
In general, I’m very open for it to be extended, and I’ll review the namespaces for everything for the next release.
DbParameterAccessor was intentionally locked down to prevent mistakes, not to make maintenance easier. You can use the constructor:
public DbParameterAccessor(DbParameter dbp, Type dotNetType, int position)
to clone the parameter then fill in your details further on.
I’m not really sure how you are going to use the extended version of the parameter accessor, as all standard fixtures will keep using the standard version. Can you send me more info on what you are trying to achieve and we’ll work something out.
Hi Gojko,
Two questions…
1. Can you extend dbFit to support Oracle collection types?
I’ve got a procedure that has a collection type (table) as a parameter:
CREATE OR REPLACE
TYPE “example”.”ex_ID_TABLE” IS
TABLE OF ex_id
PROCEDURE example_proc(arg1 IN tab1.id%TYPE, arg2 IN NUMBER := 0, arg3 IN NUMBER := 0, arg1 ex_id_table := ex_id_table())
I’ve got this error when trying to test:
|Execute Procedure|example_proc|
|arg1|
|<<da_id|
System.NotSupportedException: Type TABLE is not supported
at dbfit.OracleEnvironment.GetDBType(String dataType) in D:\work\dbfit\impl\dotnet\src\environment\OracleEnvironment.cs:line 149
at dbfit.OracleEnvironment.ReadIntoParams(String[] queryParameters, String query) in D:\work\dbfit\impl\dotnet\src\environment\OracleEnvironment.cs:line 115
at dbfit.OracleEnvironment.GetAllProcedureParameters(String procName) in D:\work\dbfit\impl\dotnet\src\environment\OracleEnvironment.cs:line 67
at dbfit.fixture.ExecuteProcedure.InitParameters(Parse headerCells) in D:\work\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 116
at dbfit.fixture.ExecuteProcedure.DoRows(Parse rows) in D:\work\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 43
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
2. I’ve tried to get around this by wrapping this in another proc, since that parameter is optional:
CREATE OR REPLACE procedure ex.ex_wrapper(arg1 in number) is
begin
example_proc(arg1);
EXCEPTION WHEN NO_DATA_FOUND THEN NULL ; END ex_wrapper;
/
but no cigar:
System.ApplicationException: Cannot read list of parameters for ex_wrapper - check spelling and access privileges
at dbfit.OracleEnvironment.GetAllProcedureParameters(String procName) in D:\work\dbfit\impl\dotnet\src\environment\OracleEnvironment.cs:line 68
at dbfit.fixture.ExecuteProcedure.InitParameters(Parse headerCells) in D:\work\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 116
at dbfit.fixture.ExecuteProcedure.DoRows(Parse rows) in D:\work\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 43
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
Where dbFit gets the Oracle procedure parameters from - is it some Oracle system view which may be somehow restricted for viewing? The proc and wrapper work in Toad, no spelling errors, I’m using the same user to connect in dbFit and in Toad… I get the same error when I put a name that doesn’t exist, so I believe it just can’t see the wrapper I added for some reason. I’ve tried creating public synonims but it doesn’t work.
There is a happy end - finally I managed to get around it by doing this
|Execute|begin example_proc(!-:da_id-!); end;|
(the proc stores the result in temporary table so I can run the query on it afterwards to check the results)
And a final comment - it would be truly great if you can store the query in variable (comment 54 above), connect to two instances and compare. That would come really handy for some data migration testing we’re doing at the moment. It is tedious to create the rows manually in Fitnesse and for different migration runs the data can change significantly, so we’d have to recreate/update the expected test results which is a royal pain.
Thanks for the great tool.
Cheers,
Steve
Hi Steve,
1 - Oracle collection types are on my list. A few people already asked for that, but it turned out to be much trickier to implement and will have to wait for a major restructuring of DbFit. Until that happens, I suggest sending a CSV list to a stored proc and unpacking it there.
2 - “Cannot read list of parameters for ex_wrapper” - is dbfit connecting as user “ex” or some other user. If as a different user, then prefix the procedure name with the schema name (i.e. |Execute procedure|ex.ex_wrapper|). If this does not help, please send me your whole test page.
DbFit reads stored procedure metadata from all_arguments view (you can see the .net version of that code on sourceforge, see lines 41-65.
3 - storing results into a query variable will be done for the next release.
thanks for the support!
Hello,
I’m not sure why, but I’ve got a Query command that is behaving like an Ordered Query command. Here’s the example:
!|Query|EXEC proc_MyRetrieveProc 123, xyz|
|Col1?|Col2?|Col3?|
||1|aaaaa|
||2|bbbbb|
I’m actually getting a failing result because the results are coming back as:
|Col1?|Col2?|Col3?|
||2|bbbbb|
||1|aaaaa|
Since I’m not using the Ordered Query command I thought order wouldn’t matter? Am I missing another directive?
Thanks for your help!
Marisa,
all your columns are non-key (all have ? in the name), so DbFit does not consider any of them when matching. I suggest you remove ? from all column names (or if any of those is the primary key, remove ? for that column).
If you are using DbFit and you are happy with it, I need some information from you for the new DbFit web site. Please look at are-you-using-dbfit? to see how you can participate.
Hi Gojko,
Removing the ? from the columns I wanted to use as matching columns worked - thank you!
I will work on my comment for your new site and get it to you early next week.
Hi Gojko,
I’m getting the “Object reference not set to the instance of an object” error in a particular case. Here’s a simplified version of a query I’m running:
!|Query|SELECT * FROM Addresses WHERE AddressID = @testAddressID|
|AddressID|Line1?|Line2?|UpdatedBy?|IsValid?|
In the above, AddressID and UpdatedBy are both guids. Line1 and Line2 are nvarchar, and IsValid is a nullable bit.
When the following data is returned, I do NOT get the exception:
|AddressID |Line1? |Line2? |UpdatedBy? |IsValid?|
|(valid guid)|123 Test Lane|Test, CA 12345|(valid guid)|False|
However, when the UpdatedBy field returns as null (which is valid for the case), I get the exception for the IsValid bit field.
Here’s the exception message:
System.NullReferenceException: Object reference not set to an instance of an object.
at fitnesse.handlers.BoolHandler.HandleEvaluate(Fixture fixture, Parse cell, Accessor accessor)
at fitnesse.handlers.AbstractCellHandler.HandleCheck(Fixture fixture, Parse cell, Accessor accessor)
at dbfit.AbstractDataTableFixture.CheckMatchingRow(Parse row, DataRow d) in D:\work\dbfit\impl\dotnet\src\fixture\AbstractDataTableFixture.cs:line 82
at dbfit.AbstractDataTableFixture.DoRows(Parse rows) in D:\work\dbfit\impl\dotnet\src\fixture\AbstractDataTableFixture.cs:line 52
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
I know that the older version of FIT for .NET (without your updates) has problems with nullable Boolean values…but I’m just stumped because I have your .NET updates, and am only getting the exception in a very specific case.
As always, I appreciate your help.
Hello,
I have a quick question regarding the downloads. what’s the difference between the regular binaries and the ones labeled with “fdn”? thanks!
Hi Kevin,
DbFit was upgraded to use the latest version of FitNesse.NET (1.3) in 0.92 release, so binaries without -fdn-13 are linked with FitNesse .NET 1.2, and those with -fdn-13 are linked with FitNesse.NET 1.3. Upcoming releases will be based on the FitNesse.NET 1.3 branch, so if you are just starting with DbFit, get those with -fdn.
Marisa,
i’m not sure how the null updatedby field can effect the next field in the table. what is in the cell for updatedby when you expect null? is it empty? If yes, please use the keyword null. Can you send me the create table statement and your test page so I can reproduce the problem here and fix it?
I’m looking at the 20071126 snap and I do not see any support for SQLServer in the Java sources. Do you have any idea when this will be available. Thanks.
HI Andy,
I never had a request for that, everyone using SqlServer is doing so with the .NET version. If you need it in Java, I can port it fairly quickly.
Really. I was looking at AbstractDBEnvironment and subclassing my own for both SQLServer and Sybase. We have a few targets you see and countless stored procs with no tests. I need to modify a couple and need decent test bed in place before I venture forth with the new requirements.
Can you use the .NET version? the syntax is absolutely the same and it will give you sql server support out of the box (don’t know how much sybase is different, but if you are willing to help with testing, we can implement this relatively easily). If you have to use the java version because of integration testing, let me know which JDBC driver you are using for Sql Server so that I can implement and test the port using the same thing. Alternatively, if you don’t mind getting your hands a bit dirty, subclassing AbstractDBEnvironment is the way to go. See the .NET implementation for details.
I thought you might say that
Sure, I will give it a whirl. .NET? I develop on a Mac/Unix most of the time. In my case I have more than one driver with which to test Sybase over and it seems that AbstractDBEnvironment subclasses are tied to a particular driver e.g. getDriverClassName(). I’ll probably end up passing some kind of Driver encapsulation to SybaseEnvironment. Just thought I’d pass that on.
Hi Gojko,
Is the Inspect feature available in the current version of DBFit? I tried the following:
!|dbfit.SQLServerTest|
|Connect|MyConnectionString|
!|Inspect|MyTableName|
I got the error: fitlibrary.exception.InvalidMethodException:Missing method ‘inspect’ with 1 argument(s).
Yes, but you have to tell it what to inspect. Use this
!|Inspect table|MyTableName|
See this for further examples.
Doh! Just pulled the dbfit DLL into a project and saw that it’s “InspectProcedure” etc. Thanks!
Hi again Gojko,
I was wondering, is it possible to store multiple rows of results in a single variable? what i want to do is compare the contents of two database tables which may have an arbitrary number of rows. Let me know your thoughts on this; thanks!
Also i’ve found a bug in how saving null values into variables works; if you save a null and try to compare it to a null in the future, it fails. This may be an issue in column fixture itself. here’s the tables to replicate it:
!|dbfit.SqlServerTest|
!|Connect|Server=${server};Database=${database};Trusted_Connection=yes;|
!|Query|Select null as test|
|test?|
|>>nullcheck|
!|Query|Select null as test|
|test?|
|<<nullcheck|
Hi Kevin,
I’m working on the feature to store a query result into a symbol and than use that for comparison. That will be released with 0.93 in the next week or so. Regarding null and comparisons, null comparison is typically managed by the NULL cell handler, so individual type comparison handlers often do not check for that. I may be able to develop a workaround for DbFit, because all DbFit processing goes through the DbParameterAccessor. Give me a day or two to try things out and i’ll get back to you.
Hiya,
Did anyone get round to writing an environment for sybase? I am about to start but don’t want to go re-inventing the wheel.
Thanks,
Bobby
Hi Bobby,
there were a few queries about that, but nobody actually started to work on it as far as I know. let me know if you need any help with it.
dbfit 0.93 is just released, with support for storing query results into variables and comparing them… see http://gojko.net/2007/12/27/dbfit-093-storing-and-comparing-queries for more information
Awesome, thanks for the new features!
I have one issue though with the new query comparison feature; When i use it, i don’t see any results regardless of if they are equivalent queries or not. My question is two-fold:
does the DatabaseTest Flow-fixture support this?
and can we compare queries from different databases/servers (i.e. if i reconnect with a new connection string)? my example utilizes both scenarios and it doesn’t seem to work.
Thanks for all the hard work!
Hi Kevin,
You should see equivalent rows in green. Can you send me your test table and page?
Yes, using absolutely the same syntax as standalone. You should be able to reconnect to another database and use comparisons, because query results are detached and cached in memory. Again, please send me a test page and db code to double-check what your problem is…
Here’s a stripped down table that is giving me the same issue as what i had mentioned previously. I have tried it on a sql server 2000 and a 2005 database, neither of which work:
!|dbfit.SqlServerTest|
|Connect|Server=${server};Database=${database};Trusted_Connection=yes;|
|Store Query|Select ‘blah’ as one, ‘blah2′ as two|first|
|Store Query|Select ‘lala’ as one, ‘lolo’ as two|second|
|compare stored queries|first|second|
|one|two|
Hello Gojko,
I am having to use the SQL CAST function to cast int values to nvarchar in order for certain verifications to pass. Here’s an example:
|Set Parameter|expectedCountOfRecords|10|
!|Query|SELECT COUNT(*) FROM Customers AS CountOfCustomers WHERE CustomerState = ‘TN’|
|CountOfCustomers?|
|<<expectedCountOfRecords|
Even if the COUNT query returns 10, the cell is still marked as failing/red. If I convert/cast the expectedCountOfRecords variable to nvarchar, the test will pass.
Is there something inherently wrong with the way I’m setting values in variables, or is it that DBFit (or FitNesse) is converting all values to strings when running comparisons (in which case I guess I just need to continue casting to nvarchar)?
Thanks for your help.
Hi Marisa,
The problem is in strongly typed FitNesse plumbing. As ‘Set Parameter’ does not know which type the value is, it is made string by default. But the data coming out of the database in your example is a number, so the comparison fails. There is a good workaround in this case - use markup variables instead of symbols if you just want to parameterise tests and do not actually need the value in a bound variable. FitNesse parses markup variables in place of their usage, so it will be just as if you wrote 10 in the cell.
!define expectedCountOfRecords {10}
!|Query|SELECT COUNT(*) FROM Customers AS CountOfCustomers WHERE CustomerState = ‘TN’|
|CountOfCustomers?|
|${expectedCountOfRecords}|
Hi Kevin,
‘compare stored queries’ has to be a separate table, like query (it behaves as a row fixture). The test works ok if you use it in flow mode (just put a blank line between the last store query and compare stored queries
!|dbfit.SqlServerTest|
|Connect|Server=${server};Database=${database};Trusted_Connection=yes;|
|Store Query|Select ‘blah’ as one, ‘blah2′ as two|first|
|Store Query|Select ‘lala’ as one, ‘lolo’ as two|second|
|compare stored queries|first|second|
|one|two|
if you cannot use flow mode (i.e. if dbfit.SqlServerTest is not the first table on the page) then you should use the database environment table to connect to the database. StoreQuery and CompareStoredFixtures are then available as fixtures in the dbfit.fixture namespace. Let me know if this does not help, i’ll write a complete test page for you to try.
thanks for the tip, i actually had to break the table out further:
!|dbfit.SqlServerTest|
|Connect|Server=${entserver};Database=${entdatabase};Trusted_Connection=yes;|
|Store Query|Select ‘blah’ as one, ‘blah2′ as two|first|
|Store Query|Select ‘lala’ as one, ‘lolo’ as two|second|
|compare stored queries|second|first|
|one|two|
having two consecutive Store query statements doesnt work, as the second one seems to not get run (might be a bug). The strange thing about breaking the table up is that the normal query fixture works if it is part of the whole table:
!|dbfit.SqlServerTest|
|Connect|Server=${server};Database=${database};Trusted_Connection=yes;|
|Query|select * from ${entTable}|
|DashboardID|UserID|DashboardValue?|DashboardValue2?|
|1|person1|$434,821|12.34|
|14|person1|12|null|
|19|person2|11/10/2005|null|
|19|person3|11/10/2005|null|
…
works fine for me.
And yes, i cannot use true flow mode without combining all the statements into one table, since this is the last assertion i make in my tests after a lot of setup. I’ll try to use the database environment fixture, but I’m having trouble finding examples on how to use them. If you could point me towards examples on using database environment fixtures with other ones, or if you are willing to write up an example page for me I’d be grateful
Thanks!
Hi Kevin,
See http://www.fitnesse.info/dbfit:databaseenvironment for a more detailed explanation of DatabaseEnvironment and a simple example. Also, see http://www.fitnesse.info/dbfit:modes for a more detailed explanation of standalone and flow mode differences.
If you download the dbfit-complete package, you’ll find a bunch of examples in DotNet.AcceptanceTests.OracleAcceptanceTests.StandaloneFixtures, DotNet.AcceptanceTests.SqlServerAcceptanceTests.StandaloneFixtures, NewJava.AcceptanceTests.OracleTests.StandaloneFixtures and NewJava.AcceptanceTests.MySqlTests.StandaloneFixtures namespaces.
Hi Gojko
Is it possible to check number of records in ref cursor. Similar functionality to QueryStats which would take a ref cursor for input.
Thanks
Awesome, thanks i got it working!
One quick note; i could not find a fixture class in the .net dlls named CompareStoredQueries, so i had to utilize the DatabaseTest flow-fixture for this. i’m not sure if this is intentional or if there’s supposed to be a comparestoredqueries fixture defined somewhere that i couldn’t find right away. My final tables look like this:
!|dbfit.fixture.DatabaseEnvironment|sqlserver|
|Connect|Server=${server};Database=${database};Trusted_Connection=yes;|
!|dbfit.fixture.StoreQuery|select * from ${entTable}|first|
!|dbfit.fixture.DatabaseEnvironment|sqlserver|
|Connect|Server=${entserver};Database=${entdatabase};Trusted_Connection=yes;|
!|dbfit.fixture.StoreQuery|select * from ${sourceTable}|second|
!|dbfit|SqlServerTest|
|CompareStoredQueries|first|second|
|DashboardID|UserID|DashboardValue?|DashboardValue2?|
thanks again for your help!
Hi Mitja,
not yet, I’ll add that to the next version.
Hi Kevin,
My bad - nice catch! Get the new version from
http://dbfit.svn.sourceforge.net/svnroot/dbfit/dbfit/dist/dbfit-dotnet-20080107.zip
I’ve changed the class to public, you should be able to use it directly now.
Btw, no need to use |sqlserver| the second time, it will actually be better if you don’t. Use that only to connect to a different type of database. So here is what I suggest you use:
Hi.
I’m analyzing dbfit por my development team but we still develop on SQL Server 2000.
I have made tests with QUERY and EXECUTE and everything works fine. However, since SQL SERVER 2005 uses sys.columns, I cannot use INSERT in dbfit tables… Do you think you could make dbfit compatible with SQL Server 2000 for Inserts?
This code works fine:
!|Execute|Insert into MyTable values (’123456789012345′,’012345678901234′|
The next code doesn’t work. The error is “System.Data.SqlClient.SqlException: Invalid object name ’sys.columns’.
”
!|Insert|MyTable|
|Column1|Column2|
|123456789012345|012345678901234|
I think dbfit works awesome and its a gret idea and tool, thanks!
Hi Oscar,
I’d be more than happy to make it work with Sql Server 2000, but I’ll need your help to do it. If you have a bit of time to spend testing and know how to extract meta-data from sql server 2000, let’s do it.
Initial support for SQL Server 2000 is done: see initial support for sqlserver 2000 in dbfit for more information.
Hello,
Is there any way to get a return value from a Stored Procedure on SQL Server 2005? I tried following the docs for return values from functions — using a question mark — but this doesn’t seem to work.
For example:
!|EXECUTE PROCEDURE|SomeStoredProc|
|SomeInput|?|
|Blah|1|
However, the ‘?’ is hillighted red when I run this and I get the following exception:
System.ApplicationException: Cannot find parameter
at dbfit.fixture.ExecuteProcedure.InitParameters(Parse headerCells) in D:\work\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 128
at dbfit.fixture.ExecuteProcedure.DoRows(Parse rows) in D:\work\dbfit\impl\dotnet\src\fixture\ExecuteProcedure.cs:line 43
at fit.Fixture.DoTable(Parse table)
at fitlibrary.FlowFixtureBase.ProcessRestOfTable(Fixture theFixture, Parse theRestOfTheRows)
I tried working around this by doing
|QUERY|!-
DECLARE @RetVal BIT
EXEC @HasAccess = SomeStoredProc ‘Blah’
SELECT @RetVal as Ret
!-|
|Ret|
|1|
However, the !- -! syntax doesn’t seem to be working on SQL Server 2005.
I tried this:
!|QUERY|!-
SELECT 1 as Val
!-|
|Val|
|1|
and got this exception:
fitlibrary.exception.InvalidMethodException: Missing method ‘val’ with 0 argument(s).
at fitlibrary.Method.FindFirst(Object[] theTargets, String theMethodName, Int32 theParameterCount)
at fitlibrary.FlowFixtureBase.FindMethod(CellRange theCells)
at fitlibrary.FlowFixtureBase.ProcessFlowRows(Parse theRows)
BTW, DBFit is awesome. Thanks so much for working on it.
Thanks,
Reuben
Hi Reuben,
the error suggests that your stored procedure has an output parameter and is not a stored function. ? by itself only works with a stored function. with a stored procedure, specify the output parameter name (i.e. myvalue?). If this does not answer your question, please send me the stored procedure to test.
Stored procedures in SQL Server 2005 can return values without having an output parameter.
For example:
CREATE PROCEDURE dbo.ReturnOne
(@Arg VARCHAR(100))
AS
BEGIN
RETURN 1
END
Then we could use this stored procedure as:
DECLARE @One INT
EXEC @One = ReturnOne ‘blah’
It would be great if I could get this returned parameter using the same syntax as a stored function (using ‘?’ to denote the return value:
!|EXECUTE PROCEDURE|ReturnOne|
|Arg|?|
|Blah|1|
Hi Reuben,
In that case, I would suggest that you write a small wrapper function. There is no way to know that such a procedure will return any output and what the type of such output will be. In order to have it integrated with DbFit, I need to know the type of result in advance.
There is one exception - if the procedure opens a cursor, then it can be handled with the “query” fixture. see StoredProcReturningQuery.
Hello,
first thank you for publishing dbfit, it improves our tests a lot. I on the way to use more features with our oracle db and get the following exception when i try to put the query result in a variable. The problem arises with a column which is defined as NUMBER(13)
java.lang.NumberFormatException
at java.math.BigDecimal.(BigDecimal.java:368)
at java.math.BigDecimal.(BigDecimal.java:647)
at dbfit.util.BigDecimalParseDelegate.parse(BigDecimalParseDelegate.java:6)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at fit.TypeAdapter$DelegateClassAdapter.parse(TypeAdapter.java:394)
at dbfit.util.DbTypeAdapter.parse(DbTypeAdapter.java:46)
at dbfit.fixture.RowSetFixture.findMatchingRow(RowSetFixture.java:102)
at dbfit.fixture.RowSetFixture.doRow(RowSetFixture.java:86)
at fit.Fixture.doRows(Fixture.java:165)
at fit.ColumnFixture.doRows(ColumnFixture.java:20)
at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:73)
at fit.Fixture.doTable(Fixture.java:157)
at fitlibrary.FlowFixture.interpretTableWithFixture(FlowFixture.java:89)
at fitlibrary.FlowFixture.runRow(FlowFixture.java:123)
at fitlibrary.FlowFixture.doTable(FlowFixture.java:99)
at fitlibrary.FlowFixture.doTable(FlowFixture.java:92)
at fitlibrary.FlowFixture.interpretTableWithFixture(FlowFixture.java:89)
at fitlibrary.FlowFixture.interpretTable(FlowFixture.java:62)
at fitlibrary.FlowFixture.interpretTables(FlowFixture.java:46)
at fitlibrary.FlowFixture.interpretTables(FlowFixture.java:27)
at fit.Fixture.doTables(Fixture.java:73)
at fit.FitServer.process(FitServer.java:74)
at fit.FitServer.run(FitServer.java:50)
at fit.FitServer.main(FitServer.java:41)
Hi Gojko,
I just implemented the compare stored queries feature for a procedure that returns about 50 columns and hundreds to thousands of records…this feature is awesome! It only took me about half an hour to do - and that was just because I had to write the complicated query that returns the expected results. Thank you!
Hi Gojko,
I am trying to use the DBFit DatabaseTest fixture on a SetUp page for a Selenium WebTest page.
eg
TryWebTest — a Test page that implements the Selenium WebTest fixture you have here on your site.
TryWebTest.SetUp — a page that implements SQLServerTest from DBFit — I’m using it to set up data so that I can bring up a web page with certain data via the TryWebTest page.
When the test runner tries to execute the first WebTest command, I receive the following error:
fitlibrary.exception.InvalidMethodException: Missing method ’startbrowser’ with 7 argument(s).
I believe this is because DBFit’s DatabaseTest is the first fixture instantiated and it is a flow-style, so when I try to use the WebTest fixture, the WebTest fixture is not recognized.
What would be the best way to organize these pages so that I can use DBFit to create test data and then run the WebTest fixture such that it utilizes values from symbols assigned on the DBFit set-up page?
Thanks for your help…and I hope that made sense.
Hi Marisa,
You can use DbFit in standalone mode. Then webtest fixture should control the page in flow mode, and dbfit fixtures will be used as standalone. See http://fitnesse.info/dbfit:modes and http://fitnesse.info/dbfit:databaseenvironment
Hi Gojko,
I’m attempting to use DBFit to test against Oracle stored procedures that have IN/OUT parameters, not plain IN or OUT params. A sample stored procedure I test against is as follows:
create or replace
procedure sut_procedure
( param in out varchar2
) as
begin
if param = ”
then
param:=’XX’;
end if;
end sut_procedure;
The test tables that I’ve used, are:
1>
!|dbfit.OracleTest|
!|connect|10.80.150.187:1521|fcjitr2|fcjitr2|fcjdev|
!|execute procedure|sut_procedure|
|param?|
|XYZ|XYZ|
2>
!|dbfit.OracleTest|
!|connect|10.80.150.187:1521|fcjitr2|fcjitr2|fcjdev|
!|execute procedure|sut_procedure|
|param|param?|
|XYZ|XYZ|
3>
!|dbfit.OracleTest|
!|connect|10.80.150.187:1521|fcjitr2|fcjitr2|fcjdev|
!|execute procedure|sut_procedure|
|param?|param|
|XYZ|XYZ|
Both of them, when executed, throw java.sql.SQLException. The stack trace is as follows:
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1681)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at dbfit.fixture.ExecuteProcedure.runRow(ExecuteProcedure.java:208)
at dbfit.fixture.ExecuteProcedure.executeStatementForEachRow(ExecuteProcedure.java:158)
at dbfit.fixture.ExecuteProcedure.doRows(ExecuteProcedure.java:117)
at fit.Fixture.doTable(Fixture.java:157)
at dbfit.fixture.ExecuteProcedure.doTable(ExecuteProcedure.java:96)
at fitlibrary.FlowFixture.interpretTableWithFixture(FlowFixture.java:89)
at fitlibrary.FlowFixture.interpretTable(FlowFixture.java:62)
at fitlibrary.FlowFixture.interpretTables(FlowFixture.java:46)
at fitlibrary.FlowFixture.interpretTables(FlowFixture.java:27)
at fit.Fixture.doTables(Fixture.java:73)
at fit.FitServer.process(FitServer.java:74)
at fit.FitServer.run(FitServer.java:50)
at fit.FitServer.main(FitServer.java:41)
Any ideas on how to go ahead resolving this ? I presume that there would some changes made to the source code. Btw, the Callable Statement is prepared using the following string { call sut_procedure(?)} in the first case, and { call sut_procedure(?,?)} in the latter two.
On a related note, I’ve noticed that a minor fix needs to be made in GetParameterDirection() of dbfit.environment.OracleEnvironment class. Parameters that are both IN and OUT, are represented using IN/OUT and not ‘IN OUT’. You may want to include this change. Sorry, couldnt get a diff file out to help you
Thanks for all the help, offered here!!
Hi Gojko,
Thank you - I am in the process of updating our tests to use standalone mode where required.
I read the databaseenvironment documentation and saw that there is a way to exit standalone mode - is there a way to exit flow mode? Here’s what I’m wanting to do:
1) Initialize the WebTest fixture
2) Connect via flow mode so that I can
3) Use SetParameter and ClearParameters (I use a lot of included pages that implement symbols, so I need to initialize those all to null in each suite’s SetUp) - from what I can tell by trying to use SetParameter and ClearParameters, and by looking at the source code, it looks like these are only available in DatabaseTest (flow mode)?
4) Exit flow mode if possible, then Connect via standalone mode so that I can set-up test data
5) Use the WebTest fixture
I hope that makes sense - I appreciate any guidance you can provide.
Hi Gojko,
Sorry - my last post was not very clear. I can successfully use standalone mode on WebTest pages, so that’s great. The core issue is that I cannot use SetParameter or ClearParameters. From looking at the source, it looks like those are only available in flow-mode. If I had more development experience, I would try to figure out how to add those commands to standalone mode…
So at this point is it just not possible to use SetParameter and ClearParameters in standalone mode?
THANK YOU for your help!
Geez…sorry…don’t know why I didn’t think of this before. I just added a method to WebTest that calls fit.Fixture.ClearSaved - that’s all I needed. Thanks.
Hi Vineet,
IN OUT parameters are not yet supported, I still cannot think of a good enough syntax for that. That is one of the things that is on the top of my list. A workaround is to create a simple wrapper procedure that splits that IN OUT into one IN and one OUT argument.
Hi Gojko,
I was about to mail you. Thought my comment was treated as spam, for a moment. One of the methods, that I had thought of, to achieve IN OUT parameters, was to create 2 separate columns in the test table, one for the IN param, the other for the OUT param (the name of this column will be the same as the column for IN, but with a trailing ?).
Of course, this results in a different CallableStatement being prepared, than the one we expect to create. And if I’m not wrong, the support for IN OUT parameters, might have to create the appropriate CallableStatement. Havent got to work on this idea yet, but I’ll let you know how this comes across. Will send you the patch, if this comes through. Thanks once again.
Regards,
Vineet
Hi Vineet,
I was away for a few days, that is why I did not reply earlier. I thought about using that syntax already, but that would require changing the underlying structure quite a bit, because one DB parameter will be mapped to more than one cell. However, from the point of usage, that seems the simplest, so it would be a logical thing to do.
Does anyone else have any thoughts on this?
Hello again gojko,
I have another request for comparing stored queries against each other. what i’m doing is comparing results from INFORMATION_SCHEMA.COLUMNS in sql server between a static table and a generated table. The table that gets generated has a few extra columns added to it by design, and i’d like to make sure that the columns in the static table are in the generated table as well. however i cannot use the compare stored queries functionality because of the extra columns that are in the generated table. I would normally remedy this by using subqueries but the two tables i’m comparing now are on different databases and different servers. So my request is this:
Would it be possible to add a facility to compare stored queries with the assumption that one query is supposed to return a subset of the other query?
Let me know what you think. Thanks so much!
Hi Kevin,
compare queries should work only on the columns that you listed in the fitnesse table. just don’t specify the columns that are not in the other table. does this solve your problem (or have I misunderstood something?)
Thats not exactly what i meant, yeah. i query the INFORMATION_SCHEMA.COLUMNS view of the DB in sqlserver and that returns a bunch of rows with descriptions of the columns in the table i want. thats why i’m getting this issue, since the query about the columns for the generated table returns more rows than the static table.
However this isn’t so important anymore, because i just hardcoded a filter into the query to ignore the extra columns in the generated table.
Experimental support for IN/OUT parameters is now available. Please help test it by verifying that your current tests work.
See http://gojko.net/2008/02/04/dbfit-experimental-support-for-inout-parameters/ for more information
Hi Gojko,
I’m curious if you plan on implementing support for SyBase at some point? My company does some sybase work and it would be nice to be able to leverage dbfit against that. It’s not a huge priority though, since i would imagine extending support to a brand new database server would likely be difficult i’d imagine but it definitely would be nice to have :).
Hi Kevin,
A few people asked about sybase support already — I do not expect that implementing that would be more then a few hours of effort if someone knows how to use sybase. My problem is that I don’t have a test server for sybase and have never used it, so I cannot implement that. If you would be interested in helping out (or perhaps get someone from your company to do it), I would be glad to help.
Hi Gojko,
I tried to use ConnectUsingFile but got an error about my connection string value. I’ve tried specifying the connection string in the following ways, but got the exception each time.
connection-string=Data Source=ourServerName;Initial Catalog=ourDBname;Integrated Security=True
connection-string=Data_Source=ourServerName;Initial_Catalog=ourDBname;Integrated_Security=True
connection-string=server=ourServerName;database=ourDBname;uid=username;pwd=password
This is the exception I get with any of the above connection strings:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
I’m sure I just don’t have the connection string in the right format - the page on fitnesse.info http://fitnesse.info/dbfit:connection_properties_file states to use a “full ADO.NET connection string” - I Googled for that and couldn’t really find a “spec” for that.
Thanks for your help!
Hi Marisa,
SQLServerEnvironment uses this format for the connection string
data source={0};user id={1};password={2};database={3};
so the equivalent of specifying properties individually would be something like
data source=ourDbServer;user id=dbfitDbUser;password=dbFitDbPassword;database=testDb;
That format should work for you. let me know if it does not and i’ll add some troubleshooting info to that method.
Hey gojko,
If you need help testing sybase support in .net/java, i’d be happy to test or do whatever is needed on our local sybase server. let me know.
Hi Kevin,
yes - that’s one of the things I need to implement sybase support (thanks for offering to help). We also need someone who knows his way around sybase internals, especially how to retrieve meta-data about table columns and procedure parameters.
Hi Gojko,
I searched the DbFit source code and it looks like VarBinary is supported - but when I try something like the following, I get strange results:
!|Query|SELECT CAST(’junkjunkjunk’ AS VARBINARY) AS VarbinValue|
|VarbinValue?|
|>>myValue|
What gets stored in myValue is “System.Byte[]”
I’ve tried a few different things but can’t seem to get this working (it works in a SQL query window though).
Thanks for your help.
Hi Marisa,
You see System.Byte[] because that is how .NET prints a byte array. The object should have been stored correctly as a byte array, and you should be able to use 0xHEXDIGITS syntax for comparisons. See BinaryTests acceptance test for examples.
OK that makes sense…and the symbol works fine in the rest of the test page.
Thanks!
Insert and null values.
I would like to setup known data in my SQL2005 with dbFit. To make it look like liva data I need to insert null values to several columns. I just can’t find out if it’s possible and if it is how to? I get an error message if a column is empty ‘Cannot use input parameters as output values. Please remove the question mark after ‘.
Thanks
Hi Haxen,
I have not heard anyone else complaining about inserting nulls. The error would suggest that you are doign an insert into a column that has a question mark in the header. question marks are used to signal output (i.e. columns with automatically generated IDs that should be populated into symbols). Can you paste your FitNesse table here?
Sure Gojko,
!|Insert|cmtyUsers|
|FName|LName|RegistrationStep|COAddress|Address|ZipCode|City|CellPhone|Phone|
|Test|User|4||Snickargatan 5|90360|||703980395|
Is it because the camelcased column names?
Hi Haxen,
In FitNesse, empty cell generally means “print the current value, don’t test”. I guess that’s why you get the message that the input parameter (insert value) cannot be used for output. The error message is wrong, and I’ll look into how to disable this functionality for inserts. You should be able to use the keyword NULL to insert nulls. try
!|Insert|cmtyUsers|
|FName|LName|RegistrationStep|COAddress|Address|ZipCode|City|CellPhone|Phone|
|Test|User|4|NULL|Snickargatan 5|90360|NULL|NULL|703980395|
it’s definitely not because of camelcased names, since column names are case insensitive.
Hi Gojko,
I have a question about the following excerpt from the PDF:
* Test suite includes and initialisations can be added to either SuiteSetUp or SetUp – however, I
strongly suggest using SetUp for that - this makes sure that the environment will be properly
initialised for all the tests if they are executed separately. In some versions of FitNesse,
SuiteSetUp will not be run when you execute individual test pages in the suite.
* Only the actions that really have to be executed once and only once for the entire suite run
should be added into SuiteSetUp.
I am reorganizing our suites right now and wanted to try to put the DbFit connection on the main SuiteSetUp page - I verified that individual tests still work because SuiteSetUp is run prior to the test (b/c I have the latest version of FitNesse). However, Running the suite causes exceptions such as — “Type ‘executeprocedure’ could not be found in assemblies.”
It appears as though the DB connection is lost after exiting the SuiteSetUp page - is this what you would expect? (Is that why you suggested that the DB Connection is put on the SetUp page instead?)
Thanks for your help.
I would imagine that SuiteSetUp is executed as a full, separate page. That would cause the connection to be closed and transaction rolled back on the end. I suggest using SetUp/TearDown to prepare the connection - they are executed as part of the test page, not as a separate page.
In reply to wat Johannes said in Coment number 45
I got the create procedure workin by doin som thing lik this, but I had to spend a lot of time aariving at this hack. It now aalows me to create procedure which has variabls declared
!|execute|!-exec (’create proc TestUtil_DropCampaignDisplayTables
as
declare @campaignDisplayTables TABLE (
rowID int identity (1,1),
tableName varchar(250)
) ;
insert into @campaignDisplayTables select name from sys.tables where name like ”%_Campaign_Display%”;
declare @CurrentRowId int;
declare @NoOfRows int;
declare @TableName varchar(250);
select @NoOfRows=isnull(count(*),0) from @campaignDisplayTables;
if(@NoOfRows>=1)
begin
set @CurrentRowId=1;
while(@CurrentRowId<=@NoOfRows)
begin
select @TableName=TableName from @campaignDisplayTables where rowId = @CurrentRowId;
exec(”drop table ['' + @TableName+'']”);
set @CurrentRowId=@CurrentRowId+1;
end
end’)-!|
How can I create tables with dynamic names for eg.
We have tables that get created evry day which are prefixed with dates for eg. (20070221_Campaigndisplay).
We have another RaiseInvoice procedure which picks up rows from this daliy table and enters the rows in Transaction tables and renames this daily table.
So for testing the RaiseInvoice procedure I needed to create setup data through DBFit test so for doin this I needed to created dialy tables with date appended, the only hack I could find out was this way.
I have not added the actual column names of the daily tables to keep it simple.
|Query| select getdate() + ‘_Campaign_Display’ as yesterdaystablename, ‘done_’ + getdate() + ‘_Campaign_Display’ as Donetablename |
|yesterdaystablename?|Donetablename?|
|>>fetchedTable|>>doneTable|
!|Query| Exec ( ’select 1 as RR;create table ‘ + @fetchedTable + ‘ (id int)’)|
|!-RR-!|
|1|
!|Query| Exec ( ’select 1 as RR;create table ‘ + @doneTable + ‘ (id int)’)|
|!-RR-!|
|1|
may be there is som better way of doin this
Hi Vipresh,
you can use markup variables to do that. Markup variable is a pre-processing macro, so you could do something like this
!define currentdate {20070221}
!|execute|create table ${currentdate}_campaign_display …|
you can initialise a variable from the properties file or operating system environment (use -D in the run.bat file that starts fitnesse).
Please note, however, that I would recommend changing this approach to a single table that has a date column, possibly partitioned by dates if you want to split storage. Using tables like this is very strange.
Thanks Gojko,
Sorry for the late reply but it worked out with the NULL keyword. That was what I was looking for, is it in the doc’s?
Null keyword is in fitnesse docs. I’ll add more about it into the new dbfit docs as well.
Hi Gojko,
I am seeing strange behavior in a particular case - can you please let me know if I am doing something wrong?
When I try to insert a value into an NTEXT column using the Insert fixture, the value is truncated to 16 chars:
!|Insert|MyTable|
|RecordID|ContentNtext|
|123|This is some content that is longer than 16 chars.|
The following fails:
!|Query|SELECT ContentNtext FROM MyTable WHERE RecordID = 123|
|ContentNtext?|
|This is some content that is longer than 16 chars. EXPECTED|
|This is some con ACTUAL|
If I use !|Execute|INSERT INTO(….| then the value is NOT truncated, so I can use this method instead but just wondered why I can’t use the Insert fixture. Thanks for your help.