1. Falguni Patel
  2. PowerBuilder
  3. Monday, 13 December 2021 22:42 PM UTC

Hi, 

 

I am getting below error message while inserting record to the table. 

I need an assistance urgently to resolve this.

 

Thanks,

Falguni

Accepted Answer
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 13 December 2021 23:59 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Falguni;

  I would suggest turning on SQL tracing to help you see exactly what SQL is being generated & how the DB client is trying to handle it.

SQLCA.DBMS = "ODBC" // change to ...

SQLCA.DBMS = "TRACE ODBC" 

HTH

Regards .. Chris

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 14 December 2021 01:56 AM UTC
  2. PowerBuilder
  3. # 1

Hi Falguni,

Though your error occurs while doing an INSERT, I recently experienced the exact same error message when doing a SELECT statement. It occurred in .Net. We use Oracle, but in .Net, the .Net driver seems to have some kind of a relation with the MS driver. I think for the "types", if I recall well what our .Net developer told me. I'm not an expert.
It turned out that some decimal value, stored in a FLOAT field in oracle had like more than 32 decimal digits and  .Net couldn't handle that much decimals.
I'm not saying that the error you are getting is the same, but please set DisableBind=1 and run your debug again. That way you will see the values you're actually trying to insert. Something must be wrong with one of them.

I've chopped down your pasted code to the bits that actually show an ERROR.
There's three ERRORS

1. A SELECT on the dba.sy_control table: Invalid object name 'dba.sy_control'.
2. Your failing INSERT: [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
3. Another failing SELECT (same as in 1.): [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dba.sy_control'.

Here's your pasted code with only the 3 "*** ERROR"s:

/*---------------------------------------------------*/
/* 14/12/2021 12:17 */
/*---------------------------------------------------*/
(037AA838): SELECT sy_control.dealer_id , sy_control.licensee , sy_control.system_license , sy_control.max_company , sy_control.expiry_date FROM dba.sy_control (1.122 MS / 66.863 MS)
(037AA838): *** ERROR 208 ***(rc -1) : SQLSTATE = S0002
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dba.sy_control'.
(037AA838): PREPARE WITH BIND VARIABLES:
(037AA838): INSERT INTO dbo.utf_transaction ( company_id , tran_id , batch_id , fund_id , plan_id , account_no , trantype , trandate , quantity , price , amount , status , dc_type , tranref , receipt_no , bank_id , chq_no , user_id , del_mode , pay_type , category , chg_owed , rct_tran_id ) VALUES ( ? , ? , ? , ? , ? , ? , 'RCT' , ? , ? , ? , ? , 'U' , 'C' , ? , ? , ? , ? , ? , 'P' , ? , 'Sale' , ? , ? )
(037AA838): VCHAR Length=10 ID=? *UTOFM*
(037AA838): LONG Length=0 ID=?
(037AA838): LONG Length=0 ID=?
(037AA838): VCHAR Length=4 ID=? *10*
(037AA838): VCHAR Length=2 ID=? *1*
(037AA838): VCHAR Length=20 ID=? *101-VIN030*
(037AA838): DATE Length=0 ID=?
(037AA838): LONG Length=0 ID=?
(037AA838): DECIMAL Length=0 ID=? *2.38*
(037AA838): DECIMAL Length=0 ID=? *59.50*
(037AA838): VCHAR Length=0 ID=? **
(037AA838): VCHAR Length=20 ID=? *1310001069*
(037AA838): VCHAR Length=0 ID=? **
(037AA838): VCHAR Length=0 ID=? **
(037AA838): VCHAR Length=6 ID=? *DBA*
(037AA838): VCHAR Length=2 ID=? *B*
(037AA838): DECIMAL Length=0 ID=? *0.50*
(037AA838): LONG Length=0 ID=? (0.909 MS / 892.049 MS)
(037AA838): EXECUTE: (0.038 MS / 892.087 MS)
(037AA838): *** ERROR 999 ***(rc -1) : SQLSTATE = 22005
[Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
(037AA838): ROLLBACK: (2.476 MS / 894.563 MS)
(037AA838): GET AFFECTED ROWS: (0.000 MS / 894.563 MS)
(0CBE6C50): EXECUTE:
(0CBE6C50): SELECT sy_control.dealer_id , sy_control.licensee , sy_control.system_license , sy_control.max_company , sy_control.expiry_date FROM dba.sy_control (0.933 MS / 33.729 MS)
(0CBE6C50): *** ERROR 208 ***(rc -1) : SQLSTATE = S0002
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dba.sy_control'.

As stated before, if you set DisableBind=1 (temporarily) in your sqlca.dbparm values, you might be able to see which actual values you are trying to INSERT in that table, instead of seeing '?' in the values of the insert statement.
Copy that INSERT into Sql Server Management Studio and see what's wrong with it.

Beware: The help of PB on connection to SQL Server says that:
"The MSOLEDBSQL and SNC interfaces support inserting and updating values of large datatypes using embedded SQL INSERT and UPDATE statements. You must set the DisableBind database parameter to 0 to enable the MSOLEDBSQL or SNC interface to bind large data values."
So just use the DisableBind parameter only temporarily for debugging purposes.

Since myself I found the problem to be in values with too many decimal digits, I'd recommend you specially look at the values you're trying to insert into the fields like 'amount' and 'price', though the error message in the image of the MessageBox that you showed us, doesn't seem to indicate any absurd amount of decimals. Could be a Date field maybe?

HIH,

regards.

MiguelL

Comment
  1. John Fauss
  2. Tuesday, 14 December 2021 02:20 AM UTC
Thanks for doing the heavy lifting, Miguel!



Falguni, without seeing the actual data values and without knowing the various table/column data types, it's hard to say for sure, but I use SQL Server exclusively and have for many years (although not through ODBC), and what I think is happening with the INSERT statement is there is a character string value in the VALUES clause that is being placed into a column that has a numeric data type (such as Int or Smallint. Obviously, you should never do this, but SQL Server will try to perform an implied CAST function in the hopes that the string value can be cast (converted) to a numeric value that matches the column's data type. In this particular case, that implicit CAST fails. I suggest you look for a data type mismatch in DataWindow column definitions and the table's column definitions.



Oh, in the future, would you PLEASE attach lengthy text information as a file instead of pasting hundreds of lines into the Q&A forum editor? It would help us help you more easily. Thank you.
  1. Helpful
  1. Miguel Leeuwe
  2. Tuesday, 14 December 2021 02:24 AM UTC
lol, yw John!

In our case that's also what I thought: some implicit cast not working well, but turned out to be a lot more difficult to trace. The error message SQL Server is giving is very poor though. It should at least say on which field it's giving the error.

regards.
  1. Helpful
  1. Miguel Leeuwe
  2. Tuesday, 14 December 2021 02:25 AM UTC
though thinking of it ... trying to push too many decimals into a decimal field IS kind of a conversion error.
  1. Helpful 1
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.