Re: arabic character not saved in MySQL - Database Discussion Boards

Re: Generating Custom XML from multiple rows in SQL server

Richard MacCutchan19-Jun-13 7:00

Richard MacCutchan

19-Jun-13 7:00

Take a look at these links[^].

Use the best guess

Re: Generating Custom XML from multiple rows in SQL server

RedDk23-Jun-13 9:21

RedDk

23-Jun-13 9:21

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

XML

<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.

How to permanently solve (blocked because of many connection0 error?

Jassim Rahma18-Jun-13 2:25

Jassim Rahma

18-Jun-13 2:25

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

Re: How to permanently solve (blocked because of many connection0 error?

jschell18-Jun-13 8:40

jschell

18-Jun-13 8:40

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.

Re: How to permanently solve (blocked because of many connection0 error?

Bernhard Hiller19-Jun-13 21:13

Bernhard Hiller

19-Jun-13 21:13

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.

arabic character not saved in MySQL

Jassim Rahma16-Jun-13 10:20

Jassim Rahma

16-Jun-13 10:20

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

Re: arabic character not saved in MySQL

Simon_Whale16-Jun-13 22:32

Simon_Whale

16-Jun-13 22:32

Have a read of this Stackoverflow - MYSQL save data in arabic[^]

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

modified 17-Jun-13 6:25am.

Re: arabic character not saved in MySQL

Jassim Rahma17-Jun-13 11:27

Jassim Rahma

17-Jun-13 11:27

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:

XML

<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

Database Design For Multiple Product Types

Member 770211015-Jun-13 0:28

Member 7702110

15-Jun-13 0:28

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.

Re: Database Design For Multiple Product Types

Eddy Vluggen15-Jun-13 2:41

Eddy Vluggen

15-Jun-13 2:41

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Database Design For Multiple Product Types

David Mujica17-Jun-13 4:08

David Mujica

17-Jun-13 4:08

Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue)

Example:
100,Color,Black
100,KosherCertified,False
100,InsuranceValue,123.99

A structure like this gives you the maximum flexibility for storing varying attributes for each one of your Products.

Just an idea. Java | [Coffee]

Re: Database Design For Multiple Product Types

Jörgen Andersson17-Jun-13 7:19

Jörgen Andersson

17-Jun-13 7:19

And AttributeType, so you can cast the attribute to the right type. And then normalize the Attribute and AttributeType into its own table.
Just my $0.02

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

Creating New Database in SQL 2012 Management Studio

Gary Heath13-Jun-13 6:41

Gary Heath

13-Jun-13 6:41

Fairly basic question really, if each of my Tables has a Primary Key of UNIQUEIDENTIFIER, when I create a Foreign Key to point at the PK of another Table, what Data Type should it be ?

Re: Creating New Database in SQL 2012 Management Studio

Eddy Vluggen13-Jun-13 9:45

Eddy Vluggen

13-Jun-13 9:45

Also uniqueidentifier. Types of PK and FK needs to match.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Creating New Database in SQL 2012 Management Studio

Gary Heath13-Jun-13 11:44

Gary Heath

13-Jun-13 11:44

Really ? I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field ... I have a lot to learn here Blush | :O

!!!

Re: Creating New Database in SQL 2012 Management Studio

Bram van Kampen14-Jun-13 14:17

Bram van Kampen

14-Jun-13 14:17

Hi,

As I understand it, A Unique Identifier is only created if you also set it as Primary Key.

Regards Smile | :)

Bram van Kampen

Re: Creating New Database in SQL 2012 Management Studio

Eddy Vluggen15-Jun-13 1:54

Eddy Vluggen

15-Jun-13 1:54

Gary Heath wrote:
I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field

They're not generated by default; it's just a datatype to hold GUID's. For the Primary Key to generate one, it'll have to have a default - usually coming from the NewId function.

The PK is the unique value that identifies your record. An FK only references it; we copy the unique value of the PK that we want to link, and keep that information in the FK.

SQL

Person
PersonId -- (PK, gets the value from it's default)
Name
OtherStuff 

User
UserId  -- (PK of this table)
PersonId -- (FK of Person, to link that person-record to this one)

Person1: 145, Joe, Stuff
User1: 132, 145 -- referencing a user by using it's PK

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Creating New Database in SQL 2012 Management Studio

Amol_B24-Jun-13 19:22

Amol_B

24-Jun-13 19:22

I think it should be on same data type

need recommandation for database for vs.net 2010 c# web service project

neodeaths12-Jun-13 5:14

neodeaths

12-Jun-13 5:14

hi,

i am using vs.net 2010 to make a web service project with linq to connect to a ms sql server.

however i notice that although i added the annotation for the classes classes that need to be persisted.

it is still very tedious to make changes to the class it self as i need to manually go into the database to make the table changes.

can anyone advice me if i should continuing using linq or is there something better out there? which is as simple to use as linq but can automatically change the ms sql table to reflect the annotation i used in my classes

Re: need recommandation for database for vs.net 2010 c# web service project

Richard Deeming12-Jun-13 7:11

Richard Deeming

12-Jun-13 7:11

Sounds like you're looking for Entity Framework Code First Migrations[^]. With automatic migrations[^], most changes can be scripted for you.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: need recommandation for database for vs.net 2010 c# web service project

jschell12-Jun-13 8:59

jschell

12-Jun-13 8:59

neodeaths wrote:
or is there something better out there

Incremental changes handled over time to a database schema with the only record being C# code annotations would be a nightmare for a non-trivial business system.

Of course in terms of that it makes me wonder how you are tracking your changes to the database now given that you are doing it in "the database".

Connecting to an SQL Database.

Bram van Kampen11-Jun-13 13:41

Bram van Kampen

11-Jun-13 13:41

Hi,

I am using C#.Net-VS2012 and SQL2008R2.
My Computer Network name is b-pc, The Connection is Softguard.

I have the following code snippet:-

SqlConnection myConnection = new SqlConnection("server=b-pc\\Softguard;"+
                           "database=SgTextiles; " +
                           "connection timeout=5");
 myConnection.Open();

It excepts with the following reason:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
.....And Much More!!

It seems to look for some form of authorisation.No username or password was provided (nor asked for) when the connection was created new in DbExplorer. Widows Authorisation was used at the time.

What more must I do to connect to the server.

Kind Regards,

Smile | :)

Bram van Kampen

Re: Connecting to an SQL Database.

Eddy Vluggen11-Jun-13 22:28

Eddy Vluggen

11-Jun-13 22:28

Either use integrated security (the preferred way) or specify a user. Bookmark this[^] site, add below part to the connectionstring;

Trusted_Connection=True;

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Connecting to an SQL Database.

Bram van Kampen12-Jun-13 12:59

Bram van Kampen

12-Jun-13 12:59

Thanks,

That worked! Unfortunately, the whole proces turns out to be One Step at the Time!
It connected to the SQL Database, to arrive at the Next hurdle:

Exception:
-

Quote:
System.Data.SqlClient.SqlException (0x80131904): Cannot open database "SgTextiles" requested by the login. The login failed.
Login failed for user 'b-PC\Bram'.

Now, this very database exists in Ms SQL Management Studio under the Path:

-

Quote:
b-pc\SOFTGUARD\Databases\SgTextiles

It is definitely populated with tables with content. The correct spelling and case has been meticulously checked. The Tables were constructed and populated with the SQL management studio, and, also saved. In a further check, (done while writing this, I performed a 'Save All' in the studio, and closed it down. (this went without any complaint from the Studio about Unsaved Files etc) When re-starting the studio, The database had the 'SgTextiles', and all its components in the tree display. When Clicked upon, there was a Dlg stating that 'SgTextiles' is un-available, and the branch cleared. SgTextiles is still unavailable, and is displayed as if totally empty.

Any idea what other "magical incantations" are required to get to the next step.

To anticipate a following issue, I named the first two tables "Users" and "Roles". They are shown in the studio as "dbo.Users", resp "dbo.Names". Is this the correct way of addressing these tables in a queery string, or should I leave out the 'dbo' part.

Thanks for your much appreciated help sofar.

Regards, Smile | :)

N.B.(Modification)

A thought just entered my mind. I am running all this software under a Standard Win7 User Account.
Is there a problem with permissions? I have both Amin and User Access to this machine.
In retrospect, I noted a few times SQL Studio purporting to do thing without complaint, and then not doing them, without stating so. I had already a few detours via the 'Admin', trying to set HKLM Reg Keys by hand.

Thanks Smile | :)

Bram van Kampen

Re: Connecting to an SQL Database.

Tim Carmichael13-Jun-13 2:03

Tim Carmichael

13-Jun-13 2:03

If you can't get it with a trusted connection, try creating a named user in SQLServer and give that user access to the database. Then changed your connection to use the username and password you assign.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.