Dec 27 2007
DbFit 0.93: Storing and Comparing Queries
DbFit 0.93 has just been released. Pick it up from SourceForge.
New and noteworthy
- Storing query results into fixture symbols
- Comparing queries stored into symbols
- SQL Server support for GUID and SQL_Variant types (variant is treated like a string)
- .NET version updated to use FitNesse.Net 1.3
- Loading connection settings from a text file on server
Storing and comparing queries
DbFit 0.93 allows you to store a query result for later and then compare it to another query result. All database integrations for both .NET and Java support storing queries directly into fixture symbols (global variables) with the following syntax:
|Store query|select ....|symbolname|
after this, the query results in detached form (DataTable class) are stored into the fixture symbol symbolname. You can use it later to compare against another dynamically loaded query:
|Compare stored queries|symbol1|symbol2| |col1|col2|col3|
The query structure must be listed in the second row. Use InspectQuery to build it quickly if you do not want to type it. Column structure is specified so that some columns can be ignored during comparison (just don’t list them), and for the partial row-key mapping to work (put a question mark after the column names that do not belong to the primary key). The comparison will print out all matching rows in green, and list rows that are in just one query with red (and fail the test if such rows exist).
Loading connection properties from a file
Use Connect Using File instead of Connect if you want to keep connection properties stored in a file. Specify the file path, either absolute or relative to run.bat, as the argument. For connection file example, see TestDBConnection.properties. Here is an example how to use it:
!|dbfit.OracleTest| |Connect using file|testdbconnection.properties|
 |
 |
Hello, Gojko,
My best wishes for a creative 2008.
I already read your pdf files getting fit with .Net and with Databases in the middle of 2007.
I had a lot of coldwater fear (I don’t know if it exists in english, but in Flemish it means I was afraid of going forward)
The first resolution of 2008 was Just do it pattern…
The database test are going well altough I am only at the start of the journey.
Thank you very much for the work done and I will try to give you more feedback in 2008…
Trying to use Connect using file with ver 093
I get the error Missing method: public TypeOfResult connectUsingFile(Type1 arg1) { } in class dbfit.OracleTest
Hi Paul,
thank you for pointing this out — I have no idea how this was missing, but it is now back in. Download the intermediate build with support for that and please test dbfit-20080220.jar.
I LOVE the idea of storing the results of a query into a variable and then being able to compare them later. But my question has to do with the instance where one of the sets of rows is coming from a DBFit query, and the other set is coming from a user-written RowFixture (we can change the fixture we use if need be).
For example:
|UserFixture.GetCollegeList()|
|collegeID?|decisionType?|
|>>id1|>>decision1|
|>>id2|>>decision2|
|>>id3|>>decision3|
|Query|Select collegeID, decisionType where userID = 6|
|collegeID|decisionType|
|<<id1|<<decision1|
|<<id2|<<decision2|
|<<id3|<<decision3|
EXCEPT…that we don’t know how many rows are going to come back for each test, so doing it the above way won’t work.
We could, if need be, do the two things in a different order (ie we could do the Query first if that would help).
Any thoughts on using the new features of DBFit to accomplish this?
Hi Becky,
If you store the output of your rowfixture into a DataTable (and put that into a symbol), you can pass that to the query and it will handle it correctly. (in .NET it’s the standard DataTable class, in Java it’s the dbfit.util.DataTable class.
gojko
Gojko,
Thanks! I am guessing you mean for us to write the fixture so that it looks something like this?
|store|UserFixture.GetCollegeList()|ourTable|
|store query|Select collegeID, decisionType where userID = 6|ourQuery|
|Compare stored queries|ourTable|ourQuery|
|collegeID|decisionType|
Yes, exactly. instead of “store”, you can use “Set Parameter” from DbFit.
Gojko,
How do I store my rowfixture output into a DataTable (.Net)?
If I try the following, it returns an error:
“System.ApplicationException: Cannot load a stored query from testTable – is is empty”
!|StoreQuery|!-SELECT * FROM TempTable-!|queryResult|
!|Set Parameter|FitnesseProject.FitnesseTest.TestRowFixture|testTable|
|Compare Stored Queries|queryResult|testTable|
|col1|col2|col3|
.Net Code
Public Class TestRowFixture
Inherits fit.RowFixture
Public Overrides Function Query() As Object()
Dim _returnArray As New ArrayList
_returnArray.Add(MyDataTable)
Return _returnArray.ToArray
End Function
Public Overrides Function GetTargetClass() As System.Type
Return GetType(DataTable)
End Function
End Class
Hi Davesh,
you need to do that from the code. Set Parameter assigns string values to symbols, and
!|Set Parameter|FitnesseProject.FitnesseTest.TestRowFixture|testTable|
will actually create a symbol “FitnesseProject.FitnesseTest.TestRowFixture” and give it a value of “testTable”. Create a symbol with Fixture.Save and then use that symbol in compare stored queries.
Hi again Gojko,
This is Becky. I am working with Davesh. We are trying to figure out the same issue.
We have a fixture (RowFixture) that we are trying to compare the output of with a “store query” from dbfit.
You mentioned above to “store the output of your rowfixture into a DataTable”. How is it that we do that? If we inherit RowFixture, and try to have it return a DataTable, we get a compiler error (since it needs an array). If we return an array OF DataTables, that also doesn’t seem to work.
Could you point us to a sample of doing this?
I apologize if we are not seeing the obvious here, but we are feeling quite stuck.
Thanks!
- Becky
Hi Becky,
you said that you could modify the source for your rowfixture. Instead of doing the query and putting it to the screen, store the result into a symbol.
So don’t return the datatable, use Fixture.Save to store the result into a symbol.
hi gojko,
Can we use the result of Dbfit query as input to coulumn fixture?
We are using Fitness+Selenium for our functional testing.
We want to use values from Database.
Can you pls explain?
Hi raghavendra,
you can store individual cells of dbfit output into symbols and then use that in the column fixture.
Hi gojko!
thanks for your reply.
Can you give an example!
here i used some thing like this!
!|dbfit.MySqlTest|
!|Connect|real-map-lm03.websys.aol.com:3306|travelread|travelread|travel|
get cityid from db
|Query|select continent,country as ct,cn from hubpage where country_name=”France”|
|ct?|cn?|
|>>continent|>>country|
|travelQa.Hub_country|
|url|continent|country|verifytagcloudmodule?|
|/travel-guide|<<continent|<<country|Passed|
can you pls send your reply to my mail also
Thanks
Raghavendra CH
Hi,
it seems that you are using the java version, which still does not support < < and >> syntax for symbols. DbFit extends the standard fitnesse model to provide that in dbfit fixtures, but normal java fixtures do not support that out of the box. you should map your cells to symbols manually. See the fixture gallery on http://fitnesse.info for more information on how to do that.
Hi gojko,
Thanks for your information.
I could able to use the parameters from DBfit using symbols.
one more query
i want to use parameters in query section in Dbfit
can it be possible?
like
|Query|select continent,country as ct,cn from hubpage where country_name=”country”|
|ct?|cn?|
|>>continent|>>country|
Raghavendra,
symbols are supported in the query fixture in both java and .net
hi gojko,
i need some info.
can it be possible to test xml using fitnesse.
if possible can you pls explain with example
thanks
raghavendra ch
hi raghavendra,
.net implementation has an xml fixture. i don’t know of a similar fixture for java.