Digital Workshop

Hi everybody,

I'm certainly not an expert in SQL ond Opus, and I really need help to fix a bug with a script to update a database.

In a few words : I have a script consisting of 140 lines (see attached) to update records in an Access database. I do not understand why only 30% of the targeted records are updated.
- has it got to do with my syntax ?
- ...with the speed of Access modification vs script lecture?

Has anybody encountered the same problem and found a solution ?

Thanks in advance for help.

You might want to try putting a wait statement after each to see if that solves the problem. I'm experiencing some of the same problems with my program as well. From what I've experienced you are right about the speed differences between the script and Access execution.

Jason

Thank you Jason.

That is indeed something to try. I noticed that my Access database needs - by default - 500 msec to update every record. I'll try to include a wait(0.6) instruction after each ExecuteSQL line.

Anyway, there might be another voodoo problem... Let me explain:

Because I have already 140 lines with ExecuteSQL instructions, I have very often copied and pasted previous lines then just modified them.

Tonight, I made this test:
- I erased one of those copied&pasted lines
- I wrote it all again with my keyboard
- I ran the script

>> Surprisingly, the newly (re)written line was executed successfully

I therefore suspect "copy&paste" to be responsible for this mess. Can it be possible that some Ctrl invisible symbol - or any other invisible symbol - alter the good reading of a txt / ils file by Opus?

Is there a way to compare a txt/ils script 100% written by hand with the same script where 90% of the lines have been sequentially copied&pasted? ...a "smart" utility?... (I can't see anything special when using Words' option "Show everything")

Please keep in touch. I am not giving up...

I've also run into a problem where the code looks fine but doesn't work until you retype it. But I don't recall it happening within the script object, it was always from an external source. I don't know any program off hand you can use to check for characters, but if it's always the same statements that fail you may have to retype them or try copying and pasting a few of your newly type statement. Let me know how it turns out.

Jason

If you want to clean up code and remove any invisible formatting,
pass it through notepad before you paste it into Opus.
That will remove anything that isn't supposed to be there.

cheers
Paul

In fact, my script is external. It is a text file to which I give the ils extension. I now think that Ctrl+V might be the cause. I suspect it generates invisible caracters somewhere that Opus and/or the DSN ODBC do not like. I tried to find a text editor more sophisticated than Notepad in order to see those suspected invisible codes. Tried Notepad2 and Subpad... No success. I'm currently rewriting my script from scratch into a new ils(text) script.

I finally found a solution. Simply by importing the script back into my Opus app, and testing it. I must admit a few errors of synthax were the cause on non-execution of most of the other lines. SQL doesn't like apostrophes to be inserted between SQL-apostrophes.