SQL and MySql

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 641 Views - Last Post: 25 September 2017 - 03:13 PM Rate Topic: -----

#1 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

SQL and MySql

Posted 23 September 2017 - 02:48 PM

I need some help with sql or mysql. I've got a game project I'm messing with in C# but am having a hard time getting to the database part. I'm used to Mysql from php, but have no real knowledge of SQL.

I've downloaded SQL management suite 2017 from Microsoft which has everything I need. Upon opening SQL 2017 configuration manager I'm faced with error: You don't have permissions or server cannot be reached.

Also is the daunting startup of the management suit which is a blank solution looking page, which askes for a connection to something, but it needs authentication. I've read up on database engine, so I have a basic grasp, just need to get it all working.

The fixes to permission errors are all for older versions, no fix listed for 2017.

Also I downloaded Mysql Ado.net connector as part of an attempt to use ADO instead. Any pointers for a newbie here?

Which should I got with? Should I keep plowing through SQL since I've learned a bit about? If so, and knowledge on my problems here?

Thanks, Jeremy.

This post has been edited by JeremyBenson11: 23 September 2017 - 03:07 PM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL and MySql

#2 ndc85430  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 588
  • View blog
  • Posts: 2,482
  • Joined: 13-June 14

Re: SQL and MySql

Posted 23 September 2017 - 03:13 PM

Regarding the first problem, which is it? Have you started the database server?

Note that SQL is the language supported by many RDBMS, of which MySQL and Microsoft's SQL Server are examples.
Was This Post Helpful? 0
  • +
  • -

#3 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 23 September 2017 - 03:36 PM

1. In Object Explorer, connect to the instance of the Database Engine, right-click the instance of the Database Engine you want to start, and then click Start, Stop, Pause, Resume, or Restart.

Take the above step. When I open sql server management studio I'm faced with a blank solution and a dialogue. The dialog is titled "connect to server".

Three fields are 1. Server type, 2. server name, 3. authentication type.

I'm guessing I want to connect to a database engine, but I haven't created one.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: SQL and MySql

Posted 24 September 2017 - 12:19 AM

From that dialog (that opens with Management Studio) there is a dropdown to browse for servers. Are there any servers available?

When you installed Management Studio did you choose a version that includes SQL Server Express? I don't think all installs necessarily include the Express version. Although, it is typically installed with Visual Studio, but can be excluded, as I recall.

When you open the Configuration Manager (a separate app from Management Studio) it should list available servers. If this list includes the SQL Server Express then you can start and stop it from here, before you open the Management Studio and select the same server. For me this is ANDREW-PC\SQLEXPRESS. Or, as I recall, .\SQLEXPRESS for the default local instance. Try typing this if it isn't automatically populated.

(I am assuming that you intend to use the Express version to work and test locally?)
Was This Post Helpful? 0
  • +
  • -

#5 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 24 September 2017 - 01:31 AM

Okay, I get ya Andrew. Yes, I simply want a local server for creating a DB for my C# game. I got ya though. I'm missing software. I didn't get SQL Server Express. I have an installer for SQL Server 2017 RC, evaluation. Is this the same thing? Or should I just search an installer for SQL Server Express?

Thanks for the reply.

This post has been edited by JeremyBenson11: 24 September 2017 - 01:36 AM

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: SQL and MySql

Posted 24 September 2017 - 02:32 AM

It depends. Do you specifically want to work with SQL Server? Is the evaluation of the full version acceptable or do you want the free, smaller, Express version?

If Express I would want to check, possibly from a command line, that it is not already available. Then I would see if it can be installed via the setup for VS or Management Studio.

If you are happy with an embedded database and MySql then I would consider obtaining a .NET adapter for SQLite and forgetting about Management Studio.
Was This Post Helpful? 0
  • +
  • -

#7 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 24 September 2017 - 07:42 AM

Okay, after a lot of head banging I have this solved. Problems were complication of SQL Management Suite and with Mysql the server needs to be installed on every client machine. That's a bit of overhead, consider the installer has to be configured to do so, and a lot needed for beginner game which really just needs an embedded database. Solution was SQL Compact. A version of the DB which can be created on the fly, and embedded with simple code. Install, add dependency, check if DB exists, if not create it.

I'm facing a new issue with SQL. I've never created tables without visual help, such as phpmyadmin. You can't get that for free with .sdf.

Is everything right here?


 
                string query = "creat table characters (" +
                		"ID int PRIMARY KEY UNIQUE," +
                	   	"breather nvarchar (40) not null," +
                		"vest nvarchar (40)," +
                		"hp smallint,"       +
                	    "token nvarchar (40)" +
                	")";


This post has been edited by JeremyBenson11: 24 September 2017 - 07:48 AM

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: SQL and MySql

Posted 24 September 2017 - 08:45 AM

If you are happy with your create table syntax then execute it and see if it creates a table, although your spelling isn't creative.
Was This Post Helpful? 0
  • +
  • -

#9 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 24 September 2017 - 10:46 AM

No, there was an error:

There was an error parsing the query. [Token line number = 1], Token line offset = 7, Token in error = table]

from this exception:

catch (SqlCeException sqlexception)
			
{
			
       MessageBox.Show(sqlexception.Message, "Oh Fudge.",
			
       MessageBoxButtons.OK, MessageBoxIcon.Error);
			
}


Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: SQL and MySql

Posted 24 September 2017 - 12:26 PM

How do you spell create?
Was This Post Helpful? 0
  • +
  • -

#11 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 24 September 2017 - 04:03 PM

Perfect, lol. Everything seems to be working great. Also created a createTable function to streamline the work. Really appreciate the help. I lost some HP on this one.
Was This Post Helpful? 0
  • +
  • -

#12 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 24 September 2017 - 06:52 PM

Hey, I'm having another problem creating the DB. I'm creating a room table for room descriptions, but I'm getting error nulldescription.

string[] roomsFields = {
				
    "ID int IDENTITY(1, 1) PRIMARY KEY not null, ",
    "name nvarchar (40) not null, ",
    "outside nvarchar (40) not null, ",
    "firstDescription nchar(500) not null",
    "description nvarchar(500) not null, ",
    "area nvarchar (40) not null, ",
    "seen nvarchar (40) not null, ",
    "token nvarchar (40) not null "
			
};



Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: SQL and MySql

Posted 24 September 2017 - 11:08 PM

Line 6 is different to the other lines.
Was This Post Helpful? 0
  • +
  • -

#14 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 233
  • Joined: 10-January 14

Re: SQL and MySql

Posted 25 September 2017 - 09:34 AM

Ah, I caught that one, lol.

I'm facing a slightly bigger problem. I'm trying to do a select, and an insert in the same function. I'm not sure how to go about it in SQL, everything is different than myadmin.

I'm getting an exception on the executeReader line. I'm not sure if it's got to do with having too many connections or what.

public void insertRoom(room Room)
		{
			
			// select token to see if it has been used before
			
			SqlCeConnection cn = new SqlCeConnection(connectionString);
			
			 if (cn.State == ConnectionState.Closed)
           	 {
                cn.Open();
                
                
                Room.token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
                bool tokenInserted = false;
              
                while(!tokenInserted)
                {
                	
                	// test token
					SqlCeDataReader rdr = null;
				
 	           	 	SqlCeCommand cmdTokenTest = new SqlCeCommand("SELECT token FROM roooms where token = " + '\'' + Room.token + '\'', cn);
 	           	 	
					rdr = cmdTokenTest.ExecuteReader();
 	           	 		
 	           	 	while(rdr.Read())
 	           	 	{
 	           	 		
 	           	 		if(rdr.GetString(0) == Room.token)
 	           	 		{
 	           	 			
 	           	 			Room.token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
 	           	 			
 	           	 		}else{
 	           	 			
 	           	 			// insert room here, token not used
 	           				
							SqlCeCommand cmdRoom;
			                
			                string query = "insert into rooms (name, outside, firstDescription, description, area, token) values (" +
			                	
			                	'\'' +  Room.name + '\'' + ',' +
			                	'\'' +  Room.outside + '\''  + ',' +
			                	'\'' +  Room.firstDescription + '\''  + ',' +
			                	'\'' +  Room.description + '\''  + ',' +
			                	'\'' +  Room.area + '\''  + ',' +
			                	'\'' +  Room.token + '\'';
			                	
			                query += ");";
			                
			                MessageBox.Show(query);
			                
			                cmdRoom = new SqlCeCommand(query, cn);
			
						      try
						
						      {
						
						        cmdRoom.ExecuteNonQuery();
						
						        MessageBox.Show("room insert complete: "+Room.name+"", "Message", MessageBoxButtons.OK);
						
						      }
						
						      catch (SqlCeException sqlexception)
						
						      {
						
						        MessageBox.Show(sqlexception.Message, "Sqlexception.",
						
						          MessageBoxButtons.OK, MessageBoxIcon.Error);
						
						      }
						
						      catch (Exception ex)
						
						      {
						
						        MessageBox.Show(ex.Message, "ex.Message.", MessageBoxButtons.OK,
						
						          MessageBoxIcon.Error);
						
						      }
						
						
						        cn.Close(); 	           	 			
 	           	 			
 	           	 				tokenInserted = true;
 	           	 			
 	           	 			// end insert room here token not used
 	           	 		}
 	           	 		
 	           	 	}
 	           	 	
                	// fetch token and test if used before
                	
		     
			        // end insert room with token loop
				}
			
                // end if connection closed
			 }
			
			// end insert room
		}


Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,770
  • Joined: 12-December 12

Re: SQL and MySql

Posted 25 September 2017 - 09:38 AM

What exception/error?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2