GarBear
4th April 2007 19:18 UTC

Building an SQL Interface
Hi all;

I want to find and check databases for things and provide greater customizability for my customers. I have been building a new SQL Server 2005 Interface because there really aren't any out there, not in the open source, freeware, or closed source/install market (Install Shield, Wise etc...).

That is,not with the kind of flexibility and customizability we can achieve with a powerful scripting language like NSIS.

Mind you, I am not a great programmer, but I can do a few things. I will eventually convert all my scripts over and compile them in VB (If anyone wants to help with that feel free) as a dynamic link library (.DLL) file.

I thought I would start a new topic and share my code as I go along, gleaning suggestions as we go. I have found to get the larger window size I need I have to use Experience or Ultra UI’s. (My Choice is ExperienceUI)

NSIS Install Code Begin:
#Defines
!define APPNAME "DBConnetion"
!define APPNAMEANDVERSION "DBConnetion 1.0"
!define dbini "DBList.ini"
!define sqlini "SQLList.ini"
!define XP_STYLE "ON"
#Varriables
#Note: Bug causes Global use all the time right now.
Var /Global Count
Var /Global SQLUserName
Var /Global SQLPassword
Var /Global DBServer
Var /Global Database
Var /Global DBConnectionType
#Custom Install Options
BrandingText "Example DB Connect"
Name "${APPNAMEANDVERSION}"
InstallDir "$PROGRAMFILES\DBConnetion"
;InstallDirRegKey HKLM "Software\${APPNAME}" ""
OutFile "DBConnectionExample.exe"
#Includes
!include "XPUI.nsh"
!include "logiclib.nsh"
!include "WinMessages.nsh"
!include "TextFunc.nsh"
!include "WordFunc.nsh"
!insertmacro "WordFind"
!define MUI_ABORTWARNING
!insertmacro MUI_PAGE_WELCOME
#Custom Page that does everything
Page custom "DBConnectionPage" "validate"
!insertmacro MUI_LANGUAGE "English"

Function .onInit
!insertmacro MUI_INSTALLOPTIONS_EXTRACT "DBConnectObject.ini"
Call CreateSQLSVRVBS
Call CreateDBVBS
nsExec::Exec /TIMEOUT=20000 '"$SYSDIR\CScript.exe" "$PLUGINSDIR\SQLServers.vbs" "$PLUGINSDIR\${sqlini}"'
Call PopulateDBConnectObject
FunctionEnd

# Macro to handle screen manipulation
!macro SNDMESSAGE lg cmd lparm rparm
#usage: SNDMESSAGE DialogueItem MessageType leftparameter rightparameter
SendMessage ${lg} ${cmd} ${lparm} ${rparm}
!macroend


# Macro to handle getting combobox info from ini and putting it into dropdown
!macro GETINI fileName sectionName valueName strDelimiter dlgNumber dlgIniFile dlgFieldname dlgValue
StrCpy $Count ""
# Expects ini file format to match NSIS default line delimiter "|" then line i.e. "line|line"
ReadINIStr $9 ${fileName} ${sectionName} ${valueName}
${WordFind} $9 ${strDelimiter} "*" $R9
${If} $R9 >= 1
# Clear current values
!insertmacro SNDMESSAGE ${dlgNumber} ${CB_RESETCONTENT} 0 0
WriteINIStr ${dlgIniFile} ${dlgFieldname} ${dlgValue}
${Do}
# Find out how many times to loop
${WordFind} $9 ${strDelimiter} "*" $R9
StrCpy $Count $R9
${WordFind} $9 "${strDelimiter}" "-1(" $8
${WordFind} $9 "${strDelimiter}" "-1{" $9
# Update the Dialogue with the next item in Messagebox
!insertmacro SNDMESSAGE ${dlgNumber} ${CB_INSERTSTRING} 0 'STR:$8'
IntOp $Count $Count - 1
${LoopUntil} $Count = 0
# Set first text item
!insertmacro SNDMESSAGE ${dlgNumber} ${WM_SETTEXT} 1 'STR:$8'
# Enable dropdown
!insertmacro SNDMESSAGE ${dlgNumber} ${WM_ENABLE} 1 1
# Show the new values
!insertmacro SNDMESSAGE ${dlgNumber} ${CB_SHOWDROPDOWN} 1 1
${EndIf}
StrCpy $Count ""
!macroend

Function validate
# To Do: Needs a check for cancel button the skips all code and exits
ReadINIStr $R0 "$PLUGINSDIR\DBConnectObject.ini" "Settings" "State"
FindWindow $0 '#32770' '' $HWNDPARENT
${IF} $R0 == 18
WriteINIStr "$PLUGINSDIR\DBConnectObject.ini" "Field 13" State "0"
WriteINIStr "$PLUGINSDIR\DBConnectObject.ini" "Field 18" State "1"
GetDlgItem $1 $0 1203
SetCtlColors $1 ${MUI_TEXT_COLOR} ${XPUI_TEXT_LIGHTCOLOR}
SendMessage $1 ${WM_ENABLE} 0 0
GetDlgItem $1 $0 1219
SetCtlColors $1 000000 FFFFFF
SendMessage $1 ${WM_ENABLE} 0x50810000 1
GetDlgItem $1 $0 1218
SetCtlColors $1 000000 FFFFFF
SendMessage $1 ${WM_ENABLE} 0x50810000 1
# Show Activate
SendMessage $1 ${SW_SHOWNOACTIVATE} 0x50810000 1
# NOTE: Need to figure out why I can't make this work right.
#SendMessage $1 ${WM_SETFOCUS} '' ''
Abort
${ElseIf} $R0 == 13
WriteINIStr "$PLUGINSDIR\DBConnectObject.ini" "Field 13" State "1"
WriteINIStr "$PLUGINSDIR\DBConnectObject.ini" "Field 18" State "0"
GetDlgItem $1 $0 1220
SendMessage $1 ${BM_SETSTATE} 0 'STR:NotUsed'
GetDlgItem $1 $0 1219
SetCtlColors $1 FFFFFF ${XPUI_TEXT_BGCOLOR}
SendMessage $1 ${WM_ENABLE} 0x50810000 0
GetDlgItem $1 $0 1218
SetCtlColors $1 FFFFFF ${XPUI_TEXT_BGCOLOR}
SendMessage $1 ${WM_ENABLE} 0x50810000 0
GetDlgItem $1 $0 1213
SendMessage $1 ${SW_SHOWNOACTIVATE} 0x50810000 0
SendMessage $1 ${WM_SETFOCUS} 0x50810000 1
Abort
${ElseIf} $R0 == 21
GetDlgItem $1 $0 1206
# To be done: Need vb script to validate SQL user is connected
# If validated will set Connected Else Connection Failed.
SendMessage $1 ${WM_SETTEXT} '0' 'STR:Connected'
SendMessage $1 ${WM_ENABLE} 1 1
Abort
${ElseIf} $R0 == 15
nsExec::Exec /TIMEOUT=20000 '"$SYSDIR\CScript.exe" "$PLUGINSDIR\SQLServers.vbs" "$PLUGINSDIR\${sqlini}"'
Sleep 250
Call PopulateDBConnectObject
GetDlgItem $1 $0 1213
!insertmacro GETINI $PLUGINSDIR\${sqlini} "SQLServers" "List" "|" $1 "$PLUGINSDIR\DBConnectObject.ini" "Field 14" "ListItems"
Abort
${ElseIf} $R0 == 17
#Validate has entered some name
ReadINIStr $4 "$PLUGINSDIR\DBConnectObject.ini" "Field 14" "State"
${If} $4 == ""
MessageBox MB_OK "You must first choose a SQLServer."
Abort
${Else}
nsExec::Exec /TIMEOUT=20000 '"$SYSDIR\CScript.exe" "$PLUGINSDIR\FindDB.vbs" $4 "$PLUGINSDIR\${dbini}"'
GetDlgItem $1 $0 1215
!insertmacro GETINI $PLUGINSDIR\${dbini} $4 "List" "|" $1 "$PLUGINSDIR\DBConnectObject.ini" "Field 16" "ListItems"
Abort
${EndIf}
${ElseIf} $R0 == 22
# Test connection and report back
Abort
${EndIf}
ReadINIStr $0 "$PLUGINSDIR\DBConnectObject.ini" "Field 16" "State"
ReadINIStr $1 "$PLUGINSDIR\DBConnectObject.ini" "Field 14" "State"
ReadINIStr $3 "$PLUGINSDIR\DBConnectObject.ini" "Field 18" "State" #Radio SQL Auth
ReadINIStr $4 "$PLUGINSDIR\DBConnectObject.ini" "Field 19" "State"
ReadINIStr $5 "$PLUGINSDIR\DBConnectObject.ini" "Field 20" "State"
${IF} $0 == ""
MessageBox MB_OK "You must select or type in the name of your database first."
Abort
${ElseIf} $1 == ""
MessageBox MB_OK "You must select or type in the name of your database Server first."
Abort
${ElseIf} $3 == 1
${If} $4 == ""
MessageBox MB_OK "You have chosen to use SQL Authentication but neglected to provide a username. Please enter a SQL username first."
Abort
${ElseIf} $5 == ""
MessageBox MB_OK "You have chosen to use SQL Authentication but neglected to provide a password. Please enter a SQL username first."
Abort
${EndIf}

${EndIf}
Call SetDBVars
FunctionEnd

Function SetDBVars
ReadINIStr $0 "$PLUGINSDIR\DBConnectObject.ini" "Field 18" "State"
${If} $0 == 1
StrCpy $DBConnectionType "SQL"
ReadINIStr $SQLUserName "$PLUGINSDIR\DBConnectObject.ini" "Field 19" "State"
ReadINIStr $SQLPassword "$PLUGINSDIR\DBConnectObject.ini" "Field 20" "State"
${Else}
StrCpy $DBConnectionType "NT"
${EndIf}

ReadINIStr $DBServer "$PLUGINSDIR\DBConnectObject.ini" "Field 14" "State"

ReadINIStr $Database "$PLUGINSDIR\DBConnectObject.ini" "Field 16" "State"

FunctionEnd

# Not currently in use
Section -FinishSection
;
; WriteRegStr HKLM "Software\${APPNAME}" "" "$INSTDIR"
; WriteRegStr HKLM "Software\Microsoft\Windows\CurrentVersion\Uninstall\${APPNAME}" "DisplayName" "${APPNAME}"
; WriteRegStr HKLM "Software\Microsoft\Windows\CurrentVersion\Uninstall\${APPNAME}" "UninstallString" "$INSTDIR\uninstall.exe"
; ;WriteUninstaller "$INSTDIR\uninstall.exe"

SectionEnd

Function DBConnectionPage

!insertmacro MUI_INSTALLOPTIONS_INITDIALOG "DBConnectObject.ini"
FindWindow $0 '#32770' '' $XPUI_HWND
Push $R0
!insertmacro MUI_HEADER_TEXT "${APPNAME}" "${APPNAMEANDVERSION}"
!insertmacro MUI_INSTALLOPTIONS_DISPLAY "DBConnectObject.ini"
GetDlgItem $1 $0 1200
SetCtlColors $1 ${XPUI_TEXT_BGCOLOR} ${XPUI_TEXT_LIGHTCOLOR}
ShowWindow $1 1
!insertmacro MUI_INSTALLOPTIONS_SHOW
Pop $R0

FunctionEnd

Function PopulateDBConnectObject
ReadINIStr $0 "$PLUGINSDIR\${sqlini}" "SQLServers" "List"
WriteINIStr "$PLUGINSDIR\DBConnectObject.ini" "Field 14" ListItems $0
FunctionEnd

# Visual Basic Script that Gets a list of SQL Servers
Function CreateSQLSVRVBS
StrCpy $1 "'"
FileOpen $0 "$PLUGINSDIR\SQLServers.vbs" w
FileWrite $0 '$1---------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$1---------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1 Licensed Materials - Property of MyCompany (TSW)$\r$\n'
FileWrite $0 '$1 $\r$\n'
FileWrite $0 '$1 Governed under the terms of the International$\r$\n'
FileWrite $0 '$1 License Agreement for Non-Warranted Code.$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1 (C) COPYRIGHT MYCOMPANY Corp. 2006 - 2007$\r$\n'
FileWrite $0 '$1 All Rights Reserved.$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1---------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1 SOURCE FILE NAME: SQLServers.vbs$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1----------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Const FOR_WRITING = 2$\r$\n'
FileWrite $0 'Dim strResults$\r$\n'
FileWrite $0 'fileOutput = WScript.Arguments(0)$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'CheckSQL()$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Private Function CheckSQL()$\r$\n'
FileWrite $0 'Set oSQLApp =CreateObject("SQLDMO.Application")$\r$\n'
FileWrite $0 'Set oNames = oSQLApp.ListAvailableSQLServers()$\r$\n'
FileWrite $0 'For i = 1 To oNames.Count$\r$\n'
FileWrite $0 ' If count <= 0 Then$\r$\n'
FileWrite $0 ' strResults = "[SQLServers]" & vbNewLine & "List=" & oNames.Item(i)$\r$\n'
FileWrite $0 ' count = 1$\r$\n'
FileWrite $0 ' Else$\r$\n'
FileWrite $0 ' strResults = strResults & "|" & oNames.Item(i)$\r$\n'
FileWrite $0 ' End If$\r$\n'
FileWrite $0 'Next $\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'CheckFile()$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'End Function$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Sub CheckFile$\r$\n'
FileWrite $0 'Set objFS = CreateObject("Scripting.FileSystemObject")$\r$\n'
FileWrite $0 ' If objFS.FileExists(fileOutput) Then$\r$\n'
FileWrite $0 ' objFS.DeleteFile(fileOutput)$\r$\n'
FileWrite $0 ' End If$\r$\n'
FileWrite $0 ' If Not objFS.FileExists(fileOutput) Then$\r$\n'
FileWrite $0 ' objFS.CreateTextFile(fileOutput)$\r$\n'
FileWrite $0 ' End If$\r$\n'
FileWrite $0 ' WriteFile()$\r$\n'
FileWrite $0 'End Sub$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Sub WriteFile$\r$\n'
FileWrite $0 'Set objFS = CreateObject("Scripting.FileSystemObject")$\r$\n'
FileWrite $0 ' Set objTS = objFS.OpenTextFile(fileOutput, FOR_WRITING)$\r$\n'
FileWrite $0 ' objTS.Write strResults$\r$\n'
FileWrite $0 'End Sub$\r$\n'
FileClose $0
FunctionEnd

# Visual Basic Script that gets a list of Databases on for a selected Server
Function CreateDBVBS
StrCpy $1 "'"
FileOpen $0 "$PLUGINSDIR\FindDB.vbs" w
FileWrite $0 '$1---------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$1---------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1 Licensed Materials - Property of MyCompany (TSW)$\r$\n'
FileWrite $0 '$1 $\r$\n'
FileWrite $0 '$1 Governed under the terms of the International$\r$\n'
FileWrite $0 '$1 License Agreement for Non-Warranted Code.$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1 (C) COPYRIGHT MYCOMPANY Corp. 2006 - 2007$\r$\n'
FileWrite $0 '$1 All Rights Reserved.$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1---------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1 SOURCE FILE NAME: FindDB.vbs$\r$\n'
FileWrite $0 '$1$\r$\n'
FileWrite $0 '$1----------------------------------------------------------------------------$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Const FOR_WRITING = 2$\r$\n'
FileWrite $0 'Dim strResults$\r$\n'
FileWrite $0 'strDBServerName = WScript.Arguments(0)$\r$\n'
FileWrite $0 'fileOutput = WScript.Arguments(1)$\r$\n'
FileWrite $0 'Call GetDatabases(strDBServerName)$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Private Function GetDatabases(s)$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 ' $\r$\n'
FileWrite $0 'Set objSQLServer = CreateObject("SQLDMO.SQLServer")$\r$\n'
FileWrite $0 ' objSQLServer.LoginSecure = True$\r$\n'
FileWrite $0 ' objSQLServer.Connect s$\r$\n'
FileWrite $0 ' $\r$\n'
FileWrite $0 ' Set colDatabases = objSQLServer.Databases$\r$\n'
FileWrite $0 ' $\r$\n'
FileWrite $0 ' For Each objDatabase In colDatabases$\r$\n'
FileWrite $0 ' If count <= 0 Then$\r$\n'
FileWrite $0 ' strResults = "[" & s & "]" & _$\r$\n'
FileWrite $0 ' vbNewLine & "List=" & objDatabase.Name$\r$\n'
FileWrite $0 ' count = 1$\r$\n'
FileWrite $0 ' Else$\r$\n'
FileWrite $0 ' strResults = strResults & "|" & objDatabase.Name$\r$\n'
FileWrite $0 ' End If$\r$\n'
FileWrite $0 ' Next$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 ' CheckFile()$\r$\n'
FileWrite $0 'End Function$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Sub CheckFile$\r$\n'
FileWrite $0 'Set objFS = CreateObject("Scripting.FileSystemObject")$\r$\n'
FileWrite $0 ' If objFS.FileExists(fileOutput) Then$\r$\n'
FileWrite $0 ' objFS.DeleteFile(fileOutput)$\r$\n'
FileWrite $0 ' End If$\r$\n'
FileWrite $0 ' If Not objFS.FileExists(fileOutput) Then$\r$\n'
FileWrite $0 ' objFS.CreateTextFile(fileOutput)$\r$\n'
FileWrite $0 ' End If$\r$\n'
FileWrite $0 ' WriteFile()$\r$\n'
FileWrite $0 'End Sub$\r$\n'
FileWrite $0 '$\r$\n'
FileWrite $0 'Sub WriteFile()$\r$\n'
FileWrite $0 ' Set objFS = CreateObject("Scripting.FileSystemObject")$\r$\n'
FileWrite $0 ' Set objTS = objFS.OpenTextFile(fileOutput, FOR_WRITING)$\r$\n'
FileWrite $0 ' objTS.Write strResults$\r$\n'
FileWrite $0 'End Sub$\r$\n'
FileClose $0
FunctionEnd
NSIS Install Code End

INI for Custom Page Begin:

[Settings]
NumFields=22

[Field 1]
Type=Groupbox
Text=SQL 2005 Server
Flags=NOTABSTOP
Left=4
Right=268
Top=1
Bottom=198

[Field 2]
Type=Label
Text=SQLServer:
Left=30
Right=68
Top=115
Bottom=123

[Field 3]
Type=Label
Text=Database:
Left=33
Right=68
Top=134
Bottom=142

[Field 4]
Type=Groupbox
Text=SQL Server Authentication
Flags=NOTABSTOP
Left=13
Right=260
Top=12
Bottom=102

[Field 5]
Type=Label
Text=Username:
Flags=NOTABSTOP
Left=46
Right=80
Top=49
Bottom=57

[Field 6]
Type=Label
Text=Password:
Flags=NOTABSTOP
Left=46
Right=79
Top=65
Bottom=73

[Field 7]
Type=Label
Text=Not Connected
Flags=NOTABSTOP
Left=186
Right=240
Top=84
Bottom=92

[Field 8]
Type=Label
Text=Version #.#.#.#
Flags=NOTABSTOP
Left=78
Right=176
Top=153
Bottom=161

[Field 9]
Type=Label
Text=Database Version:
Flags=NOTABSTOP
Left=10
Right=68
Top=153
Bottom=161

[Field 10]
Type=Label
Text=SQLServer Version:
Flags=NOTABSTOP
Left=9
Right=71
Top=166
Bottom=174

[Field 11]
Type=Label
Text=SQL 2005 Server
Flags=NOTABSTOP
Left=78
Right=133
Top=166
Bottom=174

[Field 12]
Type=Link
Text=techsupport@mycompany.com
Flags=NOTABSTOP
State=mailto:techsupport@mycompany.com
Left=80
Right=197
Top=180
Bottom=188

[Field 13]
Type=RadioButton
Text=Current User Credentials
Flags=NOTIFY
State=1
Left=32
Right=131
Top=32
Bottom=41

[Field 14]
Type=Combobox
Text=Combobox
ListItems=
Left=69
Right=204
Top=113
Bottom=126

[Field 15]
Type=Button
Text=Refresh
Flags=NOTIFY
Left=208
Right=258
Top=112
Bottom=126

[Field 16]
Type=Combobox
Text=Combobox
ListItems=
Left=69
Right=204
Top=131
Bottom=145

[Field 17]
Type=Button
Text=Refresh
Flags=NOTIFY
Left=208
Right=258
Top=129
Bottom=145

[Field 18]
Type=RadioButton
Text=Use SQL Server Authentication
Flags=NOTIFY
Left=136
Right=250
Top=32
Bottom=41

[Field 19]
Type=Text
Left=82
Right=176
Top=46
Bottom=59

[Field 20]
Type=Password
Left=82
Right=176
Top=64
Bottom=76

[Field 21]
Type=Button
Text=TestConnection
Flags=NOTIFY
Left=82
Right=176
Top=80
Bottom=94

[Field 22]
Type=Button
Text=Test Database
Flags=NOTIFY
Left=204
Right=260
Top=176
Bottom=190

INI for Custom Page End:

sgiusto
4th April 2007 20:37 UTC

Hi there,
I'm interested in SQL Interfaces
I developed a plugin to execute generic sql scripts using SQL OLEDB interface, so you don't have to manage vbscript or similar.
You can find it at the plugins section of nsis site.
I think that using my plugin your posted script wuold be a lot less complex.
Drop me email if you find useful the plugin and have requests of new features

GarBear
12th April 2007 16:02 UTC

Thanks for the SQL Help!
I am excited to try out your SQL tools. I have a release this Monday so won't be able to update my application until after then, but here is the example of the app as it currently sits. Once you have seen that you may have other suggestions for improvements as well.

My hope is that people can download my plug-ins for MSSQL and IIS and encorporate real interfaces for their installations.

Thanks;

GarBear

GarBear
19th April 2007 20:55 UTC

I attempted to attach the new file but it is too big for the forumn now at 133KB. Send me a message if you would like the latest version.

kichik
19th April 2007 21:04 UTC

You can upload to stashbox.org.

valentz142
20th April 2007 08:52 UTC

Hi sgiusto,

I found that your plugin only can display 4 character max per column, I have already tried to select one column only, it was still display first 4 character of my string.
Could you help me?

sgiusto
20th April 2007 21:58 UTC

Hi valentz142,
did you tried the given sample? it reads much more than 4 characters and it works on my tests.
Provide some script fragment with examples of wrong behaviour and I will check.

valentz142
23rd April 2007 07:05 UTC

Hi sgiusto,

actually i used your example, i put my query in your query field, i used "select columid" instead of "select *". And this query has already been tested in sql query program, it will get one 1 row and 1 column only.
I should expect 10 character of string, but i've just only received 4 character.

I have tested it in sql2000 and sql express 2005

sgiusto
26th April 2007 22:03 UTC

Can you post the complete query? the table in my example does not contain a 'columid' column