Use XBasic UPDATE to update ALL records in a SQL Server Table

[psamuels56]

Until tonight, I thought I was pretty good with XBasic Server-Side Functions. I use them all the time to Select, Insert, Update and Delete Alpha data.

I have an interesting situation: In my SQL table, I have a "Social Security #" field that was encrypted by Alpha (a5_encrypt_string). For my current project, I need to update a field in the SQL table (called "Last") to the value of the last 4 UNENCRYPTED digits of the Social Security #. NOTE: Getting the Last 4 digits, unencrypted, was easy (I used the a5_decrypt_string and "Right(SS_NO,4)" to get the value). It displays perfectly in the grid.

So, now I have a simple grid that has two fields: 1) the "SS_NO"; and 2) "Last"... and displays them perfectly. Not that it matters, but there are ~800 records in the table that I want to update with my XBasic function.

To perform this task, I wrote the following Function (activated by an "Action Button" on the Navigation Bar:

function myFunction5 as c (e as p)
Dim cn as sql::connection
Dim args as SQL::Arguments
Dim sqlCommand as c
Dim EmpNum as n
Dim PINNum as c
Debug(1)
PINNum = e_currentRowDataNew.PIN
EmpNum = e_currentRowDataNew.SS_No

args.set("PIN", PINNum)
args.set("EmpNum",EmpNum)

flag = cn.open("::Name::Training")
if flag = .t.
sqlCommand = "UPDATE AllEmployees SET PIN = :PIN WHERE SSL_NO = :EmpNum);"
flag = cn.execute(sqlCommand, args)
else
myFunction5 = "alert('Could Not Connect to Server!');"
endif
cn.Close()

end function

Simply, my code doesn't work! There are two problems:

1. First, the function doesn't like "e_currentRowDataNew" (which doesn't surprise me because I'm really not wanting "row-by-row" data... I want the function to update all 800+ records in the table); and;
2. Second, I don't know what I need to do to get the UPDATE command to update all records in the table, not just the ones on the page being displayed.

I think this should be easy, but I have a mental block about how to proceed. I'm sure someone reading this will be able to point me in the right direction. I'd appreciate any help you can give.

Thanks,
Phil

[alexc]

Re: Use XBasic UPDATE to update ALL records in a SQL Server Table

Phil,

To perform this action in One SQL Update for the entire table is not straight forward as your SN numbers are needing to be decrypted currently you are decrypting them in the grid on the fly.

Normally you would select the table then merge the selected with a update statement as seen in an example found here

https://www.techonthenet.com/sql/update.php

'***Example in URL Above ***

UPDATE summary_data
SET current_category = (SELECT category_id
FROM products
WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT category_id
FROM products
WHERE products.product_id = summary_data.product_id);

'***

Read up on the SQL Result Set Update Data and Apply Data

https://www.alphasoftware.com/docume...20Function.xml

Basically you should be able to SELECT your ALLEmployees table then loop through the result set decrypting and setting a value for your Pin then use that result set and do a SQL update with SQL::Connection::UpdateData Function or optimally use SQL::Connection::ApplyData Function.

Alternatively Look into how alpha Updates the database by putting a debug(1) in the serverside event onbeforesqlcommandexecute. In this section you can see they perform the SQL Update via Arguments for Columns and placeholders of arrays. No reason you could not use this method and create Sqlagruments as above instead of using the SQL Connection Update Data or Apply Data Functions

Would love to see the final result of your function

Regards

Alex