• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

How to set the location of mysql db?

Dear EE experts,

I would like to ask for tech support on how to set the location of the mysql databases created.
From this cmd: connectString = "Driver={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=" & myDb & ";UID=steproducts;PWD=Ste2010Aeg

I set the SERVER=localhost, but where should I save my db? Or from the ODBC Data Source Administrator, how do I locate my db?

Because if I use MS Access as my db, I can just easily include its path from the cmd. But with MySql, this my 1st time to use that's why I'm littly bit crawling... :-)

Thank you & please advise!
0
Stiebel Eltron
Asked:
Stiebel Eltron
  • 10
  • 10
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
You don't set the location of MySQL, you connect to the MySQL server which takes care of all of that.  Do you have MySQL already installed somewhere or are you connecting to a server on your hosting account?
0
 
Stiebel EltronAuthor Commented:
Hi DaveBaldwin! Nice to see your reply again!
I'm testing it on a Windows 7 system. I already installed it + the MySql connector. But the problem is, I don't know how to connect the MySql & its db.

Is there a way that I can create a new db, something like phpMyAdmin (if you use PHP)?
0
 
Dave BaldwinFixer of ProblemsCommented:
You can add PHP to IIS7 and use phpmyadmin or you can download MySQL Workbench.  http://wb.mysql.com/  You have to create the database with the tables and the user with the appropriate permissions before you can connect to the MySQL server and database.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Stiebel EltronAuthor Commented:
So MySQL Workbench is something like phpMyAdmin that you can create mysql db, am I right?
Can I use the mysql db that has been created already from another machine? Or do I need to create a new one from my machine?
0
 
Dave BaldwinFixer of ProblemsCommented:
You can export the database from the other machine and import it into this one.  You will need to recreate the users and permissions.
0
 
Stiebel EltronAuthor Commented:
I'm currently downloading the MySQL Wokbench 5.2, is it the same as the phpMyAdmin?
0
 
Dave BaldwinFixer of ProblemsCommented:
It's not the same, it probably does more.  But all the basics are the same, you just have to look for them in different places.  It should have a help file with it.
0
 
Stiebel EltronAuthor Commented:
I followed the sample script on this article: http://www.devarticles.com/c/a/ASP/Using-MyODBC-To-Access-Your-MySQL-Database-Via-ASP/1/

I can create database, establish connection of my mysql db & ASP, then I tried to create another db, which is for my use (for testing purpose only), but I didn't got any Query OK result, unlike from the sample of that article. Here's my script:
create database steproducts;

use steproducts;

create table Members

(

UserId int(11) auto_increment not null,

Name varchar(50),

Surname varchar(50),

Username varchar(15),

Password varchar(15),

Email varchar(50),

Telephone varchar(15),

Mobile varchar(15),

Company varchar(50),

Address varchar(50),

GroupID int(10)

primary key(UserId),

unique id(UserId)

);

Please advise where did I get wrong & why if I test the connection from the ODBC data Source Administrator, I can see the database that I created & when I test its connection, it success...

But when I test my ASP page, I got 500 Internal server error result.
I attached my ASP Script (for the result page)...

Please advise...
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%  Option Explicit             %>
<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rsMembers

myDb = "steproducts"

'connectString = "Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=" & myDb & ";UID=root;PWD=Ste2010Aeg"

Set myConnection = Server.CreateObject("ADODB.Connection")
'myConnection.Open connectString
myConnection.Open "DSN=steproducts"

sqlContent = "INSERT INTO Members(Name, Surname, Username, Password, Email, Telephone, Mobile, Company, Address, GroupId) "  
sqlContent = sqlContent & "VALUES('" & REPLACE(request("fname"), "'", "''") & "', '" & REPLACE(request("lname"), "'", "''") & "', '"  
sqlContent = sqlContent & request("username") & ", '" & REPLACE(request("password"), "'", "''") & "', '"  
sqlContent = sqlContent & REPLACE(request("email"), "'", "''") & "', '" & REPLACE(request("telephone"), "'", "''") & "', '"  
sqlContent = sqlContent & REPLACE(request("mobile"), "'", "''") & "', '" & REPLACE(request("company"), "'", "''") & "', '"  
sqlContent = sqlContent & REPLACE(request("address"), "'", "''") & "', 3) "

Set rsMembers = Server.CreateObject("ADODB.Recordset") 
Set rsMembers = myConnection.Execute(sqlContent)
' there's no need to close the rs because it closed itself after performing an operation instead 
' of filling it with a value (a recordset)
myConnection.close
Set rsMembers = Nothing
Set myConnection = Nothing
%>
<h1 align="center">Thank You</h1>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
Why aren't you using the 'connectstring' which is known to work?  I have not had any success using a DSN from the ODBC data Source Administrator.  For the sake of clarity, you should put all those REPLACE statements on their own lines before the 'sqlcontent' statment.

Did you also grant permissions on that database to your user?  It is considered bad form to use 'root' as your user because 'root' is too powerful.  You should be using a user with privileges only on that database.  'root' is normally set up to only be used from 'localhost', not remotely.
0
 
Stiebel EltronAuthor Commented:
Hi there DaveBaldwin! Sorry for the late reply.

Do u know how to use MySql Workbench?
I tried to create a db on the workbench named steproducts, table: Members
And here's the error result I'm receiving:
Hi there MuffyBunny!

I'm testing it locally and here's the error that I got (latest):
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 5.1 Driver][mysqld-5.1.40-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OliverDan', 'oliver@stiebeleltronasia.com', '123456', '123456', 'Stiebel', 'Eltr' at line 1

/TestLOG/signup_result.asp, line 25
------------------------------------------------------
I'm attaching my script (signup_result.asp)
and the line 25 is the: Set rsMembers = myConnection.Execute(sqlContent)

Hope to hear from you again soon...
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%  Option Explicit             %>
<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rsMembers

myDb = "steproducts"

connectString = "Driver={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=" & myDb & ";UID=admin;PWD=stethadmin;"

Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

sqlContent = "INSERT INTO Members(Name, Surname, Username, Password, Email, Telephone, Mobile, Company, Address, GroupId) "  
sqlContent = sqlContent & "VALUES('" & REPLACE(request("fname"), "'", "''") & "', '" & REPLACE(request("lname"), "'", "''") & "', '"  
sqlContent = sqlContent & request("username") & ", '" & REPLACE(request("password"), "'", "''") & "', '"  
sqlContent = sqlContent & REPLACE(request("email"), "'", "''") & "', '" & REPLACE(request("telephone"), "'", "''") & "', '"  
sqlContent = sqlContent & REPLACE(request("mobile"), "'", "''") & "', '" & REPLACE(request("company"), "'", "''") & "', '"  
sqlContent = sqlContent & REPLACE(request("address"), "'", "''") & "', 3) "

Set rsMembers = Server.CreateObject("ADODB.Recordset") 
Set rsMembers = myConnection.Execute(sqlContent)
' there's no need to close the rs because it closed itself after performing an operation instead 
' of filling it with a value (a recordset)
myConnection.close
Set rsMembers = Nothing
Set myConnection = Nothing
%>
<h1 align="center">Thank You</h1>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
First, do you have the MySQL dump from the other database?  Second have you downloaded and installed the "MySQL ODBC 5.1 Driver"?
0
 
Stiebel EltronAuthor Commented:
Yes, I have downloaded that version, but changed back to version 3.51.
However, I'm attaching the script of my another test, just showing some data from the database, it's working. But when I tried to change the SQL statement into INSERT INTO statement, I failed.
I would like to insert or get data from another page. So I can apply it to my signup_form page & result page...
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%  Option Explicit             %>

<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rssomeTable

myDb = "steproducts"

connectString = "Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=" & myDb & ";UID=admin;PWD=stethadmin;"

Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

'sqlContent = "INSERT INTO sometable (intWhateverId, vchWhatever) VALUES (11111, 'DanArcillo')";

sqlContent = "Select * From someTable"

Set rssomeTable = Server.CreateObject("ADODB.Recordset") 
Set rssomeTable = myConnection.Execute(sqlContent)
	' there's no need to close the rs because it closed itself after performing an operation instead 
	' of filling it with a value (a recordset)

%>
<META HTTP-EQUIV="REFRESH" CONTENT="360;URL=http://www.cssasia.info/ipix/login/page1.asp">
<h1 align="center">Thank You</h1>

<% do while not rssomeTable.EOF %>
							<font face="verdana" size=2>
								<b><%=rssomeTable("intWhateverId")%></b>
							</font>
                            <font face="verdana" size=2>
								<b><%=rssomeTable("vchWhatever")%></b>
							</font>
                            <br />
					
		<% rssomeTable.MoveNext %>
		<%loop %>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
I thought you had all this running before?  I don't see anything wrong with your INSERT statement.  Assuming you replaced the nonsense info with the correct data.  If you have MySQL Workbench running, you should be able to try your SQL statements there.
0
 
Stiebel EltronAuthor Commented:
When I change it to INSERT INTO statement, I'm receiving this error:
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/TestLOG/page2.asp, line 20

sqlContent = "INSERT INTO sometable (intWhateverId, vchWhatever) VALUES({intWhateverId: INT},{vchWhatever: VARCHAR})";
-----------------------------------------------------------
Line 20 is the: sqlContent = "INSERT INTO sometable (intWhateverId, vchWhatever) VALUES({intWhateverId: INT},{vchWhatever: VARCHAR})";
0
 
Dave BaldwinFixer of ProblemsCommented:
I think it doesn't like the ';' at the end of the line.  ';' would be used by PHP to designate the end-of-line but not in VBscript.
0
 
Stiebel EltronAuthor Commented:
I've tried this script:
sqlContent ="INSERT INTO someTable(intWhateverId,vchWhatever) VALUES ('"&Request.Form("intWhateverId")&"','"&Request.Form("vchWhatever")&"')"

It worked.
Then I try to apply it to my form, here's the script:
sqlContent = "INSERT INTO Memberss(UserID, Name, Surname, Username, Password, Email, Telephone, Mobile, Company, Address, GroupId)
VALUES ('"&Request.Form("fname")&"','"&Request.Form("lname")&"','"&Request.Form("username")&"','"&Request.Form("password")&"','"&Request.Form("email")&"','"&Request.Form("telephone")&"','"&Request.Form("mobile")&"','"&Request.Form("company")&"','"&Request.Form("address")&"')"

I received this error result:
Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/TestLOG/signup_result.asp, line 19

sqlContent = "INSERT INTO Memberss(UserID, Name, Surname, Username, Password, Email, Telephone, Mobile, Company, Address, GroupId)
--------------------------------------------------------------------------------
I was thinking is it because of the UserID & GroupID that wasn't present in the form that the user can see...
Because UserID & GroupID should be hidden & UserID should generate automatically_increment.

Kindly advise...
0
 
Dave BaldwinFixer of ProblemsCommented:
You would get a MySQL error for the UserID problem but "Microsoft VBScript compilation error '800a0409' " is an ASP error before the query gets used.

Try this and see what you get.
sqlContent = "SELECT UserID, Name, Surname, Username, Password, Email, Telephone, Mobile, Company, Address, GroupId FROM Memberss LIMIT 10"

Open in new window

0
 
Stiebel EltronAuthor Commented:
I tried the attached script, and it's working.

BTW, I created a new table named: GrupoA, because after I deleted the table "Memberss", I can't create a new table with the same name in MySql Workbench, don't know why...
sqlContent = "INSERT INTO GrupoA (Name,Surname,Username,Password,Email,Telephone,Mobile,Company,Address) VALUES ('"&Request.Form("Name")&"','"&Request.Form("Surname")&"','"&Request.Form("Username")&"','"&Request.Form("Password")&"','"&Request.Form("Email")&"','"&Request.Form("Telephone")&"','"&Request.Form("Mobile")&"','"&Request.Form("Company")&"','"&Request.Form("Address")&"')"

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
Good, glad you got it working.
0
 
Stiebel EltronAuthor Commented:
Thanks! And will be going to my next upcoming thread! Hope you're still there to support :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now