Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
Add a reply View Replies (6)

Resolved Query result in Valentina Studio different to Xojo code

  1. Beatrix Willius
    Beatrix Willius
  2. Valentina Database ADK
  3. Tuesday, December 27 2022, 11:04 AM
  4.  Subscribe via email
I have a really weird problem. My app can read rgb values from an existing SQLite database that belongs to another app. For whatever reason the rgb values from that app are now slightly different. I didn't want to update the existing values. Therefore, I wanted to make an SQL query that ignores small differences in the individual values.

The query works fine in Valentina Studio. The query doesn't give any result using Xojo 2022r3 on Ventura. On High Sierra the result is better but not quite 100% what it should be.

I've attached the result in Valentina Studio. For testing Xojo I copy-and-pasted the query to the Open Any example and ran a cursor:



dim theCursor as VCursor = mDatabase.SqlSelect(ColorQuery1)

if theCursor = Nil then Return

dim temp as Integer = theCursor.RecordCount


The record count is 0.

The query:



SELECT Message.MailboxID,Message.RecID,Message.OriginatorFrom as 'From',Message.ReceiverTo as 'To',Message.MainSubject as 'Subject',Message.MainDateTime as 'Date',Message.AttachmentCount as 'Att',Mailbox.MailboxpathName as 'Mailbox',lower(Message.OriginatorFrom) as 'Order_From',lower(Message.ReceiverTo) as 'Order_To',lower(Message.MainSubject) as 'Order_Subject' FROM (Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID) WHERE (  ( (message.MailtagsMessageColor <> '' AND  abs(list_nthitem(message.MailtagsMessageColor, 1, ' ') - 249) <= 5 AND  abs(list_nthitem(message.MailtagsMessageColor, 2, ' ') - 230) <= 5 AND  abs(list_nthitem(message.MailtagsMessageColor, 3, ' ') - 5) <= 5) ) )  AND ( 1 ) ORDER By "Date" DESC



Am I going crazy?

Xojo 2022r3 on Ventura and High Sierra. Valentina 12.6 for Studio and Xojo.
Attachments (1)
Screenshot 2022-12-27 at 12.00.45.png Screenshot 2022-12-27 at 12.00.45.png
Comment
There are no comments made yet.
Responses (6)
Sergey Pashkov
Sergey Pashkov Accepted Answer
Hello Beatrix,

So in both cases, it is the same VDB file, right?
Do you close the database in the Valentina Studio and run it in the Xojo app? It should not be a problem, just checking.

What if you remove/change the WHERE condition in the query - does it give equal results in Xojo and VStudio?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Beatrix Willius
Beatrix Willius Accepted Answer
Hi Sergey,

yes, it's always the same test database. The query works fine if I remove the where part - it's the main query for my app. I've been playing around with the query. If I remove the check for the blue part of the color then I get a result in Xojo. But I have 7 colors and then the other colors don't work.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Ivan Smahin
Ivan Smahin Accepted Answer
Hello Beatrix,

Is it possible to send me that db?
BTW, is it diagnose ok there?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Beatrix Willius
Beatrix Willius Accepted Answer
Hi Ivan,

I've sent the database. It's just a small one for testing.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
Ivan Smahin
Ivan Smahin Accepted Answer
Hello Beatrix,

I tried it - no such effects here.
In both cases, vStudio and your app (with an original query) produce 7 records in the cursor.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 5
Beatrix Willius
Beatrix Willius Accepted Answer
Really really weird. I just ran the Open Any example with logging of params and I get 0 records:

Database_SqlSelect_1...
PARAM: instance = 105553165530048
PARAM: inQuery = "SELECT Message.MailboxID,Message.RecID,Message.OriginatorFrom as 'From',Message.ReceiverTo as 'To',Message.MainSubject as 'Subject',Message.MainDateTime as 'Date',Message.AttachmentCount as 'Att',Mailbox.MailboxpathName as 'Mailbox',lower(Message.OriginatorFrom) as 'Order_From',lower(Message.ReceiverTo) as 'Order_To',lower(Message.MainSubject) as 'Order_Subject' FROM (Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID) WHERE ( ( (message.MailtagsMessageColor <> '' AND abs(list_nthitem(message.MailtagsMessageColor, 1, ' ') - 249) <= 5 AND abs(list_nthitem(message.MailtagsMessageColor, 2, ' ') - 230) <= 5 AND abs(list_nthitem(message.MailtagsMessageColor, 3, ' ') - 5) <= 5) ) ) AND ( 1 ) ORDER By "Date" DESC"
PARAM: inCursorLocation = 1
PARAM: inCursorType = 2
PARAM: inDirection = 1
return object = 105553149418880

Cursor_GetRecordCount...
PARAM: inCursor = 105553149418880
return res = 0

I can find a different solution.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 6
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories