MSSQL Plugin

[sgiusto]

A new plugin to execute SQL commands, parse results and handle errors. Useful to create SQL devices, backup devices before program installation, check or modify SQL configuration, ...

http://nsis.sourceforge.net/MSSQL_OLEDB_plug-in

nsis.sourceforge.net/MSSQL_OLEDB_plug-in

[TheShadowHawk]

Looks good. but can updates be accomplished with this?

Also when executing MSSQL_OLEDB::SQL_Execute which takes a string query can there be several statements in the string?

[TheShadowHawk]

I'll answer my own issue. Updates will work as long as you just execute a single query at a time. Putting multiple queries wont work. Silly me forgot that simple principle. :P

[Afrow UK]

Very nice

Stu

[jplatter]

Is there any trick to getting the plugin to work with files. I am having trouble making this work:

MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "$INSTDIR\filename.sql"

It never completes. I call this after the file is installed to the hard drive so it should exist. I also use File filename.sql command in the beginning of the function. I have tested the sql file using the sqlcmd at the command line and it works without any errors.

Thanks.

[sgiusto]

The script must consist of a single SQL statement.
You cannot execute more than a command at a time
The sample script given with the plugin is working correctly?

[jplatter]

Ok. So if I understand correctly, when the plugin executes a script file "somefile.sql" there can only be one command in the file?

I understand that using the Execute command it can only be one, but I thought with the ExecuteScript command the file that is referenced could have more than one command.

Thanks.

[sgiusto]

The problem is how to handle multiple result sets.
I whould have to parse the script file, execute the first command and prepare the result set. let the user get the rows of the first result set or handle the error and then execute the second command and so on.
It's just a bit too complex for an installation utility.
I could assume a file with a command per line. but this will limit the length of the single command that can be executed.
But the problem of the multiple result sets will remain.
Regards
Stefano

[Afrow UK]

Where did the actual plugin go from that page?
The Zip file only contains an executable.

Stu

[is99]

Where is the plugin? There is only exe example on that page

[sgiusto]

Hi there,
I fixed the zip file. Now it should be ok.
sorry for the inconvenience.

[is99]

Thank you very much

[is99]

Tell me what if I don't want to use SQL username and password and instead to use Windows securety? What do I need to do in MSSQL_OLEDB::SQL_Logon ?

[sgiusto]

leave username and password blank (empty strings). it will use integrated authentication

[is99]

I was trying to logon to SQL 2005 and was not able.
While using SQLCMD I wasable to connect wihout any problem.

Do you have any idea what can be the problem? Is this plugin suppose to work with SQL 2005? Are there any special things that suppose to be installedon this PC in order for this plugin to work?

Thank you

[sgiusto]

The plugin use SQLOLEDB driver, and has been tested against SQL2000 and SQL2005.
Integrated security logon has been tested against sql2005 and works.
the syntax to use is:
MSSQL_OLEDB::SQL_Logon /NOUNLOAD "$SQLSERVER" "" ""

You must provide username and password as empty strings (you cannot omit the parameters)
The variable $SQLSERVER is the name of the sql box.

The SQL OLEDB driver is usually part of the operating system from windows XP and above. The SQL client installs it also and the MDAC package from microsoft does the same.

Hope this helps

[njpride]

First, great! Really useful, thanks for all your effort!

Now to my issue, if I try to logon to a server at localhost\instanceName it doesn't login however if I try machineName\instanceName it does connect without any problems.

Any idea how to get around this? Using other tools I can connect via the localhost.

Thanks

------
Seems to have been some interference between keyboard and chair.

Right way to do it : "(local)\instanceName"
Another way that works but is pointless! Look up the machine name in registry and use that.

Thanks for being a sounding board

[isawen]

Originally Posted by njpride

First, great! Really useful, thanks for all your effort!

Now to my issue, if I try to logon to a server at localhost\instanceName it doesn't login however if I try machineName\instanceName it does connect without any problems.

Any idea how to get around this? Using other tools I can connect via the localhost.

Thanks

------
Seems to have been some interference between keyboard and chair.

Right way to do it : "(local)\instanceName"
Another way that works but is pointless! Look up the machine name in registry and use that.

Thanks for being a sounding board

I'm encountering the same problem. On my Windows XP SP3 VMWare Virtual Machine with only SQL Server 2008 Express installed and .NET 3.5 the plugin example fails to log-in on localhost\sqlexpress.

What could be the real reason for such log-in fail message?
"SQL State: 0x80004005 - Native: 0 - Message: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

[sgiusto]

You can also try 127.0.0.1\instanceName

[foxmt]

Hi

I'm using actually something like this, and it works:
ExecWait "osql -U$SQL_User -P$SQL_Password -S$SQL_Server -d$SQL_DataBase -i$TEMP\sem2400.sql"

I want to use "MSSQL OLEDB plug-in", because I like attach method . That's greater than my vbs method.
But I don't find how to do this.

I'm trying this:

MSSQL_OLEDB::SQL_Logon /NOUNLOAD "$SQL_Server" "$SQL_User" "$SQL_Password"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "sp_attach_single_file_db [$SQL_DataBase],[$INSTDIR\DATA\Semaphore_Data.MDF]"
(it works)
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "USE $SQL_DataBase"
MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "$TEMP\sem2002.sql"
(it don't works)

When i'm lauching my nsis excutable, it blocked and crash nsis executable. I think ExecuteScript can only use SQL file ,if "USE DATABASE" is defined under it.

Do you know something to do with ExecuteScript action?

Best Regards

Sorry for my english.

[foxmt]

UP pls

[Afrow UK]

As already mentioned, the SQL script can only contain one query instruction.

Stu

[sgiusto]

New Plugin Version Released (1.4)
Added support for SQL Scripts in Unicode format

[stonkers]

This plugin seems to be able to handle whatever you throw at it in the sql script now (other than GO commands). Is that the case? For instance, I did something like this:

code:


OutFile TestSQL.exe



Section "TestSQL"

  detailprint "Log on to SQL server"

  MSSQL_OLEDB::SQL_Logon /NOUNLOAD "machine1" "" ""

  pop $0

  detailprint $0

  pop $0

  detailprint $0

  MSSQL_OLEDB::SQL_Execute /NOUNLOAD "use DB1"

  pop $0

  detailprint $0

  pop $0

  detailprint $0

  MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "Script.SQL"

  pop $0

  detailprint $0

  pop $0

  detailprint $0

  MSSQL_OLEDB::SQL_GetError /NOUNLOAD

  pop $0

  detailprint $0

  pop $0

  detailprint $0

  MSSQL_OLEDB::SQL_Logout



SectionEnd

In Script.SQL, I did something like this:

code:


CREATE TABLE [dbo].[myTable](

	[col1] [int] IDENTITY(1,1) NOT NULL,

	[col2] [int] NOT NULL,

	[col3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[col4] [bit] NOT NULL,

	[col5] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[col6] [bit] NOT NULL,

 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 

(

	[col1] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];



ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [FK_myForeignKey] FOREIGN KEY([col2])

REFERENCES [dbo].[myOtherTable] ([col2]);



INSERT INTO [dbo].[myTable](

	col2,col3,col4,col6)

SELECT 999,101010,1,0;



INSERT INTO [dbo].[myTable](

	col2,col3,col4,col6)

SELECT 1000,101010,1,0;



INSERT INTO [dbo].[myTable](

	col2,col3,col4,col6)

SELECT 888,100000,1,0;



INSERT INTO [dbo].[myTable](

	col2,col3,col4,col6)

SELECT 889,100000,1,0;



UPDATE myTable SET col6 = 1 WHERE col3 = 101010;



UPDATE myTable SET col4 = 0 WHERE col3 = 100000;

And everything succeeded.

[stonkers]

Anybody?

[sgiusto]

The script will execute, but you will miss the result sets and/or the errors that will be raised by statements before the last one.
So it is better to have a single statement per file (better control of what happens)

[minghung]

Dear sgiusto,
Why the size of script file must be smaller than 60kb ?
Can I modify the default size in source file ?
Thanks.

[sgiusto]

For the ones that asked, I managed to compile the plugin for the Unicode version of NSIS.
I attach a pre-release version, please test it since I do not have any unicode system ad hand.
Scripts are supportet in unicode format only.
Regards
Stefano

PS: this version coforms with the new plugin api. No need to use /UNLOAD any more.

[sgiusto]

Hello,
I just released version 2.0 of the plugin.

You can find it here:
http://nsis.sourceforge.net/MSSQL_OLEDB_plug-in

Major improvements are:
* Support for new plugin API (no more need to use /NOUNLOAD switch)
* Unicode version (unicode scripts are handled ONLY by unicode version)
* Unlimited script-file size

Regards
Stefano

[frodelan]

Hello.
I'm using MSSQL_OLEDB plugin. I do a logon, execute and logout in a page. Everything is working ok. Then I press next, and then back. When I then enter my SQL Page, only a logon is done and then the installer crash. Is there a special task I have to do if I would like to have the back button enabled?

[sgiusto]

Hello,
upon plugin initilization some static structures are allocated. Pushing the back button may cause the plugin to try to use some structure that is no more in memory (due to logout)
Can you post some example code so I can try it?
regards
Stefano

[Nzali]

I actually have the same problem. I'm attaching my script.

[sgiusto]

Hello,
I compiled your script (changing only the sql server name and credentials) and everything worked.
Can you explain in more detail the problems you are experiencing?

[Nzali]

Hello,

The problem is when you've hit the back button after the DB name page. This is what I did...I entered the db name and click next and it works fine...takes me to the components page. But when I go back to the DB page from the components page and enter the DB name again then the installer crashes.

Why does it crash?

Also, try entering a DB name that doesn't exist in your server and click next. There'll be a pop-up message. Click 'No' on that pop-up. Try to enter the DB name again and click Next. The installer crashes.

I hope I've explained clearly.

[sgiusto]

That's a bug.
I am working on that already. I will release a fixed version soon.
The bug is that when you close the connection to the db not all the variables are cleared (but they are released). So when to connect again you will try to use some unallocated memory.
Regards
Stefano

[sgiusto]

Plugin version 2.0.1 released.
Fixed the allocation bug.
Fixed documentation too (windows versions compatibility)
Regards
Stefano

[cornhoulio]

Hi,

I installed the plugin and compiled the MSSQL_OLEDBTest.nsi without errors.
If I try the installer on a machine with SQL Server 2000 the installer crashs during logon without any message.
The logon credentials are correct.
I tried it on 3 different machines.

Do I miss something.

cornhoulio

[sgiusto]

Hello,
what is the size of installer?
are the plugins placed in the tmp directory at execution time?
do you fill in the right values in the dialog asking for server name and credentials?
Stefano

[cornhoulio]

Hi Stefano,

the size of MyApp.exe is 125kb.
I can't see the plugin dll in the temp dir of the installer.
I have to correct my statement. The installer crashes after the screen choose installation location.
My own script crashes during log on.

cornhoulio

[sgiusto]

Try the attached installer, on my pc works fine
Regards
Stefano