I am using PB 9.0.1 Build 7236. I am using a datawindow to do an Insert.
The column in question should default to zero. It is NOT included as an
updateable column in the datawindow. But PowerBuilder is including it in the
Insert syntax and erroring as the default of zero is not being recognized.
When I use the PB Native MS SQL Server interface, the column default is
recognized. In Enterprise Manager, the default of zero shows on the
in-question column.
I have asked in PB groups but have only got one reply that states that this
is the way the OLE DB interface has always worked. Is this a peculiarity to
OLE DB? Or do you think it is a PB problem?
"Scott Morris" wrote:
> You need to be much more specific. What does "not recognizing column
> defaults" mean? What problems? Are you using datawindows. If so, the only
> time columns are included in insert statements is when your application code
> sets the value of that column. Is that what you are doing? If your code
> (and user) do not provide a value, then it is left out of the inserted
> column "list". In this case, the default is used. You also failed to
> specify which version of Powerbuilder. At the very least, you should review
> the fix list for the most current build of the version you are using to
> determine if there is a bug that has already been fixed.
> Incidentally, you are more likely to get help posting in a powerbuilder NG.
> The more information you post, the more likely someone will help.
> "roz" <roz@.discussions.microsoft.com> wrote in message
> news:091BC8BE-02BE-41AE-8F5C-07621F96BFA5@.microsoft.com...
> problems,
>
>
Sounds to me like your code (or perhaps in an ancestor class) is doing
something unexpected. If the column is not included the the list of
updateable columns, then it will not be included as a column in the insert
list. I've never had a problem with PB in this respect. I suspect that
something else is occurring that is causing the behavior. You may want to
use the preview pane in the dw painter to insert a row (if possible); this
would at least identify PB or your code as the problem. The next step would
be to create a simple test case using the existing datawindow (and without
any of your ancestor/application code). If these succeed, then the problem
lies with the application code. Although I haven't moved beyond PB9 6533, I
suspect that application code or design is the problem. It also may be
related to some odd cirucumstances (sharedata, linkage, get/setfullstate,
etc.). You may also want to investigate whether some piece of code is
dynamically modifying the datawindow and (implicitly or explicitly) setting
the update properties.
Be aware that the default you define in the table and the default you define
within PB are different "things" entirely. PB will not automatically update
a default defined in the datawindow to be consistent with any table-level
default. This should not be an issue in this particular situation.
One last thing: The phrase "... syntax and erroring as the default of zero
is not being recognized" is not particularly meaningful. I will repeat.
Help is much likely to be more "helpful" and more forthcoming if you
accurately and completely describe the problem. By itself, a default (or
lack thereof) will not produce an error when a row is inserted into a table.
Perhaps there is a constraint or trigger that enforces the use of a specific
domain of values for a column, producing the error. It helps to know the
exact text of the error. I'm still not certain what "...the column default
is recognized." means but I'm not certain it matters at this point.
"roz" <roz@.discussions.microsoft.com> wrote in message
news:D6DE6DA2-C749-4546-B121-D097E2C3A25F@.microsoft.com...
> I am using PB 9.0.1 Build 7236. I am using a datawindow to do an Insert.
> The column in question should default to zero. It is NOT included as an
> updateable column in the datawindow. But PowerBuilder is including it in
the
> Insert syntax and erroring as the default of zero is not being recognized.
> When I use the PB Native MS SQL Server interface, the column default is
> recognized. In Enterprise Manager, the default of zero shows on the
> in-question column.
> I have asked in PB groups but have only got one reply that states that
this
> is the way the OLE DB interface has always worked. Is this a peculiarity
to[vbcol=seagreen]
> OLE DB? Or do you think it is a PB problem?
>
> "Scott Morris" wrote:
only[vbcol=seagreen]
code[vbcol=seagreen]
code[vbcol=seagreen]
review[vbcol=seagreen]
NG.[vbcol=seagreen]
2000.[vbcol=seagreen]
out?[vbcol=seagreen]
|||I am trying to do an insert on a table using a datawindow. The dw does not
use the
column 'dues_paid_lifetime' in the select (I searched the source!). Thus
there is no initial value to set to zero. It is not in the select so there
is no column to remove from the update list.. I set the dbparm to
DisableParm = 1. Still on the insert I get the error:
SQLSTATE = 23000
Microsoft OLE DB Provider for SQL Server
Cannot insert the value NULL into column "DUES_PAID_LIFETIME'; column does
not allow nulls. INSERT fails.
The insert syntax also displays in the error message. The column
"DUES_PAID_LIFETIME' is not in the sql syntax.
This does not happen with Oracle or with the Native MSSQL Server interface
sent with Powerbuilder.
Is this a SQLOLEDB bug?
"Scott Morris" wrote:
> Sounds to me like your code (or perhaps in an ancestor class) is doing
> something unexpected. If the column is not included the the list of
> updateable columns, then it will not be included as a column in the insert
> list. I've never had a problem with PB in this respect. I suspect that
> something else is occurring that is causing the behavior. You may want to
> use the preview pane in the dw painter to insert a row (if possible); this
> would at least identify PB or your code as the problem. The next step would
> be to create a simple test case using the existing datawindow (and without
> any of your ancestor/application code). If these succeed, then the problem
> lies with the application code. Although I haven't moved beyond PB9 6533, I
> suspect that application code or design is the problem. It also may be
> related to some odd cirucumstances (sharedata, linkage, get/setfullstate,
> etc.). You may also want to investigate whether some piece of code is
> dynamically modifying the datawindow and (implicitly or explicitly) setting
> the update properties.
> Be aware that the default you define in the table and the default you define
> within PB are different "things" entirely. PB will not automatically update
> a default defined in the datawindow to be consistent with any table-level
> default. This should not be an issue in this particular situation.
> One last thing: The phrase "... syntax and erroring as the default of zero
> is not being recognized" is not particularly meaningful. I will repeat.
> Help is much likely to be more "helpful" and more forthcoming if you
> accurately and completely describe the problem. By itself, a default (or
> lack thereof) will not produce an error when a row is inserted into a table.
> Perhaps there is a constraint or trigger that enforces the use of a specific
> domain of values for a column, producing the error. It helps to know the
> exact text of the error. I'm still not certain what "...the column default
> is recognized." means but I'm not certain it matters at this point.
> "roz" <roz@.discussions.microsoft.com> wrote in message
> news:D6DE6DA2-C749-4546-B121-D097E2C3A25F@.microsoft.com...
> the
> this
> to
> only
> code
> code
> review
> NG.
> 2000.
> out?
>
>
|||It looks to me like there is some other problem. The insert statement
generated by PB does not include the column in the insert list. If this is
true (and you can verify this using profiler - just to be absolutely
certain), then the default constraint you think is present isn't or the
default constraint is not enabled. Another possibility is a mal-functioning
trigger
Based on your information, you should be able to take the insert statement
from the error message, paste it into QA, and generate the error yourself by
executing the statement.
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:E5F27698-9BC8-4909-919E-63F11B9AF80E@.microsoft.com...
> I am trying to do an insert on a table using a datawindow. The dw does
not
> use the
> column 'dues_paid_lifetime' in the select (I searched the source!). Thus
> there is no initial value to set to zero. It is not in the select so
there[vbcol=seagreen]
> is no column to remove from the update list.. I set the dbparm to
> DisableParm = 1. Still on the insert I get the error:
> SQLSTATE = 23000
> Microsoft OLE DB Provider for SQL Server
> Cannot insert the value NULL into column "DUES_PAID_LIFETIME'; column does
> not allow nulls. INSERT fails.
> The insert syntax also displays in the error message. The column
> "DUES_PAID_LIFETIME' is not in the sql syntax.
> This does not happen with Oracle or with the Native MSSQL Server interface
> sent with Powerbuilder.
> Is this a SQLOLEDB bug?
>
> "Scott Morris" wrote:
insert[vbcol=seagreen]
that[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
would[vbcol=seagreen]
without[vbcol=seagreen]
problem[vbcol=seagreen]
6533, I[vbcol=seagreen]
get/setfullstate,[vbcol=seagreen]
setting[vbcol=seagreen]
define[vbcol=seagreen]
update[vbcol=seagreen]
table-level[vbcol=seagreen]
zero[vbcol=seagreen]
table.[vbcol=seagreen]
specific[vbcol=seagreen]
default[vbcol=seagreen]
Insert.[vbcol=seagreen]
an[vbcol=seagreen]
in[vbcol=seagreen]
recognized.[vbcol=seagreen]
is[vbcol=seagreen]
peculiarity[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
application[vbcol=seagreen]
inserted[vbcol=seagreen]
to[vbcol=seagreen]
should[vbcol=seagreen]
to[vbcol=seagreen]
powerbuilder[vbcol=seagreen]
me[vbcol=seagreen]
