SQL server inclusion-VBForums
Results 1 to 10 of 10

Thread: SQL server inclusion

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    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

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,138

    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.

  3. #3
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,501

    Re: SQL server inclusion

    Quote Originally Posted by DataMiser View Post
    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,490

    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.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,107

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    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

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,490

    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.

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    Re: SQL server inclusion

    Thanks
    Elico

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,105

    Re: SQL server inclusion

    Quote Originally Posted by elico View Post
    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).

    Quote Originally Posted by elico View Post
    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

  10. #10
    Hyperactive Member
    Join Date
    Jul 2013
    Posts
    310

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.