Couple questions migrating to MySQL
-
hello QUESTION 1: I scouted around a little bit, seems like in MySQL there's no such thing as NTEXT, only TEXT? Am I right? http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html QUETSION 2: In MySQL you have DATETIME and TIMESTAMP, with TIMESTAMP you can have a table column to default to "current_timestamp" (but for DATETIME, in MySQL, it can only DEFAULT to NULL) I hesistated to declare column as TIMESTAMP because my nhibernate file uses NHibernate type DateTime: ... QUESTION 3: I am using VARBINARY in MySQL create table xxx { ... blob varbinary(MAX), -- you don't have MAX as in M$SQL, what should I use in MySQL? ... ); QUESTION 4. Reading blob - haven't got around to test this feature yet, big util lib ... is this right? ... if (!Convert.IsDBNull(oRdr["blobValue"])) { byte[] blobValue = new Byte[(oRdr.GetBytes(10, 0, null, 0, int.MaxValue))]; oRdr.GetBytes(10, 0, blobValue, 0, blobValue.Length); oValue.BlobValue = blobValue; } ... QUESTION 5. Does this corresponds to System.Data.DbType.Binary? I need to make this work for M$SQL/MySQL/Oracle. I understand there's "SqlDbType.VarBinary" but that's M$SQL specific and this is not available for IDbParameter (Db vendor neutral version of db param class in .net) QUESTION 6: Is it true that you can only declare variable within stored proc or UDF in MySQL? After scouting around for a long time it seems to be you can only declare variables from within storedproc or function!! http://dev.mysql.com/doc/refman/5.0/en/if-statement.html QUESTION 7. Is there an equivalent of TSQL PRINT statement (or dbms_output.put_line in case of oracle) in MySQL? Thanks dev
modified on Wednesday, March 25, 2009 10:43 PM
-
hello QUESTION 1: I scouted around a little bit, seems like in MySQL there's no such thing as NTEXT, only TEXT? Am I right? http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html QUETSION 2: In MySQL you have DATETIME and TIMESTAMP, with TIMESTAMP you can have a table column to default to "current_timestamp" (but for DATETIME, in MySQL, it can only DEFAULT to NULL) I hesistated to declare column as TIMESTAMP because my nhibernate file uses NHibernate type DateTime: ... QUESTION 3: I am using VARBINARY in MySQL create table xxx { ... blob varbinary(MAX), -- you don't have MAX as in M$SQL, what should I use in MySQL? ... ); QUESTION 4. Reading blob - haven't got around to test this feature yet, big util lib ... is this right? ... if (!Convert.IsDBNull(oRdr["blobValue"])) { byte[] blobValue = new Byte[(oRdr.GetBytes(10, 0, null, 0, int.MaxValue))]; oRdr.GetBytes(10, 0, blobValue, 0, blobValue.Length); oValue.BlobValue = blobValue; } ... QUESTION 5. Does this corresponds to System.Data.DbType.Binary? I need to make this work for M$SQL/MySQL/Oracle. I understand there's "SqlDbType.VarBinary" but that's M$SQL specific and this is not available for IDbParameter (Db vendor neutral version of db param class in .net) QUESTION 6: Is it true that you can only declare variable within stored proc or UDF in MySQL? After scouting around for a long time it seems to be you can only declare variables from within storedproc or function!! http://dev.mysql.com/doc/refman/5.0/en/if-statement.html QUESTION 7. Is there an equivalent of TSQL PRINT statement (or dbms_output.put_line in case of oracle) in MySQL? Thanks dev
modified on Wednesday, March 25, 2009 10:43 PM
ANS 1: Thats correct. ANS 2: You are right. One can not use CURRENT_TIMESTAMP() function as a defult value for DATE and DATETIME datatypes in MySQL. However, this function can be used in TIMESTAMP datatype. Additionally, one can also use NOW() function as a defult value for TIMESTAMP datatype. ANS 3: MAX can not be used for VARBINARY. The allowable maximum length is the same for VARBINARY as it is VARCHAR, except that the length for VARBINARY is a length in bytes rather than in characters. ANS 4,5: Thats the way! ANS 6: Variables can be declared and accessed outside Procedure and Functions as follows: --------------------------------------- mysql> SET @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.01 sec) --------------------------------------- ANS 7: SELECT statements can used directly in Procedures/Functions in MySQL for viewing desired output as follows: --------------------------------------- mysql> DELIMITER // mysql> CREATE PROCEDURE print_test() -> BEGIN -> SELECT 'This is test printing' AS "Output"; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call print_test(); +-----------------------+ | Output | +-----------------------+ | This is test printing | +-----------------------+ 1 row in set (0.00 sec) ---------------------------------------
-
ANS 1: Thats correct. ANS 2: You are right. One can not use CURRENT_TIMESTAMP() function as a defult value for DATE and DATETIME datatypes in MySQL. However, this function can be used in TIMESTAMP datatype. Additionally, one can also use NOW() function as a defult value for TIMESTAMP datatype. ANS 3: MAX can not be used for VARBINARY. The allowable maximum length is the same for VARBINARY as it is VARCHAR, except that the length for VARBINARY is a length in bytes rather than in characters. ANS 4,5: Thats the way! ANS 6: Variables can be declared and accessed outside Procedure and Functions as follows: --------------------------------------- mysql> SET @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.01 sec) --------------------------------------- ANS 7: SELECT statements can used directly in Procedures/Functions in MySQL for viewing desired output as follows: --------------------------------------- mysql> DELIMITER // mysql> CREATE PROCEDURE print_test() -> BEGIN -> SELECT 'This is test printing' AS "Output"; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call print_test(); +-----------------------+ | Output | +-----------------------+ | This is test printing | +-----------------------+ 1 row in set (0.00 sec) ---------------------------------------