sqlrecord_find,sqlrecord_delete,sqlrecord_update not working

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

sqlrecord_find,sqlrecord_delete,sqlrecord_update not working

Post by trevordevore » Fri Oct 11, 2013 2:42 pm

[This post is an archive from the old SQL Yoga forums]

Hi,

first allow me to say that I love to use your lib as it eases much my work !

Currently I'm using Valentina 4.3 for my project (and with SQLYoga it's really easy to handle; I like being able to use an encrypted DB for my sensible user data...)

Almost everything is working like a charm, but I don't get it how to use "sqlrecord_find" ; I want to update or delete entries in my db but trying to use these commands is not working for me:

- e.g. sqlrecord_find "journal,tEntryID,tRecordA [where tEntryID is an integer (here: the stored value obtained from the ticks); the Primary Key field in the DB is EntryId]

returns

"Message execution error:

Error description: Handler: error in parameter expression"

so there is nothing I can do about it;

I use the "dbconn_ExecuteSQL mySQLCode" to do the job, but that is not the way I want to work with your lib ;-)

Is there anything I'm doing wrong ? Perhaps I did not understand the whole process or are there special things I should be aware of when working with Valentina ?

Any help would be appreciated !

Thanks !

Christian
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqlrecord_find,sqlrecord_delete,sqlrecord_update not wor

Post by trevordevore » Fri Oct 11, 2013 2:42 pm

Hi Christian,

Assuming SQL Yoga knows that EntryID is the primary key field for the journal table your code looks fine (assuming that the lack of trailing quote after journal is a typo in your message). You will need to get the error message that is being thrown.

The chapter on error handling in the SQL Yoga manual discusses techniques for displaying the error messages thrown by SQL Yoga. Read through the chapter (very short) and use one of the methods to get the exact error message. Here is the link:

http://revolution.screenstepslive.com/s ... pters/2311

Once you have the error message report back and I can help you track down the problem.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqlrecord_find,sqlrecord_delete,sqlrecord_update not wor

Post by trevordevore » Fri Oct 11, 2013 2:43 pm

Hi,

I solved it by doing this :

tableobj_createObject "journal"

tableobj_set "journal","primary key","EntryID"

sqlrecord_find "journal", tRecID, theRecordD

sqlrecord_delete theRecordD

tableobj_DeleteObject "journal"

Hope this is the correct way as it is not really documented...

but it works ;-)

Cheers,

Christian
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqlrecord_find,sqlrecord_delete,sqlrecord_update not wor

Post by trevordevore » Fri Oct 11, 2013 2:43 pm

Regarding EntryID as primary key: For some reason SQL Yoga does not think that EntryID is the primary key for your "journal" table. How have you defined the EntryID column as the identity column in Valentina?

As for setting the "primary key" property of a table manually - this is supported but you would want to permanently define the table object and primary key property, not do it on a temporary basis each time.

I'm still working on the table objects chapter in the manual but here is a quick overview.

SQL Yoga stores the db schema for your database and automatically defines properties for each table in the database based on the columns in the table, the defined primary key, etc. This is all provided by default.

You can define additional properties for tables by explicitly defining a table object. You should define table objects in a special handler that SQL Yoga has reserved for this purpose. This lesson in the to-do app manual discusses where and how to do this:

Tell SQL Yoga About Table Relationships

Basically you would add your code for creating the table object and assigning the primary key to the dbobject.createTables message. Now any time your SQL Yoga code references "journal" table SQL Yoga knows that "EntryID" is the primary key for that table.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqlrecord_find,sqlrecord_delete,sqlrecord_update not wor

Post by trevordevore » Fri Oct 11, 2013 2:43 pm

I finally understood how to create Primary Key Identity Fields in Valentina --> you have to choose ULONG field types... (in VStudio click on Identity); so the EntryId becomes a synonym of RecID...

Now, after having updated the dbobject_schema, EntryId is recognized as the Primary Key field...

Everything's working fine now...

No need do define tableObjects anymore...

Christian
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

Post Reply

Return to “SQL Yoga”