Database

Luc Pattyn26-Jun-09 6:55

Luc Pattyn

26-Jun-09 6:55

Hi,

two suggestions:

1. for single-user applications, you could use Access; from a .NET perspective all this takes is an MDB file; once created (requiring Access as part of Office), you no longer need MS Access itself. Drawback is it has some functional limitations, and offers an SQL subset.

2. SQL Server Express is downloadable and free; it supports a limited number of users (10?) and a limited DB size (4GB?)

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:04

26-Jun-09 7:04

Well that's the problem, i don't know how many users will use my database Smile | :)

It could be only 10 or maybe 1000 till 5000 Smile | :)

And i have a lot data to save Smile | :)

Not big but many, it should be used on a webservice or a web-application.

Luc Pattyn26-Jun-09 7:08

Luc Pattyn

26-Jun-09 7:08

Have a look then at what your web host is offering.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:14

26-Jun-09 7:14

It's my own server/s, or dedicated root servers.

So i "easily" need to storage data (a lot of data) and must access them pretty fast. Is there a way to do it on my own? What's with DataSet and XMLDataDocument? How is there the performance?

Luc Pattyn26-Jun-09 7:18

Luc Pattyn

26-Jun-09 7:18

I can't tell you from my own experience, I only use a small MySQL db.
Most people here seem to favor SQL Server (the non-Express one for real applications).

This is something I copied here from earlier this year:

16-JAN-2009 Mika Wendelius, on choosing a database
- for single user application with small amount of local data => SQL Server Compact Edition
- for multi user application with medium amount of shared data => SQL Server Express / Oracle XE
- for multi user application with large amount of shared data => SQL Server Std or Enterprise / Oracle 11g

I believe that the next question you would have is: what is a small or large amount of data. Again a matter of opinion but perhaps some quantities (these are certainly not accurate but give some idea):
- small amount could be less than 1 GB, perhaps less than a hundred tables and no table contain over 10 000 rows
- large amount could be more than 4 GB of actual data, perhaps 500+ tables tables and and some tables contain over 10 000 000 rows. User amount is high, let's say over 1000 concurrent.

And maybe this could be useful:
Comparison of relational database management systems
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^]

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:34

26-Jun-09 7:34

Hm OK, i think that my DB really wouldn't have more than 4GB this should really be enough.
My idea was, if i use a db-system, i had the idea to make run it on linux. So MS-SQL isn't the right DB for me.

Web Apps only have 1 user

David Mujica26-Jun-09 7:21

David Mujica

26-Jun-09 7:21

Don't waste your time trying to develop a data storage engine. There have been hundreds of man-years invested in developing database engines. MS-Access is really quite fast and the license for MS-SQL server is really not that bad, if this is going to be a professional web application.

You speak of "clients" right ? Then they are paying a fee, right? Don't cheap out. Consider it an investment.

See my other post about starting with MS-Access and migrating to a more robust DB when you need it.

David Mujica26-Jun-09 7:16

David Mujica

26-Jun-09 7:16

If it is a web application, then you could argue that there is only 1 user making an actual connection to the database; the web app itself! Remember that this is not a client server application where you need to create a user logon for everyone who wants to access the database.

Hmmm ... things to think about.

Consider this, it sounds like you really don't know how much data you are going to store or how frequently it will be accessed, so why don't you start with MS-Access as the database and make sure that you design you application in such a manner that it would be relatively easy to change database enginges without causing a massive re-write. It can be done. Make sure you have a good data abstraction layer designed and you could actually have your application capable of running on all kinds of database engines, MS-SQL, MySQL, Oracle. It all starts with the design.

Just a thought. Cool | :cool:

softwarejaeger26-Jun-09 7:27

26-Jun-09 7:27

Not bad, my application now, can work with all this connectors. I must only exchange them. But i for example don't know how it is with this MySQL DB licenses!

I have for example 2 Server, one is the DB-Server and one is the "processing Server" (the server where the server-side-application is running) which has a connection to the db-server (and there's the problem with only 1 user, maybe i want to make a server-farm so i have a lot of connections). The client is talking only to the processing server, so i don't give any libraries or something away. Maybe like the PHP<->MySQL relation, there i don't need to pay for it and could make commercial applications. But why does MySQL say, that if i use it commercial i need to pay for a enterprise-license.

Has somebody any idea with this problem? And, now which method is the best only to try to make a own db-system? Should i save it "Binary", something like this?:

Table-schema-file:
Table:Test
Field:ID,isKey="true",length="10",type="string"
Field:sample,isKey="false",length="5",type="string"

Data-File:
TestID1   Hello!
TestID2   Hallo!

So there in the Data-File i have my Data saved, each line is a new record. Byte 0-9 is my Field "ID" (because of length-1) and Byte 10-14 is the "sample"-field.

Or should i save it into XML and read/query and so on it with XQuey? Then is the problem, maybe i have a DB-File which has a size of e.g. 4GB. How to update my DB-Files? I couldn't save every 5 Minutes 4GB files, if i only changed for example 500KB? Is there any solution to do this? I found something like XmlDataDocument and DataSet which works exactly, if i would work with a DB. Has anybody experiences with this?

Enver Maroshi26-Jun-09 7:33

Enver Maroshi

26-Jun-09 7:33

You don't save 4GB, you save only part of that file. You don't load whole file and then do something with it, you open partition of file this aprtions lets say is field in database, which has more space than it's filled (done so it can only add inside data, thats why database files are so large) and you fill that space with data. You use streams, you don't open file. And, it is really not that easy to make database engine. There is not only read-write, it's problem also in locking, so one does not owerwrite other user data, etc... auditing, transactions, etc...

With your knowladge, there is no way you could make database engine. Not at least you spend really long time on it. And let's say that 10 year licence for sql server would cost less then your working hours developing real database engine for 1 year Smile | :)

But if you insist on xml, etc... why not serialize objects to xml? Maybe a row by row as different file for faster loading, but many files wwould kill windows FS. Maybe create virtual FS. Oh, so much more to considere here, it is really not that easy to make your own database engine.

softwarejaeger26-Jun-09 7:49

26-Jun-09 7:49

Yes, that's right, but i have only very simple and "foolish" data. Ok, something like logging and so on, that's right, this could be a problem, but i think, before i overwrite something, i could read it first to check if it's the correct line.

Yes, but i heard, that the XML-Architecture in .NET shouldn't be very fast. I saw a lot of tutorials for using XmlDataDocument in DataSet. I have a simple xst-file where is my schema and my xml-file for the data. With those both files i'm able to work with my data inside of DataSets.

But what's better? My "own" System, save all records byte for byte in a file with streams or use the XmlDataDocument and DataSet. My Application works now with SQL-Databases, so i want to try it with this system, and if i see, that really nothing works, i could buy a mysql or mssql license. Another question, what's with MSDNAA licenses? I get a MSDNAA license of mysql, can i use that? And is it able to use that, if i want the client to pay for their client-application? (Not for the server-app or the db, only for their user-account).

softwarejaeger26-Jun-09 8:21

Re: XML, Serialization, Object Oriented

26-Jun-09 8:21

Another idea, which could interest a lot of developer here...

We have a object-oriented programming-language, why not a object-oriented database. With DB4O you can store the data "as it is" as a class into a DB. Something like i would do with XmlSerializer.

Enver Maroshi26-Jun-09 21:09

Enver Maroshi

26-Jun-09 21:09

You wrote before about XML, fast? not really, but since you really would like to write your own database, and best way would be to have object oriented database, look into serialization that is in .Net, you can "convert" class to xml, and vice versa.

Also, take a look at CP articles, there is a lot of stuff on persistence frameworks.

This project really sounds fun, if you can afford it (by losing time, and not making any sallary out of it) go for it Smile | :)

David Skelly28-Jun-09 23:01

David Skelly

28-Jun-09 23:01

Building your own multi-user database is a serious undertaking. I would only do that as a last resort, if I had some really unusual requirements that didn't seem to be handled by any of the existing databases out there. In fact, I probably wouldn't do it even then because I would question why my requirements were so unusual. I guarantee it will turn out to be a lot more work than you expect, and you will spend a long time trying to track down niggling little problems that don't show up until the system is under load.

The licensing terms for MySQL are a bit unclear: most people never bother reading them but just go ahead and use it anyway without thinking about whether they are in breach of the licence or not. Contrary to popular opinion, it is not universally free. Under certain circumstances you will need to buy a commercial licence.

To answer your question: at the end of the day, all databases store their data as a file on the disk somewhere. The exact location of that file and the format of the data inside it and how it is accessed varies from database to database. Database vendors put a lot of time and effort into tweaking this and polishing it and refining it, and they give you loads of configuration options so that your DBAs can tweak it and polish it and refine it even more to suit your requirements. Massive amounts of time and money have been spent trying to figure out the fastest, most efficient way to execute a SQL SELECT statement as a series of reads from the underlying disk files. It sounds trivial, but it's really not.

There are plenty of free databases out there, with very liberal licensing terms. Some are better for certain applications, some may suit your personal taste more. You don't say what technology you are using for your app, but you could take a look at Firebird, Postgres, SapDB, Apache Derby. They are all good databases with open licences. For some of them you can even download the source code and have a look at how they work under the covers if that's what floats your boat.

Out of range exception when using spaces to SQL? [modified]

smcnulty200029-Jun-09 21:30

smcnulty2000

29-Jun-09 21:30

Consider looking at sqlite.
http://sqlite.org/[^]

_____________________________
When life hands you marmots, make marmalade.

cdietschrun26-Jun-09 4:02

cdietschrun

26-Jun-09 4:02

I'm new to SQL and am getting an exception on my webpage when I have an argument to my SQL DB that has spaces in it.

Basically, the page is being passed a name like "Name with spaces", which is being sent as templatename: (in VB)

ra = New SqlDataAdapter("exec sp_GetTemplateByName '" & templatename & "'", TestStatConn)
ra.Fill(dt)

The exception gets thrown at this line only for names that have spaces, not if a templatename is passed like "name_without_spaces". The exception is

System.IndexOutOfRangeException: There is no row at position 0.

ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"

The sp_GetTemplateByName:

Select
..stuff..
from tbl_checklist_templates
where templatename=@TEMPLATENAME
order by stepnumber

Can anyone shed some light on what's going on here?

Edit:

If I remove the single quotes from this line

"exec sp_GetTemplateByName '" &amp; templatename &amp; "'"

and make it

"exec sp_GetTemplateByName " &amp; templatename &amp; ""

everything acts the same, except when I click on a template name with spaces, I get this exception:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '%'.

From line 55:

ra = New SqlDataAdapter("exec sp_GetTemplateByName " & templatename & "", TestStatConn)
           (THIS is lne 55) ra.Fill(dt)

modified on Friday, June 26, 2009 10:43 AM

Re: Out of range exception when using spaces to SQL?

Niladri_Biswas26-Jun-09 5:49

Niladri_Biswas

26-Jun-09 5:49

I guess the error is coming while the control reaches this line

ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"

Just check the count of your datatable, something like this(the code is in c#)

if(dt != null)
{
  if(dt.Rows.Count > 0)
  {

    // your code [ CheckForNullVal(dt.Rows(0).Item("lastchange")) ]
  }

}

N.B.~ I don't know VB.net, henceforth presented the code in C#
Hope this helps
Smile | :)

Niladri Biswas

Is it possible to write stored procedures and views in ms access

Member 426027026-Jun-09 0:35

Member 4260270

26-Jun-09 0:35

Hi
Is it possible to write stored procedures and views in ms access 2000

Thanks
Denny

Re: Is it possible to write stored procedures and views in ms access

riced26-Jun-09 2:05

riced

26-Jun-09 2:05

Yes - sort of.
You can create views simply by using the query designer, or do it in VBA code.
You can use VBA to emulate stored procedures or use it to create querydefs that act like stored procedures.
If you want to invoke stored procedures on a linked database (e.g. on SQL Server) you can use pass-through queries.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Float or else?

VisualLive25-Jun-09 16:37

VisualLive

25-Jun-09 16:37

Hi ,
i have a doubt to choose a datatype in my database.I have a table with 3 fields :

1 - Code (nvarchar) PK;//in this field there is a code to refer a product.
2 - ProductName (nvarchar);
3 - here i have a doubt ,in this field i need to insert a number (percentage) but i don't know if the best choice is the d float(datatype), the purpose of this field is after the Total of some products we use this percentage to increase the Total for example :
if the Total is 25.000 USD i need calculate the Total + 25,7% so 25.000 +25,7%.
Which is the better datatype to use in this field to calculate the Total ,is it Float(datatype) or isn't?Can you advice me to apply this step in the right way?

Thanks so much for your attention.

Smile | :)

Mycroft Holmes25-Jun-09 17:18

Mycroft Holmes

25-Jun-09 17:18

Is your data language English, I note you use a comma as a thousand seperator.

If Yes then use VARCHAR() instead of nvarchar which supports unicode characters.

I use decimal rather than float, there is a slightly larger penalty when using float and IIRC you cannot use a float in an index/constraint.

Never underestimate the power of human stupidity
RAH

J4amieC25-Jun-09 23:50

J4amieC

25-Jun-09 23:50

Mycroft Holmes wrote:
use VARCHAR() instead of nvarchar which supports unicode characters

Wrong way round, and whats it got to do with thousand separators?

Mycroft Holmes26-Jun-09 0:29

Mycroft Holmes

26-Jun-09 0:29

nvarchar() supports unicode - I guess the sentence is ambigious.

Comma as the thousand seperator indicates non english format, reasonable assumption. All too many people accept the SQL Server default format with no knowledge why it may not be valid.

Never underestimate the power of human stupidity
RAH