|
Maybe I missed something in reading through the thread but I'll ask anyway.  What RDBMS are you using?
If it is SQL Server (or probably most others) you can create a query that will generate a SELECT statement from the table and column information in the system tables (in SQL Server, sys.tables and sys.columns). Once that is generated (and a DECLAREd variable set to that varchar string, you can execute the r results.
|
|
|
|
|
Good morning, Ralph.
The database we use is SQL Server 2008. Is the idea you have in mind different to the other suggestions? If it is do you think you could paste an example for me/us?
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Th following could be implemented as a Sotred Procedure or simply executed as is in a query:
USE {enter your desired database name here};
DECLARE @NameOfTable VarChar(128);
SET @NameOfTable = '{enter your desired table name here}';
DECLARE @SQLStatement VarChar(8000);
SET @SQLStatement = 'SELECT ''' + @NameOfTable + ''' AS TableName';
PRINT @SQLStatement;
EXEC(@SQLStatement);
WITH TableColumns_CTE
AS
(
SELECT T.name AS TableName
,C.name AS ColumnName
,ROW_NUMBER() OVER (PARTITION BY T.name ORDER BY T.name, C.name) Seq
FROM sys.tables T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
AND T.name = @NameOfTable
)
SELECT @SQLStatement = @SQLStatement + ', ' + ColumnName
FROM TableColumns_CTE;
SELECT @SQLStatement = @SQLStatement + ' FROM ' + @NameOfTable + ';';
PRINT @SQLStatement;
EXEC(@SQLStatement);
|
|
|
|
|
Ralph, thank you for that. I appreciate it and will try it at the office tomorrow.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
|
Ralph, I completely forgot to try it. I just c&p into ssms but it didn't run clean and throws some errors. I need to schedule some time to look at it, but off the shelf, I couldn't run it even though I replaced the database name and table with correct values.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
HI ALL,
Is it possible to secure sql database using user name password, not the mixed mode user name password, (user name password ) on database it self to prevent it when restore on other sql engine(instance).
the second questions is how to hide columns on sql server database engines, when open the database can't see the tables and columns until i enter the database user name password
Thanks all
|
|
|
|
|
zead wrote: Is it possible to secure sql database using user name password, not the mixed mode user name password, (user name password ) on database it self to prevent it when restore on other sql engine(instance).
Yes; put them in a password-protected zipfile. There's no password required for a backup or the database-file.
zead wrote: the second questions is how to hide columns on sql server database engines, when open the database can't see the tables and columns until i enter the database user name password
Limit the users' rights.
There's no way to keep the DBA of the server "out".
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
You can encrypt the database and that mat protect your data across a restore, I have never had to do this so I do not have a definitive answer!
Hiding columns in a table view is not possible (I think) but you can create views exposing the columns you want to display for each group of users. Then only give permission for the user to the views and not any other objects.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am building a trading system and seem like other than C++, Java + Ubuntu is the ideal choice. I have no idea which one is a better open source database for speed and stability. Any idea?
modified 22-Jun-13 5:32am.
|
|
|
|
|
MySql and Postgre are both very good choice. If your database is going to be less than 10 GB then you might want to consider Oracle Express edition.
But instead of Ubuntu I would suggest CentOS. Because it's a free version of RHEL, you will find much more help/tutorials/guidance/product support for CentOS than Ubuntu. Every single Product Configuration Guide or Tutorial for RHEL will automatically apply to CentOS.
|
|
|
|
|
When deciding between MySQL or Postgresql look at the granularity of your data. Postressql is designed for big lumps of data like astronomy images or video streams, MySQL for small lumps of data like account information.
Both will likely work but the thinking behind each is different and this will be reflected in performance and ease of use.
"The secret of happiness is freedom, and the secret of freedom, courage."
Thucydides (B.C. 460-400)
|
|
|
|
|
my data would be many small size data like a column of "open price", another column of "highest price of that day", something like that.
|
|
|
|
|
Also consider the operations to be performed on the database. If it's only create-retrieve-update-delete, MySQL will cope with lots of data. But when you want to do some reporting, MySQL becomes too slow early. And for more complicated reports based on several joined subqueries, also Postgres won't do the job - Oracle can handle that (but otherwise Oracle is quite a PITA).
|
|
|
|
|
I have the following table and i would like to generate an XML as shown below. How do i go about doing this please? All help appreciated. Thanks
id school, name, Subject, results, studentID
----------------------------------------------------------------
1 oxford tom Maths 98 1
2 oxford tom English 87 1
3 oxford tom Chemistry 63 1
4 Cambridge john Maths 50 2
5 Cambridge john English 72 2
6 Cambridge john Chemistry 32 2
<Register>
<Schools>
<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths<Name>
<grde>98<grde>
<Subject>
<Subject>
<Name>English<Name>
<grde>87<grde>
<Subject>
<Subject>
<Name>Chemistry<Name>
<grde>63<grde>
<Subject>
<Subjects>
<Student>
<Students>
<School>
<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths<Name>
<grde>50<grde>
<Subject>
<Subject>
<Name>English<Name>
<grde>72<grde>
<Subject>
<Subject>
<Name>Chemistry<Name>
<grde>32<grde>
<Subject>
<Subjects>
<Student>
<Students>
<School>
<Schools>
<Register>
|
|
|
|
|
|
Well, there's no "grade" here ... so I'll use "results" instead. But to illustrate only a point:
CREATE TABLE [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister](
[id][int],
[school][nvarchar](15),
[name][nvarchar](38),
[Subject][nvarchar](45),
[results][int],
[studentID][int]
)
That's the table.
BULK INSERT [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
FROM 'C:\Users\OT\registerOT(td)_.txt'
Where registerOT(td)_.txt has the posted data tab-delimited.
SELECT * FROM [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
WHERE [results] = 72
ORDER BY [id]
FOR XML PATH('Schools'), ELEMENTS, ROOT('Register')
Not what OP is after but perhaps more key than the usual webpage link.
<Register>
<Schools>
<id>5</id>
<school>Cambridge</school>
<name>john</name>
<Subject>English</Subject>
<results>72</results>
<studentID>2</studentID>
</Schools>
</Register>
[edit]
Is it possible that THIS is what is sought (notice the closed tags that have been added)?
<Register>
<Schools>
<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>98</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>87</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>63</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>50</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>72</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>32</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
</Schools>
</Register>
[/edit]
modified 3-Jul-13 14:36pm.
|
|
|
|
|
Hi,
I keep getting the following message on MySQL on Windows Server.
How can I get a permanent fix for it?
Please try again....... Host '192.168.1.254' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Technology News @ www.JassimRahma.com
|
|
|
|
|
One way of course would be to stop creating that many connections.
One reason that happens is because the client is not closing the connection after usage. A variation on that is where there are many user clients and some one misused connection pools in that thus creating a many connections for each client when either a properly configured connection pool or no pool at all would have been a better solution.
|
|
|
|
|
According to http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connect_errors[^], the number of failed connection attempts are counted: i.e. the client tries to connect, but the connection fails for which ever reason. Network problems, wrong credentials, etc. But also - see Jeff's answer - having opened too many connections without closing them; in that case, you should find some entries in the logs that the maximum number of connections was excessed.
|
|
|
|
|
Hi,
I am using INSERT into MySQL database. I am inserting arabic character. The database character set is utf8 and collation is utf8_unicode_ci same with the field article_title.
but the title saved in the article_title field is like this ?????????? (just question marks)
look at this : http://www.volow.com/bahrain.images/About[^]
where is the problem?
Thanks,
www.volow.com
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
this is what I did but I still have the same problem.. Please Help..
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL51\share\charsets\
collation_connection utf8_general_ci
collation_database utf8_unicode_ci
collation_server utf8_unicode_ci
completion_type 0
concurrent_insert 1
connect_timeout 10
and in my connectionstring:
<add name="SQLdb" connectionString="Server=xx.xx.xx.xx;uid=admin;pwd=xxxxxxx;database=xxxxx;Charset=utf8;" providerName="MySql.Data.MySqlClient"/>
Technology News @ www.JassimRahma.com
|
|
|
|
|
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything.
Each product can have any number of different properties.
I have a huge database,which containing about 500000 product in product table.
So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly.
Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
|
|
|
|
|
Member 7702110 wrote: Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
That's not due to a "single" thing, it requires a lot of things to be checked. 500.000 items isn't much.
A normalized design (upto BCNF, see wikipedia) would, IMHO, be the best startingpoint. Next, you'd need to determine indexes. You'd also need to optimize the calls you make to the database - a rewrite of the SQL statement can often improve it's performance.
..and do check the health of database-server once in a while; is the database fragmented? Latest Service Pack installed? Enough memory?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
