[sqlite] Fwd: Having trouble using sqlite to handle query that contains Chinese characters

[ME Support]

> Subject: Having trouble using sqlite to handle query that contains Chinese 
> characters
> 
> Dear Sir:
>  
> I was working on a windows 8 app, which involve using SQLite database. I 
> downloaded .dll from your website. I have some trouble when using sqlite to 
> handle query that contains Chinese characters.
>  
> When I debug the code, the strSQL variable looks correct to me.
>  
> SQLite.SQLiteAsyncConnection _dbConnection = new 
> SQLite.SQLiteAsyncConnection(_dbPath);
> ObservableCollection  result = new 
> ObservableCollection();
> ...
> result = new ObservableCollection(await 
> _dbConnection.QueryAsync(strSQL))
>  
> It seemed to me that the query I was trying to run eventually became the 
> following based on the result returned.
>  select column1, column2 from table where columnx like ‘%?%’
>  
> where “?” is where the chinese character should have been. I had no problem 
> getting Chinese characters from the database if the where clause doesn’t 
> contain any chinese character.
>  
> I run the same query in “SQLite Expert personal version” and I was able to 
> see the correct result returned.
>  
> So what can I do to get this problem fixed? Do you have any suggestion?
>  
> Thanks very much!
>  
>  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Enforcing SQLite column type

[Me]

You can enforce a column's TYPE using CHECK() and/or TRIGGER.
It might be expensive if used on all columns for large inserts
or updates; depends on what the priorities are.

INTEGER, REAL and BLOB are easy to enforce with triggers.
typeof( NEW.cEnforceReal ) <> 'real'
typeof( NEW.cEnforceInteger ) <> 'integer'
typeof( NEW.cEnforceBlob ) <> 'blob'

Date: depends on how dates are stored.
CREATE TABLE [tEnforceDate] (
 [cDate] TEXT CHECK( cDate LIKE'-__-__' )
)

I put together some notes on it:
http://www.sqlight.com/enforce-type-triggers/

Any corrections/additions would be appreciated.

Stan...
igiga &&& @ &&& swbell &&& . &&& net



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] [ANN]SQLight v2 - complete Manager/Browser/Editor for SQLite Databases

[Me]

http://www.sqlight.com/

Complete Rewrite.

The only database manager for SQLite that allows query results to be edited 
in a meaningful order!


Try editing the data returned from a simple query like this in any other 
SQLite manager.


"SELECT rowid,Product,Name,Description,Price,SKU FROM Products WHERE 
Price>29.99 ORDER BY Product,Name LIMIT 100"


Edit PhotoshopT, WordT and ExcelT documents stored as BLOBs in an SQLite 
database.


Instant Query
 - double-click table name

No Locks
 - Queries never held open.
 - The current value is retrieved when an item is clicked and changes 
stored immediately

 - the same table may be edited in various sort orders, simultaneously

Transactions supported
 - BEGIN, COMMIT or ROLLBACK at any time

Four Query View Panels
 - execute any SQLite query
 - if a result is returned, it will be displayed
 - any single table query with Primary Key may be edited
 - Instant Query, double-click table name
 - queries may be saved
 - column view widths may be saved for table

Lookup List
 - any Table/Column may serve as a lookup list

Dupes
 - any row of the four query views may be marked for duplication
 - Ctrl+D, dupe value from remembered row
 - Ditto copies column above

External Text Editors Supported
 - built-in text editor for editing column values
 - any external Text or HTML editor may be used to edit text column values
 - edit HTML with FrontPage - (tm)Microsoft

Blob
 - Load and Save blob files
 - any application may be use to view/edit a blob column value
 - store and edit PhotoshopT documents in SQLite database
 - store and edit WordT documents in SQLite database
 - store and edit ExcelT documents in SQLite database

Schema Tool
 - Schema Tool - Table
   - view table creation SQL
   - list columns in table - column definitions
   - list all indexes associated with table - view SQL
   - list all triggers associated with table - view SQL
 - Schema Tool - Indexes
   - view index creation SQL
 - Schema Tool - Trigger
   - view trigger creation SQL
 - Schema Tool - View
   - see View creation SQL

Table Tool
   - Table Tool - Create
 - select TYPE - edit list to suit
 - text collation order
 - Integer Primary Key
 - Primary Key, ON CONFLICT, Order, Auto Increment
 - set DEFAULT value
- set SQLite; CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
 - NOT NULL constraint, select ON CONFLICT action
 - UNIQUE constraint, select ON CONFLICT action
 - define CHECK() constraint
   - Table Tool - Rename Table
   - Table Tool - Drop Table
   - Table Tool - Clone Table
 - clone another table's structure
 - select columns to include
   - Table Tool - Schema
   - Table Tool - Indexes
 - list associated indexes
   - Table Tool - Triggers
 - list associated triggers

Column Tool
 - Column Tool - Add Column
   - column Type - customize list
   - text collation sequence
   - set DEFAULT value
   - select NOT NULL and ON CONFLICT action
   - set CHECK() constraint
 - Column Tool - Rename Column
 - Column Tool - Drop Column
 - Column Tool - Reorder Columns
   - change column order in table
 - Column Tool - Redefine Column
   - completely change column definition
   - change TYPE
   - change text collation sequence
   - set DEFAULT value
 - set SQLite CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
   - set NOT NULL constraint, select ON CONFLICT action
   - set UNIQUE constraint, select ON CONFLICT action
   - define CHECK() constraint

Index Tool
 - Create
   - unique constraint
   - text collate
   - sort order
 - Drop
 - Rename
 - Edit SQL
   - edit SQL statement and save changes

Trigger Tool
 - Drop
 - Rename
 - Edit SQL
   - edit SQL statement and save changes

View Tool
 - Drop
 - Rename
 - Edit SQL
   - edit SQL statement and save changes

File
 - open SQLite Type 3 database
 - open resent database
 - create new database
 - backup database
 - VACUUM database

SQLite Version
 - SQLite version may upgraded at any time by swapping SQLite DLL

Download 30-Day Trial
http://www.sqlight.com/
Purchase: $39.95


Application Requirements
 - Windows OS
 - SQLite Type 3 database

Not Supported
 - Unicode
 - UTF-16
 - SQLite Type 2 database


[EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Announce: SQL Maestro Group SQLite tools introduction

[Me]

Hi Maestro Group,


NOT linked with SQLight team


I guess that's a dig on me.
They were FREE little tools I had laying around.
Thought someone might get some use out of them.
They won't know if I don't tell them.

StanD
http://www.sqlight.com/
IS linked with SQLight team. :)
Lots of free SQLite tools.

- Original Message - > Hi All,


Two small notes first:

1. SQL Maestro Group is NOT linked with SQLight team, which messages you 
could read here last days.
2. It's our first announce and we promise to community to NOT send such 
messages too often - unfortunately, we don't release a new version every 
day. :-)


Best Regards,
SQL Maestro Group Team
http://www.sqlmaestro.com 

Re: [sqlite] Grabbing "default values" from the database...

[Me]

The value has to be NULL for the default value to fire.

If it's an empty string, "", that's what you'll get.



StanD.

- Original Message - 
Subject: [sqlite] Grabbing "default values" from the database...




I'm working on a project in C# that uses an SQLite database back-end.

I have a table set up so that some columns have default values, such as:

CREATE TABLE mc_question (id integer primary key autoincrement, q_type
varchar(5) default "MC", single_multi bool, negative_score
bool, cumulative_all bool, title_text varchar(200), question_format
bool, question varchar(200), image_file varchar(200), ans_order
bool, indices bool, ans_layout bool, feedback varchar(200), category_id
integer);

The "q_type" column is of type varchar(5) and has a default value of "MC".

I'm using DataGridView Controls which are bound to different BindingSource
objects, and I would like to populate the DataGridView columns with the
default values that are set within the database itself, so that I don't 
have

to set each column manually...

Does anyone know how I would do this?  If not, could someone tell me how 
to

grab the "default values" from the database? (I thought that FillSchema
would do it, but it doesn't)

Sincerely,

Gordon E.

P.S. I have asked in a MS ADO .NET forum, but they're no help.  They just
keep telling me I should set all the columns' default values manually... 
but

that's such a pain in the .

Re: [sqlite] [ANN]SQLtNoCase - convert SQLite TEXT columns to TEXT COLLATE NOCASE

[Me]

Probably.
Then again, it's was done.

- Original Message - 


I don't mind receiving occasional announcements on new programs using
SQLite (even if I'll never use them), but don't you think one per day
is just too much?


Regards,
~Nuno Lucas

[sqlite] [ANN]SQLtNoCase - convert SQLite TEXT columns to TEXT COLLATE NOCASE

[Me]

FREE - SQLtNoCase - convert TEXT columns to TEXT COLLATE NOCASE

SQLite handles text columns different, depending on how COLLATE is defined 
when the column is created. The default is COLLATE BINARY.


TEXT or TEXT COLLATE BINARY - columns will be treated case sensitive for 
ORDER BY and WHERE comparisons.


TEXT COLLATE NOCASE - columns will be handled case insensitive. 'smith' = 
'Smith'


For the most part, TEXT COLLATE NOCASE will give the preferred results on 
short text columns such as [Name].


While SQL can be used to force case insensitive comparisons - converting a 
column to TEXT COLLATE NOCASE is a permanent solution.


Converting a column from TEXT, to TEXT COLLATE NOCASE, isn't easily done 
with SQL - that's where SQLtNoCase comes in.


http://www.sqlight.com/sqltnocase/

sd _ at _ sqlight _ dot _ com

[sqlite] ANN:SQLtTableImprot - Free SQLite to SQLite Table Import Tool

[Me]

SQLtTableImprt is a little tool for importing a table from one SQLite 
database into another SQLite database.

- This can easily be done in SQL with ATTACH DATABASE.
- SQLtTableImprt automates the process.
- Windows only.
- Should work with any SQLite Type 3 database that the Window's file system 
can connect to.

- Blobs are OK.
- Download: http://www.sqlight.com/sqlttableimprt/
- Free.

StanD
sd _ at _ sqlight _ dot _ com

Re: [sqlite] primary key information

[Me]

another way
"SELECT rowid FROM MyTable LIMIT 1"
the column name returned will be the Integer Primary Key

StanD
sd at sqlight.com

- Original Message - 
From: "Mario Frasca" <[EMAIL PROTECTED]>

To: 
Sent: Monday, August 07, 2006 9:33 AM
Subject: Re: [sqlite] primary key information



Nemanja Corlija wrote:


I think in any other case unique index would be created as expected.


sqlite> drop table test;
sqlite> create table test (ni integer, pk_name varchar(32) primary key, 
info integer);

sqlite> pragma index_list(test);
0|sqlite_autoindex_test_1|1
sqlite> pragma table_info(test);
0|ni|integer|0||0
1|pk_name|varchar(32)|0||1
2|info|integer|0||0
sqlite>

that seems to be correct... interesting, thanks,

Mario


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.7/410 - Release Date: 8/5/2006

[sqlite] ANN: SQLtAlarm - Alarm monitor for SQLite Databases

[Me]

SQLtAlarm - Alarm system for SQLite Databases.
- Set an alarm on any piece of data in an SQLite database.
- Turn any table into a to-do list.
- No limitations , no time-limit, no nags - donationware.
- Built-in tool to add alarm column.
- Requirements, Windows OS.
- http://www.sqlight.com/sqltalarm/

[sqlite] [ANN] SQLite Manager - Browser - Editor + Deep SQL

[Me]

[ANN] SQLite Manager - Browser - Editor + Deep SQL

Windows/UTF-8 only.
www.sqlight.com

Four query views.
Transactions supported.

Table Edit
- complete editing flexibility. No limitation how a table is arranged for 
editing as long as Primary Key present.


- Lookup List
- Save Queries
- Save Defalut Query
- Ditto Columns
- Dupe Rows
- Save Table View Layout

Data Manager
Schema
- Tables
- Indexes
- Views
- Triggers
Tables
- Create
- Rename
- Drop
Index
- Create
- Rename
- Drop
Columns
- Add
- Drop
- Rename
- Reorder - change column order
- Redefine - completely redefine columns - (type, collate, default, not 
null, unique, check)



Blob Data
- Edit blobs as if a local file - Excel®, Word®, Photoshop®, etc...

Deep SQL
- BLOB-stored SQLite databases
Work on blob-stored SQLite databases - down to any level. DB stored in 
Row/Column of parent, that's stored in Row/Column of its parent, that's 
stored in Row/Column of its parent, etc... 
www.sqlight.com/features/deep-sql.html


This appears to be the only SQLite browser actually designed for editing 
tables.
Most (all the ones I've found) restrict you to editing tables in the 
original creation order.


SQLight supports editing any single table query (use WHERE, use ORDER BY) as 
long as the Primary Key is present.
"SELECT rowid,Cat,Sub,Item,Brand,Name,Dscrpt,Price,Sale,OnSale FROM Products 
WHERE OnSale='Y' AND Price>20 ORDER BY Cat,Sub,Item,Brand LIMIT 300"

This query can be edited.

www.sqlight.com

StanDurham
sd  (at)  sqlite DOT com

Re: [sqlite] SQLite browser/manager + Deep SQL - testers please

[Me]

thanks
- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Friday, July 28, 2006 12:00 PM
Subject: Re: [sqlite] SQLite browser/manager + Deep SQL - testers please



On 7/28/06, Me <[EMAIL PROTECTED]> wrote:


Also - How do I get it listed in the wiki - ManagementTools?



It's a wiki...

Just go to the page click the edit link and add it yourself.

HTH
Dennis Cote







No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.4/402 - Release Date: 7/27/2006

[sqlite] SQLite browser/manager + Deep SQL - testers please

[Me]

Windows SQLite browser/manager.

Four query views. View/Edit four simultaneous queries or same table in 
various sort orders.
No limitation how a table is arranged for editing as long as the Primary Key 
is in the query.

Transactions supported.

Deep SQL: Work on blob-stored SQLite databases - down to any level.
Edit blobs as if a local file - Excel®, Word®, Photoshop®, etc...

Save queries, Lookup list, Ditto columns and save column view widths.

Table tool: create, rename and drop.
Column tool: add, drop, rename, reorder and redefine (change type, default, 
check(), constraints).

Index tool: create, rename and drop.

www.sqlight.com

I'd appreciate it anyone has the time to test.
Also - How do I get it listed in the wiki - ManagementTools?

Windows Only!

[EMAIL PROTECTED]
StanDurham