You are not logged in.
- Topics: Active | Unanswered
Pages: 1
#1 mORMot 1 » Unexpected output of GetJSONObjectAsSQL » 2011-05-08 04:02:49
- Wayne
- Replies: 1
Using version 1.12 .. I looked at the notes for 1.13 but didn't see anything related to this.
The handling of null values seems incorrect in GetJSONObjectAsSQL. I think the pointer, "P", doesn't get Inc'd past the null value when GetValue is called. Here's some test code that should illustrate the point. When the parser hits the null values it doesn't update P correctly, resulting in an empty string for the field name, and the actual field name where the value should be.
sReference := '(ID,Name,Role,Last Login,First Login,Department) VALUES (:(1):,:(''Alice''):,:(''User''):,:(null):,:(null):,:(null):,:(''Sales''):)';
sTest := GetJSONObjectAsSQL('{"ID":1,"Name":"Alice","Role":"User","Last Login":null,"First Login":null,"Department":"Sales"}]', false, true);
Check(sReference = sTest);
// Instead output is invalid SQL, with field names and values all mixed up:
// (ID,Name,Role,Last Login,,,Sales) VALUES (:(1):,:('Alice'):,:('User'):,:(null):,:('First Login'):,:('Department'):,:():)UPDATE: There is also some kind of memory corruption occurring. The string I receive back from GetJSONObjectAsSQL will have varying lengths of garbage at the end of it, sometimes with the contents of previously allocated strings. This occurs only when the JSON string passed to GetJSONObjectAsSQL contains NULL values.
Line 7532 in SQLite3Commons.pas sets the return value of GetValue to 'null', but nothing updates the position of the pointer, P. When the GetValue function returns (@7636), the value of P is such that the "null" value is still in the string. It does not advance to the next field name. Thus, when GetJSONField is called (@7632) on a string like 'null,"First Login":null...' it returns an empty string. This breaks the rest of the parsing, and apparently the size of the string gets set to include bogus memory, resulting in very interesting errors.
#2 Re: mORMot 1 » Save Objects to JSON » 2011-02-23 22:06:58
I think for my purposes GetJSONObjectAsSQL will be the most direct path.
I'll get a chance to test later tonight. Thanks for the help, and thanks for an awesome framework!
#3 Re: mORMot 1 » Save Objects to JSON » 2011-02-23 19:56:17
You can save a Table content to JSON creating a TSQLTable then using GetJSONValues() method.
Another possibility is to use the fast in-memory engine storage instead of SQLite3.
It will rely on TSQLRestServerStaticInMemory classes, one per table, which can load its content from a JSON file and save it via the UpdateFile method.
You can either create separate TSQLRestServerStaticInMemory instances, or create a Client/Server architecture, using a regular TSQLRestServer then calling StaticDataCreate() for all the TSQLRecord classes.
Note that the TSQLRestServerStaticInMemory is very fast, but less powerful than SQLite3. In particular, you have all the ORM or Client/Server features of the framework, but you won't have an SQL engine to work with.
To be clear, I'm not trying to save JSON *instead of* SQLite3, but, *in addition to* SQLite3. Sort of an alternate storage mechanism. My goal is to keep a large SQLite3 database containing all objects, but then be able to save a given "Project" and its associated "Resources" and many-to-many table in a human-readable format to a separate file. This data may be altered while disconnected from the main database, and its data would need to be updated again at a later point.
With that limitation in mind, I believe you were certainly pointing me in the right direction, thanks a lot, Arnaud! It looks like TSQLRestServerDB.DB.ExecuteJSON does the saving part properly. I am able to specify a query and receive the results back as JSON.
However, I'm not sure how to go about loading the saved JSON data back into SQLite3.
If I have a string like the following:
[{"ID":1,Artist="test",Name="first"},
{"ID":2,Artist="test",Name="second"}]What is the best way to update an existing SQLite3 database with the data? If it makes a difference, I'm actually using a GUID field as my own primary index, and not the ID field. I was experimenting with using TSQLRestServerDB.StaticDataCreate, but couldn't get it to work right. It would appear to run, no exceptions generated, but the database didn't contain any data. Is there something I need to run after calling StaticDataCreate to actually save the data to the SQLite3 database?
#4 mORMot 1 » Save Objects to JSON » 2011-02-22 22:43:56
- Wayne
- Replies: 4
Hello everyone,
First, I'd like to say how great the SQLite3 framework is. So far I've found it easy enough to learn, and incredibly powerful. However, I could use some direction on where to go next.
My data structure is very simple at the moment.
TSQLProject: TSQLRecord
TSQLProjectResource: TSQLRecord
TSQLProjectResources: TSQLRecordMany
I have successfully added TSQLProjectResource objects to TSQLProject objects using the Many-to-Many pattern. I was able to easily save this to a SQLite3 database using the HTTP REST server implementation.
My program will not only save these objects to SQLite via HTTP REST, but my intention is to also save them to a local file in JSON format. Is there an easy way to save all of these objects to a single file using JSON and then load them again?
I was thinking something along the lines of creating a new TSQLRestServer and calling something like TSQLRestServer.SaveToJSON to save the entire database. Does such a function exist? If not, what are some alternatives?
Pages: 1