Zugg Software :: View topic

Posted: Sat May 21, 2005 5:49 pm

This is a continuation of the question asked by theNerd about Stored Procedures in zApp.

I looked into the ZeosLib database code that I'm using and found the support for stored procedures that you asked about. It only required a few tweaks to the code.

There is now a new property of a zQuery object called "StoredProc" that you can set to the name of your stored procedure. Or, you can set the normal "Text" property to $ProcName where ProcName is the name of your stored procedure (if you just set it to ProcName, zApp assumes that its the name of a table rather than a stored procedure).

To execute the stored procedure (or any SQL for that matter), I added support for the Exec method. This takes an optional SQL string to execute (which can also have the $ProcName format). If you don't specify the SQL, then it just executes the current stored procedure using the StoredProc property.

In other words, your code would look like this:

Adept Joined: 01 Mar 2005 Posts: 277

I'll test it with our SQL Server db this week!

Posted: Sun May 22, 2005 12:12 am

That would be wonderful. I've been meaning to install the single-user SQL Server system that Microsoft has (I forget what it's called) and haven't had the time, so I haven't had a chance to do *any* testing with the Microsoft SQL Server.

I'm sure there will be plenty of little issues to fix in the first public release and it will be great to have some help testing this on databases that I don't have!

Adept Joined: 01 Mar 2005 Posts: 277

You mean MSDE (which can comfortably be used by around 5 concurrent users.) One thing that stored procedures use are input parameters and output parameters (not all SPROCS return recordsets.) For input and output parameters I specify type (int, numeric, varchar, etc.) Does ZeosLib support these? I am assuming for input parameters I could either do something like Query.Exec("mySproc FirstName='Zugg', LastName='Master') (or something along those lines.) I will let you know very quickly after the public release.

If you release one more CVS build some of us could do a once over to make sure you didn't overlook any obvious bugs. (Like give us 2 or 3 days.)

Posted: Sun May 22, 2005 4:46 am

For input parameters, take a look at the zQuery.Params property. I think that does what you want. For output parameters, I'm not sure how that works. If the stored procedure returns a dataset, then it should be part of the query results just like normal. I'll take a look at the ZeosLib stuff and see if I learn anything else about output parameters.

I don't think the syntax you mention will work, unless that is standard SQL syntax.

And yes, you'll probably see a CVS build on Sunday.

Posted: Thu Jun 02, 2005 2:03 am

theNerd, did you ever get a chance to try zApp with your MS SQL database?

I've been having a heck of a time trying to use MSDE. I just cannot even seem to connect to anything on the localhost server. The MSDE SQL server is running, and I *finally* got the Web Data Administrator to work (needed a new version of a .NET framework file installed, which looks like yet more problems with distributing .NET apps).

Anyway, I'm hoping that maybe I've got a bad NTWDBLIB.DLL file, since MSDE doesn't seem to come with this needed DLL. I found a copy on some web site, but have no idea if it's the right version. So hopefully zApp will work better for you on a real MS SQL server installation.

But please let me know soon. I really need to know if there are problems with the MSSQL database support that I need to fix.

Wizard Joined: 10 Oct 2000 Posts: 1551 Location: Australia

MSDE is a 'real' SQL Server installation...

Did you browse to C:\Program Files\Microsoft SQL Server\80\Tools\Binn and run svrnetcn.exe and make sure that TCP/IP was enabled?

One of those "security" things they added was to disable any useful methods of communication by default... think you might have to add it to the XP firewall rules too, but it's been a while since I've installed it so I can't remember if that was done automatically or not.

The version of that DLL which I have is 2000.80.194.0 ... it's in the windows\system32 directory on my system. Not sure if MSDE installed it or it came with XP.

Posted: Thu Jun 02, 2005 2:35 am

Looks like the NTWDBLIB.DLL file I've got is for SQL Server 7.0 and I need the one for SQL Server 2000. I really don't understand this mess. Microsoft claims this file is redistributeable, but I can't find *anywhere* to download it. I must have spent two hours with Google tonight trying to find this file.

I'm now trying to download the SP4 for MSDE, and something called the Database Components for SQL Server. Supposedly, the DLL file is somewhere in here.

Once I find the proper DLL file that works with zApp, I'll upload it to the Database section of the zApp Download page. I just can't believe how much of a pain it is to get this file.

My personal opinion is that MSDE is a mess, just like ADO before it. In fact, MSDE still requires an up-to-date MDAC installation, so it's going to have all of the same version control problems that MDAC/Jet has always had.

End users of software like zApp are *not* going to go to the trouble of installing MSDE, then the service pack, etc, just to get database functionality. Not when there are much simpler alternatives. I just don't know why Microsoft can't get their act together when it comes to installers and make something that end users can actually install. I consider myself a computer expert and this stuff still takes me HOURS to get working correctly.

Very disappointing.

Wizard Joined: 10 Oct 2000 Posts: 1551 Location: Australia

The "dbview.zml" connected to my MSDE installation on the lappy fine... I ran a couple of "SELECT" statements on my work database, and even updated a couple of fields (nice grid component!).

I think I am getting an access violation executing stored procedures using that dbview, but not sure if it's my fault or something wrong in zApp... here's the message:
"Access violation at address 009DDDA6 in module 'Zappl.exe'. Read of address 00000000. Line: 3, Column: 9 [TekWSHProxy]".

This might be caused because the stored proc doesn't return a result, but it looks like the proc was executed and the values inserted into the database as they should be.

Oh, I was running it from the SQL input window eg:
EXEC stored_proc_name 'variable1', 'variable2'

Not sure if that was the info you were after or if you had a special 'zStoredProc' thing to try. (as you may be able to tell, I haven't been following zApp that closely, it's eMobius that I'm waiting for :))

Wizard Joined: 10 Oct 2000 Posts: 1551 Location: Australia

And I think this is what you want to install: http://www.microsoft.com/downloads/details.aspx?FamilyId=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en (that's the full install, not the upgrade from an existing version).

To run the setup once you get the file you hafta do it from the command line because you need to specify an admin password etc.
Then you run that thing I said above -- C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe and make sure that TCP/IP is enabled.

But that one download should come with everything you need to run MSDE.

Posted: Thu Jun 02, 2005 3:17 am

OK Rainchild, maybe you can help me with this MSDE stuff since it seems to work for you.

First, I finally got the correct DLL file. MSDE is up and running and I can connect to it from the SQL Web Data Administrator and from the OSQL command line.

I had to change the authentication to "mixed" mode and when I run the Web Data Administrator, I select the SQL Login method, then enter "sa" for the username, and the password that I specified at install time in the password field, and the actual Windows name of my machine ("ZUGG" in this case).

OK, that works, but I can't get DBVIEW.ZML to work at all. I can't even get the sample app that came with Zeos to work. My guess is that somehow I'm just not specifying the login information correctly somehow.

For the Server name, I've tried "ZUGG", "localhost" and "127.0.0.1". For the Username/Password, I've tried the "sa" account with the password that worked with the Web Admin, and I've tried my own Windows account name and password.

None of the above combinations work. I'm really at a loss here. Can you give me more details on what you are entering into DBVIEW.ZML in the Connection screen to get it to work with MSDE?

Posted: Thu Jun 02, 2005 3:25 am

Also, I installed the full version of MSDE from the exact link you gave above. That's the SP3a version. I still needed to install the SP4 update (so I thought).

However, after installing, I do not have anything like a C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory.

Edited: Ah-ha! I found the SVRNETCN.EXE in the Database Component SP4 download that I got the DLL from. I ran this and TCP/IP was not enabled. I enabled it and now it works!

Geez, I'll whine about this once again...WHY does this MSDE installation have to be such a PAIN!? No where in any of the instructions did I see anything about running SVRNETCN and enabling TCP/IP, nor was there anything in the instructions for the initial SETUP.EXE that talked about this. The fact that the SETUP program is command-line only is enough of a pain. But this whole thing is a complete mess in my opinion.

Wizard Joined: 10 Oct 2000 Posts: 1551 Location: Australia

Yeah the install is pretty ugly, the full version of SQL server takes down a lot of that stuff in the install wizard, I have no idea why they didn't bundle that with MSDE as well.. I guess to make it 'harder' for end users.

You should be able to get to the database with either your machine name - ie "ZUGG", or with "(local)". I was using the latter.

I think there's a switch you can put on the command line that enables TCP, but I'm not sure what it is (I've never used that myself). Curious that the SP3a didn't install the SVRNETCN ... I've never had a problem with that.

Sorry, I forgot to warn you about mixed mode logins... by standard it uses your windows account details, it's a real pain... at work we hack the registry to force the mixed mode logins on which is why I forgot.

It's weird that you had missing files, I've never seen that problem on any of the installs we've done over the years (even with dev machines and vanilla servers and the wide range between). For us it's just a matter of running setup, doing the registry hack, enable tcp/ip and reboot, then all systems go... In theory you don't even have to reboot, just re-start the SQL server service, but I prefer rebooting the machine 'just in case'.

Adept Joined: 01 Mar 2005 Posts: 277

Zugg, I will try it today. Things have calmed down at work quite a bit.

Posted: Thu Jun 02, 2005 3:45 pm

The best way to administer MSDE is to download an eval version of SQL Server 2000, and only install the Client Tools. This way you get the same administration tools that you would use for the full blown version of SQL Server. To save you a Google search, here's the download link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=D20BA6E1-F44C-4781-A6BB-F60E02DC1335&displaylang=en

Posted: Thu Jun 02, 2005 4:53 pm

But BortaS, think about that....if someone writes a zApp program that uses SQL Server (or MSDE), then the *end-users* will need to be able to install MSDE on their system. I'm *never* going to ask my end users to download the eval version of SQL Server and then install the Client Tools, just to get a copy of the utility that lets them enable TCP/IP mode!

The whole MSDE mess just isn't designed for end users. It's designed for a bunch of DB Admins that want to have an automatted install tool. Microsoft is assuming that MSDE will only be used in a business environment where there are sysadmins and dbadmins around to help with this kind of stuff.

Their web site makes claims about MSDE replacing the use of Jet/MSAccess databases, which I just find absurd. End-users already have enough trouble getting a correct MDAC/Jet installation, and MSDE just builds on top of all of that and makes it worse.

Here's a more concrete example: Imagine that zMUD uses MSDE for the Mapper database instead of Jet! I'd be buried in the support email for that. I'd end up having to write my own custom installer that has the correct tools in it, gets rid of the stuff that I didn't need, scripts to handle the registry changes (yeah, I had to use RegEdit to modify the authentication scheme too). And then after people downloading a 7MB zMUD program, they'd have to do a 42 MB download for MSDE!

As I said, it's just absurd. Having just done installs of MSDE, SQLite, MySQL, Firebird (Interbase) for zApp testing, I can say without a doubt that MSDE was the worst and not something I'll be using myself in any future products.

Microsoft should either simplify the package for end-users, or put the Jet drivers back into the standard MDAC release (why did they stop bundling Jet???) like they used to so that developers can actually use the Microsoft database tools without all of these install and configuration headaches. Not that this will ever happen.

Posted: Thu Jun 02, 2005 4:59 pm

Rainchild, the only thing "different" that I did when I ran Setup.exe the first time was to specify both the TARGETDIR and the DATADIR parameters. Maybe when specifying the TARGETDIR then you don't get the tools for some reason. I used a TARGETDIR of C:/MSDE/ and within that directory, I did get a MSSQL/Binn directory that had various tool-like files and DLLs. But no svrnetcn.exe and no NTWDBLIB.DLL file was in that directory.

Posted: Thu Jun 02, 2005 5:54 pm

Zugg,

I completely understand about the users. That post was for the people on this thread that were having problems with MSDE. I went through the same thing, and I installed Client Tools for something else, when I realized that I was managing the MSDE database through Enterprise Manager. Just trying to be helpful. Cool

In the same vein, I found a nice admin utility for SQLite a few weeks ago. I dislike doing thing with the command line when I'm testing, but I love it for automated stuff. Here's the link:
http://www.kraslabs.com/sqlite_analyzer.html

Adept Joined: 01 Mar 2005 Posts: 277

bortaS, both of your last posts were helpful. Since I work with both MSDE and SQL Server at work I use the admin tool for both. However, I never thought of downloading the trial version of SQL Server in order to use the admin tools with MSDE. That is a good idea that I'll try at home. As for that last post, I believe that could easily be written in zApp.

Adept Joined: 01 Mar 2005 Posts: 277

Zugg, you are absolutely right that MSDE is not for the average user and it would be insanity for a developer to create a retail software package dependent on MSDE as the db engine. I think it was M$ poor attempt at getting people to develop for SQL Server by getting their foot in the door with MSDE. It almost seems like they deliberately sabotaged their own attempts with the most inane installation in history.

Posted: Thu Jun 02, 2005 6:52 pm

Yeah, I wonder if they got into trouble for making MSDE essentially a free version of SQL Server for the desktop, thus taking away sales from the full database product, or if they were worried about trouble from competitors by giving MSDE away for free. So they geared it towards developers, but made it hard for general users. So maybe they only wanted experienced dbadmins to use it.

Anyway, back to the question of stored procedures for a minute. It's clear from Rainchilds post that there are problems with the stored procedure implementation in zApp. So I have a couple of questions for everyone:

1) Are there any stored procedure examples in the sample NorthWind database that Microsoft distributes? If I can get an example database with some stored procedures, then I could do some local testing. And I think I found the spot on the Microsoft site to download the NorthWind sample database.

2) Is the "EXEC procname param, param ..." syntax that Rainchild showed a *standard* SQL syntax for executing stored procedures, or is it Microsoft (SQL Server) specific?

zApp really needs to work with stored procedures, but I'm at a disadvantage since I haven't used them myself and don't have any examples to play with.

Adept Joined: 01 Mar 2005 Posts: 277

Zugg, as far as #2 goes, you can. This covers input parameters (where you pass values into a stored procedure.) Although most stored procedures return recordsets some also (or instead) return output parameters. Of all the stored procedures we use only a couple return output parameters.

BTW, I am in the proccess of downloading the trial version into a virtual machine (my time ran out on my main work computers and I didn't want to waste my licenses for work computers Very Happy

) so I can test the stored procedures with our database.

Adept Joined: 01 Mar 2005 Posts: 277

Zugg, the stored procedures worked for me in the dbview demo. I could do the following:

Wizard Joined: 10 Oct 2000 Posts: 1551 Location: Australia

I think my stored procs had access violations because they didn't return results, but I'm not sure. Also, I haven't tried the zQuery syntax that Zugg posted on the first page:

Wizard Joined: 10 Oct 2000 Posts: 1551 Location: Australia

My workmate says the command line options you want is: