Hi all,

I have a problem using dynamic sql and updating the index field of the table.

After the updating, the same record is selected again and so on, and always the same record is updated.

Could someone help me?
Thanks

this is my script:

tblid = db.bind("t" & table.name, "", company.fis)
if tblid > 0 then
sql = sql.parse(
"select " & table.name & ".*" &
" from " table.name & " for update" &
" where " & table.name & "._compnr = " & str$(company.fis))

sql.exec(sql)

while ( TRUE )
on case ( sql.fetch(sql) )
case EENDFILE:
break
case ENOREC:
sql.break(sql)
sql.close(sql)
sql = 0
error.message = "No data"
rprt_send()
return(FALSE)
case 0:
str1 = column.name & ":=" & column.name & "+" & str$(offset.in.seconds)
var1 = expr.compile(str1)
s.expr$(var1) db.update(tblid, db.retry)
commit.transaction()
continue
default: | error
sql.break(sql)
sql.close(sql)
sql = 0
error.message = "Error"
rprt_send()
return(FALSE)
endcase

break
endwhile

sql.break(sql)
sql.close(sql)
sql = 0

db.unbind(tblid)
endif

I think u r missing db.update(tblid, db.retry) statement .

Do you try not commit every record?

I think u r missing db.update(tblid, db.retry) statement .

db.update is there..

but not with the correct indentation.

Hi,

I moved the expr.compile() to outside of the while loop and it worked perfect! All record descriptions get an underline as ending

Hier the 3GL-Code I used for testing:

function main()
{
long tblid, sql, company.fis, var1

string table.name(10)
string column.name(15)
string str1(100)
string error.message(100)

table.name = "tcmcs085"
column.name= "tcmcs085.dsca"
company.fis = 200

tblid = db.bind("t" & table.name, "", company.fis)
if tblid > 0 then
sql = sql.parse(
"select " & table.name & ".*" &
" from " & table.name & " for update" &
" where " & table.name & "._compnr = " & str$(company.fis))

sql.exec(sql)

str1 = column.name & ":= strip(" & column.name & ") & " & quoted.string("_")
var1 = expr.compile(str1)

while ( TRUE )
on case ( sql.fetch(sql) )
case EENDFILE:
break
case ENOREC:
sql.break(sql)
sql.close(sql)
sql = 0
error.message = "No data"
|rprt_send()
break
case 0:
s.expr$(var1)
db.update(tblid, db.retry)
commit.transaction()
continue
default: | error
sql.break(sql)
sql.close(sql)
sql = 0
error.message = "Error"
|rprt_send()
break
endcase

break
endwhile

sql.break(sql)
sql.close(sql)
sql = 0

db.unbind(tblid)
endif
}

That means that is not allowed to execute alternately 2 runtime functions?
I did not understand why it wasn't working ¬¬"

Hi All,

I Have found the solution. Simply I forgot to put an "as prepared set" in the SQl statement. This allow to modify the primary key without looping.

Thanks to all you.

Steno

That means that is not allowed to execute alternately 2 runtime functions?
I did not understand why it wasn't working ¬¬"

While it is good practice to reduce dynamic expression compilation (expr.compile ) overhead by placing it at right place and reducing its execution in loops,it is perfectly valid to have multiple different dynamic expressions together.