Results 1 to 17 of 17

Thread: [RESOLVED] I want an alternative to SQL/MySQL Database

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Resolved [RESOLVED] I want an alternative to SQL/MySQL Database

    Hello everybody,

    Here is my problem. I developed a application for my job using a SQL Database (SQL Server 2008R2). It works ok on my local computer but this is only because I have spent many hours and many days just to figure out how to do this. No I want to run this app from another computer on a network and it doesn't work. I tried everything, permissions, firewalls, settings, etc. etc.. nothing.

    Then I changed SQL with MySQL and it's the same. It works fine locally but I can't make it work over a network. So I am thinking to an alternative to all the server-based solutions because it's really too difficult for my needs and the last thing I want is to spend day after day for maintenance and server management.

    The idea is that my app is running by only 4 users so I don't think I need the complexity and performance of Sql or MySQL server so I want to keep it as simple as possible.
    My only request is that I need the 4 users to be able to write at the same time a certain info to the db.

    So, please tell me if there is an alternative, like a simple database file that is stored on a simple folder on a simple computer that can be accessed by 4 users.

    SQL and MySQL have very great performance but I am not a database specialist and I only want to create a simple app. The database itself is also quite simple.

    The other problem is: let's say I want to sell this software to another company. They would have to hire a SQL server specialist just to make it work an to be sincere I think this is the wrong to do this... Not for what I need anyway.

    Pls help me with this.

  2. #2

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: I want an alternative to SQL/MySQL Database

    What about Local Database (SQL Server Compact 3.5 database for local data)? As I understand there is no need for SQL Server to be installed. Is it easier to work with? And can I put it on a server computer so multiple users can read/write to it?

    When I mean users I am not referring to database users. They are people that have the same app installed and that access the database in the same way, using the same connection string. There will be no need for user/password kind of thing...

    Edit:
    It doesn't work because it Server Compact doesn't allow to create a database on a network share.
    Can anybody please give me some suggestions?
    Last edited by ovi_gm; Feb 14th, 2014 at 07:12 AM.

  3. #3
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: I want an alternative to SQL/MySQL Database

    When you say network do you mean over LAN or WAN? If it's LAN, I'm assuming you allowed remote connections and the client pc had the correct driver installed too?

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: I want an alternative to SQL/MySQL Database

    Quote Originally Posted by smendoza View Post
    When you say network do you mean over LAN or WAN? If it's LAN, I'm assuming you allowed remote connections and the client pc had the correct driver installed too?
    I mean LAN. I did what you were saying but I couldn't make it work. Anyway, I didn't like the idea of SQL Server in the first place because it is too complicated for what I need. Besides, I am not a specialist in this field, this is just a hobby.

    I barely could make it work on my local computer. And there is a possibility to give this software to another company and they will get scared when I will tel them they need a SQL Server and somebody to be able to maintain it, cause I won't be there. So I need something to install and work without any complications.

    I did some tests with Access database today and I am surprised how easy it worked over the network. It is just a file that can be copied on a PC on a network and it can be accessed by anyone. I was thinking to go this way.

    My app will be accessed by a maximum 4 - 5 people and rarely they will be on the same time, on the same project. So maybe, when someone edits a database table, somebody else will access the same table (if needed) on a read-only level. Something like this...

    What do you think?

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: I want an alternative to SQL/MySQL Database

    And the correct connection string too... that's important... "it doesn't work. " statements like that without any further information, like what the error was or anything... is meaningless... maybe all you needed to do was set the right server on your database... maybe it's simply a matter of getting the right drivers or 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
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: I want an alternative to SQL/MySQL Database

    Probably. Anyway, I tried a lot of things, all the possible connection strings, I got a lot of different errors, read a lot of forums, a lot of problems and solutions... and to be honest I'm tired of it. So I really want to leave SQL Server forever and focus on something much simpler, that is ok for my application. It really doesn't need all this performance and I loose valuable time trying to make it work. If there isn't any simpler solution then this project will be abandoned.

    I repeat, this is just a hobby of mine and I don't want it to be so much time consuming. I think I have spent several days just to make it work on my local computer. I had to make a "possible errors book" of my own with problems that might come up when installing SQL Server and solutions to it like modifying registries and so on... Really guys, this is too much for me. I really don't have the physical time to make this work. So this was the idea. A software that installs very quickly and that can work very easy.

    If I have to reinstall my windows i don't want to spend a week just to make this work again.

    Maybe that's just me, but everytime I installed SQL Server I got different errors. Not to mention the impossibility to uninstall. While I created this app I think I reinstalled windows for 4 times just because my SQL Server Setup screwed up.

    I'm sure this is great for complicated multi-user applications but I really need something simple. So please just tell me if there is an alternative that I should focus on, and please consider SQL Server forgotten in my case

    Thx.

  7. #7
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: I want an alternative to SQL/MySQL Database

    I wouldn't recommend access because the sql syntax is MS ACCESS syntax and I waste a lot of time looking the correct syntax. There are a few other reason but I don't feel like explaining them.

  8. #8
    Lively Member
    Join Date
    Nov 2003
    Location
    California
    Posts
    79

    Re: I want an alternative to SQL/MySQL Database

    Hi,
    Reading your thread I agree SQL Server is too difficult to install for local apps. I use VistaDB you can check it out at : http://www.gibraltarsoftware.com/
    Robert

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: I want an alternative to SQL/MySQL Database

    If there is no data sharing... then SQL CE might be an option... 7 dlls, one sdf file and that's it... but it re----


    ugh... I jsut read this:
    What about Local Database (SQL Server Compact 3.5 database for local data)? As I understand there is no need for SQL Server to be installed. Is it easier to work with? And can I put it on a server computer so multiple users can read/write to it?
    No... you can't... if you need multiple users to be able to read/write from/to the database then you're looking at Access (ick), SQL Server, My SQL, or Oracle...

    Yes, you could use a local database, but that's what it is... a local database. Yes, it does not require SQL Server, as I started to mention, it's 7 DLLs and the sdf. Is it easier to work with? That's debatable... it's a different flavor of SQL Server, so there's differences, some of them minor, some of them significant. And as I mentioned, no you cannot put it on the server for multiple people to access. It is a local database, it is designed to be accessed by a single user at a time.

    -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??? *

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: I want an alternative to SQL/MySQL Database

    Thank you crmfghtr for VistaDB. I will take a look and see how it fits. Yesterday I changed some of my code using Access database. I am doing everything by code (connections, datasets, datatables, dataadapter, etc...). This access solution seems very convenient to me right now. I find it easy to work with, also over a network. I have to check now how it can be accessed and modified by multiple users. I was thinking to somehow control each table access so that, at a given point, only a single user can modify a table. The other users will be able to see the table but not modifying it.

    I don't know yet how to achieve this. Maybe I will create a text file near the database (or a field in the database) where I will store the opened for edit tables. So, if a user opens a table that is already opened then he/she can't change any data.

    And something else: my app is something like a project manager inside a design company. So for each of the company's projects I was thinking to create a separate database so each project has it's own database, together with other useful data, stored in a folder of it's own. So, in case something happens with the database I will not loose every project that was created in time. I'm not worried about disk usage because each project has got gigas of data stored in it. So 1MB of data plus or minus for the database is insignificant.

    Do you think I'm on the right track?
    Last edited by ovi_gm; Feb 16th, 2014 at 04:37 AM.

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: I want an alternative to SQL/MySQL Database

    Honestly? No. I don't think you are on the right track at all. Especially with multiple databases, text files for... what? "checking out" tables? Personally I think you're setting yourself up for disaster in a major way.

    Whether you want it or not, here's my opinion: You really should take the time to learn how to use a server-based database...have ONE database, and a proper maintenance and back up process in place. Use a table to segregate your data... that's one of the points of a database... create Project table... then use the ID of that to segregate your other records.

    -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??? *

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: I want an alternative to SQL/MySQL Database

    Thx techgnome for you advice. Could you please give me some arguments as to why I shouldn't use the above method? The idea behind the multiple db (each project with its own db) is because I have heard that access database might at some point get destroyed by unknown reasons. That's why I was thinking that no matter what happens I can't loose ALL of my data.

    I agree that SQL is safer but wit Access maybe this is better. The projects themselves are not even related to one another in any way...

    And another advantage (besides the absence of a db server) would be the possibility to export a project database to a xls file that could be transmitted to somebody that doesn't have this app installed. I did this in my first version to export to a xml file and it was quite a lot of code...

  13. #13
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: I want an alternative to SQL/MySQL Database

    You will have to learn server based solutions. If you can make it work over the network then it will work via a localhost connection (still over "network"). Your solution has to be scalable. What if the business grows to more than 4 users? As to XLS, there are a lot of tools and reporting technologies that can do this. No need to reinvent the wheel. But you will need to know how to connect these to your server based database.

    You are already worrying overly on the details of implementation when you can still come up with a better implementation plan which may render obsolete/inconsequential the things you are worrying about right now. See the forest for the trees.

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: I want an alternative to SQL/MySQL Database

    The projects themselves are not even related to one another in any way
    Right up until the moment someone asks "how many tasks have we got outstaning in total, across all our projects?" or something similar. You suddenly find yourself needing to query each database separately and manually aggregate the results. And then someone adds a task to the third database while you're still querying the second and throws yor calculations off. And so on and so on. That's a pretty basic example of why ou try to keep everything under one roof but it doesn't take much imangination to see that there are a load of scenarios where you will regret separating them. As TG said, you would be better keeping them as one DB and introduciong a "project" field to the apropriate tables.

    This:-
    So, in case something happens with the database I will not loose every project that was created in time.
    ...is not a good argument for separating them either. It's an argument for taking regular backups and ensuring that you can restore them in a timely and resilient fashion.

    That's not really pertinent to your original enquiry though. As to what DB you should use, in your case I think I probably would recommend Access (which is not something I do often). It sounds like yor instances of paralel access will be very rare so you should be fine. I believe (though perhaps an Access expert could confirm because it's a loooong time since I did anything with Access) that Access will not allow two users to access the database simultaneoulsy, the second user is locked out of the whole file, not just the individual tables. If I'm right in that then you have no concerns about parallel access.

    Whether I'm right or wrong, though, do not do this:-
    Maybe I will create a text file near the database (or a field in the database) where I will store the opened for edit tables
    What you're doing tere is trying to implement the logging and locking capabilities of a "proper" server based DBMS. It's a huge subject and one which the developers of those DBMS's have invested hugely in... because it's hard! If you you are struggling to get your head around SQLServer then you aer certainly not equipped to rewrite a huge swaythe of it's functionality. (I'm pretty good as a SQL Server developer and I'm damn certain I wouldn't be able to do it).

    Overall, I think you would be best off taking the time to learn SQLServer. It does have a bit of a learning bump but folks here will be happy to help you past it. And once you've got it down pat you'll find it's actually pretty easy and you'll be glad you did it. That said, if you're talking about 4 users with rare parallel access then Access deployed to a shared drive is probably a legitimate alternative for you.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15
    Junior Member
    Join Date
    Feb 2014
    Location
    Sydney, Australia
    Posts
    23

    Re: I want an alternative to SQL/MySQL Database

    Most sql servers (including mysql) installed on a local machine only listen on 127.0.0.1
    If you want something small and portable, give sqlite a try.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Sep 2012
    Posts
    221

    Re: I want an alternative to SQL/MySQL Database

    Quote Originally Posted by FunkyDexter View Post
    If you you are struggling to get your head around SQLServer then you aer certainly not equipped to rewrite a huge swaythe of it's functionality. (I'm pretty good as a SQL Server developer and I'm damn certain I wouldn't be able to do it).
    Thx FunkyDexter for your comment. You are right in every aspect of what you said, I just realized it. When you first think of how to solve a problem you never now how deep it actually goes and you think the solution is quite simple when it actually isn't.

    Anyway, I decided to go with Access. I will rarely have simultaneous access to the same records in the database so it wouldn't be a problem.

    Besides, I did some tests with a app where me and other user tried to change the same record in the same time. I get no error and the change is the one that was clicked last. Something like this... But again, there will be rare times, almost never, when this will happen.

    I also read some articles that said Access is recommended for less than 25 users (I have about 5) and less then 50.000 records / database which is almost impossible to reach for now.

    So I will go this way and in the future if the app proves itself to be useful and productive I will take into consideration SQL Server. And of course if Access proves to be unproductive. I guess this is a battle between advantages and disadvantages, comfort, costs and so on. Thank you all - thread resolved.

  17. #17
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: [RESOLVED] I want an alternative to SQL/MySQL Database

    If you are going to use Access then pls. consider to always have a back-up of it since Access is prone to corruption. =)
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width