Problem description: I have multiple sqlite3 databases that I use: - on Windows XP SP3 with Ch. Werner's sqliteodbc driver v. 0.85 and MDAC 2.8 Sp1 - on Ubuntu 10.04 Lucid with Ch. Werner's sqliteodbc driver v. 0.80 and unixodbc 2.2.11-21 On both systems I see with Libreoffice 3.5.1 and at least 5 different tables of the connected Base databases: Opening the table (or using the preview windows) shows the primary key field mostly empty, to be exact: the 41th row has an entry, the following not; when scrolling down to later rows and then back the view gets complete out of sync, having sometimes alternating empty rows and one entry repeated every other row. Those tables have in common: all fields are defined "NOT NULL PRIMARY KEY" and are either char(n) or varchar(n) datatype, the Base database has them as "Text (varchar)" All databases open flawlessly in Libreoffice 3.4.5. Seems something broke severely in the ODBC handling. I experienced another issue, but that is probably a different bug: Using a version newer than 0.85 of the sqliteodbc driver (only on Windows, there is no newer version than 0.80 for Lucid) results in some of my tables showing only 4 empty rows, and this happened already with OpenOffice 3 and later with Libreoffice 3.3 and 3.4. Those tables have a timedate field as primary key, and Christian Werner had changed the formatting of timedates between 0.85 and 0.86 (millionth of seconds I believe) and told me, that in this case the the handling of Base was in error, and I should avoid using timedates as primary key, but that was no option. Now new with Libreoffice 3.5.1: when I try to open one of those tables with a timedate primary key Libreoffice reproducibly crashes. Steps to reproduce: see above: I try to find a minimal database that shows this behavior, as the ones that do hold private data. Platform (if different from the browser): presumably all, at least Ubuntu Lucid and Windows XP Browser: Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:11.0) Gecko/20100101 Firefox/11.0
adding keyword regression (3.4.5->3.5.1) and setting importance major as data-loss is possible
Heinz, I think your "probably a different bug" deserves a bug report of its own. If they turn out to be the same (but they look quite different to me), it is easy to mark one as a duplicate of the other. Keeping two bugs separate in one discussion, takes ongoing effort. I look forward to seeing your demonstration database.
I have succeeded in demonstrating the first described problem with ubuntu-natty (11.04) 32-bit libsqliteodbc version 0.87.2 libsqlite3-0 version 2.8.17-6build2 and LibreOffice master commit 1199713, pulled 2012-04-01 and configured with --disable-mozilla --enable-symbols --enable-dbgutil --enable-crashdump --disable-build-mozilla --without-system-postgresql --enable-python=internal Here are the steps. Of course, change the name of my working directory "/home/terry/lo_hacking/notes/bug_047520/" to something that you find convenient. (1) Download the attached file init.sql to your working directory. (2) In your working directory, use the attached file init.sql to create an example database: $ sqlite3 bug_047520.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .read init.sql Error: near line 5: no such table: byNr Error: near line 6: no such table: byTs Alternatively, download the attached database, bug_047520.db. (3) Observe the created tables. $ sqlite3 bug_047520.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .mode column sqlite> select * from byNr; 1 2012-04-06 12:34:56.654321 Friday 2 2012-04-05 13:45:57.123456 Thursday sqlite> select * from byTs; 1 2012-04-06 12:34:56.654321 Friday 2 2012-04-05 13:45:57.123456 Thursday sqlite> .quit (4) Define bug_047520 as an ODBC data source referencing the SQLite database bug_047520.db. I attach screenshot from ODBCConfig. The stanza in my .odbc.ini reads ... [bug_047520] Description=timestamp primary key Driver=SQLite3 Database=/home/terry/lo_hacking/notes/bug_047520/bug_047520.db Timeout=100000 StepAPI=No ShortNames=No FKSupport=No SyncPragma=NORMAL JournalMode=DELETE LoadExt= (5) Create LO database file ... (a) From command line, source ooenv, and then `soffice.bin --base`. Program displays Database Wizard Step 1 of 2 "Select Database" with default selection "what do you want to do?" = "Create a new database". (b) Select "Connect to an existing database". Program extends the list of steps to four steps and enables the dropdown list of existing databases. (c) In the list of databases, select ODBC. Program displays ODBC as the selected existing database. (d) Click "Next >>". Program displays step 2 of 4 "Set up ODBC connection". (e) In "Name of the ODBC data source on your system", type "bug_047520", and click "Next >>". Program displays step 3 of 4 "Set up user authentication". (f) Click "Test Connection". Program displays message box "Connection Test ... The connection was established successfully." (g) Click "OK". Program returns focus to Database Wizard. (h) Click "Next >>". Program displays step 4 of 4 "Save and proceed". (i) Select "No, do not register the database", leave "Open the database for editing" selected, and click "Finish". Program presents Save dialog. (j) Type Name "Play1", navigate to your working directory, and click Save. Program displays window play1.odb with "Tables" selected; the lower right pane shows tables byNr and byTs. (6) Observe good results from the table with an integer key. (a) Double-click byNr. Program displays window "byNr - play1 - ... Table Data View". Observe two rows of data. I attach screenshot. (b) Close the Data View. Program displays window play1.odb. (7) Observe bad results from the table with a timestamp key. (a) Double-click byTs. Program displays window "byTs - play1 - ... Table Datata View". Actual result: No data is shown. I attach screenshot. Desired result: Two rows of data shown.
Created attachment 59688 [details] sql statements setting up demonstration SQLite3 database
Created attachment 59689 [details] demonstration sqlite3 database file
Created attachment 59690 [details] definition of ODBC data source
Created attachment 59691 [details] table with integer primary key displays data
Created attachment 59692 [details] table with timestamp primary key does not display data
Finally I arrive at providing a test case. Unfortunately, the case you, Terrence, provided outlines the second "probably different bug": no data shown when primary key is timestamp using sqliteodbc newer than 0.85 that you confirmed on Linux - I experienced it under Windows. This might have something to do with the changes in timestamp handling of sqlitodbc starting with version 0.86, and can be seen with older openoffice.org releases as well. My case with this bug is different: it is new to LibreOffice 3.5, and occurs with presumably every sqliteodbc version. I stripped my case down to the essentials, and could reproduce it under Linux (Ubuntu Lucid, sqliteodbc 0.80) and Windows XP (sqliteodbc 0.94) and LibreOffice 3.5.1 and 3.5.2 with the following: create a new sqlite3 database with the sqlite3 shell and the following lines: CREATE TABLE "table1" ("number" varchar(50) NOT NULL,"name" varchar(50), PRIMARY KEY ("number")); INSERT INTO "table1" VALUES('0123456789','name 1'); INSERT INTO "table1" VALUES('1234567890','name 2'); INSERT INTO "table1" VALUES('2345678901','name 3'); INSERT INTO "table1" VALUES('3456789012','name 4'); INSERT INTO "table1" VALUES('4567890123','name 5'); INSERT INTO "table1" VALUES('5678901234','name 6'); INSERT INTO "table1" VALUES('6789012345','name 7'); INSERT INTO "table1" VALUES('7890123456','name 8'); INSERT INTO "table1" VALUES('8901234567','name 9'); INSERT INTO "table1" VALUES('9012345678','name 0'); I attach the resulting sqlite file as "expose.sqlite". Then use ODBCConfig (Linux) or the ODBC Data Source Administrator (Windows) to create a new sqlite3 data source using this file. Then start the Base Wizard and choose: "Connect to an existing database", and then "ODBC", and choose the data source just created. Choose where to store the ODB file, and then open it. Open the table1 table: the first column is empty. expected result: you should see the data above (they are still there as can be seen by using any sqlite database tool). I see this error with many of my tables, always with char(n) or varchar(n) data type as the primary key field (in the first column), in Base the type is shown as Text[wvarchar]. I wonder if the "w" (wide) has anything to do with error, but I don't think so: as I recall it, Libreoffice 3.4 / OpenOffice 3.3 did show this type also, and they did not exhibit this error.
Created attachment 60314 [details] testcase that shows the error
In my developmen tree, based on libreoffice-3-5 (3.5.5 in preparation), the situation is slightly different: - Heinz's example: Only the first line has "primary key" field empty. - Terrence's example: timestamp primary key crashes LibO. I suspect it is linked to the fix for bug 48345, so this gives me an idea where to look... Hopefully I'll have a solution soon. Did someone file a separate bug for "table with timestamp primary field displays no data"?
The problem comes from a weirdness of the ODBC spec: One cannot retrieve a (VAR)CHAR column twice in a row. The second time one tries, the driver answers "I gave you all data, no more data to give", so LibO has only an empty string. With sqliteodbc, if one asks a third time, one gets the data again, but that is not guaranteed by the spec. What *is* guaranteed by the spec is that if one requests e.g. column 1, then column 2, and then column 1 again, one gets the beginning (or whole if it fits in the limits) of column 1 again. But the capacity to retrieve a *lower* column (e.g. 1) after a higher column (e.g. 2) has been retrieved is *optional* and does not have to be supported by all drivers. Sigh... So I don't know of a robust solution to this. I wanted to change connectivity;;odbc::OTools::getStringValue (and the other functions that use SQLGetData) to retrieve another column and then the column one truly wants, but this is impossible in face of the constraints above; I have tested that retrieving an invalid column does not reset the "give me fresh data" flag :-( The only real solution seems to be to be very cautious not to retrieve the same data twice. I'm going to try to do that.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=228c360e7d53a74d9908e9b164b12ace316cd34e fdo#47520 use the already retrieved row instead of driver row
I am in the process of building with the referenced patch. Before I noticed your comment with the patch, I was writing ... > Aha! So while > > select number, name from table1 > > gives blanks in the number in the first row, > > select cast( number as char ), name from table1 > > shows the data. Similarly, changing the type of number from > varchar(50) to char(50) in the table definition also lets LO show the > data. Do you really intend the reference to CHAR in the commit message?
(In reply to comment #14) > I am in the process of building with the referenced patch. Before I > noticed your comment with the patch, I was writing ... >> (...) changing the type of number from >> varchar(50) to char(50) in the table definition also lets LO show the >> data. > Do you really intend the reference to CHAR in the commit message? You are right, as CHAR is a fixed-length datatype, it won't show that problem. Which makes me realise that LibO fetches CHAR data incorrectly, because it tries to use "retrieve in parts" semantics (if there is more than 2048 bytes), and this is forbidden... Although it *may* work with some/most/all drivers anyway, as a "be extra nice" extension, not sure. For (W)CHAR data, we have to allocate ONE buffer that is big enough ("easy" since the length of the data is known...) and then do *one* SQLGetData call, not multiple as with variable-length data. Actually, the commit message is even more wrong than that. <sigh> It will *not* cause LibO to use the "already retrieved row", but to issue a fresh request "SELECT * FROM table WHERE primary_key=value" to fetch the other columns. But I realised this only later :-(
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-3-5": http://cgit.freedesktop.org/libreoffice/core/commit/?id=16910edcae542e8eb8a5f9b4278aceaa39262b44&g=libreoffice-3-5 fdo#47520 use the already retrieved row instead of driver row It will be available in LibreOffice 3.5.5.
On the system described in comment 3, and with master commit 0752710 pulled around 2012-06-06 12:50 GMT, I can successfully open Heinz' table1. Thank you.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=82555fdad50b68bdfe7912c58a1ec42889c86177 fdo#47520 ODBC: overhaul data fetching The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=570cb8ffd4e174aa936f15217fd4dbe163c44e6a&g=libreoffice-4-0 fdo#47520 ODBC: overhaul data fetching It will be available in LibreOffice 4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eminently composed article, if just all bloggers offered the same substance as you, the web would be a far superior spot. https://enjoy-the-life-baby.com
On the off chance that you set out to make me think today; mission achieved! I truly like you"re composing style and how you express your thoughts. Much thanks to you. https://paydayloanslowdown.com
I read a article under the same title some time ago, but this articles quality is much, much better. How you do this. https://www.educationcareeradvisors.com
I truly like you're composing style, awesome data, thankyou for posting. https://elephant-sky.com
ODBC with sqliteodbc: primary key field mostly empty, broken scolling. https://www.geometrydash.me/
The reality your paper feels so startling is on the grounds that you've been told through your whole scholastic profession that this bit of composing is the most significant report you'll ever make. While the facts demonstrate that it is the most significant piece you'll compose at college, it isn't as critical as it appears. By: https://articleoftheweek.com/2020/02/17/delete-email-account-in-hostgator/
Heaps of editors will express this as a trap for creators. Great writing by http://www.ukwritingexperts.co.uk/ doesn't depend on simply mentioning to perusers what's going on or expressing how a character feels, rather you should draw on the creative mind by making scenes that individuals can feel and see and smell. It's that flash in somebody's psyche that will truly bring a novel alive for them.
The are just for reference purpose https://www.essaychamp.co.uk/buy-essay and you need to create your own by the help of received paper.