Ideas on designing program

Xcellor

Hi all,

Just wondering if anyone has suggestions on how to implement this. Basically I want to automate part of a testing routine. I need to check the presence of data in specific columns in a database. So the value should not be NULL or whatever I set it not to be.

The basic SQL command would be

SELECT *
FROM TABLE
WHERE COLUMN is NULL

Now there can be hundreds of columns involved and these columns are generated based on data in columized XLS sheets.

The format is Sheet2 contains in Column A the table name in Column B the columns for that table like...

Table1 Thisisonecolumn_of_table1
Table1 This_is_anotherof_table1
Table1 This_is_anotherof_table1
Table2 This_the_first_column_oftable2
Table2 This_is_the_second

So essentially what I want to do is extract this data to form an SQL statement along the lines of

SELECT * FROM TABLE1, TABLE2
WHERE COLUMNSFROMABOVE IS NULL

I would prefer to develop the solution in Java but could also do in PHP

Any suggestions on approaches, tutorials etc. I've not developed code in a long time so be kind

Thanks,
X

seamus

PHP might be the boy for this.

From what I understand, you want to take a table/column pair from the spreadsheet, find out if that column contains any null values and report back?

In that case, I'd save the excel sheet as CSV.

Then read the CSV in, one line at a time, check for nullity and output the number of matching rows to another CSV file

PHP Pseudocode which probably doesn't work:
[php]
while(!feof($infile)) {

//Read in the next line
$line = fgets($infile);

//Break out the columns
list($tablename, $column) = split("," $line);

//Perform the query and get the number of rows returned
$query = "SELECT * FROM `$tablename` WHERE `$column` IS NULL";
$row_count = mysql_num_rows(mysql_query($query));

//Output it to another csv file
$output = "$tablename,$column,$row_count";
fputs($output, $outfile);

}[/php]

You could also have a third column in your input file which contains the value that you're looking for. So your query looks like
SELECT * FROM `$tablename` WHERE `$column` = `$value`

Just remember that you can't use the = operator to check for nullity, you have to say IS NULL, so you'd need to code for that.

kayos

It’s easy enough if you do it all via SQL. BTW I’m assuming SQL Server here but it even if it is not most of what I’ve below will still apply

All you need to do is allow your testers to use the excel sheet to input their test params. Then a quick import of this into the DB after that its one big script/stored procedure .

Some things I would add to this

1) A TestID to allow you group multiple conditions into one test e.g. WHERE ColA = 1 AND ColB = 2
2) A operator column to allow the testers to specify =, >, IN, NOT IN etc etc

Once the excel sheet has been completed you can upload this via DTS/SSIS creating this import will be a two second thing. The DB tables I would create something along the following format

TestDetail
TestID
TestName
TestTable
CreatedDate <- The date you imported the test
LastRunDate <- The last Datetime the test was run
LastRunResult <- The Result
LastRunSQL <- The SQL used


TestSteps
TestID
StepID
TestColumn
TestOperator
TestValue

Then your code would be sometime along the lines of

Foreach Test
Create the start of your SQL Statement i.e. SELECT 1 FROM TestTableName WHERE
Foreach Step
Find Datatype of Column use this to either wrap your test value in quotes etc
Create the Step SQL String i.e. ColA = ‘1’
Append the Step SQL to the Test SQL
loop
Run the SQL
Check your rowcount
Update the TestDetailTable with a datetime stamp in LastRunDate, Put the result in LastRunResult and store the SQL.
Loop
Return the TestID, Name and Result to the user.

QED

This is a very basic way of doing it. I would prob expand this to include a TestResults table so we can store the results off on their own. Also would expand it to allow for a multiple table query assuming the tables have been setup with correct FK’s as you can write the SQL to join them without extra input from the users then.

Just to point out you will need to ensure the part where you make up your SQL checks the column datatype and your SQL is written to take this into account; failing to do this will cause problems.

Another option rather than excel and a DTS import would be using Access to look directly at the tables on the SQL Server.

stephenlane80

kayos wrote: »

Once the excel sheet has been completed you can upload this via DTS/SSIS creating this import will be a two second thing. The DB tables I would create something along the following format

You could dynamically link to the spreadsheet from TSQL rather than import it:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Sheet1$])