How can you use MySql?

singletp wrote:

Hi Paul,

This is what I am doing for this issue:

Create a table name temptable, then modify the SP "Nop_splitstring_to_table" to insert the table into this table. Off course dropping all old data before inserting new data into this temptable.

then modify Nop_producloadAllPaged:

CALL NOP_splitstring_to_table(v_FilteredSpecs,',');
   INSERT INTO tt_FilteredSpecs(SpecificationAttributeOptionID)
   SELECT cast(data as SIGNED INTEGER) FROM NOP_temptable;

I am still modifying the SP script, so I haven't known it is working.
By the way, do you change all the input parameter in SP, by adding "IN" infront of every input parameter? I gota do it else i will get an error like "expect parameter, 2 but got 1".

Nice to share with you.

Hi Ken,

Thanks for your help, I will try what you have shown me.
Regarding the SP Conversions, I used SQLWays (DEMO) to do half the job, this demo converts majority successfully, but does not recognise certain variables such as nvarchar(MAX). Also, the demo version of SQLWays cuts off letters on all variables, so I had to compare all SP in MSSQL then copy the correct values to my MYSQL variables, this took a while but atleast its done now.

Also, I noticed in your first post on this topic you mentioned XML data type conversion, is this for the NOP_LanguagePackExport/NOP_LanguagePackImport? These I have not been able to convert.

Regarding your question: do you change all the type- Database and SqlDatabase to MySqlDatabase in all over the place?

Well....... I learnt the hard way !! First time I tried to convert nopCommerce 1.40 I did change SqlDatabase to MySqlDatabase all over the place, but it did not work for me even after changing it all, I havnt used EntLibContrib in the latest conversion I am doing for nopCommerce 1.50 I spent a month trying to get it to work with this then gave up and tried a different tactic, I am using the bin DIR from MySqlDemo on Codeproject which I put in the dependencies folder then referenced to it in DataAccess.SqlServer, I then changed NopDataHelper.cs to:

        public static Database CreateConnection(string ConnectionString)
        {
            Database db = DatabaseFactory.CreateDatabase("NopSqlConnection");
            return db;
        }

The NopSqlConnection is what I have put in the connectionstrings.config:

<add name="NopSqlConnection" connectionString="Data Source=localhost;Database=nopcommdb;Uid=root;Pwd=nopcommerce;" providerName="MySql.Data.MySqlClient"/>

Before you go ahead and try this I must state that I worry that this may not work later on because the Microsoft.Practices.EnterpriseLibrary.Data.dll is only version 2 but includes the MySql Data Objects whereas Microsoft.Practices.EnterpriseLibrary.Data.dll Version 4 does not have data objects for MySql which is now conflicting with Ajax aswell :( If u have it working with EntLibContrib then I would continue using this.

I myself still have a fair few errors need ironing out, and would like to keep in contact with you throughout, as Im sure we can help each other out.

Thanks again.

Paul.

Thanks for your quick response. I am using EntLibContrib  4.1 in this project. Same as you, I use SQLWays(demo) to convert all the SP, one by one, and this is killing me.

Currently I am facing one problem, which is passing parameter into stored procedure.

for e.g:
Database db = CreateConnection(settings.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_TopicLocalizedLoadByNameAndLanguageID2");
            db.AddInParameter(dbCommand, "v_Name", DbType.String, name);
            db.AddInParameter(dbCommand, "v_LanguageID", DbType.Int32, lanID);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    gv1.DataSource = dataReader;
                    gv1.DataBind();
                }
            }

When I load the page, I get the exception:
Incorrect number of arguments for PROCEDURE nopcommdb.Nop_TopicLocalizedLoadByNameAndLanguageID2; expected 2, got 1

No matter what method I try, same error. Do you think it is because the dbcommand?

Glad to have discussion with you here, it is definitely a very helpful partner to each other.

I think I found something. When DbType is string, but the SP is expected a varchar(200), error happen.
Paul, how do you handle this?

When Nop_TopicLocalizedLoadByNameAndLanguageID is converted, it has converted v_name to TEXT:

DELIMITER $$

DROP PROCEDURE IF EXISTS `Nop_TopicLocalizedLoadByNameAndLanguageID` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Nop_TopicLocalizedLoadByNameAndLanguageID`(v_Name TEXT,
  v_LanguageID INT)
BEGIN
SELECT
   tl.* FROM Nop_TopicLocalized tl
   INNER JOIN Nop_Topic t
   ON tl.TopicID = t.TopicID
   WHERE tl.LanguageID = v_LanguageID and t.Name = v_Name;
END $$

DELIMITER ;

Is this how yours looks? I dont think I am able to advise on this just yet, as I havnt managed to test the Forum Section, the blog works okay, the front page with new products works, my login doesnt still got problems with this.

Any chance you can take me through the steps you took to get EntLibContrib to work please, I dont think I should move any further until I have got mine working with this thanks.

Ken,

Can you please also show me your NOP_splitstring_to_table as I cannot get this to work still thanks.

Paul.

Also, I have just tried to load the topic page on its own but it reverts back to the home page, and my home page does not have any link to the forum, but this is prob because of the settings that are in the admin section which I also cannot get to because my login does not work.

Hi Paul,

The EntLibContrib is working ok. I can't tell you how's it working on nopcommerce, because I still have the problem on the datatype conversion. However I create a testing application, which is as below:

using MySql.Data.MySqlClient;
using MySql.Data.Types;
using EntLibContrib.Data;
using EntLibContrib.Data.MySql;
using EntLibContrib.Data.MySql.Properties;
using EntLibContrib.Data.DB2;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

public Database CreateConnection(string ConnectionString)
        {
            MySqlDatabase db = new MySqlDatabase(ConnectionString);
            return db;
        }

        public void GetData(int lanID, string name)
        {
            Database db = CreateConnection(settings.ConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_TopicLocalizedLoadByNameAndLanguageID2");
            db.AddInParameter(dbCommand, "v_Name", DbType.String, name);
            db.AddInParameter(dbCommand, "v_LanguageID", DbType.Int32, lanID);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            gv1.DataSource = ds;
            gv1.DataBind();
       }

I am able the bind the gridview with data from mysql server. All you need is just change the createConnection function. So I believe in nopcommerce, you do not need to change all the SQL calling function.

I think I need to spend sometime on the datatype modification today.

Thanks!

Ken

Hi Paul,

Below is my NOP_splitstring_to_table.


DELIMITER //
CREATE PROCEDURE NOP_splitstring_to_table(IN v_string NATIONAL VARCHAR(1000),
IN v_delimiter CHAR(1))


BEGIN
   DECLARE v_start INT;
   DECLARE v_end INT;
   DELETE FROM NOP_temptable;
   SET v_start = 1;
   SET v_end = LOCATE(v_delimiter,v_string);

   WHILE v_start < LENGTH(v_string)+1 DO
      IF v_end = 0 then
         SET v_end = LENGTH(v_string)+1;
      end if;
      INSERT INTO NOP_temptable(data)
VALUES(SUBSTRING(v_string, v_start, v_end -v_start));

      SET v_start = v_end+1;
      SET v_end = LOCATE(v_delimiter,v_string,v_start);
   END WHILE;

END;
//