Re: [sqlite] Differences from 3.7.11 to 3.7.16/17 ?
Are the old prebuild binary versions (dll windows) available from somewhere ? Marten ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Differences from 3.7.11 to 3.7.16/17 ?
I have written a wrapper for VASmalltalk and I had tested it (under Windows 7/32bit - with the downloadable dll's) up to 3.7.11. Today I used the 3.7.16/17 and foudn out, that all the stuff, where callbacks to Smalltalk (from SQLite) are used (tracing, external functions) did not work any more. Has anything changed (calling conventions ?) between these (official) versions (dll) under Windows? I have not found anything in the version documentation. I was not able to find/download 3.7.12/13/14/15 versions to find out, where the problems started ? Thanks, Marten Feldtmann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] german documentation
Marcus Grimm schrieb: > this will be a lot of work and I'm wondering why > you do this ? > Despite beeing a german with a rather poor english knowledge, > I guess a programmer should still be able to understand > the english sqlite documentation, right ? :-) > There are still lots of programmers out there, who prefers documentation in German and buy and use tools, books and journals which are written/translated in German. Though I would not like to do this work - there might be a community for it ... Marten ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use function in binding parameters ?
Igor Tandetnik wrote:
> It's not clear why you would want to, considering that
> date('1964-04-01') = '1964-04-01'. date() function produces a string in
> -MM-DD format (the same format you are starting with).
Due to a totally misunderstanding of these functions ... forget
my question I thought, that these function convert it to float
internally - but I was totally wrong
Sorry !
Marten
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to use function in binding parameters ?
I'm not sure how to use a function call in a prepared statement:
insert into persons(name, birthday) values( ?,?)
how to I bind the value of date('1964-04-01') to one of the
parameters ? I want to store not the string, but the value of
the internal date-function-call into that column ?
Somehow I do not get it how I could do that using the API !?
Thanks,
Marten
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count of rows in every table
select count(*) from tablename ... if sqlite supports count(*) ... Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Meta Information: How to retrieve the column names of a table ?
Dennis Cote schrieb: I think it should be possible to create a subset of the standard information schema in sqlite using virtual tables. That would be very nice and consistent ! Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Meta Information: How to retrieve the column names of a table ?
Igor Tandetnik schrieb: Marten Feldtmann wrote: How can I get all the names of a table without doing a query against the table ? PRAGMA table_info(table-name); Thats it ! Thanks ! What's sqlite3_column_meta_data? It doesn't seem to be mentioned in documentation. Sorry, this function call is not available - it was mentioned in the book "The Definitive Guide to SQLite" - but only, when specific preprocessor settings were set - actually it seems, that the official dlls have no call like this ... Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Meta Information: How to retrieve the column names of a table ?
How can I get all the names of a table without doing a query against the table ? I need all the names of columns within tables/views, the column index within the raw table. With that information I may use sqlite3_column_meta_data to get additional information about the coumns ... Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite3.3.11 - No differences except for sqlite3.h and os_win.c
And the dll is ok ??? Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to get the information if threadsafe ...
Is it possible to get the information, if a SQLite library one uses is threadsafe compiled or not ? Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL error: near "READ_ONLY": syntax error
LuYanJun schrieb: > Hi guy: > what does the follow meaning? > sqlite> BEGIN READ_ONLY; > SQL error: near "READ_ONLY": syntax error READ_ONLY is not a valid option for this command. BEGIN [DEFERRED | EXCLUSIVE | IMMEDIATE] Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3 close() run time errors
Michele Santucci schrieb: Hello, I'm trying to use sqlite3 into a CVI (National Itruments ANSI C dev tool). I took the last sqlite3 dll and source. I create the .lib file linked it to the binary and included the sqlite3.h file... but as long as I start the application I got an error about a missing entry point of the _sqlite3_close() function... why? Calling convention and name decoration. Your linker knows, that sqlite3_close() has to be called via cdecl calling convention and does not look for sqlite_close(), but for _sqlite_close(). You have to tell your IDE, Linker - that it should not use naming decoration. Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] New Wrapper library available for IBM VisualAge Smalltalk or VA Smalltalk
After most of the problems could be solved (thanks to this list). Here is the result. A wrapper library for this product: http://www.schrievkrom.de/uk/tips/small-projects/vasqlite/index.htm Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_column_double - problems when assigning values to variable
Marten Feldtmann schrieb: [EMAIL PROTECTED] schrieb: Make sure sqlite3_column_double() really is declared to return a double and that dVal really is declared to be a double. If both of those things check out, then I would assert that this is a compiler bug. Yes, I think also that this is a very low level error - I've posted a message in the OpenWatcom forum and hope for an answer. Solution: When using OpenWatcom one should use __syscall calling convention and not __cdecl - then it works without problems. Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_column_double - problems when assigning values to variable
[EMAIL PROTECTED] schrieb: Make sure sqlite3_column_double() really is declared to return a double and that dVal really is declared to be a double. If both of those things check out, then I would assert that this is a compiler bug. Yes, I think also that this is a very low level error - I've posted a message in the OpenWatcom forum and hope for an answer. Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_column_double - problems when assigning values to variable
John Stanton schrieb:
Maybe your statement does not reference a valid row.
No, actually I have in my test program several lines to retrieve
the values in different ways:
sql = "select aColumn from TESTME2;";
sqlite3_open("c:\\sunittest.db", &db);
sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL);
rc = sqlite3_step(stmt);
/* --- */
/* this is ok and returns "5000.0" */
txt = sqlite3_column_text( stmt, 0);
/* this works */
sqlite3_column_double( stmt, 0);
/* this crashed ...
dVal = sqlite3_column_double( stmt, 0);
/* --- */
sqlite3_finalize(stmt);
sqlite3_close(db);
return( dVal);
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] sqlite3_column_double - problems when assigning values to variable
I have written some interface code to the sqlite3 API using the OpenWatcom 1.5 compiler and I wrapped several functions like sqlite3_column_text, sqlite3_column_int and all went fine. Then I tried to wrap sqlite3_column_double(..) and when I do something like ... sqlite3_column_double( stmt, 0); ... the statement works without problems, but when I write: double dVal; ... dVal = sqlite3_column_double( stmt, 0); ... I get a general protection fault. I'm not that C guru - but what is the problem here ? Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Unicode Help
Ulrich Schöbel schrieb: SQLite includes a Tcl API. Tcl does all these conversions with ease. See the encoding convertto/convertfrom commands and fconfigure But Tcl is not part of SQLite (and this is good) - this is just an add-on. The idea with the additional functions are pretty good ! Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Unicode Help
Igor Tandetnik schrieb: Da Martian <[EMAIL PROTECTED]> wrote: So if I look at a name with umlaughts in the database via sqlite3.exe I get: Städt. Klinikum Neunkirchen gGmbH -- | an "a" with two dots on top "A with umlaut" is represented as two bytes in UTF-8. sqlite3.exe just dumps these bytes onto the console, which is not smart enough to interpret them as UTF-8 sequence. In other words, the data in the databse is fine, it's just displayed incorrectly (in the wrong codepage). You don't need to worry. So I tried the *16 versions, but now the field size returned by "sqlite3_column_bytes16" always seems to be larger than the string I get back resulting in junk characters on the end. In my storage framework for VASmalltalk I have to convert all my strings - to get all the stuff right. Each string you work with is not only characterized by the characters you have, but also the code page the string is defined in. In general this is very often the code page of the operating system (under Windows 1252, under OS/2 850 - using them in a German locale). To make it more complicate it is also needed to have a suitable font for that code page. VASmalltalk for example works internally with code page 819 on all available platforms, but under Windows it has to support the code page 1252. Therefore when storing strings within this IDE one has to convert the string from code page 819 to UTF-8. UTF-8 is a special code page under Windows: 65001. Therefore I convert the strings from 819 to 65001 and then send this converted string to the API call. Perhaps it would be nice to change sqlite3 in that way, that (when columns with storage class text) these columns are converted to the host platform code page. But actually even in that situation you may have strings, which are not displayable on your screen - because you have no suitable font. Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3 and threads ...
[EMAIL PROTECTED] schrieb: the 2.4 kernel. If you are using win32 or mac os-x or linux with a 2.6 kernel, this restriction does not apply. Thats interesting ! Thanks ! Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT on empty fields ??
There are good reasons to have "NULL" values - though they can make the live a bit harder. Use them - even Microsoft has found out, that they made a big mistake in .NET 1.x and corrected them in 2.0 by introducing nullable types. Darren Duncan schrieb: You can save your self a lot of grief by declaring all of your fields to be NOT NULL and default the text fields to the empty string, ''. Use '' rather than NULL when you don't have a name. Then you can simply say "where foo=''". -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3 and threads ...
I've read the book from Michael Owens about SQLite and also read the parts about threads and sqlite3. Actually it seems to be possible to use the same connection handler from different threads - but within a transaction only one thread is allowed to access the library with that particular connection handler. Is this also true for functions like "errcode" and "errmsg" ? Background: I'm writing a wrapper for VASmalltalk and in that tool it is possible to execute an external API call with a feature named "staticFuture" call (an API call is always done with the same native thread) - but this feature is time consuming. After that costly API call I would like to call the fast API calls "errcode" and "errmsg" in the virtual machine thread (which is a different native thread than the thread mentioned above). Is that possible ? Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite 3.3.8 and OpenWatcom ?
Has anyone compiled sqlite 3.3.8 under OpenWatcom 1.x ? I'm not able to do it, because of this: void (*interrupt)(sqlite3*); in sqlite3ext.h ... seems to be, that interrupt is a reserved word in OpenWatcom, but this is just a guess ... Any idea ? Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] calling convention ...
I wanted to use the prebuild dll of sqlite - and I was looking for the information, what calling convention was used within the sqlite.dll. It seems to me, that "cdecl" is used and NOT "stdcall" - right ? Marten
Re: [sqlite] querying hierarchy
Jim Crafton schrieb: In my table(s) I need to model a class hierarchy, as well as a class/var/function belonging to a namespace, and/or a function/var belonging to a class. In other words a parent/child relationship. If I simply add a new column to my primary symbols table, call it "Parent", how would I query for a given row plus any children (and any of their children, and so on)? If this is too general an SQL question and it's not appropriate to the list, my apologies. This is a classical problem, when working with trees and hierarchies. It was discussed here last week (?) when a person asked here about a special database layout. In general it can not be done in ONE SQL statement unless the database gives you the power of some kind of recursive SQL queries. What other databases offers you can be read in either Joe Celko - SQL for Smarties (2000 or 2005) or Joe Celko - Trees and Hierarchies in SQL for Smarties (2005) In those books he mainly uses stored procedures to get the work done. Databases like DB2, MaxDB or Oracle offer you these recursive SQL statements. The main winning is, that you execute ONE query and retrieves all rows belonging to a sub tree, which is MUCH faster, that to execute multiple queries ... In a special case, where your items only belong to one main sub tree (and the sub trees are not interesting for you), you could also store the reference to this main sub tree (or the root of that tree) in each row. Summary: If you have a fixed/known levels of hierarchies this can be done in one query. Marten
Re: [sqlite] Database design and SQLite
michael munson schrieb: Type refers to an int value that represents an in-server datatype. The main difference is that I realized that I wouldn't necessarily need a row for EVERY property, only if a property is different than the value stored on the parent object. If its the same, it can have no entry and the code will just look to its parent (and its parent's parent, and so on) until it finds the correct value to inheriet. I think that will reduce the number of rows significently, if add a couple of CPU cycles but I think it will make up for it in the querying. Look for typical use cases and try to count the SQL commands you need to execute to solve these use cases. For my application (an interactive GUI based client server application) I have found, that I at most have to get ALL needed data in 3 - 6 queries (where the upper end is critical ...). With SQLite I would expect (local database), that 6 is not criical at all. If you do need too many queries, "bring the data to the queries": change your layout until its ok. Otherwise the interaction of the GUI is not good and the user will not be happy. Organizing the application in tab pages also helps you - checking from one page to another means activity for the user and you can use this time for your SQL queries. If you have tree structure go the Explorer way of Windows - show initially, that the object have children - only if the user checks them, solve the reverenfences and update the GUI. My customer were not happy about this until I showed them, that Windows does it the same way. Then suddenly the customer were quite - even though they did not believe me until they saw it in Windows. Marten
Re: [sqlite] Database design and SQLite
Teg schrieb: Hello Marten, Monday, January 16, 2006, 2:14:59 PM, you wrote: To me duplicate entries or near duplicate entries in a table are a no-no. Sounds to me like you're talking about inserting the object multiple times in the same table each instance describing one "property" of the object. What you're suggesting sounds like ... Which duplicates not only the ID but, the entries (from the original poster's requirements). Whereas I'm suggesting Table 1: Table 2: ... I don't belive you can do ... Because you really have no idea how many properties there are going to be. If you have one object with 2 properties and one with 100, you'll have 200 columns of properties even for objects that only have 2 properties. Now, if an object was ONLY properties then I agree, you could make a table of only properties but, he's using the object table to define some parent child hierarchy (It does sound like a sqliteized version of XML). Your suggestion is a possible extension - this table can be used to add additional columns (used for optimistic locking - as an example). Some futher thoughts about this: The column is a simple varchar(x) column and the software has to convert the attribute value in a possible and suitable string representation. An search index is set on this column (to search objects using some attributes). Therefore the conversion from the native datatype to the string representation has to be done carefully (as an example: how can one compare a negative number against a positive number with different number of digits and decimal points - when these numbers are only represented as a string ...). The modelling of associations are just the matter of using one single row for one object in an association (the property value contains the object id referenced by this association). If you have to model a :n association you may have multiple rows for this object for one . and so on, and so on please remember: it's not just a model of persistency. Perhaps useless, perhaps usefull in special situations. Marten
Re: [sqlite] Database design and SQLite
Teg schrieb: Hello Marten, I wasn't suggesting one table for all object, I was suggesting a table for objects and a table for object properties. Using the object ID as a way to identify which properties belong to what objects in the properties table. The "Vertical" part was simply for the object properties since, there is no predefined limit on the number of properties any object might have. What do you store in the object table ? Why do you need the object table ... just use the property table ! The entries within the property table defines the object ! It's not clear to me why a single properties table is a bad idea from an SQL standpoint. Is it that the properties can be read in a random order? What did I learned from history - one may use Oracle as a storage management and if it does not work, ok. If one uses PostgreSQL and it does not work - bad for the person. If I store the data in the normal teached way in relational database ok, but if it wents wrong and a typical relational database administrator may look at your database you will be in trouble, because you use the tool in a way which is very untypical - thats all I wanted to say. Do whatever you want to do - beside the mainstream - but be prepared to be attacked. I love relational databases and I like to work with them very much. With an index on the object id's of the properties table, I'd even expect the performance to be reasonably good. The time for the insert statement will grow linear - consider this as a possible problem. The amount of bytes transfered to the application will grow. You need several statements to insert one object. Consider this as a timing problem in a network environment. But again: the vertical approach may be the way to go. Marten Marten
Re: [sqlite] Database design and SQLite
Just some additional comments: The "vertical" approach (described by Teg) leads also to a very untypical relational database and if a pure sql administrator would look at it . Ok, but it works, but when using a vertical approach you have consider some points: * you have to throw away the column (attribute) constraints (contraint, unique) ... * accessing the data needs SQL commands, whichs stresses parts of SQL one normally would not use and perhaps you may find some bugs (group by) ... * insert speed increases pretty linear (when having ONE table for all objects - again different mapping may improve this). queries are not that bad. * you need tool support - otherwise you are lost. If you can read Germany - I've described the vertical approach in a presentation on page 28: http://www.schrievkrom.de/uk/tips/nordakademie-2004/Vortrag-Nordakademie-2004.pdf In this presentation there's also stuff about using/creating database portable object-ids and stuff like this. +++ The "XML" approach is - more or less - a marketing idea ... XML is now used for everything and the main problem of XML is: speed. +++ "SQL for Smarties" is a well written book - (by the way: the author is Joe Celko) +++ Another points FOR SQL oriented databases: they are initially open for ALL programming languages and therefore pretty different from all these language centric databases (mainly in the Java world). This is achieved by introducing an API - which every language has the same possibilities to get most of the database - and NOT by using language bindings. +++ If you have to consider tree structures you should consider the special SQL extension some databases offer: (SAP-DB, MaxDB, Firebird). These SQL extensions (sometimes called recursive sql selects) can improve the speed of your application in a DRAMATIC way ! Marten Jay Sprenkle schrieb: On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: Greetings, I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I have run into a bit of a wall while attempting to design a database for a C++ program I am attempting to write. The needs of the database are to: represent an object oriented hierarchy of any number of objects, where each object may have custom properties of several different datatypes and permission bits. What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. I do wonder the same thing as another poster. Is a database really the tool you want to be using for this? I can't imagine what you really need with a database for that application.
Re: [sqlite] Database design and SQLite
Indeed it may be questionable to use SQLite for stuff like this, but its a very fast relational db library - and therefore it can be used as any other relational database to store objects. What is needed is very simple: you need a object-oriented relation database wrapper - either as a commercial library or as a home written software. Be aware, that storing objects in relational database IS not a trivial task (concerning the fact, that you want to have good speed). Especially tree structures are always difficult to handle (in an efficient way). Some databases have special SQL commands extension for that. Tree structures are often used in the GUI applications. I've worked with Smalltalk and relational databases and yes it can be done, but going this way, one has to consider BOTH sides of the development: the object oriented side and the relational side and perhaps one should not use the most hottest feature in the OO world, because mapping can only be done very badly. Accept the relational side and then you may get happy. How to map oo structures into databases has been described in the literature over the last 15 years (starting with Smalltalk, then with Java and now with C# and all hottest languages) in many online arcticles, in many books etc. Making a good mapping assumes the knowledge about relational databases, about the application one has to write and the special needs of this application. In general: without tool support, you will get lost. As an example: I've over the year (in Smalltalk) used a commercial library, which allows me to define a oo model (classes, hierarchies, attributes, associations). Then it creates a "simple" relational database layout and the Smalltalk base code for the model. You may defined searchable attributes (leading to indices) and several different kinds of mappings: one class to one table, several classes to one table etc. Databases like this are (in newer days) called integrational databases, because they look like normal databases and the values can be retrieved from all other languages. Other databases are the application oriented databases - databases with - perhaps - strange layouts (in the normal sense of relational databases): one simple example is a table with two coloumns: one for the object id, the other for a BLOB column, holding the binary representation of the object). Now I go another way: I've written my modeller by myself and also wrote a code generator for the languages I wanted to support (in this case C#). Within the model each attribut, each association and each class gets a unique number (for management purposes). The data types supported are restricted to Integer, String, Floats, Decimal, Date, DateTime, Boolean - mainly the basic stuff. Even support for arrays are possible. Then I defined a general micro language to create a binary representation of my object and changed the modeller to create source code for this representation. The textual representation of ths language would be like (simplified) set objectid environment to set attribute 1 to value <12.23> set attribute 2 to value set attribute 3 to value <2006/01/20> add association at attribute 4 with object with close objectid environment This representation is pretty compact and can be created automatically and even the code to restore the object from that representation can be created automatically and pretty fast. Searchable attributes are (in addition) not only stored in this binary (BLOB) representation, but also in special tables: each searchable attribute has its own table and always the same layout: column1: object id, column2: attribute value. The name of this table may be created automatically using the attribute id of the attribute. (The drawbacks of this procedure are quite clear - several insert for ONE object. For a local database (file oriented) this may be not that critical, for a networked database this approach is more critical). Associations are handled using additional tables You see: there are standard ways of doing the work and very specialized ways of doing it Marten Jay Sprenkle schrieb: On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: Greetings, I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I have run into a bit of a wall while attempting to design a database for a C++ program I am attempting to write. The needs of the database are to: represent an object oriented hierarchy of any number of objects, where each object may have custom properties of several different datatypes and permission bits. What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. I do wonder the same thing as another poster. Is a database really the tool you want to be using for thi
