Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 24/01/2014 09:06, Max Vlasov wrote: So, if you plan get best performance I do! and thank you for your very detailed analysis. I had no idea about nearly all of what you've said and very much appreciate you sharing your findings. It helps a lot. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
Clemens, Ralph Thank you very much for your detailed advice on the subject. So far my options are 1) the dll + http://code.google.com/p/hiasm/source/browse/elements/delphi/code/SqLite3Api.pas 2) linking an obj file made using e.g. BC5.5 3) using wrappers off the net 4) using DISQLite3 pro Is it just option 3 that requires msvcrt.dll? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 23/01/2014 08:45, Clemens Ladisch wrote: What exactly do you want to know? What is your goal? More specifically...is there the equivalent of that powerbasic include file for D5 i.e. that enables you to access the dll's function calls unchanged? Failing that...anything that will let me work with the latest sqlite 3 dll The meaner and leaner...the better. Thanks for your question ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 23/01/2014 08:45, Clemens Ladisch wrote: dean gwilliam wrote: I'm just wondering what my options are here? Many. What exactly do you want to know? What is your goal? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users my goal is to be able to use sqlite 3 in my delphi 5 programs by, ideally, accessing the raw sqlite api as I do in c++ and powerbasic i.e. with powerbasic I just use an include file and the dll and in C++ I mostly compile the amalgamation into the code I use my self. I'd like to do it both ways in D5 but if I can have only one it would be using the standard sqlite api via the dll. Does that answer your question? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 23/01/2014 02:52, Bogdan Ureche wrote: you may want to take a look at UTF-8VCL. Again that's very useful information and thank you for the link. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 22/01/2014 22:56, RSmith wrote: Hope some of this helps! Yes that's extremely helpful information Ryan. Thanks very much indeed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] latest sqlite 3 with Delphi 5 professional
I'm just wondering what my options are here? Any advice much appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select only records with fields that BEGIN with a certain sub-string
Again, thank you for your advice. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select only records with fields that BEGIN with a certain sub-string
Thank you all for your helpful advice. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select only records with fields that BEGIN with a certain sub-string
...is this possible or should I return the whole lot and subject it to a regexp filter to get my cut-down list? Any advice much appreciated and BTWmerry Christmas to you all! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is there a shorter way to get select results into an associative tcl array?
set lst [gDb eval "SELECT nm, itm_typ FROM std_nms;"]
array set cells {}
set r 0
set c 0
foreach {nm typ} $lst {
set cells($r,$c) $nm
incr c
set cells($r,$c) $typ
incr r
set c [expr $c -1]
}
Any advice much appreciated
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select all fields of a column in one table that are not in a column in another table
On 17/10/2013 14:45, Igor Tandetnik wrote: On 10/17/2013 3:35 AM, dean gwilliam wrote: On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all "name" fields in "itms" that match neither "std_name" or "raw_name" in "aliases" and where the resulting list of "name"s contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that "std_name" would be better placed in another table eg "std_names" and I'm not sure how you'd change the query to reflect the extra table select distinct name from items where name not in (select std_name from std_names) and name not in (select raw_name from aliases); -- or select distinct name from items where name not in ( select std_name from std_names union all select raw_name from aliases ); Igor. Thank you very much indeed! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select all fields of a column in one table that are not in a column in another table
On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all "name" fields in "itms" that match neither "std_name" or "raw_name" in "aliases" and where the resulting list of "name"s contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that "std_name" would be better placed in another table eg "std_names" and I'm not sure how you'd change the query to reflect the extra table ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select all fields of a column in one table that are not in a column in another table
I'm just wondering... if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all "name" fields in "itms" that match neither "std_name" or "raw_name" in "aliases" and where the resulting list of "name"s contains no duplicates. I ask because I want to add these "name"s (in items) to the "raw_name" column in "aliases" I hope I've explained my self clearly and any help much appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Kurt, Keith Thanks very much for your help. It's very much appreciated. Best Regards Dean ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
JKL, Igor, Tom Thank you very much for the advice. It's very much appreciated and helps A LOT! Best Regards Dean ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
It's working now I just needed to use a capital first character in alias.raw_nm. Now it's working in the big program too. Simon, Rob...thanks for your help/reassurance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
It's not working in my big program though so... I'll try to see what's different i.e. I'm getting nothing in itms.std_nm using the test program update query despite it working in my test program ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
I seem to have cracked it with a cut-down example
package require sqlite3
set gDb {}
set db_fl_nm "[pwd]\\test.db" ;# :memory:
proc mbx {x} {
set answer [tk_messageBox -message $x -type yesno -icon question]
switch -- $answer {
yes {}
no exit
}
}
proc mfl {x} {global gOfl; puts $gOfl $x}
proc tbl_app {tbl data} {
set max_id [expr [gDb eval "select max(id) FROM $tbl;"]]
if {$max_id == ""} {set max_id 0}
set id [expr $max_id + 1]
gDb eval "insert into $tbl values ( $id, $data )"
}
proc doit {db_pth} {
global gDb
sqlite3 gDb $db_pth
gDb eval "drop table if exists itms"
gDb eval "drop table if exists aliases"
gDb eval "create table if not exists itms
(id integer primary key, std_nm text, raw_nm text);"
gDb eval "create table if not exists aliases
(id integer primary key, std_nm text, raw_nm text);"
tbl_app itms "null, 'aaa'"
tbl_app itms "null, 'bbb'"
tbl_app itms "null, 'ccc'"
tbl_app aliases "'std_nm1', 'aaa'"
tbl_app aliases "'std_nm2', 'bbb'"
gDb eval "UPDATE itms SET std_nm=
(SELECT std_nm FROM aliases WHERE itms.raw_nm =
aliases.raw_nm);"
#(SELECT std_nm FROM aliases WHERE itms.raw_nm = 'bbb');"
gDb close
}
doit $db_fl_nm
exec SQLiteSpy $db_fl_nm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Thanks Simon I tried that but it writes nothing to itms.std_nm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
RobR Sorry for the delay...I needed to regenerate the database which took several minutes and processes after I messed it up. Here's the "completed" itms table. I don't understand why sales is written to the std_nm column in itms when each rows corresponding raw_nm field does not appear in aliases and particularly with a corresponding sales field. Hope I've made my self clear and thank you for your help SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select * from aliases; 1|sales|sales 2|sales|turnover 3|sales|revenue 4|sales|revenues 5|cogs|cost_of_sales sqlite> select * from itms limit 10; 1|dummy|2005|inc|sales|for_the_year_ended_30_April|2005.0 2|dummy|2005|inc|sales|Turnover|150645.0 3|dummy|2005|inc|sales|Cost_of_sales|-6327.0 4|dummy|2005|inc|sales|Gross_profit|144318.0 5|dummy|2005|inc|sales|Selling_and_distribution_costs|-48106.0 6|dummy|2005|inc|sales|Research_and_development|-23407.0 7|dummy|2005|inc|sales|Amortisation_of_goodwill|-3769.0 8|dummy|2005|inc|sales|Share-based_compensation_payments|-3581.0 9|dummy|2005|inc|sales|Reorganisation_costs|-2302.0 10|dummy|2005|inc|sales|Other_administrative_expenses|-27229.0 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Simon, Rob Thanks very much for your responses. I downloaded and used the shell...it gives the same results i.e. the first std_itm fld in aliases irrespective of what the value of raw_fld is in itms SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); sqlite> select std_nm from itms limit 10 ...> ; sales sales sales sales sales sales sales sales sales sales sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] updating using a value from another table
Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm);" Any help much appreciated ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] making a meal of text substitution
It's ok I cracked it...
I just needed to enclose the script in nothing more than double quotes
to get the variables to
substitute correctly i.e.
set script "CREATE TABLE $nm ( id integer primary key, $flds )"
db eval $script
and ignoring the example syntax's '{}'i.e.
gDb eval {CREATE TABLE std( fld defs here )}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] making a meal of text substitution
Can someone please help me to understand why my substitution of the
single line that creates the table 'std'
isn't working. Any help much appreciated!
#==
proc msg {vlu} {tk_messageBox -message "$vlu"}
::oo::class create cDbase {
variable db tmp
constructor {} {
package require sqlite3
set tmp {}
set db {}
}
method db_open {fPth} {
sqlite3 db $fPth ;# :memory:
}
method tbl_open {nm flds} {
db eval {CREATE TABLE std( id integer primary key, epic text,
itm text, yr integer, vlu real)}
why isn't this a replacement for the line above???
#==
#msg "nm=$nm\nflds=$flds"
#set script {CREATE TABLE [$nm]( id integer primary key, [$flds] )}
#db eval $script
#==
db eval {INSERT INTO std VALUES(1,'mcro','revs',2003, 10)}
db eval {INSERT INTO std VALUES(2,'mcro','revs',2004, 11)}
set x [db eval {SELECT vlu FROM std WHERE itm='revs' ORDER BY id}]
tk_messageBox -message $x
db close
}
method add {} {
}
method destructor {} {
}
}
cDbase create d
d db_open :memory:
d tbl_open std "epic text, itm text, yr integer, vlu real"
d destroy
cDbase destroy
exit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
