Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

MySQL equivalent

I am doing a port from SQL Server over to MySQL.

What would be the MySQL syntax for this?

if not exists(select 1 from sys.columns where object_id = object_id('ImportedData') and name = 'Leadid') alter table ImportedData add Leadid nvarchar(200)



Thank you,


Tom
0
Tom Knowlton
Asked:
Tom Knowlton
  • 7
  • 4
2 Solutions
 
k_murli_krishnaCommented:
if not exists(select 1 from information_schema.columns where table_name = 'ImportedData' and column_name = 'Leadid')
alter online table ImportedData add column  Leadid  varchar(200);

there is no nvarchar datatype in mysql. You have convert it to varchar.

MSSQL: nvarchar(100)
translates to
MySQL: varchar(100) character set UTF8

works as well for the text / ntext - in mysql unicode is switched on by the 'character set UTF8' option.
0
 
Tom KnowltonWeb developerAuthor Commented:
if not exists(select 1 from information_schema.columns where table_name = 'ImportedData' and column_name = 'Leadid')
alter online table ImportedData add column  Leadid  varchar(200)

gives this error:

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 'if not exists(select 1 from information_schema.columns where table_name = 'Impor' at line 1
0
 
Tom KnowltonWeb developerAuthor Commented:
If I do JUST this part:

select 1 from information_schema.columns where table_name = 'ImportedData' and column_name = 'Leadid'

returns a   1
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how/where do you run that code?
in MySQL, you don't have the feature of a "anonymeous" code block like in t-sql of mssql server.
you need to put all of that inside a stored procedure ...
0
 
Tom KnowltonWeb developerAuthor Commented:
I am running the code from "MySQLQueryBrowser" which is a utility off of sun's website.
0
 
Tom KnowltonWeb developerAuthor Commented:
Eventually I want to be running this from a C# Windows Application, where I build the sql dynamically:

     foreach (string h in newtokens)
                {
                    if ((h != "") && (h.Trim().Length > 0) && (h.Trim().Length < 51))
                    {
                        string tempH = GrabOnlyLettersAndNumbersFromString(h);

                        DataSet ds = DataHelper.ReturnDataSet("Select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'importeddata'");
                        if (ds.Tables[0].Rows.Count < 1)
                        {
                            string tempsql = "alter table ImportedData add " + tempH + " nvarchar(200)";
                            DataHelper.RunSQLStatement(tempsql);
                        }
                    }
                }

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume that will indeed work better ...
0
 
Tom KnowltonWeb developerAuthor Commented:
It will...


Now a new problem.  ALTER TABLE is taking an extremely long time.

It is a large table.  over 9 millions rows x 20 columns
0
 
Tom KnowltonWeb developerAuthor Commented:
{"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."}

Is the error I get
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you "just" need to change the CommandTimeout from the default (30 => seconds) to 0 (to wait "forever")
however, I don't know how/where to do that with the "helper" classes.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: do not confuse the CommandTimeout with the ConnectionTimeout. the second one is the time allowed to connect, the first one to execute a command (aka run a query and return the results)
0
 
Tom KnowltonWeb developerAuthor Commented:
setting

"default command timeout"

in the connection string fixed the problem.

BUT MAN IS IT SLOWWWWWWWWWWWW
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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