Re: [sqlite] Sqlite Concurrent access issue

[Simon Slavin]

On 22 Dec 2011, at 4:35am, Virparia, Chetan J (GE Energy) wrote:

> Query : Select * from mytable where  in ( around 0.2 million
> comma separated values)

Anything that requires handling .2 million things is going to be tricky.  
Optimizing this query would be done differently depending on what governs these 
values: whether they're the same each time, or completely different each time, 
or you have a few different sets of values.

Under some circumstances you could define a table, put the values into it, and 
use a JOIN as part of your SELECT.  You could even then define a VIEW and index 
the view. This would make the query extremely fast.

Under other circumstances you would just define a very long string that had all 
those values in it (possibly separated by commas), then search this string 
using LIKE.

It really depends on what you're trying to do.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] command line to get number of tables in sqlite

[Mohit Sindhwani]

On 22/12/2011 7:25 AM, Matt Young wrote:

select count() from sqlite_master;

No??

...where type='table';




Cheers,
Mohit.
22/12/2011 | 12:51 PM.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Concurrent access issue

[Virparia, Chetan J (GE Energy)]

 

Hi,

 

We have been using SQLite database for a while using single threaded WCF
service for our condition based monitor solution. Since, we are facing
issues related to scalability for our next generation products due to
single threaded architecture, We have been working on using SQLite
concurrency access feature through multiple threads and enabling WCF
concurrency feature. 

 

We did a small prototype using SQLite using ADO.NET by changing
Isolation mode = ReadCommited and executed few queries sequentially and
parallel and found huge performance improvement. However, when we
executed huge select query found that it degraded performance
drastically so it will be great help if someone help me out to figure
out right usage pattern for this scenario.

 

Here is the execution timing of my query

 

Database size : 856 MB

Query : Select * from mytable where  in ( around 0.2 million
comma separated values)

Single query execution time: 1388 MS

10 Sequential queries total execution time : 13380 MS 

10 Parallel queries total execution time: 37370 MS

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about add sqlite database cloumn

[Simon Slavin]

On 22 Dec 2011, at 2:52am, YAN HONG YE wrote:

> I have a question about C binding for sqlite, I have a table like this:
> 
> Name  Price1  Price2  Sum
> A123  231  
> A222  12   
> A321  223  
> 
> how to use functin int  sum(price1+price2)
> result to put into sum cloumn use sqlite in c code?

You should not call a column "Sum" because that word is reserved in SQLite.  So 
call it "PriceTotal" instead.

UPDATE MyTable SET PriceTotal = Price1 + Price2

But there is no need to do that and store the result.  Just add the two prices 
together whenever you want the total:

SELECT (Price1 + Price2) AS PriceTotal FROM MyTable

To learn how to call SQLite functions from C read the end of this page:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about add sqlite database cloumn

[YAN HONG YE]

> I have a question about C binding for sqlite. I have seen those for
> integer, float... but I was wondering how to deal with a NUMERIC (x, y)
> type ?
> We can't use float or double, we could loose precision, so maybe with a
> string ?


I have a question about C binding for sqlite, I have a table like this:

NamePrice1  Price2  Sum
A1  23  231  
A2  22  12   
A3  21  223  

how to use functin int  sum(price1+price2)
result to put into sum cloumn use sqlite in c code?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of sqlite-users-requ...@sqlite.org
Sent: Thursday, December 22, 2011 1:00 AM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 48, Issue 21

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@sqlite.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Re: Strange behavior for timeouts in transactions (romtek)
   2. Re: How about the way put a database of SQLite 3 beside   web
  folder? (romtek)
   3. Re: Strange behavior for timeouts in transactions (Simon Slavin)
   4. insert error (jim-on-linux)
   5. Re: insert error (Petite Abeille)
   6. Re: insert error (epank...@comcast.net)
   7. Re: Procedure (Conditional statement) workaround (Nico Williams)
   8. Re: Procedure (Conditional statement) workaround (Petite Abeille)
   9. run a script in Visual Basic.NET (Esteban Cervetto)
  10. Re: run a script in Visual Basic.NET (Simon Slavin)
  11. sqlite3_column_text() returning partial results (Jacob A. Camp)
  12. Re: sqlite3_column_text() returning partial results
  (Igor Tandetnik)
  13. Re: sqlite3_column_text() returning partial results (nobre)
  14. Re: sqlite3_column_text() returning partial results (Simon Slavin)
  15. Re: Bug in trigger: when comparing a value of an int column
  to a quoted value (Alexey Pechnikov)
  16. sqlite3_column_text() returning partial results (Jake)
  17. Re: sqlite3_column_text() returning partial results
  (Igor Tandetnik)
  18. Re: sqlite3_column_text() returning partial results
  (Jacob A. Camp)
  19. Re: sqlite3_column_text() returning partial results
  (Black, Michael (IS))
  20. Re: sqlite3_column_text() returning partial results (Pavel Ivanov)


--

Message: 1
Date: Tue, 20 Dec 2011 11:21:16 -0600
From: romtek 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Strange behavior for timeouts in transactions
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On Tue, Dec 20, 2011 at 6:08 AM, Simon Slavin  wrote:
>
> First, there's more than one way of using SQLite3 from PHP. ?There's also the 
> interface SQLite3:: which is a much thinner wrapper around the basic SQLite C 
> library. ?I have no way of knowing what proportion of programmers use one 
> library or the other.

I started using SQLite with PHP in version 5.2.x, when there wasn't
SQLite3:: interface, so I chose to use PDO. Moreover, I don't want to
lock myself into SQLite, PDO provides a somewhat more flexible
approach.


> As long as you set an acceptable timeout, which you do with one of these 
> calls depending which library you're using
>
> 
>
> 
>
> your web service probably isn't going to run into problems. ?What might be 
> worrying you right now is the result of the default timeout being zero, 
> something which is arguably weird. ?In other words, unless you specify a 
> timeout yourself any report of a lock is instantly treated like an error. 
> ?Set your timeout to one second or five seconds and the behaviour will be 
> more reasonable.

I'd done some research into this since I asked my question and learned
some things. According to http://bugs.php.net/bug.php?id=38182 and
http://www.serverphorums.com/read.php?7,118071, PDO_SQLITE defaults to
a 60 second busy timeout. This should be enough.

> Second, most transactions and locks in SQLite from PHP are fleeting. 
> ?Generally you want your web page to list some records or do one update. ?You 
> wouldn't have a process keep a lock active for a long time because this would 
> correspond to your server taking a long time to show a web page, and nobody 
> wants that. ?So a lock by one process probably isn't going to last very long 
> -- probably a fraction of a 

Re: [sqlite] command line to get number of tables in sqlite

[Matt Young]

select count() from sqlite_master;

No??

On Wed, Dec 21, 2011 at 10:32 AM, smallboat  wrote:

> Hello,
>
> I have a sqlite file. I would like to open it and know how many tables in
> it.
>
> What is the command line to open a sqlite file and get to know how many
> tables in it? Thanks.
>
> Regards,
> Joe
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Petite Abeille]

On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:

> Is this truly a limitation of SQLite or am I doing something wrong?

The later. Simply create your view. Then restrict it.

In other words:

create view foo as select bar from baz

select * from foo where bar = ?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parameters are not allowed in views

[Chris Mets]

When I execute the following SQL statement:  select * from test2 where f2 = 
@param;
 
it prompts me correctly for a parameter value.
 
However, when I try to create a view:  create view testview as select * from 
test2 where f2 = @param;
 
I receive the following error message: Parameters are not allowed in views.
 
Other SQL databases allow me to do this. When reviewing the SQLite limitations, 
this is not called out as one of the limitations. I have tried from three 
different environment (Visual Studio, SQLite Database Browser, and SQLite3 
command line utility). All give me the same error.
 
Is this truly a limitation of SQLite or am I doing something wrong?

Thanks in advance for any help,

Chris
 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Jacob A. Camp]

There it is, when I printed this value out to a txt file everything was there. 

Apparently there is some sort of character limit when displaying strings in 
debug mode in Visual Studio 2010, news to me...

Thanks everyone!

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Wednesday, December 21, 2011 2:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite3_column_text() returning partial results

Now, this is a very good test case and explanation what happens, except...

How did you check value of CString testing after iterating though the entire 
char array? Did you look at it in your debugger? Maybe your debugger is not 
capable of showing CString contents longer than 2048 symbols? The value of 
string testing you gave with Windows-style line ends gives me 2040 bytes, 
indentation of the next line is 8 bytes, so if total limit is 2048 bytes then 
you won't see any contents of the next line...

Can you output contents of CString testing using printf() and see if it shows 
the same 2040 bytes?


Pavel


On Wed, Dec 21, 2011 at 1:34 PM, Jacob A. Camp  wrote:
> So delving further into this has gotten me nothing else; this is the best I 
> could do to simplify this to understandable steps.
>
> (I'm new to this service so no idea if attachments are allows so sorry 
> about the length in advance)
>
> I have this code:
>
>                sqlite3_stmt* state = NULL;
>                CString sql = _T("SELECT RelationshipHierarchyXML FROM 
> TlAssembly WHERE id=?");
>                //Query the database
>                RawSQLTlID (sql, id, );
>                bytes = sqlite3_column_bytes(state, 0); //This line 
> returns 8955
>                //Try getting data as blob
>                const void * temp = sqlite3_column_blob(state, 0);
>                //Set the size for simplification
>                char chararray[9000];
>                //Copy value into the char array
>                memcpy( chararray , temp , bytes);
>                CString testing;
>                //Iterate through all elements
>                for (int x = 0; x < 8999; x++)
>                {
>                int temp = chararray[x];
>                char temp2 = chararray[x];
>
>                //if the char is null or not supportted don't append it 
> to CString
>                if (temp < 1 || temp > 255)
>                {
>                //This gets hit a few times with negative int values
>                temp = temp;
>                }
>                else
>                {
>                //Append the char to the XML string
>                testing.AppendChar(temp2);
>                }
>                }
>
>                return success;
>
> At this point the CString testing contains on a portion of the characters of 
> the XML field which makes it unusable.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mastercam.com
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Pavel Ivanov]

Now, this is a very good test case and explanation what happens, except...

How did you check value of CString testing after iterating though the
entire char array? Did you look at it in your debugger? Maybe your
debugger is not capable of showing CString contents longer than 2048
symbols? The value of string testing you gave with Windows-style line
ends gives me 2040 bytes, indentation of the next line is 8 bytes, so
if total limit is 2048 bytes then you won't see any contents of the
next line...

Can you output contents of CString testing using printf() and see if
it shows the same 2040 bytes?


Pavel


On Wed, Dec 21, 2011 at 1:34 PM, Jacob A. Camp  wrote:
> So delving further into this has gotten me nothing else; this is the best I 
> could do to simplify this to understandable steps.
>
> (I'm new to this service so no idea if attachments are allows so sorry about 
> the length in advance)
>
> I have this code:
>
>                sqlite3_stmt* state = NULL;
>                CString sql = _T("SELECT RelationshipHierarchyXML FROM 
> TlAssembly WHERE id=?");
>                //Query the database
>                RawSQLTlID (sql, id, );
>                bytes = sqlite3_column_bytes(state, 0); //This line returns 
> 8955
>                //Try getting data as blob
>                const void * temp = sqlite3_column_blob(state, 0);
>                //Set the size for simplification
>                char chararray[9000];
>                //Copy value into the char array
>                memcpy( chararray , temp , bytes);
>                CString testing;
>                //Iterate through all elements
>                for (int x = 0; x < 8999; x++)
>                {
>                int temp = chararray[x];
>                char temp2 = chararray[x];
>
>                //if the char is null or not supportted don't append it to 
> CString
>                if (temp < 1 || temp > 255)
>                {
>                //This gets hit a few times with negative int values
>                temp = temp;
>                }
>                else
>                {
>                //Append the char to the XML string
>                testing.AppendChar(temp2);
>                }
>                }
>
>                return success;
>
> At this point the CString testing contains on a portion of the characters of 
> the XML field which makes it unusable.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] command line to get number of tables in sqlite

[Roger Andersson]

Somthing like
sqlite3  sqlite.file
sqlite> select count(*) from sqlite_master where type = 'table';

/Roger
On 12/21/11 19:32, smallboat wrote:

Hello,

I have a sqlite file. I would like to open it and know how many tables in it.

What is the command line to open a sqlite file and get to know how many tables 
in it? Thanks.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] command line to get number of tables in sqlite

[Tim Streater]

On 21 Dec 2011 at 18:32, smallboat  wrote: 

> I have a sqlite file. I would like to open it and know how many tables in it.
>
> What is the command line to open a sqlite file and get to know how many tables
> in it? Thanks.

See:

http://www.sqlite.org/sqlite.html

and:

http://www.sqlite.org/faq.html#q7

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Jacob A. Camp]

So delving further into this has gotten me nothing else; this is the best I 
could do to simplify this to understandable steps.

(I'm new to this service so no idea if attachments are allows so sorry about 
the length in advance)

I have this code:

sqlite3_stmt* state = NULL;
CString sql = _T("SELECT RelationshipHierarchyXML FROM 
TlAssembly WHERE id=?");
//Query the database
RawSQLTlID (sql, id, );
bytes = sqlite3_column_bytes(state, 0); //This line returns 8955
//Try getting data as blob
const void * temp = sqlite3_column_blob(state, 0);  
//Set the size for simplification
char chararray[9000];
//Copy value into the char array
memcpy( chararray , temp , bytes);  
CString testing;
//Iterate through all elements
for (int x = 0; x < 8999; x++)
{
int temp = chararray[x];
char temp2 = chararray[x];

//if the char is null or not supportted don't append it to 
CString
if (temp < 1 || temp > 255)
{
//This gets hit a few times with negative int values
temp = temp;
}
else
{
//Append the char to the XML string
testing.AppendChar(temp2);
}
}

return success;

At this point the CString testing contains on a portion of the characters of 
the XML field which makes it unusable.

Supporting Code:

bool TlToolLibrarySQL::RawSQLTlID (const CString& sql, const 
TlID& id, sqlite3_stmt** statement, UINT expectedResult)
{
bool found = false;

// Pointer to the uncompiled portion of the SQL query
const char* unused = NULL;

// Prepare the SQL statement
int result = sqlite3_prepare_v2 (m_db, sql, -1, statement, 
);

// Bind the GUID ID to the statement
GUID tlid = id;
int result1 = sqlite3_bind_blob (*statement, 1, , 
sizeof(tlid), SQLITE_TRANSIENT);

// If we prepared and got a row back, we are successful!
if (SQLITE_OK == result && SQLITE_OK == result1)
{
if (expectedResult == sqlite3_step (*statement))
{
found = true;
}
else
{
//Try again with a String GUID instead
sqlite3_reset (*statement);
sqlite3_clear_bindings (*statement);
result1 = sqlite3_bind_text (*statement, 1, 
id.ToString(), -1, SQLITE_TRANSIENT);
if (SQLITE_OK == result1 && expectedResult == 
sqlite3_step (*statement))
{
found = true;
}
}
}
return found;
}

Value of CString testing after iterating though the entire char array:

"
http://www.w3.org/2001/XMLSchema-instance; z:Id="i1" 
xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/; 
xmlns="http://schemas.datacontract.org/2004/07/MDM.MachineDefinition.Models;>
  true
  
I
1
0
0
0
0
1
0
0
0
0
1
0
0
0
0
1
  
  Machine
  
  
I
1
0
0
0
0
1
0
0
0
0
1
0
0
0
0
1
  
  

  true
  
I
1
0
0
0
0
1
0
0
0
0
1
0
0
0
0
1
  
   3/8 DRILL
  
  
I
1
0
0
0
0
1
0
0
0
0
1
0
0

CSV Dump of the TlAssembly Table:

ID,Name,Description,MainHolder,MainTool,ToolNumber,MachineGroup,RelationshipHierarchyXML,Stickout
{9B6CB288-E5E0-4A8A-88C8-20E3A09A7429}, 3/8 DRILL 
Assembly,"",{351FDBA2-B09F-4264-8227-8FE41C26865C},{02BBEE7C-7856-4E3C-9FDB-FFFB17050197},128,0,"?
http://www.w3.org/2001/XMLSchema-instance"; z:Id=""i1"" 
xmlns:z=""http://schemas.microsoft.com/2003/10/Serialization/"; 
xmlns=""http://schemas.datacontract.org/2004/07/MDM.MachineDefinition.Models";>
  true
  
I
1
0
0
0
0
1
0
0
0
0
1
0
0
0
0
1
  
  Machine
  
  
I
1
0
0
0
0
1
0
0

[sqlite] command line to get number of tables in sqlite

[smallboat]

Hello, 

I have a sqlite file. I would like to open it and know how many tables in it. 

What is the command line to open a sqlite file and get to know how many tables 
in it? Thanks.

Regards,
Joe

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Pavel Ivanov]

On Wed, Dec 21, 2011 at 10:41 AM, Jacob A. Camp
 wrote:
> I have a line of code that executes that line:
>
> const unsigned char * temp2 = sqlite3_column_text(state, 0);
>
> This queries the database and after the call is complete I pass this value to 
> another function. This function then fails because temp2 points to a location 
> that contains the incomplete text. I then can view this text using my 
> debugger and I can see that the value has been clipped and the XML is invalid.

Let me ask Igor's questions once again. How does your function
understand that text is incomplete? Your debugger can show you
incomplete string because temp2 is not a zero-terminated string, it
can contain zeros in the middle.

> Trying to access memory locations outside whatever is allocated by the return 
> of sqlite3_column_text sounds like it would result in undefined behavior?

How do you know how much memory sqlite3_column_text allocated for the return?

> sqlite3_column_bytes returns 8960 if that's helpful.

Hint: the above number means that sqlite3_column_text allocated
exactly 8960 bytes for the text it returned. So whatever you use to
define that returned text is incomplete gives you incorrect
information.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Black, Michael (IS)]

What does strlen() tell you on the XML before you put it in the table?



Can you boil all this down to one record in a table, and some code so we can 
all see what's going on?

Do a .dump of the table and post the code.



See if you can reproduce it in a simple example.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jacob A. Camp [jacob.c...@mastercam.com]
Sent: Wednesday, December 21, 2011 9:41 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] sqlite3_column_text() returning partial results

I have a line of code that executes that line:

const unsigned char * temp2 = sqlite3_column_text(state, 0);

This queries the database and after the call is complete I pass this value to 
another function. This function then fails because temp2 points to a location 
that contains the incomplete text. I then can view this text using my debugger 
and I can see that the value has been clipped and the XML is invalid.

Trying to access memory locations outside whatever is allocated by the return 
of sqlite3_column_text sounds like it would result in undefined behavior?

sqlite3_column_bytes returns 8960 if that's helpful.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, December 21, 2011 10:29 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_column_text() returning partial results

Jake  wrote:
> For the previous post asking what occurs after 2030, there's nothing

What do you mean, nothing? What exactly happens when you try to access p[2030]?

> because the
> returned value is only allocated for those characters.

What makes you believe this?
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mastercam.com
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Jacob A. Camp]

I have a line of code that executes that line:

const unsigned char * temp2 = sqlite3_column_text(state, 0);

This queries the database and after the call is complete I pass this value to 
another function. This function then fails because temp2 points to a location 
that contains the incomplete text. I then can view this text using my debugger 
and I can see that the value has been clipped and the XML is invalid. 

Trying to access memory locations outside whatever is allocated by the return 
of sqlite3_column_text sounds like it would result in undefined behavior?

sqlite3_column_bytes returns 8960 if that's helpful.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, December 21, 2011 10:29 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_column_text() returning partial results

Jake  wrote:
> For the previous post asking what occurs after 2030, there's nothing

What do you mean, nothing? What exactly happens when you try to access p[2030]?

> because the
> returned value is only allocated for those characters.

What makes you believe this?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mastercam.com
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Igor Tandetnik]

Jake  wrote:
> For the previous post asking what occurs after 2030, there's nothing

What do you mean, nothing? What exactly happens when you try to access p[2030]?

> because the
> returned value is only allocated for those characters.

What makes you believe this?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_column_text() returning partial results

[Jake]

Simon Slavin  writes:

> 
> 
> On 21 Dec 2011, at 1:31pm, nobre wrote:
> 
> > Is there any chance you are storing a \0 char inside the xml ?
> 
> Or that you are mixing 8-bit and 16-bit Unicode in such a way that one of 
> your 
routines thinks that it has read a
> 0x00 termination character ?
> 
> Simon.
> 


>From what I looked at so far, by taking the text and putting it into a basic 
text editor so I could see any special characters that would have been added. I 
didn't see any \0 or other odd things. 

I would check the mixing 8-bit and 16-bit Unicode but I need to research how to 
even figure something like that out.

For the previous post asking what occurs after 2030, there's nothing because 
the 
returned value is only allocated for those characters. And regarding the 
recycling of the memory the actual return result of the function call after its 
complete is the same as when i'm viewing it while debugging.

I can post the stored value and the value the call to sqlite3_column_text 
returns if that would be helpful (its just long so I wont post it).

Jake

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

The problem can be fixed by variables bindings patch:
http://sqlite.mobigroup.ru/wiki?name=tclsqlite
I think, you can do same for you lang.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Simon Slavin]

On 21 Dec 2011, at 1:31pm, nobre wrote:

> Is there any chance you are storing a \0 char inside the xml ?

Or that you are mixing 8-bit and 16-bit Unicode in such a way that one of your 
routines thinks that it has read a 0x00 termination character ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[nobre]

Is there any chance you are storing a \0 char inside the xml ? 

Jacob A. Camp wrote:
> 
> Hello,
> 
> I've been looking into an issue that a few of our programmers have looked
> at as well and it left us all wondering. Basically, our database has a
> VARCHAR column that has an XML file written to it when the object is
> manipulated. I can use a tool to view the database file and I can ensure
> that the entire field is filled out correctly and the XML is correctly
> formed.
> 
> The field in the database contains 8955 characters and when I execute the
> sqlite3_column_text() on that specific column to access the data, the
> const unsigned char* that's returned only contains 2030 characters and the
> XML file that I'm trying to reconstruct from it then becomes unusable. I
> even attempted digging into the SQLite class and it seems that this value
> is obtained from the official function calls.
> 
> In sqlite3.c:
> 
> static Mem *columnMem(sqlite3_stmt *pStmt, int i){
>   Vdbe *pVm;
>   int vals;
>   Mem *pOut;
> 
>   pVm = (Vdbe *)pStmt;
>   if( pVm && pVm->pResultSet!=0 && inResColumn && i>=0 ){
> sqlite3_mutex_enter(pVm->db->mutex);
> vals = sqlite3_data_count(pStmt);
> pOut = >pResultSet[i];
> 
> After the last line has been executed pOut contains members z and zMalloc
> which both contain the same memory location that points to the char* that
> contains the first 2030 characters and none of the rest that are stored in
> the VARCHAR field.
> 
> Is there some limit to the amount of data that can be returned by this
> function? I read over most of the documentation and didn't see it
> mentioning any sort of restriction.
> 
> Thanks in advance,
> --Jake
> 
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
> 
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
> 
> www.mastercam.com
> **
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_column_text%28%29-returning-partial-results-tp33016613p33016758.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_text() returning partial results

[Igor Tandetnik]

Jacob A. Camp  wrote:
> The field in the database contains 8955 characters and when I execute the 
> sqlite3_column_text() on that specific column to access
> the data, the const unsigned char* that's returned only contains 2030 
> characters

What do you mean by that? sqlite3_column_text doesn't provide any length 
delimiter. What's in p[2030], p[2031] and so on (where p is a pointer obtained 
from sqlite3_column_text)?

Are you examining the string immediately after calling sqlite3_column_text? 
Some sqlite3_* calls invalidate the pointer (by reusing or freeing the memory 
behind it).

What does sqlite3_column_bytes return for this column?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_column_text() returning partial results

[Jacob A. Camp]

Hello,

I've been looking into an issue that a few of our programmers have looked at as 
well and it left us all wondering. Basically, our database has a VARCHAR column 
that has an XML file written to it when the object is manipulated. I can use a 
tool to view the database file and I can ensure that the entire field is filled 
out correctly and the XML is correctly formed.

The field in the database contains 8955 characters and when I execute the 
sqlite3_column_text() on that specific column to access the data, the const 
unsigned char* that's returned only contains 2030 characters and the XML file 
that I'm trying to reconstruct from it then becomes unusable. I even attempted 
digging into the SQLite class and it seems that this value is obtained from the 
official function calls.

In sqlite3.c:

static Mem *columnMem(sqlite3_stmt *pStmt, int i){
  Vdbe *pVm;
  int vals;
  Mem *pOut;

  pVm = (Vdbe *)pStmt;
  if( pVm && pVm->pResultSet!=0 && inResColumn && i>=0 ){
sqlite3_mutex_enter(pVm->db->mutex);
vals = sqlite3_data_count(pStmt);
pOut = >pResultSet[i];

After the last line has been executed pOut contains members z and zMalloc which 
both contain the same memory location that points to the char* that contains 
the first 2030 characters and none of the rest that are stored in the VARCHAR 
field.

Is there some limit to the amount of data that can be returned by this 
function? I read over most of the documentation and didn't see it mentioning 
any sort of restriction.

Thanks in advance,
--Jake

**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mastercam.com
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users