-
Oct 25th, 2015, 07:23 AM
#1
Thread Starter
Member
SQL server inclusion
Hi all
In case I use a SQL server in design time of a database app, will I have to install the same SQL server in the end user
computer that will run that database composed at the developper computer ?
Thanks
Elico
-
Oct 25th, 2015, 09:20 AM
#2
Re: SQL server inclusion
You will need sql server installed at the end users site yes.
You will also have to build or attach the database at the end users site.
The SQL Server at the client site does not have to be the same version but does need to be a version that supports whatever it is that you are doing.
I generally develop in an older version of SQL server and then script the database on my end which I can then use to build the DB on the client site. I have not ran into any issues using this method as of yet.
-
Oct 25th, 2015, 09:24 AM
#3
Re: SQL server inclusion
Originally Posted by DataMiser
You will need sql server installed at the end users site yes.
Is that necessarily a requirement? If the code being used is not database-engine specific, base SQL, wouldn't a change in connection string be pretty much all that is required? Of course that means the target database still needs the same structure (tables, relationships, etc) that the development database uses. Would think most database 'export' functions can produce something (db file) that can be imported/uploaded to another engine. Just thinking out loud
Edited: I may be misreading the question. But am assuming there will be a DB on the target machine, and the question is simply: does it have to be the same db engine that is used in development?
Last edited by LaVolpe; Oct 25th, 2015 at 09:49 AM.
-
Oct 25th, 2015, 09:44 AM
#4
Re: SQL server inclusion
But the user has to have SQL Server on his machine.
The problem is that SQL Server comes in a number of "flavors" - all of them pretty bloated and none of them preinstalled as part of Windows. This can make deployment quite a nightware. At the least you need to avoid installing a version that might overwrite components of an existing version the user's PC already has.
About the best thing you can do is use the "psuedo embedded" editions (SQL Server Compact Edition?) and do a "private install." That has its own headaches of course because a couple of the DLLs involved require registration unless you have carefully crafted reg-free COM application manifests. Failing to do that can lead to either nothing working at all (kaboom) or the VB6 runtime finding the DLLs and trying to self-register them in place... which can either screw up the user's machine pretty good or create entries in the virtualized part of the registry which can be tough to clean up later and can haunt your "victims" for years until they reinstall Windows.
Then you have all of the other issues of avoiding named instance collisions and other SQL Server niggles...
In the end most people just use Jet 4.0 which is preinstalled and always at hand or some 3rd party embedded DBMS such as SQLite.
-
Oct 25th, 2015, 11:06 AM
#5
Re: SQL server inclusion
Going one step further... if the database is to be shared, with multiple people accessing it, then no... it would only need to be installed on a central server that everyone has access to. It wasn't clear from the first post whether this is the case or not. But, if the database isn't shared, then you'll need something...
-tg
-
Oct 25th, 2015, 11:56 AM
#6
Thread Starter
Member
Re: SQL server inclusion
SQlite ?
what is special in this server ?
Does it mean that no registry issues involved when installed on target machine?
Elico
-
Oct 25th, 2015, 01:43 PM
#7
Re: SQL server inclusion
There really isn't anything particularly whizzy about SQLite that recommends it.
It has become a fairly common embedded database for non-Windows platforms which lack something native like Jet 4.0, but it is a bit of a stranger in a strange land on Windows platforms. Usage is based on a low-level API though people often create higher-level wrappers. Then you fall into the trap of servicing difficulty as bugs and security flaws are uncovered, since many wrappers embed the database engine and make updating it impossible.
There are ODBC Driver wrappers and even a few rare OLE DB Provider wrappers but these also typically embed the engine making updates difficult. And of course these also require registration anyway so you're right back a square one again.
So unless there is some compelling reason why Jet 4.0 isn't an adequate option it just isn't worth looking at SQLite. The headache of trying to keep a viable implementation in sync with SQLite security and bug fixes is enough reason to avoid it like the plague. SQLite is up to version 3.9.1 or so now, but many prebuilt binaries for Windows that you can use from VB6 are often way back at 2.x versions with all sorts of bugs that can cause data corruption.
SQLite is a moving target and far too many prebuilt implementations lag far behind.
-
Oct 25th, 2015, 02:02 PM
#8
Thread Starter
Member
-
Oct 26th, 2015, 09:16 PM
#9
Re: SQL server inclusion
Originally Posted by elico
SQlite ?
what is special in this server ?
What's special is, that it is not a true Server-DB, but - (in the same way as the MS-JET-Engine)
thought as a "SingleFile-In-Process App-Database" instead (loading the entire DB-engine as a
Dll into the Apps Process).
Originally Posted by elico
Does it mean that no registry issues involved when installed on target machine?
Not when you use the "raw" SQLite-Dll from sqlite.org.
Though the Binary they provide on their site is compiled with __cdecl Exports,
so VBs Declare-Statements cannot be used (since they require __stdcall Exports).
So, you will have to work through a (COM)wrapper-layer against the SQLite-Dll,
in case you want to use it comfortably within VB6.
That could be a combination of (describing the layers):
1) VB6 -> using the COM-based ADO-reference -> addressing an SQLite-ODBC-driver
When you'd use SQLite over an ODBC-driver, you will have to install that driver properly first...
Otherwise, when you leave the ODBC-driver Dlls unregistered, the ADO-layer cannot find
the driver over the appropriate Connection string, to make use of the SQLite-functionality.
To get a decent ODBC-driver (which is maintained and updated regularly), I'd recommend
the one from Werner: http://www.ch-werner.de/sqliteodbc/
It is currently at version 3.8.9 (roughly only 1 month behind "bleeding edge 3.9.1").
2) VB6 -> using the COM-based vbRichClient5-reference-> addressing the SQLite3.dll
So, this setup is (Dll-wise) operating over VB6 -> vbRichClient5.dll -> vb_cairo_sqlite.dll
(the latter Dll includes a vb-friendly __stdcall compile of SQLite version 3.9.0 - being
only about 2 weeks behind the most recent version 3.9.1, which only addressed a minor
issue in the new JSON-Query support which is included since version 3.9.0)
This Dll-set can be used (and deployed) regfree with your Application (without Setup).
So, the situation is not as bad (for VB6-users) as dilettante described in his posting.
There's conveniently usable COM-wrappers, which are maintained and regularly updated
to the latest SQLite-versions.
And it is definitely not a "stranger in a strange land"...
SQLite is one of the best written (and most rigorously tested!) pieces of software
currently on this planet - and the test-coverage for the Windows-platform is not
different from all the other platforms it supports.
SQLite has quite a lot of advantages over ADO/JET:
- InMemory-DBs
- unlimited DBFile-Size
- userdefinable SQL-functions and -Collations
- generally better performance
- buit-in FullText-Search
- JSON-format-support in read-direction (retrieving Resultsets of normal Select-Queries as JSON-string)
- JSON-query-support for JSON-string-content which sits in normal Text-Columns (since version 3.9.0)
The only thing which remains as an advantage of ADO/JET is,
that it comes preinstalled on any current Windows-OS.
In case you want to test the wrapper mentioned under 2), we had a thread recently,
where I explained its usage - and also posted a Link to a quite large Demo-Tutorial-Zip -
in post #17 in this thread here: http://www.vbforums.com/showthread.p...=1#post4928015
Olaf
-
Oct 26th, 2015, 09:44 PM
#10
Hyperactive Member
Re: SQL server inclusion
Yes, avoid SQLite as the plague. You can see here a partial list of unsuccessful SQLite users
http://www.sqlite.org/famous.html
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|