1. John Murphy
  2. PowerBuilder
  3. Tuesday, 25 February 2020 17:12 PM UTC

The code bellow works fine in SQLAnywhere and SQLServer 12 but fails on SQLServer Express 10.50.6000.

The result dw does not have the update property set and the key attribute in not set on the primary key fields.

ls_sql = "SELECT * FROM CMF "

ls_syntax = SQLCA.SyntaxFromSQL(ls_sql, 'Style(Type=Grid)', ls_error)

if ls_error <> "" then
   f_message_box("Warning", ls_error)
   Return -1
end if

dw_inventory.Create(ls_syntax, ls_error)


IF ls_error <> "" then
   f_message_box("Error", "Create " + ls_error)
   Return -1
end if

ll_status = dw_inventory.SetTransObject(SQLCA)

if ll_status < 0 then
   f_sql_error(SQLCA, dw_inventory, "dw_inventory.SetTransObject(SQLCA)")
end if

ll_status = dw_inventory.Retrieve()

if ll_status < 0 then
   f_sql_error(SQLCA, dw_inventory, "dw_inventory.Retrieve()")
end if

Accepted Answer
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 18:52 PM UTC
  2. PowerBuilder
  3. # Permalink
0
Votes
Undo

Hi John;

  Does the SQL ( "SELECT * FROM CMF " ) work OK if executed in the PB IDE's Database Painter's iSQL pane when connected to the same SS instance and using the same DB Client?

Regards .. Chris

Comment
No select * from cmf did not work but I noticed that in that environment all the tables were dbo. I changed it to dbo.CMF and it worked.



Thanks!!!!!!
  1. John Murphy
  2. Tuesday, 25 February 2020 20:28 PM UTC
Hi John .. that is excellent news!

Note: Normally, by adding the DB User's login in the DBMS permission to have DBO "read" authority .. then the prefix would not be required.
  1. Chris Pollach @Appeon
  2. Tuesday, 25 February 2020 21:29 PM UTC
Thanks for sharing the solution!
  1. Armeen Mazda @Appeon
  2. Tuesday, 25 February 2020 21:52 PM UTC
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Tuesday, 25 February 2020 17:25 PM UTC
  2. PowerBuilder
  3. # 1
1
Votes
Undo

to be safe, you should always set it explicitly.

set your primary key(s), update table, and all related update properties after creating your dw from syntax

 

 

 

 

 

Comment
This was just test code. In the real version the user selects the table. I don't have the data on what the key is unless there is some other way to find out.
  1. John Murphy
  2. Tuesday, 25 February 2020 19:42 PM UTC
depends on your database. quick google search for sql server: https://dataedo.com/kb/query/sql-server/list-all-primary-keys-and-their-columns
  1. mike S
  2. Tuesday, 25 February 2020 19:52 PM UTC
Thanks. That would be complicated as it is a generic program that allows a user to connect to any database. You enter a table name and import a .csv and it uses the column headers and the data to update the database. I use select * so if a column is not specified on an insert text and numbers are spaces and zeros instead of nulls.
  1. John Murphy
  2. Tuesday, 25 February 2020 23:17 PM UTC
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.