Rick -

When you use the Management Console to create a new SQL-based Business Object, and don't select to Create Entry in ID Table, then the oBiz.New() method fails to set the next PK value, leaving it at -1.

In this scenario, the SQL Stored Procedure ww_sp_NewID will try to automatically add the missing record to the wws_ID table, but it is only specifying fields tablename and id. There are also fields for pkfield, pktype and pkwidth, and those are specified as NOT NULL.

Ultimately wwBusinessObject::CreateNewID() calls wwSQL::ExecuteStoredProcedure(), and that fails because the Execute(ww_sp_NewID) call results in "Cannot insert the value NULL into column 'pkwidth', table 'test.dbo.wws_id'; column does not allow nulls. INSERT fails. [1526:515]".

CreateNewID() does set the error message. However, New() checks for EMPTY(lnPK) to forward the error, but lnPK is -1 and thus not EMPTY(), so the 'new' PK stays at -1 and New() returns .T. like everything worked correctly.

I believe the ww_sp_NewID procedure needs to include default values for the other fields, and also New() should check for lnPK = -1 coming back from CreateNewID().

- Mike McDonald

I think the best solution for that is probably in changing the template (in templates\westwind.sql) like this:

CREATE TABLE [dbo].[wws_id](
	[tablename] [varchar](50) NOT NULL,
	[id] [int] NOT NULL,
	[pkfield] [varchar(50)] NOT NULL CONSTRAINT [df_wwsid_pkfield] DEFAULT '',
	[pkwidth] [int] NOT NULL CONSTRAINT [df_wwsid_pkwidth] DEFAULT 0,
	[pktype] [varchar(5)] NOT NULL CONSTRAINT [df_wwsid_pktype] DEFAULT ''
) ON [PRIMARY]

And in the Wizard:

      lnResult = loSQL.Execute("select * from " + lcIDAlias + " where 1=0")
      IF lnResult = -1
         *** If the object doesn't exist -  create it
         IF  loSQL.aErrors(1,5) = 208
            lcSQL = ;
               "CREATE TABLE [dbo].[" + lcIDAlias + "] ("   +;
               "   [tablename] [varchar] (50) NOT NULL ,"   +;
               "   [id] [int] NOT NULL ,"   +;
               "   [pkfield] [varchar] (50) NOT NULL CONSTRAINT [df_" + lcIdAlias + "_pkfield] DEFAULT '',"   +;
               "   [pktype] [char] (1) NOT NULL CONSTRAINT [df_" + lcIdAlias + "wwsid_pktype] DEFAULT '',"   +;
               "   [pkwidth] [int] NOT NULL CONSTRAINT [df_" + lcIdAlias + "_pkwidth] DEFAULT 0"   +;
               ") ON [PRIMARY]"

            lnResult = loSQL.Execute(lcSQL )
            IF lnResult = -1
               THIS.cErrorMsg = "Couldn't create ID table"
               THIS.lError = .T.
               RETURN .F.
            ENDIF
         ELSE
            *** Some other SQL Error occurred
            THIS.cErrorMsg = loSQL.cErrorMsg
            THIS.lError = .T.
            RETURN .F.
         ENDIF
      ENDIF

      *** Check to see if we already have a record for our table
      lnResult = loSQL.Execute("select * from " + lcIDAlias + " where LOWER(tablename) = '" + lcTable +"'")
      IF lnResult # 1
         RETURN .F.
      ENDIF

      *** If it doesn't exist create it!
      IF RECCOUNT() < 1
         lnResult = loSQL.Execute([INSERT INTO ] + lcIDAlias + [ (tablename, id, pkfield, pktype, pkwidth) VALUES ('] + ;
            lcTable + [',0,'pk','I',4)])
         IF lnResult # 1
            THIS.cErrorMsg = loSQL.cErrorMsg
            THIS.lError = .T.
            RETURN .F.
         ENDIF
      ENDIF

This should take care of the creation of the table both initially and if it is created dynamically by the Wizard.

+++ Rick ---