error in SQL syntax

[jnash]

Dim TheConnection As New [COLOR=red]MySqlConnection[/COLOR]("server=localhost;" _
         & "user id=root;" _
         & "password=password;" _
         & "database=supervid")
 
 
 Dim cmd As New MySqlCommand
 
        Try
            TheConnection.Open()
 
            cmd.Connection = TheConnection
            cmd.CommandText = "backup database supervid to disk='c:\temp\testdb_1.bak'"
            cmd.ExecuteNonQuery()
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            TheConnection.Dispose()
            TheConnection = Nothing
            cmd = Nothing
        End Try
    End Sub

I took this example from one of the posts, im getting
"error in your SQL syntax, check your manual to see if its the correct version to use near backup database supervid to disk='c:\temp\testdb_1.

thanks in advance

Jon

 

[cjard]

PM a Moderator and ask them to move thius topic to whichever Database section of this forum relates to your database. THis is NOT a Data Access query, nor is it a VB.NET related issue.. Posting thread sin the msot fitting forums gets you answeres the quickest..

Given that I dont recognise the command, i would say it is neither oracle nor access.. SQLS? Surely there ms8ut be hundreds of examples of how to backup a SQLS database on the net, also one of the members here, pachjo has recently done lots on SQLS backups - see if he has anything to say?!

 

[jnash]

i thought this is how you do it for mysql!

 

[cjard]

i googled for "backup database" "to disk" and only found sqlserver results
I googled for mysql backup "via sql" and found nothing of substance

I dont think this code is good for MySQL. Where did you find it?

 

[HeavenCore]

hi, i have been working with mysql for several years with php and have never come across this command.

I'm guessing you are trying to develop a backup system for the database?

there are several easy ways of doing this depending on your host and access.

If you have shell access just run:

mysqldump --tab=/path/to/some/dir --opt db_name

Or if you only have ftp access (server farm etc) just install phpmyadmin which has a VERY good SQL Export Dump facility.

there is also a nice web interface solution http://www.hotscripts.com/Detailed/22329.html more lightweight than phpmyadmin and can be easily integrated into your web based applications.

EDIT i notice the link i just posted is no longer active, this one however is http://www.dwalker.co.uk/phpmysqlautobackup/ makes backups and emails them to you

 

[jnash]

thanks for your searching cjard, and HeavenCore i would like to backup from a touch of a button in my application in vb (winforms) and back it up to a file somewhere, the database im useing is mysql. and its local

 

[HeavenCore]

if its local then yes, in vb.net for example use Shell() to execute the mysqldump --tab=/path/to/some/dir --opt db_name

for example:

Shell "mysqldump –-user admin –-password=password mydatabase > sql.dump", vbNormalFocus

on a button_click should do the trick, ovisouly change that with your password, username and database name, for more information Google Shell() and ShellExecute() and mysqldump

 

[cjard]

noting of course, that Shell is old vb. the modern equivalent:


System.Diagnostics.Process.Start("mysqldump.exe", "–-user admin –-password=password mydatabase > sql.dump")


i think!

 

[jnash]

im getting the error

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump.exe --user root --password=password supervid > sql.dump

mysqldump: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

 

[cjard]

Get the command working from DOS first, read the mysqldump help if needs be

 

[jnash]

thats what im trying, im supplying the right user / pass / db that i use with SQLYOG cant see why its producing this error!

 

[cjard]

what's the exact command that youre using? (so long as youre not including real-world IP addresses in your post, we dont really care about what your username and password is.. i.e. just post them; we cant use them for any real world, devious purposes)

e.g. our main oracle server is called oracle1 and has an ip address of 10.0.0.1, the dba username/password is mir/mirdata.. woo, big security risk.

 

[jnash]

it now works when i type this in command prompt

mysqldump.exe --user root --password=password supervid > sql.dump

but not when i do it from vb using

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackup.Click

        System.Diagnostics.Process.Start("C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe", "--user root --password=password supervid > sql.dump")
    End Sub

 

[cjard]

ehm... that could be because the > character is specially interpreted by DOS to mean "capture all output from this command and write it to file"

try it now by typing this at the command:

DIR >c:\dirlist.txt

now look in C: for the dirlist.txt file

If you run the mysqldmp exe without that final > sql.dump i think it will fill the dos screen with garbage - i.e. its sending to screen rather than file.

Look for an option that writes a file. If there isnt one, then you'll have to start the process without the > sql.dump part of the arguments, and capture the stream yourself from the Process.StandardOutput() method that returns a streamreader linked to the STD_OUT of the process (i.e. what DOS captures with > sql.dump )