Old 13th March 2007, 16:31   #1
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
MSSQL Plugin

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
sgiusto is offline   Reply With Quote
Old 21st May 2007, 04:46   #2
TheShadowHawk
Junior Member
 
Join Date: Jun 2006
Location: Brisbane, Australia
Posts: 12
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 is offline   Reply With Quote
Old 21st May 2007, 05:35   #3
TheShadowHawk
Junior Member
 
Join Date: Jun 2006
Location: Brisbane, Australia
Posts: 12
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
TheShadowHawk is offline   Reply With Quote
Old 21st May 2007, 08:34   #4
Afrow UK
Moderator
 
Afrow UK's Avatar
 
Join Date: Nov 2002
Location: Surrey, England
Posts: 8,434
Very nice

Stu
Afrow UK is offline   Reply With Quote
Old 21st May 2007, 18:35   #5
jplatter
Junior Member
 
Join Date: May 2007
Posts: 6
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.
jplatter is offline   Reply With Quote
Old 22nd May 2007, 21:45   #6
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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?
sgiusto is offline   Reply With Quote
Old 22nd May 2007, 22:01   #7
jplatter
Junior Member
 
Join Date: May 2007
Posts: 6
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.
jplatter is offline   Reply With Quote
Old 22nd May 2007, 22:10   #8
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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
sgiusto is offline   Reply With Quote
Old 7th June 2007, 11:49   #9
Afrow UK
Moderator
 
Afrow UK's Avatar
 
Join Date: Nov 2002
Location: Surrey, England
Posts: 8,434
Where did the actual plugin go from that page?
The Zip file only contains an executable.

Stu
Afrow UK is offline   Reply With Quote
Old 7th June 2007, 11:51   #10
is99
Member
 
Join Date: Mar 2006
Posts: 69
Where is the plugin? There is only exe example on that page
is99 is offline   Reply With Quote
Old 8th June 2007, 09:00   #11
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
Hi there,
I fixed the zip file. Now it should be ok.
sorry for the inconvenience.
sgiusto is offline   Reply With Quote
Old 8th June 2007, 10:45   #12
is99
Member
 
Join Date: Mar 2006
Posts: 69
Thank you very much
is99 is offline   Reply With Quote
Old 9th June 2007, 11:56   #13
is99
Member
 
Join Date: Mar 2006
Posts: 69
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 ?
is99 is offline   Reply With Quote
Old 10th June 2007, 21:42   #14
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
leave username and password blank (empty strings). it will use integrated authentication
sgiusto is offline   Reply With Quote
Old 11th June 2007, 07:09   #15
is99
Member
 
Join Date: Mar 2006
Posts: 69
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
is99 is offline   Reply With Quote
Old 11th June 2007, 08:36   #16
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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
sgiusto is offline   Reply With Quote
Old 12th June 2007, 09:36   #17
njpride
Junior Member
 
Join Date: Jun 2007
Posts: 4
Login Question

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

Last edited by njpride; 12th June 2007 at 10:48.
njpride is offline   Reply With Quote
Old 12th June 2007, 11:51   #18
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
You can also try 127.0.0.1\instanceName
sgiusto is offline   Reply With Quote
Old 24th July 2007, 09:58   #19
foxmt
Junior Member
 
Join Date: May 2006
Location: France
Posts: 6
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 is offline   Reply With Quote
Old 31st July 2007, 10:06   #20
foxmt
Junior Member
 
Join Date: May 2006
Location: France
Posts: 6
UP pls
foxmt is offline   Reply With Quote
Old 31st July 2007, 11:41   #21
Afrow UK
Moderator
 
Afrow UK's Avatar
 
Join Date: Nov 2002
Location: Surrey, England
Posts: 8,434
As already mentioned, the SQL script can only contain one query instruction.

Stu
Afrow UK is offline   Reply With Quote
Old 11th September 2007, 09:50   #22
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
New Plugin Version Released (1.4)
Added support for SQL Scripts in Unicode format
sgiusto is offline   Reply With Quote
Old 29th September 2008, 22:28   #23
stonkers
Senior Member
 
Join Date: Sep 2003
Posts: 192
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.

Eric
stonkers is offline   Reply With Quote
Old 1st October 2008, 14:36   #24
stonkers
Senior Member
 
Join Date: Sep 2003
Posts: 192
Anybody?

Eric
stonkers is offline   Reply With Quote
Old 9th October 2008, 10:04   #25
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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)
sgiusto is offline   Reply With Quote
Old 9th February 2009, 05:20   #26
minghung
Junior Member
 
Join Date: Jul 2008
Posts: 3
Smile

Dear sgiusto,
Why the size of script file must be smaller than 60kb ?
Can I modify the default size in source file ?
Thanks.
minghung is offline   Reply With Quote
Old 2nd April 2009, 20:57   #27
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
Unicode Version

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.
Attached Files
File Type: zip umssql_oledb.zip (189.0 KB, 270 views)
sgiusto is offline   Reply With Quote
Old 13th April 2009, 12:22   #28
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
Arrow Version 2.0 Released

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
sgiusto is offline   Reply With Quote
Old 11th June 2009, 08:43   #29
frodelan
Junior Member
 
Join Date: Sep 2005
Posts: 24
Installer crash when using Back button

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?
frodelan is offline   Reply With Quote
Old 16th June 2009, 12:33   #30
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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
sgiusto is offline   Reply With Quote
Old 28th July 2009, 11:19   #31
Nzali
Junior Member
 
Join Date: Jul 2009
Location: Cape Town
Posts: 2
Same problem with back button

I actually have the same problem. I'm attaching my script.
Attached Files
File Type: nsi mssql_oledb_test.nsi (2.7 KB, 320 views)
Nzali is offline   Reply With Quote
Old 28th July 2009, 16:30   #32
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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?
sgiusto is offline   Reply With Quote
Old 29th July 2009, 06:50   #33
Nzali
Junior Member
 
Join Date: Jul 2009
Location: Cape Town
Posts: 2
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.
Nzali is offline   Reply With Quote
Old 31st July 2009, 07:31   #34
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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 is offline   Reply With Quote
Old 1st August 2009, 21:37   #35
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
Plugin version 2.0.1 released.
Fixed the allocation bug.
Fixed documentation too (windows versions compatibility)
Regards
Stefano
sgiusto is offline   Reply With Quote
Old 6th October 2009, 07:12   #36
cornhoulio
Junior Member
 
Join Date: Oct 2009
Posts: 4
Installer crash

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
cornhoulio is offline   Reply With Quote
Old 7th October 2009, 19:54   #37
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
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
sgiusto is offline   Reply With Quote
Old 8th October 2009, 10:40   #38
cornhoulio
Junior Member
 
Join Date: Oct 2009
Posts: 4
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
cornhoulio is offline   Reply With Quote
Old 8th October 2009, 19:41   #39
sgiusto
Member
 
Join Date: Mar 2007
Location: Italy
Posts: 99
Try the attached installer, on my pc works fine
Regards
Stefano
Attached Files
File Type: zip test.zip (93.9 KB, 252 views)
sgiusto is offline   Reply With Quote
Old 9th October 2009, 07:48   #40
cornhoulio
Junior Member
 
Join Date: Oct 2009
Posts: 4
Hi Stefano,

your installer works also fine for me.
But I still unable to build my own installer with this plugin.
I am using NSIS 2.35. I copied all files from the plugin zip archive to my NSIS install directory.
What is wrong?

cornhoulio
cornhoulio is offline   Reply With Quote
Reply
Go Back   Winamp & Shoutcast Forums > Developer Center > NSIS Discussion

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump