Database

Re: Read only ADO connection still allows writing to database?

6-Mar-11 11:42

Some nasty concepts are creeping in here

PJ Arends wrote:
I do not want to have to add users and permissions etc to their copy of the DB

Implies an embedded style database. Just which database are you using.

PJ Arends wrote:
I do not want to modify the database in any way.

Then control your code, you own the client you control it! If your users are writing sql script then there is no hope for you.

Never underestimate the power of human stupidity
RAH

PJ Arends6-Mar-11 17:51

PJ Arends

6-Mar-11 17:51

Mycroft Holmes wrote:
Implies an embedded style database. Just which database are you
using.

The DB is a local copy of SQL Server Express 2005

Mycroft Holmes wrote:
Then control your code, you own the client you control it! If your users are
writing sql script then there is no hope for you.

I have read about SQL injection attacks. While it is not likely that users of my app would want to screw up their own data, having a truely readonly connection would prevent it.

You may be right
I may be crazy
-- Billy Joel --

Within you lies the power for good - Use it!

Re: Read only ADO connection still allows writing to database?

Mycroft Holmes6-Mar-11 18:01

6-Mar-11 18:01

Here is what I would do.

From your app I would query the users/logins of the database, if your functional id (this describes the identity your app is going to use to communicate with the database.

create the user with user name and password using sql authentication. Give the user RO rights on all the objects (views and tables)

change your connection to use the RO credentials.

I seriously don't know if a RO connection is valid, I have certainly never heard of anyone using one.

Never underestimate the power of human stupidity
RAH

Updating Columns

whatsa3-Mar-11 14:14

whatsa

3-Mar-11 14:14

Currently I have a column in a MySQL database full of car makes such as Audi, Porsche, and BMW. Although, the make column is filled with either Audi or Audi® or Audi ® and for the other car makes. There are over 2000 rows. I'm trying to figure out how to make every row just "MAKE ®" without typing it because I'm using it to make a menu in php/javascript and it repeats menu items like "Audi" and "Audi®" and "Audi ®."

http://i54.tinypic.com/w7zm6c.png

I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format.

For example I have..

$makes_select = "SELECT DISTINCT make FROM files_avail where shw=1 ORDER BY make"; // Fetching of makes

I was looking up the MySQL Reference Pages like on here:

http://dev.mysql.com/doc/refman/5.0/en/replace.html

And query commands like UPDATE and INSERT.

But I am unsure of how to do it in MySQL, I didn't want to do anything before I screw up the entire database :3

Thanks!

-Faul

Luc Pattyn3-Mar-11 14:24

Luc Pattyn

3-Mar-11 14:24

Member 7723899 wrote:
I didn't want to do anything before I screw up the entire database

then make a backup first.
or at least copy the relevant table to a new one.
if you have PHPmyAdmin, both actions would be very easy.

Member 7723899 wrote:
make every row just "MAKE ®"

a single update could remove every trailing ® in all rows that have one
then a single update could remove every trailing space in all rows that have one
then a single update could append whatever you want in all rows

BTW: I see no use of a suffix " ®" that would be present in every row, it does not add information, it just makes things more difficult IMO. And if you want it to appear on some forms, then that is a presentation issue, something a database should not be involved in.

Note: we have a separate MySQL forum!

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

whatsa17-Mar-11 10:07

whatsa

17-Mar-11 10:07

I found it out.

Simple enough.. I was just only wondering WHERE I should make the SQL code.

I'm using SQL Manager Lite for MySQL and you just have to go to the database table, click the DLL tab, edit, delete all the code and write whatever you want and execute.

UPDATE tablename SET columnname = 'whatever' WHERE columname = 'asdf' OR columname = 'asdf ®'

jschell3-Mar-11 15:27

jschell

3-Mar-11 15:27

Member 7723899 wrote:
I've tried SELECT DISTINCT but that just takes away the repeated makes, I need to change all the makes to follow the same format

The steps are
1. Determine what values are in there
2. Determine what values should be in there.
3. Create an algorithmic solution that will convert an incorrect value to a correct one. This is based on steps 1 and 2.
4. Implement 3 in SQL.
5. Run 4.

Your description sounds like you have not completed steps 1 and 2 in the above and are attempting to jump directly to step 4.

Additionally.
1. Determine if you want to prevent future incorrect values
2. If yes then add a constraint that prevents incorrect values.

Based on your description you would do this by creating an enumeration table which is nothing but a table of correct names. Then you use a foreign key from the existing table to the enumeration table.

SQL Server Authentication issue - Fixed, but I don't understand why

PIEBALDconsult3-Mar-11 16:34

PIEBALDconsult

3-Mar-11 16:34

No, no, no...

The steps are
0) Put your left foot in
1) Take your left foot out
2) Put your left foot in
3) Shake it all about
4) Do the hokey pokey

gantww3-Mar-11 5:55

gantww

3-Mar-11 5:55

Greetings,
I've been having an issue on one of our servers here where we could not connect to the SQL database hosted on the same machine over named pipes from a web application. We could, however, connect from enterprise manager using the same credentials. Shutting the firewall off didn't help, nor did any tweak we made to the sql server configuration. While biting my lip in despair, I happened to notice that the system time was off by three years. I figured I'd fix it while I was trying to figure out what to do with SQL. Once I fixed it though, SQL authentication started working again.

Now, while I'm totally stoked that I was able to fix the problem, I don't understand WHY that fixed the problem, and I do try to avoid cargo-cult style system repairs. Can anybody explain this to me?

Thanks,
Will

complicated select

benams3-Mar-11 5:16

benams

3-Mar-11 5:16

I'm not familiar enough with SQL syntax, and I need to make a little complicated SELECT.
I have a table with 3 fields: contentID, date and readsCounter.
I have to get the top 2 read contents for the last 2 days.
For example:

contentID: 0, date: 1-1-2000, readsCounter: 10
contentID: 0, date: 2-1-2000, readsCounter: 80
contentID: 1, date: 1-1-2000, readsCounter: 40
contentID: 1, date: 2-1-2000, readsCounter: 5
contentID: 2, date: 1-1-2000, readsCounter: 20
contentID: 2, date: 2-1-2000, readsCounter: 30

the lines above are 6 records from my db, contents 0 and 2 are the contents that need to be selected(the contents and the reads sum for the last 2 days).
I'm working with MSACCESS DB. someone can help me?

Chris Meech3-Mar-11 8:45

Chris Meech

3-Mar-11 8:45

The dates are from 10 years ago, so how would they be considered for the last 2 days. Confused | :confused:

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

List of common misconceptions

Jörgen Andersson3-Mar-11 9:37

Jörgen Andersson

3-Mar-11 9:37

Maybe it's an old book.

Mycroft Holmes3-Mar-11 13:52

3-Mar-11 13:52

Use a combination of Row_Number and Partition. You need to create a 2 query select, the first injects the row_number based on the partition of contentid and date, ordered by readscounter.

The outer query selects anything with a row_number < 3.

Never underestimate the power of human stupidity
RAH

benams4-Mar-11 7:35

benams

4-Mar-11 7:35

can you add an example code for what you have suggested? I'm afraid I don't completely understand how to implement this.

Mycroft Holmes4-Mar-11 12:49

Oracle Multiple schemas Vs single schemas

4-Mar-11 12:49

Then I found a simpler way when I actually worked the query.

SELECT TOP 2
  contentID,
  RC  
FROM
  (SELECT
    contentID,
    SUM(readsCounter) RC
   FROM
    Book1 AS B
   --WHERE ReadDate BETWEEN this AND that
   GROUP BY
    contentID) D
ORDER BY RC desc

Note the where clause will be required when the data set is larger

Never underestimate the power of human stupidity
RAH

V.2-Mar-11 20:11

2-Mar-11 20:11

We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database.
The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future.

My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security.
(But what if a table that starts belonging to one application later needs to be shared among applications?)

Another option would be to seperate tablespaces, but use the same schema.

I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill.

Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case.

Many thanks in advance.

List of common misconceptions

Jörgen Andersson2-Mar-11 21:08

Jörgen Andersson

2-Mar-11 21:08

V. wrote:
His main reason being security.

Good reason.

V. wrote:
But what if a table that starts belonging to one application later needs to be
shared among applications?)

Then you have to set the access rights accordingly and add the schema to the tablename when accessing it.
Like this: Select * from schemaname.tablename

V. wrote:
Another option would be to seperate tablespaces, but use the same
schema.

Thats something completely different. That's a logical storage space[^].

Mycroft Holmes2-Mar-11 22:17

2-Mar-11 22:17

I'm pretty sure tablespaces have nothing to do with your segregation of data, it is only for logical storage.

We have just run across a real need for additional schemas, we want to move the production data (ETLd from other systems) to the dev server, if the data was in different schemas this would be simple, now we have to reengineer the database schemas to meet that requirement.

Security is another issue so I would go for the additional schemas from the start. As for moving from 1 schema to anoter I believe there are tools to help (Toad) and it is just the price you pay for getting it wrong (or changing requirements).

Never underestimate the power of human stupidity
RAH

PIEBALDconsult3-Mar-11 1:56

PIEBALDconsult

3-Mar-11 1:56

The only place I worked with Oracle we had one big happy database and it was fine. Keep it simple.

Wendelius4-Mar-11 10:28

Wendelius

4-Mar-11 10:28

Hi,

Somekind of list of thoughts on this issue:
- security is a good point. Although security can be enforced on many different levels you can use owner permissions for example in packages. This helps to handle common security problems.
- logical design. If you separate the objects to different schemas based on the data they hold, it's easier to understand the database design (and usage) especially in bigger databases.
- backups and other DDL operations. Many of the operations can be done schema based. For example you can export a certain schema so this helps in administrative tasks
- usage in applications. This is two folded, you can either define the schema in applications (like SELECT ... FROM schema.table) or you can use public synonyms to hide the structure. Public synonyms are bad in situations where you actually need for example a table with the same name twice in the database but otherwise they should simplify the use in the apps.
- isolation. Using different schemas you can isolate different portions of the database more easily (a bit same as the security) but again this can be done in multiple ways.

V. wrote:
Another option would be to seperate tablespaces, but use the same schema

This is a totally different issue. Tablespace is used to define the actual storage place for an object regardless of the schema. A simple situation is that you have a table and an index on it. In optimal case they are stored on different disks so they would be stored on different tablespaces even though they both would be defined in the same schema. So tablespace is just a way to define physical storage place without defining the actual file (since tablespace can contain multiple files).

The need to optimize rises from a bad design.My articles[^]

V.4-Mar-11 10:48

4-Mar-11 10:48

This is in fact what we did finally. Created schema per application and a common one holding configuration data and parameters etc. For that schema we made public synonyms.

After everything was moved I had to change very little to my prototypes to make them work Smile | :)

thanks for the advice.