Q: Using test units with Jenkins

Could you give any hints on how I could use SA Unit tests with Jenkins for CI and build validation?

Two methods are available for running Unit Tests unattended.
1. You can schedule a periodic job, for example, daily, and in the Unit Tests project set settings to have it email you if any test fails. Jenkins isn't used directly here.
2. You can use sacmd.exe command line utility to run Unit Tests project from external applications. This can be used to call it from Jenkins

For example:

	Code:
	"C:\Program Files (x86)\SQL Assistant 9\sacmd.exe" ut:"C:\tests\sa_my_project.unittest" sas:"%APP_DATA%\SQL Assistant\9.0\SQLAssist.sas" conn:"DB Connection Name"

Please note that I used %APP_DATA% environment variable in the above example as a shortcut, but that would work only if you run Jenkins service using your interactive account. Otherwise, you would need to specify full file path to your .SAS file not using environment variables specific to your account.

For more details please run sacmd /? from command prompt window.

I'll implement one of those methods as soon as I manage to get it on track. I recall this working in past version but now I get an exception:

Quote:

2016-12-10 09:22:30 SqlEditor64.exe SA=9.0.194; Exe=C:\Program Files (x86)\SQL Assistant 9\SqlEditor64.exe; Cmd="C:\Program Files (x86)\SQL Assistant 9\SqlEditor64.exe" ; LogLvl=1; MsgLvl=0
2016-12-10 09:22:30 SqlEditor64.exe Client: Attach
2016-12-10 09:22:30 SqlEditor64.exe Client: Create
2016-12-10 09:22:31 SqlEditor64.exe SA=9.0.194; Exe=C:\Program Files (x86)\SQL Assistant 9\SqlEditor64.exe; Cmd="C:\Program Files (x86)\SQL Assistant 9\SqlEditor64.exe" ; LogLvl=1; MsgLvl=0
2016-12-10 09:22:31 SqlEditor64.exe Prefs: Attach
2016-12-10 09:22:31 SqlEditor64.exe Client: SetCbtHook
2016-12-10 09:22:31 SqlEditor64.exe Client: CreateTargetList
2016-12-10 09:22:31 SqlEditor64.exe TargetList: SetSuspend=0
2016-12-10 09:22:31 SqlEditor64.exe TargetList: Create: Rok=1
2016-12-10 09:22:31 SqlEditor64.exe TargetList: SetSuspend=0
2016-12-10 09:22:31 SqlEditor64.exe TargetList: EnumWnd (class=TsaSyntaxMemo*)
2016-12-10 09:22:31 SqlEditor64.exe TargetList: SetSuspend=0
2016-12-10 09:22:32 SqlEditor64.exe fmDocRestore: Create
2016-12-10 09:22:34 SqlEditor64.exe fmDocRestore: Release
2016-12-10 09:22:34 SqlEditor64.exe Client: SetCbtHook
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Pref=SQL Assistant Code Editor
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Code=16
2016-12-10 09:22:34 SqlEditor64.exe Client: SetCbtHook
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Pref=SQL Assistant Code Editor
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Code=16
2016-12-10 09:22:34 SqlEditor64.exe Client: SetCbtHook
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Pref=SQL Assistant Code Editor
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Code=16
2016-12-10 09:22:34 SqlEditor64.exe Client: SetCbtHook
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Pref=SQL Assistant Code Editor
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Code=16
2016-12-10 09:22:34 SqlEditor64.exe Client: SetCbtHook
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Pref=SQL Assistant Code Editor
2016-12-10 09:22:34 SqlEditor64.exe Target: Create: Code=16
2016-12-10 09:22:35 SqlEditor64.exe fmDocRestore: Destroy
2016-12-10 09:22:35 SqlEditor64.exe fmConnect: Create
2016-12-10 09:22:35 SqlEditor64.exe fmConnect: OK
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Release
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Destroy
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Create
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: OK
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Release
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Destroy
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Create
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: OK
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Release
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Destroy
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Create
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: OK
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Release
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Destroy
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Create
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: OK
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Release
2016-12-10 09:22:36 SqlEditor64.exe fmConnect: Destroy
2016-12-10 09:22:41 SqlEditor64.exe fmUnitTests: Create
2016-12-10 09:22:41 SqlEditor64.exe Target: Create: Pref=SQL Assistant Code Editor
2016-12-10 09:22:41 SqlEditor64.exe Target: Create: Code=0
2016-12-10 09:22:41 SqlEditor64.exe fmConnect: Create
2016-12-10 09:22:41 SqlEditor64.exe fmConnect: OK
2016-12-10 09:22:41 SqlEditor64.exe fmConnect: Release
2016-12-10 09:22:41 SqlEditor64.exe fmConnect: Destroy
2016-12-10 09:22:45 SqlEditor64.exe fmSelectObjects: Create
2016-12-10 09:22:55 SqlEditor64.exe fmSelectObjects: Release
2016-12-10 09:22:56 SqlEditor64.exe fmSelectObjects: Destroy
2016-12-10 09:23:01 SqlEditor64.exe Error: Exception: Unhandled exception
operating system : Windows 10 x64 build 14393
system language : English
system up time : 10 hours 30 minutes
program up time : 29 seconds
processors : 4x Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz
physical memory : 2966/8097 MB (free/total)
free disk space : (C:) 45.54 GB
display mode : 1366x768, 32 bit
process id : $120c
allocated memory : 110.76 MB
largest free block : 131025.98 GB
executable : SqlEditor64.exe
current module : SqlAssistPrefs64.dll
module date/time : 2016-11-30 10:17
version : 9.0.194.0
callstack crc : $b2e41213, $e2bba3b7, $7939b336
exception number : 1
exception class : EAccessViolation
exception message : Access violation at address 000000000597BB3D in module 'SqlAssistPrefs64.dll'. Read of address 0000000000000018.

main thread ($24c0):
0597bb3d +0ad SqlAssistPrefs64.dll UnitTestsForm 1250 +10 TfmUnitTests.RunTest
05979977 +007 SqlAssistPrefs64.dll UnitTestsForm 750 +1 TfmUnitTests.btnExecuteClick
05502ad3 +053 SqlAssistPrefs64.dll Vcl.Forms 6241 +6 TCustomForm.WMCommand
054fdf49 +8d9 SqlAssistPrefs64.dll Vcl.Forms 4455 +206 TCustomForm.WndProc
05977c64 +054 SqlAssistPrefs64.dll UnitTestsForm 348 +7 TfmUnitTests.WndProc

thread $1dc0:
055d35ee +2e SqlAssistPrefs64.dll VirtualTrees 6375 +4 TWorkerThread.Execute

thread $fdc:
00500ec9 +49 SqlEditor64.exe madExcept 17280 +7 ThreadExceptFrame

thread $11cc:
05ccc613 +53 SqlAssistPrefs64.dll SyntaxUnit 1698 +4 TSyntaxThread.Execute

thread $196c:
05ccc613 +53 SqlAssistPrefs64.dll SyntaxUnit 1698 +4 TSyntaxThread.Execute

thread $1e50:
05ccc613 +53 SqlAssistPrefs64.dll SyntaxUnit 1698 +4 TSyntaxThread.Execute

thread $14dc:
05ccc613 +53 SqlAssistPrefs64.dll SyntaxUnit 1698 +4 TSyntaxThread.Execute

thread $2b40:
05ccc613 +53 SqlAssistPrefs64.dll SyntaxUnit 1698 +4 TSyntaxThread.Execute

thread $2a04:
05ccc613 +53 SqlAssistPrefs64.dll SyntaxUnit 1698 +4 TSyntaxThread.Execute

cpu registers:
rax = 0000000000000000
rbx = 000000000a178530
rcx = 000000000a178500
rdx = 000000000a9402b0
rsi = 0000000000000000
rdi = 00000000056486c8
rip = 000000000597bb3d
rsp = 000000000014e910
rbp = 000000000014e910
r8 = 0000000000000001
r9 = 000000000014e400
r10 = 0000000000000000
r11 = 000000000014e500
r12 = 0000000000070b0e
r13 = 0000000000000111
r14 = 0000000000000000
r15 = 0000000000000000

disassembling:
[...]
0597bb28 1249 mov rcx, [rbx+$6b0]
0597bb2f xor rdx, rdx
0597bb32 call -$430407 ($554b730) ; Vcl.Controls.TControl.SetVisible
0597bb37 1250 test rsi, rsi
0597bb3a setnz cl
0597bb3d > mov rdx, [rsi+$18]
0597bb41 xor r8, r8
0597bb44 call -$776f89 ($5204bc0) ; MySysUtils.Iif
0597bb49 mov rcx, rax
0597bb4c lea rdi, [rbp+$30]
0597bb50 xor eax, eax
[...]
2016-12-10 09:23:16 NOTEPAD.EXE SA=9.0.194; Exe=C:\Windows\system32\NOTEPAD.EXE; Cmd="C:\Windows\system32\NOTEPAD.EXE" C:\Users\gemis\AppData\Roaming\SQL Assistant\9.0\SqlAssist.log; LogLvl=1; MsgLvl=0
2016-12-10 09:23:16 NOTEPAD.EXE Client: Attach
2016-12-10 09:23:16 NOTEPAD.EXE Client: Create

Thank you. We will look into that ASAP.

Thank you very much. Running individual unit tests or tests cases works. It's running the project that causes exception.

UPDATE: method 2 works great. I think I can make it report back to Jenkins so that it can mark a build unstable if necessary.

UPDATE2: or maybe not... It looks like I was under the false impression that sacmd interprets failed tests as undesired and would set the ERRORLEVEL accordingly. Is there any other way to ascertain if there was at least one failed test apart from actually analyzing text output?

You can configure the Unit Test project to save results to a database table. You can then add another command line say using osql to check if the database table contains any failed test, which is fairly simple and make osql exit with code 1 if it found such tests, something like

	Code:
	osql -E -q "EXIT (SELECT sign(count(1)) FROM ... WHERE ...)"

More details can be found here https://msdn.microsoft.com/en-us/library/ms162806.aspx?f=255&MSPPError=-2147217396

We should have a bug fix for failing Unit Tests project runs in 64-bit SA version running on Windows 10 released soon. We are running QA testing for the build containing that fix.

SysOp wrote:

You can configure the Unit Test project to save results to a database table. You can then add another command line say using osql to check if the database table contains any failed test, which is fairly simple and make osql exit with code 1 if it found such tests, something like

	Code:
	osql -E -q "EXIT (SELECT sign(count(1)) FROM ... WHERE ...)"

More details can be found here https://msdn.microsoft.com/en-us/library/ms162806.aspx?f=255&MSPPError=-2147217396

That's simple and brilliant, I've should have thought about it.

	SysOp wrote:
	We should have a bug fix for failing Unit Tests project runs in 64-bit SA version running on Windows 10 released soon. We are running QA testing for the build containing that fix.

Thanks.