• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Why do I keep getting a DB error on an Insert statement?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Why do I keep getting a DB error on an Insert statement?

  1. Advice
0
Votes
Undo
  1. Tracy Lamb
    Tracy Lamb
  2. PowerBuilder
  3. Friday, 29 September 2023 21:31 PM UTC

Hi all,

I'm trying to create a revision to form.  The dw object and control are identical... I'm just changing a couple of values and changing the row status to NewModified! to force an INSERT instead of an UPDATE.  

// Create new Workorder
ll_NewWO = f_get_workorder_id( 1 )
if ll_NewWO < 0 then return
idw_1.SetItem(1, "workorder_id", ll_NewWO)
idw_1.SetItem(1, "revision_to", il_FormID)
idw_1.SetItem(1, "revision_by", sqlca.logid)
idw_1.SetItem(1, "revision_date", DateTime(Today(), Now()) )
idw_1.SetItem(1, "revision_reason", ls_reason )
idw_1.SetItem(1,"lab_complete", 'F')
idw_1.SetItemStatus(1, 0, Primary!, NewModified!)
li_rc = idw_1.Update(TRUE, TRUE)
if li_rc = -1 then
	MessageBox("wo update", li_rc)
	return
end if

The "workorder_id" column is the primary key, int in the table. "Revision_date" is a smalldatetime in the table.  Database is SQL Server 2012.  Using a Microsoft ODBC driver.  I sprinkled a bunch of Message boxes to double check the values I am setting, everything looks good.  INSERT statement seems to be created correctly.  Here's the error I'm getting:

I don't know how to get the values to show up, it's always question marks.  Despite the error, everything displays properly in the dw.  

It just seems like changing a few fields and forcing an INSERT should do the trick.  Any ideas would be appreciated... I might have to break down and write a function to manually insert a new record... This method is so much more convenient and easy to read!

~~~Tracy

 

  1. DataWindow
  2. PowerBuilder (Appeon)
  3. SQL Server
MSOLEDBSQL - SqlState 42000 OLE DB Driver for SQLS...
MSOLEDBSQL in PB 2019R3
Accepted Answer
Tracy Lamb
Tracy Lamb Accepted Answer Pending Moderation
  1. Friday, 29 September 2023 23:12 PM UTC
  2. PowerBuilder
  3. # Permalink

Ok, I fixed the darn thing... turns out the dw didn't have update capabilities on a column that is a foreign key (required) in the db.  That's not a column that I would ever edit... but it's needed for an insert!

Fractional Truncation error is very deceiving... 

I figured it out when I was writing a script in SQL Server to test for so I could write a new PB new function. The error message I got is a lot more informative! So the fix was to ensure that all fields in the workorder table are updateable in the dw.

~~~Tracy

 

Comment
There are no comments made yet.
Responses (3)
Brage Mogstad
Brage Mogstad Accepted Answer Pending Moderation
  1. Monday, 2 October 2023 07:57 AM UTC
  2. PowerBuilder
  3. # 1

Hello,
I'd strongly suggest using the PFC framework if its a client-server app.
For this problem in particular you'd inherit the dwo from u_dw,
and can fetch a meaningful error messages in the dberror event, served on a plate.
Even for smaller apps its a huge advantage for others that the author organized his code right,
using the PFC framework and inherit from userobjects from the beginning.

Just to make it clear, Non-PFC apps can get so messy, the code is beyond saving, even by top students.
I've seen it happen. The programmer dies, changes job or retires.
The guy taking over looses his voice, his mind, most of his hair, gains 30 pounds, and worse, his marriage will
struggle heavily and may never recover.
The memory of this former co-worker will not be respected by the coworker, believe me.

I'd say, even if you for some reason are planning to make apps for manned space crafts,
you'd have a huge problem convincing me to not use PFC.

Regards 
Brage

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 2 October 2023 21:15 PM UTC
LOL, so true! However, I've also seen applications that use the PFCs, but in a very wrong way, and you still end up with code and references "all over the place".

regards

  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 30 September 2023 17:10 PM UTC
  2. PowerBuilder
  3. # 2

Hi Tracy,

If you want to see values instead of "?" in your SQL, set the dbparm "DisableBind=1"

https://docs.appeon.com/pb2022r2/connection_reference/DisableBind.html

regards

Comment
Tracy Lamb
  1. Tracy Lamb
  2. Monday, 2 October 2023 18:21 PM UTC
Thank you! I added DisableBind=1 to the ini file, and all of the ? marks were replaced with actual values. It also fixed the problem with the ambiguous SQL error..
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 2 October 2023 21:18 PM UTC
I'm using Oracle, and I'm not exactly sure since when, (I think PB2019 R3), I have seen similar "spontaneous" errors happening with for example and INSERT statement, but not always. Without doing ANY code changes, simply by setting disablebind to 1, the error doesn't happen.

It's a shame, because supposedly having the value set to 0, things are "supposed" to run faster. I didn't notice much difference though when using Oracle and we like to see the values, so it's always a value of 1.

regards
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.