Tim,

The SELECT statement below has worked “forever” at my client Eyre & Smith. The several weeks ago the external drive that stores the EDBBackup file ran out of space. It is difficult to find out what actually happened because the accountant at Eyre & Smith and the consultant (Michael) who manages Eyre & Smith are often reluctant to talk to each other.

As far as I can tell, the only changes to the system made by Michael was to delete files on the backup drive. However, a number of procedures that previously worked are no longer operational.

Below is a SELECT statement that has worked “forever” that shows the stock items (StockNumber) that has been modified in the past week. It no longer works as expected.

The SELECT works if I type it in but being lazy it is much easier to copy and paste. I have copied the SELECT to Notepad++ and Microsoft Notepad and cannot find any extra or invalid symbols in the SELECT.

select
   StockNumber,
   EditedTimestampStr,
   EditedTimestamp,
   (Current_Timestamp() - INTERVAL '7' day) AS WeekAgo,
   (EditedTimestamp > ((Current_Timestamp() - INTERVAL '7' day))) AS RecentItems
  FROM Stock
WHERE
   (EditedTimestamp > ((Current_Timestamp() - INTERVAL '7' day)))

The SELECT fails with the message:
* ElevateDB Error #700 An error was found in the statement at line 3 and column 1 (Expected expression but instead found.)

I work my way up the list below until I find an acceptable SELECT statement. Some times this does not happen and even the SELECT statement below fails.
* select 'X' FROM Stock

The following list shows 5 SELECT statements, with each subsequent SELECTs adding an additional clauses.
I have copied the SELECT to Notepad++ and Microsoft Notepad and cannot find any extra or invalid symbols in the SELECT.

// ---------------------------------------
Select Item #1
select
   StockNumber,
   EditedTimestampStr,
   EditedTimestamp
  FROM Stock

Select Item #2
select
   StockNumber,
   EditedTimestampStr,
   EditedTimestamp,
   Current_Timestamp()
  FROM Stock

Select Item #3
select
   StockNumber,
   EditedTimestampStr,
   EditedTimestamp,
   CURRENT_TIMESTAMP() AS CurrentTime,
   CURRENT_TIMESTAMP() - INTERVAL '7' day AS WeekAgo
  FROM Stock

Select Item #4
select
   StockNumber,
   EditedTimestampStr,
   EditedTimestamp,
   (Current_Timestamp() - INTERVAL '7' day),
   (EditedTimestamp >    ((Current_Timestamp() - INTERVAL '7' day)))
  FROM Stock

Select Item #5
select
   StockNumber,
   EditedTimestampStr,
   EditedTimestamp,
   (Current_Timestamp() - INTERVAL '7' day) AS WeekAgo,
   (EditedTimestamp > ((Current_Timestamp() - INTERVAL '7' day))) AS RecentItems
  FROM Stock
WHERE
   (EditedTimestamp > ((Current_Timestamp() - INTERVAL '7' day)))

// ---------------------------------------

<<

Richard Harding wrote:
...

Below is a SELECT statement that has worked “forever” that shows the stock items (StockNumber) that has been modified in the past week. It no longer works as expected.

>>

Richard,

I'm not Tim but i copied this from your post and it runs OK here - no errors.

In my case i created a new table called Stock with StockNumber as integer, EditedTimestampStr as varchar(30) and EditedTimestamp as timestamp.

If you want to post your table actual create table statement could try that but nothing wrong with the SQL SELECT

Raul

Greeings Raul

The SELECT statement is fine. When I copy the SQL text from my Reference Manager into EDB Manager, it causes a problem. So there is a problem with how my Reference Manager is storing the text. Copying the text from other documents can also cause a problem. I keep lots of SQL code in my Reference Manager so I do not have to type it out time and time again.

Even when I copy the text to Notepad++ or Microsoft Notepad and check that there are no non-printable characters and that the End of Line characters are correct, it can still cause a problem.

Typing the SQL text into EDB Manager works but being lazy I do not want to type in a whole lot of text.

There does not appear to be way of determining if the text in the EDB Manager is valid even if it appears to be.

I believe that if the under-resourced developer views valid text in EDB Manager, then EDB Manager should be in a position to process the text as expected.

Richard

Hi Richard,

That error indicates that that SQL statement is invalid - i suspect you have some kind of a unicode code point in there.

Can save the sql that does not work in edb manager to a SQL file and attach here so we can try as well ?

Raul

Raul

The problem is that the invalid text has hex integers $0A following the end of line characters <CR><LF>. In this case, the hex integers following the end of line characters are $0A200A.

The first problem is recognising that what appears to be valid printable characters is not.

Second problem is removing them when necessary.

I have thousands of items in my reference manager. Some are code snippets and SQL text which for most situations works OK. I suspect that this has caused some confusioni with the EDB Editor (and possiblly the EWB Editor) over the years. I often use Notepad++ as my text editor which avoids some of these issues.

I can strip out the offending characters by saving the text as a PDF file and copying the text from this file.

I can also save the text as a PDF file in my reference manager which is works but all of this is more inconvenient than simply copying and pasting text directly from the reference manager.

Thank you Raul for your assistance.

Richard

Richard


If my hex memory is correct what yiou have is LF space LF probably inserted from a web site.

You don't say what your reference manager is. If its a Delphi program under your control then a small utility to strip non-ascii characters should be easy enough.

Roy Lambert

Greetings Roy,

<<If my hex memory is correct what yiou have is LF space LF probably inserted from a web site.>>

"\xa0" ( $AO or decimal 160), is Unicode for &nbsp.
"\x0A" ( $0A or decimal 10), is the LF character.



My Reference Manager is Zotero. It is nearly very good most of the time but not quite. Zotero is developed by George Mason University in Virgina. I do pay a subscription because I have lots of stuff (videos, movies, images, journal articals) stored but support is somewaht ordinary and there appears to be a lot of fiddling by different staff members It appears that how text is being stored has changed "recently".

Richard

Richard


>"\xa0" ( $AO or decimal 160), is Unicode for &nbsp.

Or as us oldie WordPerfect users would say - hard space


>My Reference Manager is Zotero. It is nearly very good most of the time but not quite. Zotero is developed by George Mason University in Virgina. I do pay a subscription because I have lots of stuff (videos, movies, images, journal articals) stored but support is somewaht ordinary and there appears to be a lot of fiddling by different staff members It appears that how text is being stored has changed "recently".


Had a quick look and its stuffing things into an SQLLite database so it would be possible to manipulate that in Delphi (Audcom components are pretty good). It would take a bit of research to work out what's where. I've done similar mods for the Kodi SQLite database. The other alternative that comes to mind is to subclass the TEDBQuery component so that it strips out the unicode stuff before running. Only difficult if you want to modify EDBManager as well.

Roy

Hello Roy

It is amazing that you were able to make any sense of my last post with some rather imaginative spelling and grammar.

I am not really in a position to do anyting very involved. I am attempting to get my EWB2, EWB3 and EDB installations working in a way that I can understand what is happening - and doing it all as simply as possible.

I still have my list of Word Perfect keyboard shortcuts - which according to Zotero I have not accessed for years.

My wife bought a refurbished iPhone last week and it has taken a few days to get it working as expected and getting data transferred from the old iPhone to the new one. Initially some of the characters on the new iPhone (d and z) refused to work so we needed to return the phone and purchase another screen cover. Getting some of the applications from the old iOS to the new iOS working was a minot challenge.

It was much easier when we walked to the local corner store, exchnaged a few coins and picked up the loaf of bread and chatted to the neigbours along the way.


Richard

Richard


I'd probably go for cleaning up Zotero - that would give you benefits all round. Working with SQLite & Audcom components is a piece of cake. The problem comes with trying to understand the database scheme. I have yet to encounter an SQLite database that hasn't been normalised to the point of being worthy of inclusion of a horror movie.

If you want to send me a sample Zotero database with some of the "duff" sql in it I'll see if I can write a clean up for you. The main problem being figuring out where Zotero has stored it.


Roy Lambert