dashCommerce

After all the trials and tribulations i went through. There are two main things that I did. Number one is that I used the method for turning off the full text search feature. There are about 3 steps to do this which are outlined in another post here: see the post by "leftend" http://dashcommerce.org/forums/p/588/2342.aspx#2342

The second thing I did was not rely on the PayPal sandbox for testing purchases. I wasted many hours using the test API credentials and test buyer accounts and the test credit cards. Finally, I said to myself "I am going to just try the live credentials and my real credit card" and lo and behold I was able to make a purchase, both from localhost and from GoDaddy.

At various points along the way I spent time dealing with .Net errors when navigating to my site regarding subsonic (I would get an error while the web.config file was loading) for example. What fixed this was just making sure I had a fresh install, perhaps installing into a different directory or updating the version I was installing. i started this project on 3.0 RC1 then installed 3.0 Web Ready when it came out.

And that's pretty much it. There are a few settings in GoDaddy to make sure you have set, such as marking the install directory as an application root. GoDaddy only has like 3 IIS related settings you can specify.

Hope this helps, I'll try and help with any other questions you might have, but I don't think that I'm too far ahead of you.

Phillip

I have taken a somewhat different approach than Phil. GoDaddy advertises a full text indexed sql database, so I insisted that they provide one. Their front end tech support reps didn't have a clue what I was talking about, so after several requests I was referred to their executive offices who made an inquiry to their "developer" team.

As I post this, I haven't had the time to see if their solution works yet, but their solution to the full text catalog problem was to add additional commands into the query that defines the full text index when building the stored procedures manually as I had attempted to do. Perhaps someone of leftend or phatPhil's ability can integrate this solution into the scripts, and get the entire setup app to work on GoDaddy.

The following are snips from my correspondence to GoDaddy support:

[This was my initial inquiry to GoDaddy support]

I am attempting to build stored procedures in both of my sql 2005
databases that require full text search to be enabled.

When I attempt to execute the query to build the stored procedure, the
following error message is returned.

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view
'dashCommerce_Store_Product' because it is not full-text indexed.

The following is the sql query that I am attempting to execute to build
the stored procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[dashCommerce_Store_ProductSearch]') AND type in
(N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- ================================================================
-- Licensed Under the dashCommerce License
-- http://dashcommerce.org/dashCommerce/license.aspx
-- Copyright (c) 2007 - 2008 Mettle Systems LLC, P.O. Box 181192
-- Cleveland Heights, Ohio 44118, United States
-- ================================================================

CREATE PROCEDURE [dbo].[dashCommerce_Store_ProductSearch]
@searchTerm nvarchar(100)
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM dashCommerce_Store_Product
WHERE
IsEnabled = 1 AND StatusId <> 99 AND (
FREETEXT([Name], @searchTerm)
OR
FREETEXT([ShortDescription], @searchTerm)
OR
FREETEXT([BaseSku], @searchTerm))

END

'
END
GO
==========================

Please advise how to provision the sql 2005 databases to implement full
text indexing.

Thanks, Ron

[I'm going to omit the useless response from their front end support team, and my requests to be referred to someone who actually knows what they are doing, and paste in the final solution from the GoDaddy developer team.]

This office recently learned of your concerns regarding our shared
hosting environment and the support for "full text indexing" as it
relates to MSSQL databases.

We apologize for all the confusion regarding this issue. We have
investigated the matter with our developers and would like to relay the
helpful information we were given. Specifically, we would like to point
out that although we do not allow a user's creation of full text
catalogs, our system will automatically create one when the database is
established. Ultimately, this should allow you to accomplish what you
need to do. We have copied the full instructions we received which
should help you better understand how to avoid the errors you've been
getting. Simply put, you need to define a FULLTEXT index in a table
before you can run queries using FULLTEXT.

Steps followed to reproduce and resolve error:
-----BEGIN-----
1. Create a new database. The system creates the Full Text
Catalog.

2. Define a table called “test” that includes two columns:
key (bigint, primary key) and vc1 (varchar(100)).

3. Execute the following in Query Analyzer (this is a simplified
version of the SQL submitted in the ticket).

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[ft_proc]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[ft_proc]

@searchTerm nvarchar(100)

AS

BEGIN

SET NOCOUNT ON



SELECT *

FROM [test]

WHERE

FREETEXT([vc1], @searchTerm)

END

'

END

GO

4. Since a FULLTEXT index has not been defined, the following
error is received: “Cannot use a CONTAINS or FREETEXT predicate on
table or indexed view 'dashCommerce_Store_Product' because it is not
full-text indexed”. This is expected. Note, this is the same error
you received.

5. Define a FULLTEXT index on the table using the following SQL in
Query Analyzer:



CREATE FULLTEXT INDEX ON test(vc1)

KEY INDEX test_PrimaryKey



6. Re-execute the SQL code from Step #3. This time it succeeds
since a FULLTEXT index is defined.

To resolve this issue, define a FULLTEXT index for the table.
Directions for doing so can be found on MSDN at
http://msdn.microsoft.com/en-us/library/ms187317(SQL.100).aspx. Our
database is meeting the published feature set in terms of support for
Full Text Searches and Indexes. You were not performing a required step
and, therefore, running into errors.
-----END-----

Please know that our goal is always to cater to the success of our
customers, and we apologize that this specific information was not
provided earlier. We anticipate that it will resolve your issue, but if
you have any remaining questions or concerns, please let us know.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

....anyway, that's how far I have gotten on this issue with GoDaddy. Since they did come through with a solution, I'll give them a "B" on their customer support at this time. Since they don't have a true ASP shopping cart, it seems that it would be in their best interest to assist us in getting this app to work on their servers. If anyone has the time to tinker with this solution (I don't) please let us know if it works. If anyone experiences other snags, please post it here, and I will forward the info back to the GoDaddy contact that I have been working with to see if they will help us along.

I hope anyone will be able to help out there.. I'm not very good with SQL. I have been trying to implement the work - around suggested here however if I try to run the following statement

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dashCommerce_Store_ProductSearch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[dashCommerce_Store_ProductSearch]
  @searchTerm nvarchar(100)
 
  -- searchTerm:  String of 1 or more search terms, all separated by spaces
  -- Results are returned in order of relevance
AS
  BEGIN
    DECLARE @i1 int;
    DECLARE @i2 int;
   
    -- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
    DECLARE @MatchType int;
    SET @MatchType = 0
    
    DECLARE @Word varchar(100);
    DECLARE @Words table (Word varchar(100) not null);
    DECLARE @WordCount as integer;

    SET NOCOUNT ON

    -- Parse the searchTerm to extract all words:
    IF (@MatchType != 2)
      BEGIN
      SET @searchTerm = ' ' + @searchTerm  + ' ';
      SET @i1 = 1;
      WHILE (@i1 != 0)
        BEGIN
        SET @i2=charindex(' ', @searchTerm, @i1+1)
        IF (@i2 != 0)
          BEGIN
          SET @Word = RTRIM(LTRIM(SUBSTRING(@searchTerm, @i1+1, @i2-@i1)))
          IF @Word != '''' INSERT INTO @Words SELECT @Word
          END
        SET @i1 = @i2
        END
      END
    else
      INSERT INTO @Words SELECT LTRIM(RTRIM(@searchTerm))

    -- Get the total # of words:
    SET @WordCount = (SELECT COUNT(*) FROM @Words)

    -- Return Results in order of relevance:
    SELECT
       T.*, a.MatchPct
    FROM
      dashCommerce_Store_Product T
    INNER JOIN
      (
      SELECT
        ProductID, COUNT(*)  * 1.0 / @WordCount AS MatchPct
      FROM
        dashCommerce_Store_Product T
      INNER JOIN
        @Words W ON (
            ' ' + T.[Name] + ' ' LIKE '%[^a-z]' + Word + '[^a-z]%'
            OR
            ' ' + T.[ShortDescription] + ' ' LIKE '%[^a-z]' + Word + '[^a-z]%'
            OR
            ' ' + T.[BaseSku] + ' ' LIKE '%[^a-z]' + Word + '[^a-z]%'
            )
      GROUP BY
        ProductID
      ) a ON T.ProductID = a.ProductID
    WHERE
      MatchPct = 1 OR @MatchType <>1
    ORDER BY
      MatchPct   
  END

'
END
GO

I get the Error

Incorrect syntax near ' + @searchTerm + '.

Any help out there.. ?