Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?
On Jan 4, 2008, at 7:57 AM, Jerry Krinock wrote:
I need to read an sqlite database generated by others. So I wrote
an outer loop which steps through the rows of a table using
sqlite3_step, and an inner loop which steps through the columns.
The inner loop finds the type using sqlite3_column_type(), then
'switches' to get the value using the appropriate
sqlite3_column_X() function.
It works fine if, when encountering an SQLITE_INTEGER type, I use
sqlite_column_int64() to get the data.
Internally, integers are all 64-bits. If you call sqlite3_column_int()
to retrieve a value, it is truncated to 32-bits before returning it.
So to be safe, you're better off always using sqlite3_column_int64().
Dan.
I don't know whether or not I'm just "lucky" that the application
which wrote the database uses 64 bit for all of its integers? If
so, what if someone throws a 32-bit integer at me someday? How can
I tell whether integer data objects in a table are 32 or 64 bit?
The column specifications I get from pragma_table_info() are
likewise uninformative, saying simply type=INTEGER.
Thanks again,
Jerry Krinock
// Method Implementation (Objective-C for Mac OS X)
- (NSArray*)dicsOfRowsInTable:(NSString*)table {
// Will return nil if fails, empty array if no rows
void* db = [self db] ;
//char* errMsg = NULL ;
int result ;
NSString* statement = [[NSString alloc] initWithFormat:@"SELECT
* FROM '%@'", table] ;
// Compile the statement into a virtual machine
sqlite3_stmt* preparedStatement ;
result = sqlite3_prepare(db, [statement UTF8String], -1,
, NULL) ;
[statement release] ;
NSArray* output = nil ;
if (result != SQLITE_OK) {
[self showError:"prepare" from:11 code:result] ;
}
else {
NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
NSArray* keys = [self keysInTable:table] ;
int nColumns = [keys count] ;
while (result = sqlite3_step(preparedStatement) ==
SQLITE_ROW) {
NSMutableDictionary* rowDic = [[NSMutableDictionary
alloc] init] ;
int iColumn ;
for (iColumn= 0; iColumn 0) {
pFirstByte = sqlite3_column_blob
(preparedStatement, iColumn) ;
object = [[NSData alloc]
initWithBytes:pFirstByte length:nBytes] ;
}
break ;
case SQLITE_INTEGER:
intValue = sqlite3_column_int64
(preparedStatement, iColumn) ;
object = [NSNumber
numberWithLongLong:intValue] ;
break ;
case SQLITE_TEXT:
// "Strings returned by sqlite3_column_text
() and sqlite3_column_text16(),
// even zero-length strings, are always
zero terminated."
// So, we ignore the length and just
convert it
utf8String = sqlite3_column_text
(preparedStatement, iColumn) ;
object = [NSString stringWithUTF8String:
(char*)utf8String] ;
break ;
case SQLITE_FLOAT:
doubleValue = sqlite3_column_double
(preparedStatement, iColumn) ;
object = [NSNumber
numberWithDouble:doubleValue] ;
break ;
case SQLITE_NULL:
default:
// Just leave object nil, will replace with
[NSNull null] soon.
;
}
if (object == nil) {
object = [NSNull null] ;
}
[rowDic setObject:object forKey:[keys
objectAtIndex:iColumn]] ;
}
NSDictionary* rowDicCopy = [rowDic
RE: [sqlite] Date Problems
> I could accept 28 Feb or 1 Mar as a
> reasonable answer and I can make that point to my users.
365/12 = 30.4
2006/03/31 - 30 days = 2006/03/01
just subtract 30 days and be done with it. i think you can justify that
to your customers.
Ron Wilson, Senior Engineer, MPR Associates, 518.831.7546
-Original Message-
From: Moodie Keith [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 9:51 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Date Problems
I think the logic might have been to subtract the number of days in the
previous month from the current date. E.g.
2007/03/25 -28 (days in Feb) will give 2007/02/25 (spot on)
2007/10/31 -30 (days in Sept) will give 2007/10/01 (as good as you can
get)
2007/09/30 -31 (days in Aug) will give 2007/08/30 (spot on)
So looks like it works well in all cases except for the last 2 days in
March
For adding a month the logic would be to add the number of days in the
month (of the date you are adding to). E.g. if date was 2007/09/30 then
add 30 days -> 2007/10/30
Two solutions I can see to your problem
A) special code to handle Feb
B) always subtract 30 days if subtracting a month.
However that means that you will have to always add 30 days if adding
a month (which is not perfect) or have a situation where ( 'date' - 1
month) + 1 month does not give you 'date' as its result.
Hope this helps :)
PS Unfortunately dates are messy, but only way to fix them is to change
the spin of the Earth!
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Friday, 4 January 2008 12:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date Problems
Look at the Sqlite date functions. File date.c describes them.
You may find that a custom function gives you exactly what you want.
[EMAIL PROTECTED] wrote:
> Hi Kees
>
> Many thanks for your quick reply, but it doesn't give me the date i
> want.
>
> 2006/03/31 - 1 month should be 2006/02/28
>
> whereas
> select date('2006-03-31', 'start of month','-1 month') obviously gives
> me 2006/02/01
>
>
>
> I need to be able to subtract 1 month from not just a single
expression,
> but from a 100s of dates that are stored in a table.
>
>
> Richard - I also just spotted your email. Thanks, I agree, it is
fuzzy.
> However :
>
> 2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a
> reasonable answer and I can make that point to my users.
>
> 03 March is not reasonable, I can't think of any logic that would give
> me that answer and I'm not able to make any case.
>
>
> Thanks
> Craig
>
>
> -Original Message-
> From: Kees Nuyt [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 03, 2008 9:25 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date Problems
>
>
> Hi Craig,
>
> On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote:
>
>> Hi
>>
>> Can somebody give any explain to this please.
>>
>> sqlite> select date("2006-03-31");
>> 2006-03-31
>> --> correct
>>
>> sqlite> select date("2006-03-31", "-1 month");
>> 2006-03-03
>> --> not correct
>>
>>
>> Can anyone confirm? Any suggestions / workarounds greatfully
received!
>
> Confirmed.
>
> Better:
> select date('2006-03-31', 'start of month','-1 month');
>
>> Many thanks
>
> HTH
>
>> Craig
> --
> ( Kees Nuyt
> )
> c[_]
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
> Visit our website at http://www.ubs.com
>
> This message contains confidential information and is intended only
> for the individual named. If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail. Please
> notify the sender immediately by e-mail if you have received this
> e-mail by mistake and delete this e-mail from your system.
>
> E-mails are not encrypted and cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The sender
> therefore does not accept liability for any errors or omissions in the
> contents of this message which arise as a result of e-mail
transmission.
> If verification is required please request a hard-copy version. This
> message is provided for informational purposes and should not be
> construed as a solicitation or offer to buy or sell any securities
> or related financial instruments.
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?
On 2008 Jan, 03, at 17:21, Kees Nuyt wrote: If I understand the info at http://www.sqlite.org/c3ref/c_blob.html well, the INTEGER is always a 64-bit signed integer. Internally, integers are compressed, so they don't occupy eight bytes all the time. sqlite3_column_int64(); will always return a sqlite3_int64. So, no need to worry. Thanks, Kees (and John S. too). So, apparently the function sqlite3_column_int() is an alternative which can be used, under "normal" circumstances, when you know the value is small enough, and want to assign it to a int. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date Problems
I think the logic might have been to subtract the number of days in the
previous month from the current date. E.g.
2007/03/25 -28 (days in Feb) will give 2007/02/25 (spot on)
2007/10/31 -30 (days in Sept) will give 2007/10/01 (as good as you can
get)
2007/09/30 -31 (days in Aug) will give 2007/08/30 (spot on)
So looks like it works well in all cases except for the last 2 days in
March
For adding a month the logic would be to add the number of days in the
month (of the date you are adding to). E.g. if date was 2007/09/30 then
add 30 days -> 2007/10/30
Two solutions I can see to your problem
A) special code to handle Feb
B) always subtract 30 days if subtracting a month.
However that means that you will have to always add 30 days if adding
a month (which is not perfect) or have a situation where ( 'date' - 1
month) + 1 month does not give you 'date' as its result.
Hope this helps :)
PS Unfortunately dates are messy, but only way to fix them is to change
the spin of the Earth!
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Friday, 4 January 2008 12:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date Problems
Look at the Sqlite date functions. File date.c describes them.
You may find that a custom function gives you exactly what you want.
[EMAIL PROTECTED] wrote:
> Hi Kees
>
> Many thanks for your quick reply, but it doesn't give me the date i
> want.
>
> 2006/03/31 - 1 month should be 2006/02/28
>
> whereas
> select date('2006-03-31', 'start of month','-1 month') obviously gives
> me 2006/02/01
>
>
>
> I need to be able to subtract 1 month from not just a single
expression,
> but from a 100s of dates that are stored in a table.
>
>
> Richard - I also just spotted your email. Thanks, I agree, it is
fuzzy.
> However :
>
> 2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a
> reasonable answer and I can make that point to my users.
>
> 03 March is not reasonable, I can't think of any logic that would give
> me that answer and I'm not able to make any case.
>
>
> Thanks
> Craig
>
>
> -Original Message-
> From: Kees Nuyt [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 03, 2008 9:25 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date Problems
>
>
> Hi Craig,
>
> On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote:
>
>> Hi
>>
>> Can somebody give any explain to this please.
>>
>> sqlite> select date("2006-03-31");
>> 2006-03-31
>> --> correct
>>
>> sqlite> select date("2006-03-31", "-1 month");
>> 2006-03-03
>> --> not correct
>>
>>
>> Can anyone confirm? Any suggestions / workarounds greatfully
received!
>
> Confirmed.
>
> Better:
> select date('2006-03-31', 'start of month','-1 month');
>
>> Many thanks
>
> HTH
>
>> Craig
> --
> ( Kees Nuyt
> )
> c[_]
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
> Visit our website at http://www.ubs.com
>
> This message contains confidential information and is intended only
> for the individual named. If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail. Please
> notify the sender immediately by e-mail if you have received this
> e-mail by mistake and delete this e-mail from your system.
>
> E-mails are not encrypted and cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The sender
> therefore does not accept liability for any errors or omissions in the
> contents of this message which arise as a result of e-mail
transmission.
> If verification is required please request a hard-copy version. This
> message is provided for informational purposes and should not be
> construed as a solicitation or offer to buy or sell any securities
> or related financial instruments.
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
The information in this email together with any attachments is
intended only for the person or entity to which it is addressed
and may contain confidential and/or privileged material.
Any form of review, disclosure, modification, distribution
and/or publication of this email message is prohibited, unless
as a necessary part of Departmental business.
If you have received this message in error, you are asked to
inform the sender as quickly as
Re: [sqlite] Date Problems
Look at the Sqlite date functions. File date.c describes them.
You may find that a custom function gives you exactly what you want.
[EMAIL PROTECTED] wrote:
Hi Kees
Many thanks for your quick reply, but it doesn't give me the date i
want.
2006/03/31 - 1 month should be 2006/02/28
whereas
select date('2006-03-31', 'start of month','-1 month') obviously gives
me 2006/02/01
I need to be able to subtract 1 month from not just a single expression,
but from a 100s of dates that are stored in a table.
Richard - I also just spotted your email. Thanks, I agree, it is fuzzy.
However :
2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a
reasonable answer and I can make that point to my users.
03 March is not reasonable, I can't think of any logic that would give
me that answer and I'm not able to make any case.
Thanks
Craig
-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 9:25 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date Problems
Hi Craig,
On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote:
Hi
Can somebody give any explain to this please.
sqlite> select date("2006-03-31");
2006-03-31
--> correct
sqlite> select date("2006-03-31", "-1 month");
2006-03-03
--> not correct
Can anyone confirm? Any suggestions / workarounds greatfully received!
Confirmed.
Better:
select date('2006-03-31', 'start of month','-1 month');
Many thanks
HTH
Craig
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Date Problems
regarding: >>2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a reasonable answer and I can make that point to my users. >>03 March is not reasonable, I can't think of any logic that would give me that answer and I'm not able to make any case. I believe the logic used is to subtract one from the month of 2006-March and obtain 2006-February, and use the same date of "31"."2006/02/31" is then interpreted to mean "the date 3 days following 2006/02/28, or 2006/03/03" -- just as one might consider it reasonable when presented with "april 31st" to interpret it as "May 1st". I agree it's considerably more odd in the first case, but since "subtracting x months" has no standardized meaning, everything depends upon the specific use. What will the end use be for the subtracted date? If it's necessary to perform the date manipulation in SQL, and if you can't use the existing logic, you could integrate your own date routines, perhaps drawing mostly from the existing code. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?
On Thu, 3 Jan 2008 16:57:12 -0800, Jerry Krinock <[EMAIL PROTECTED]> wrote: >I need to read an sqlite database generated by others. So I wrote an >outer loop which steps through the rows of a table using sqlite3_step, >and an inner loop which steps through the columns. The inner loop >finds the type using sqlite3_column_type(), then 'switches' to get the >value using the appropriate sqlite3_column_X() function. > >It works fine if, when encountering an SQLITE_INTEGER type, I use >sqlite_column_int64() to get the data. > >I don't know whether or not I'm just "lucky" that the application >which wrote the database uses 64 bit for all of its integers? If so, >what if someone throws a 32-bit integer at me someday? How can I tell >whether integer data objects in a table are 32 or 64 bit? The column >specifications I get from pragma_table_info() are likewise >uninformative, saying simply type=INTEGER. > >Thanks again, > >Jerry Krinock If I understand the info at http://www.sqlite.org/c3ref/c_blob.html well, the INTEGER is always a 64-bit signed integer. Internally, integers are compressed, so they don't occupy eight bytes all the time. sqlite3_column_int64(); will always return a sqlite3_int64. So, no need to worry. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?
An integer is always 64 bits.
Jerry Krinock wrote:
I need to read an sqlite database generated by others. So I wrote an
outer loop which steps through the rows of a table using sqlite3_step,
and an inner loop which steps through the columns. The inner loop finds
the type using sqlite3_column_type(), then 'switches' to get the value
using the appropriate sqlite3_column_X() function.
It works fine if, when encountering an SQLITE_INTEGER type, I use
sqlite_column_int64() to get the data.
I don't know whether or not I'm just "lucky" that the application which
wrote the database uses 64 bit for all of its integers? If so, what if
someone throws a 32-bit integer at me someday? How can I tell whether
integer data objects in a table are 32 or 64 bit? The column
specifications I get from pragma_table_info() are likewise
uninformative, saying simply type=INTEGER.
Thanks again,
Jerry Krinock
// Method Implementation (Objective-C for Mac OS X)
- (NSArray*)dicsOfRowsInTable:(NSString*)table {
// Will return nil if fails, empty array if no rows
void* db = [self db] ;
//char* errMsg = NULL ;
int result ;
NSString* statement = [[NSString alloc] initWithFormat:@"SELECT *
FROM '%@'", table] ;
// Compile the statement into a virtual machine
sqlite3_stmt* preparedStatement ;
result = sqlite3_prepare(db, [statement UTF8String], -1,
, NULL) ;
[statement release] ;
NSArray* output = nil ;
if (result != SQLITE_OK) {
[self showError:"prepare" from:11 code:result] ;
}
else {
NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
NSArray* keys = [self keysInTable:table] ;
int nColumns = [keys count] ;
while (result = sqlite3_step(preparedStatement) == SQLITE_ROW) {
NSMutableDictionary* rowDic = [[NSMutableDictionary alloc]
init] ;
int iColumn ;
for (iColumn= 0; iColumn 0) {
pFirstByte =
sqlite3_column_blob(preparedStatement, iColumn) ;
object = [[NSData alloc]
initWithBytes:pFirstByte length:nBytes] ;
}
break ;
case SQLITE_INTEGER:
intValue =
sqlite3_column_int64(preparedStatement, iColumn) ;
object = [NSNumber numberWithLongLong:intValue] ;
break ;
case SQLITE_TEXT:
// "Strings returned by sqlite3_column_text()
and sqlite3_column_text16(),
// even zero-length strings, are always zero
terminated."
// So, we ignore the length and just convert it
utf8String =
sqlite3_column_text(preparedStatement, iColumn) ;
object = [NSString
stringWithUTF8String:(char*)utf8String] ;
break ;
case SQLITE_FLOAT:
doubleValue =
sqlite3_column_double(preparedStatement, iColumn) ;
object = [NSNumber numberWithDouble:doubleValue] ;
break ;
case SQLITE_NULL:
default:
// Just leave object nil, will replace with
[NSNull null] soon.
;
}
if (object == nil) {
object = [NSNull null] ;
}
[rowDic setObject:object forKey:[keys
objectAtIndex:iColumn]] ;
}
NSDictionary* rowDicCopy = [rowDic copy] ;
[rowDics addObject:rowDicCopy] ;
[rowDicCopy release] ;
}
output = [rowDics copy] ;
[rowDics release] ;
}
// Finalize the statement (this releases resources allocated by
sqlite3_prepare()
RE: [sqlite] Date Problems
Hi Kees
Many thanks for your quick reply, but it doesn't give me the date i
want.
2006/03/31 - 1 month should be 2006/02/28
whereas
select date('2006-03-31', 'start of month','-1 month') obviously gives
me 2006/02/01
I need to be able to subtract 1 month from not just a single expression,
but from a 100s of dates that are stored in a table.
Richard - I also just spotted your email. Thanks, I agree, it is fuzzy.
However :
2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a
reasonable answer and I can make that point to my users.
03 March is not reasonable, I can't think of any logic that would give
me that answer and I'm not able to make any case.
Thanks
Craig
-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 9:25 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date Problems
Hi Craig,
On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote:
>Hi
>
>Can somebody give any explain to this please.
>
>sqlite> select date("2006-03-31");
>2006-03-31
>--> correct
>
>sqlite> select date("2006-03-31", "-1 month");
>2006-03-03
>--> not correct
>
>
>Can anyone confirm? Any suggestions / workarounds greatfully received!
Confirmed.
Better:
select date('2006-03-31', 'start of month','-1 month');
>Many thanks
HTH
>Craig
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?
I need to read an sqlite database generated by others. So I wrote an
outer loop which steps through the rows of a table using sqlite3_step,
and an inner loop which steps through the columns. The inner loop
finds the type using sqlite3_column_type(), then 'switches' to get the
value using the appropriate sqlite3_column_X() function.
It works fine if, when encountering an SQLITE_INTEGER type, I use
sqlite_column_int64() to get the data.
I don't know whether or not I'm just "lucky" that the application
which wrote the database uses 64 bit for all of its integers? If so,
what if someone throws a 32-bit integer at me someday? How can I tell
whether integer data objects in a table are 32 or 64 bit? The column
specifications I get from pragma_table_info() are likewise
uninformative, saying simply type=INTEGER.
Thanks again,
Jerry Krinock
// Method Implementation (Objective-C for Mac OS X)
- (NSArray*)dicsOfRowsInTable:(NSString*)table {
// Will return nil if fails, empty array if no rows
void* db = [self db] ;
//char* errMsg = NULL ;
int result ;
NSString* statement = [[NSString alloc] initWithFormat:@"SELECT *
FROM '%@'", table] ;
// Compile the statement into a virtual machine
sqlite3_stmt* preparedStatement ;
result = sqlite3_prepare(db, [statement UTF8String], -1,
, NULL) ;
[statement release] ;
NSArray* output = nil ;
if (result != SQLITE_OK) {
[self showError:"prepare" from:11 code:result] ;
}
else {
NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
NSArray* keys = [self keysInTable:table] ;
int nColumns = [keys count] ;
while (result = sqlite3_step(preparedStatement) ==
SQLITE_ROW) {
NSMutableDictionary* rowDic = [[NSMutableDictionary
alloc] init] ;
int iColumn ;
for (iColumn= 0; iColumn 0) {
pFirstByte =
sqlite3_column_blob(preparedStatement, iColumn) ;
object = [[NSData alloc]
initWithBytes:pFirstByte length:nBytes] ;
}
break ;
case SQLITE_INTEGER:
intValue =
sqlite3_column_int64(preparedStatement, iColumn) ;
object = [NSNumber
numberWithLongLong:intValue] ;
break ;
case SQLITE_TEXT:
// "Strings returned by sqlite3_column_text()
and sqlite3_column_text16(),
// even zero-length strings, are always zero
terminated."
// So, we ignore the length and just convert it
utf8String =
sqlite3_column_text(preparedStatement, iColumn) ;
object = [NSString stringWithUTF8String:
(char*)utf8String] ;
break ;
case SQLITE_FLOAT:
doubleValue =
sqlite3_column_double(preparedStatement, iColumn) ;
object = [NSNumber
numberWithDouble:doubleValue] ;
break ;
case SQLITE_NULL:
default:
// Just leave object nil, will replace with
[NSNull null] soon.
;
}
if (object == nil) {
object = [NSNull null] ;
}
[rowDic setObject:object forKey:[keys
objectAtIndex:iColumn]] ;
}
NSDictionary* rowDicCopy = [rowDic copy] ;
[rowDics addObject:rowDicCopy] ;
[rowDicCopy release] ;
}
output = [rowDics copy] ;
[rowDics release] ;
}
// Finalize the statement (this releases resources allocated by
sqlite3_prepare()
result =
RE: [sqlite] a newbie
Hi Cristopher, Actually this will be updated throughout the software's cycle because we need to always add new airports and information relating to them. Yup! You got it running on Windows Vista. I will check out Active Python and see what I can't come up with then. Scott -Original Message- From: Christopher Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, January 03, 2008 4:45 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] a newbie > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Thu, 3 Jan 2008 15:21:47 -0600 > Subject: [sqlite] a newbie > > Hey guys, > > My name is Scott Berry and I am totally blind and a newbie to Sqlite. I > have two questions concerning Sqlite. The questions I have is I have an > Excel file which will be going in to the database for an aircraft program > for the blind I am making. I want to turn this (Excel spreadsheet) in to a > .CVS file comma delimited or tab delimited. I don't care which. I am using > Sqlite Admin from Sqlite.org to do my databases since I am not too > comfortable with the syntax yet. However, two questions come to mind: > > 1. Which of the delimited files .cvs with commas or tabbed would be best to > use. Depends on if you have either commas or tab characters within your data. Commas and tabs are both ASCII characters. > 2. I need to put my info in by state and airport is it wiser to make a > table for each state? I will be using this with in a Basic 4 PPC program. If this is your first outing, I would go with something simpler. If the data support it, you could simply keep things in the original Excel arrangement. > Any help would be very much appreciated. If you have Excel files implies you're running on a Windows machine? It might be too much to try at once, but you could consider installing ActivePython, http://activestate.com/Products/activepython/ which would then let you use ActiveX data objects to pull directly from the .xls file, and then populate the sqlite file. This would be faster than writing everything out to the hard drive. However, if the data migration is a one-time event, ignore what I have just said, as it is not worth the complexity. > Scott > Cheers, Christopher L. Smith Have you pondered the meaning of life today? _ Share life as it happens with the new Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_122007 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.17.13/1207 - Release Date: 1/2/2008 11:29 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.17.13/1207 - Release Date: 1/2/2008 11:29 AM - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] a newbie
> From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Thu, 3 Jan 2008 15:21:47 -0600 > Subject: [sqlite] a newbie > > Hey guys, > > My name is Scott Berry and I am totally blind and a newbie to Sqlite. I > have two questions concerning Sqlite. The questions I have is I have an > Excel file which will be going in to the database for an aircraft program > for the blind I am making. I want to turn this (Excel spreadsheet) in to a > .CVS file comma delimited or tab delimited. I don't care which. I am using > Sqlite Admin from Sqlite.org to do my databases since I am not too > comfortable with the syntax yet. However, two questions come to mind: > > 1. Which of the delimited files .cvs with commas or tabbed would be best to > use. Depends on if you have either commas or tab characters within your data. Commas and tabs are both ASCII characters. > 2. I need to put my info in by state and airport is it wiser to make a > table for each state? I will be using this with in a Basic 4 PPC program. If this is your first outing, I would go with something simpler. If the data support it, you could simply keep things in the original Excel arrangement. > Any help would be very much appreciated. If you have Excel files implies you're running on a Windows machine? It might be too much to try at once, but you could consider installing ActivePython, http://activestate.com/Products/activepython/ which would then let you use ActiveX data objects to pull directly from the .xls file, and then populate the sqlite file. This would be faster than writing everything out to the hard drive. However, if the data migration is a one-time event, ignore what I have just said, as it is not worth the complexity. > Scott > Cheers, Christopher L. Smith Have you pondered the meaning of life today? _ Share life as it happens with the new Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_122007
[sqlite] a newbie
Hey guys, My name is Scott Berry and I am totally blind and a newbie to Sqlite. I have two questions concerning Sqlite. The questions I have is I have an Excel file which will be going in to the database for an aircraft program for the blind I am making. I want to turn this (Excel spreadsheet) in to a .CVS file comma delimited or tab delimited. I don't care which. I am using Sqlite Admin from Sqlite.org to do my databases since I am not too comfortable with the syntax yet. However, two questions come to mind: 1. Which of the delimited files .cvs with commas or tabbed would be best to use. 2. I need to put my info in by state and airport is it wiser to make a table for each state? I will be using this with in a Basic 4 PPC program. Any help would be very much appreciated. Scott No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.17.13/1207 - Release Date: 1/2/2008 11:29 AM - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
In a threaded environment the simple and effective solution is to synchronize your transactions with a mutex. You lose a little possible concurrency but if you do not need it you simplify the logic no end and have a more robust application. Using pthreads you can improve a little by using read and write locks, a sophistication on a simple mutex. Richard Klein wrote: Requiring the second transaction to complete first is expected in terms of SQLIte's concurrency system. So in terms of using SQLite, I need to close the entire transaction and restart it when I get a "database locked" return code in a writer thread? It's not enough to just retry the commit in a little while? You don't need to close the connection, but you do need to ROLLBACK the transaction, unless you have some sort of a priori knowledge that the second transaction will not try to write to the database. In such a case, the second transaction will not try to acquire the RESERVED lock already held by the first transaction, and so the second transaction will eventually run to completion. In such a scenario, the first transaction can sit in a busy wait loop (sleep for a bit, then retry the COMMIT) until the COMMIT succeeds. However, if the second transaction will (or might) try to write to the database, you must ROLLBACK the first transaction, sleep for a bit, and restart the first transaction. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
On 1/2/08, Lior Okman <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > Requiring the second transaction to complete first is expected in > > terms of SQLIte's concurrency system. > So in terms of using SQLite, I need to close the entire transaction and > restart it when I get a "database locked" return code in a writer > thread? It's not enough to just retry the commit in a little while? It's safe to retry a commit. It may not be safe to retry a writing statement, depending on the presence of other writers. These two messages should help explain what you need to consider to avoid deadlocks: http://www.mail-archive.com/sqlite-users@sqlite.org/msg27284.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg28638.html > Wouldn't it be more intuitive to allow the single handle holding the > RESERVED lock to finish? Right now, the SQLite behaviour allows only the > serialized isolation level. Making this change would make the isolation > level be more like "read committed". You could get that behavior now by simply not using an explicit transaction in the reader. Actual "read committed" isolation support comes in when there are concurrent writers, so one transaction can see its own changes as well as the changes of others that have committed in parallel. Keep in mind, SQLite has no central transaction arbiter managing the file; its concurrency is implemented in terms of OS-level file locks. In order to implement parallel writers at any isolation level, a writer would need to somehow distinguish its changes from those of other writers in progress. That makes the act of committing itself, as well as crash recovery, much more complex. It also has to deal with potential conflicts on pending changes, and with "read committed" as an option, it's complicated even more by transactions using a mix of isolation levels. A very difficult kind of change. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
Requiring the second transaction to complete first is expected in terms of SQLIte's concurrency system. So in terms of using SQLite, I need to close the entire transaction and restart it when I get a "database locked" return code in a writer thread? It's not enough to just retry the commit in a little while? You don't need to close the connection, but you do need to ROLLBACK the transaction, unless you have some sort of a priori knowledge that the second transaction will not try to write to the database. In such a case, the second transaction will not try to acquire the RESERVED lock already held by the first transaction, and so the second transaction will eventually run to completion. In such a scenario, the first transaction can sit in a busy wait loop (sleep for a bit, then retry the COMMIT) until the COMMIT succeeds. However, if the second transaction will (or might) try to write to the database, you must ROLLBACK the first transaction, sleep for a bit, and restart the first transaction. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_CORRUPT error
On Thu, 3 Jan 2008 13:52:03 -0500, "Griggs, Donald" <[EMAIL PROTECTED]> wrote: >Maybe this is implied, but you might also try to .DUMP tables >*invididually* if .DUMP'ing the entire database fails. I didn't think of that, but yes, that is a good suggestion. I hope it helps the original poster. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_CORRUPT error
Regarding: >>I suspect the answer is no, but is there any way to salvage any of the data? >You could try the .dump command in the command line tool, but I'm afraid you're out of luck. Maybe this is implied, but you might also try to .DUMP tables *invididually* if .DUMP'ing the entire database fails. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_CORRUPT error
Hi Doug,
On Wed, 2 Jan 2008 21:47:45 -0600, "Doug" <[EMAIL PROTECTED]>
wrote:
>I have a customer that has a database that has somehow become corrupted.
>I'm fairly certain he was on v3.4.1 but I'll double check. The database
>isn't completely bad, I can look at the master table and one of the two
>tables in the database. But if the second table is touched SQLITE_CORRUPT
>is returned. Running sqlite3_analyzer returns the following (StatData is
>the second table)
>
>Analyzing table StatData...
>
>ERROR: SQLITE_CORRUPT
>
>SQLITE_CORRUPT
>
>while executing
>
>"btree_next $csr"
>
>("foreach" body line 32)
>
>invoked from within
>
>"foreach {name rootpage} [db eval $sql] {
>
>puts stderr "Analyzing table $name..."
>
># Code below traverses the table being analyzed (table name $name..."
>
>He is running with pragma synchronous off, but I thought that only opened up
>the possibility for corruption if there was a power, OS, or hardware
>failure, which he doesn't think occurred (it is Windows, and the server was
>rebooted at some point.)
Some versions of Windows are known to fail to flush buffers to
disk at shutdown, because the system time-out "wait for hard
disk to complete flush" is too short.
Some applications don't listen to shutdown messages, and are
simply killed by the operating system, without commiting their
transaction, and without journal files, SLQite can't recover
(rollback unfinished transactions) when the database is opened.
Some tweaks to speedup shutdown kill services and applications
too fast.
Etc.
http://www.sqlite.org/pragma.html#pragma_synchronous
http://www.sqlite.org/atomiccommit.html
>I suspect the answer is no, but is there any way to salvage any of the data?
You could try the .dump command in the command line tool, but
I'm afraid you're out of luck.
>Thanks
>
>Doug
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: column index to name mapping
> I'm not sure I understand the problem. The "zeroth" > row sqlite3_get_table > returns reports column > names. Isn't that sufficient? Thanks Igor, Please ignore my idiocy. I had quite forgotten they were in the 0th row. Apologies for the time waster. Dave - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: column index to name mapping
Fin Springs <20dkom502-O/[EMAIL PROTECTED]> wrote: Is it possible to map column names to indices for sqlite3_get_table? I'm not sure I understand the problem. The "zeroth" row sqlite3_get_table returns reports column names. Isn't that sufficient? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] column index to name mapping
Is it possible to map column names to indices for sqlite3_get_table? I could use sqlite3_prepare with my statement string and then calls to sqlite3_column_name() to build a mapping. I could then dispose of the prepared statement and make my call to sqlite3_get_table, relying on the column mapping being the same since it would be the same statement to map column names to row values in the result returned. However, this means preparing the statement just to build the mapping. Is there a better way? I would like to do this so I can use the random access to results that sqlite3_get_table affords with the convenience of referencing columns by name, Thanks, Dave - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Possible UNICODE LIKE, upper(), lower() function solution
On Jan 3, 2008 4:10 AM, ioannis <[EMAIL PROTECTED]> wrote: > Dear all SQLite3 users, > > Recently i have been working on a dictionary style project that had to > work with UNICODE non-latin1 strings, i did try the ICU project but i > wasn't satisfied with the extra baggage that came with it. > I would like to recommend the following possible solution to the long > standing UNICODE issue, that was built in as an ICU alternative > (excluding collation's), and could be easily be included in the SQLite > core as default behavior. > > http://ioannis.mpsounds.net/blog/?dl=sqlite3_unicode.c > > The above file contains mapping tables for lower(), upper(), title(), > fold()* characters based on UNICODE mapping tables as described > currently by the UNICODE standard v5.1.0 beta, that are used by > functions to transform characters to their respective folding cases. > (These tables were built by a modified version of Loic Dachary builder > in order to included required case transformations) > * UNICODE uses case folding mapping tables to implement non-case > sensitive comparison sequences (eg LIKE). > > The above file utilizes the existing ICU infrastructure built in > SQLite in order to activate the extra functionality, to automatically > : > - override the LIKE operation, to support full UNICODE non-case > sensitive comparison > - override upper(), lower(), to support case transformation of UNICODE > characters based on UNICODE mapping tables as described currently by > the UNICODE standard v5.1.0 beta > - provide title() and fold() functions, also based on UNICODE mapping > tables as described currently by the UNICODE standard v5.1.0 beta > - provide unaccent() function, (based on the unac library designed for > linux by Loic Dachary) to decompose UNICODE characters to there > unaccented equivalents in order to perform simpler queries and return > wider range of results. (eg. ά -> α, æ -> ae in the latter example the > string will automatically grow by 1 character point) > > In comparison to ICU no collation sequences have been implemented yet. > The above functionalities have been designed to be included/excluded > independently according to specific needs in order to minimize the > size of the library. > The total overhead over the SQLite library size with all functionality > enabled is approximately 70~80KB. > > The above file has not been thoroughly tested, but i consider the > implementation to stable. > You can leave comments, bug reports, suggestions on this board or at > http://ioannis.mpsounds.net/blog/2007/12/19/sqlite-native-unicode-like-support > (PS. I am not an SQLite expert, but i had to improvise on some extent > on this matter.) > > Thank you very much. > I guess I'm confused at what the purpose of this is. I'm far from a Unicode expert but my understand thus far is that there is no One solution. Locales are there for a reason - different places can use different sort orders and case conversions. Your blog makes using locales seem as a detriment, but I'm not sure how you can get around it. -- Cory Nelson http://www.int64.org
[sqlite] Possible UNICODE LIKE, upper(), lower() function solution
Dear all SQLite3 users, Recently i have been working on a dictionary style project that had to work with UNICODE non-latin1 strings, i did try the ICU project but i wasn't satisfied with the extra baggage that came with it. I would like to recommend the following possible solution to the long standing UNICODE issue, that was built in as an ICU alternative (excluding collation's), and could be easily be included in the SQLite core as default behavior. http://ioannis.mpsounds.net/blog/?dl=sqlite3_unicode.c The above file contains mapping tables for lower(), upper(), title(), fold()* characters based on UNICODE mapping tables as described currently by the UNICODE standard v5.1.0 beta, that are used by functions to transform characters to their respective folding cases. (These tables were built by a modified version of Loic Dachary builder in order to included required case transformations) * UNICODE uses case folding mapping tables to implement non-case sensitive comparison sequences (eg LIKE). The above file utilizes the existing ICU infrastructure built in SQLite in order to activate the extra functionality, to automatically : - override the LIKE operation, to support full UNICODE non-case sensitive comparison - override upper(), lower(), to support case transformation of UNICODE characters based on UNICODE mapping tables as described currently by the UNICODE standard v5.1.0 beta - provide title() and fold() functions, also based on UNICODE mapping tables as described currently by the UNICODE standard v5.1.0 beta - provide unaccent() function, (based on the unac library designed for linux by Loic Dachary) to decompose UNICODE characters to there unaccented equivalents in order to perform simpler queries and return wider range of results. (eg. ά -> α, æ -> ae in the latter example the string will automatically grow by 1 character point) In comparison to ICU no collation sequences have been implemented yet. The above functionalities have been designed to be included/excluded independently according to specific needs in order to minimize the size of the library. The total overhead over the SQLite library size with all functionality enabled is approximately 70~80KB. The above file has not been thoroughly tested, but i consider the implementation to stable. You can leave comments, bug reports, suggestions on this board or at http://ioannis.mpsounds.net/blog/2007/12/19/sqlite-native-unicode-like-support (PS. I am not an SQLite expert, but i had to improvise on some extent on this matter.) Thank you very much.
