Re: [sqlite] temp directory?
Apparently, and unfortunately not...
using pragma temp_store_directory; returns nothing if it is not
explicitly set. In this case you know that sqlite will then "search for
best option... It would be great if the pragma returned what it is
going to use even if it wasn't explicitly set.
I know that for example, in linux, if temp_store_directory is not set,
then it checks for the common temp environment variables, failing that,
it then tries some standard hard coded directories ('/tmp','/var/tmp'
...) If all that turns up nothing then it finally settles on the
current directory.
You run into problems when the directory it uses is not writable for the
user the process is running under.
FYI, in a previous posting, I discovered that even if you set temp_store
to memory only, you still need a temp_store_directory that is writable.
I can't recall specifically, but there is one temp file that is always
on disk regardless of settings.
Kinda makes the whole memory only temp_store useless.
Robert Citek wrote:
> On Thu, Jan 7, 2010 at 12:54 PM, Kris Groves<kris.gro...@mmlab.de> wrote:
>> So it seems as if TMPDIR will work in two different OSes.
>
> What would be an easy test to verify if setting TMPDIR works or not?
>
> I did this, which shows that TMPDIR is indeed being used, but I think
> this test is a bit cumbersome:
>
> $ sqlite3 /dev/null
> SQLite version 3.6.10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create temp table foo (bar int) ;
> sqlite>
> [1]+ Stopped sqlite3 /dev/null
>
> $ ls -l /proc/$(jobs -p %1)/fd
> total 0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 0 -> /dev/pts/0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 1 -> /dev/pts/0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 2 -> /dev/pts/0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 3 -> /dev/null
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 4 ->
> /var/tmp/etilqs_4GCNtDifceoskIh (deleted)
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 5 ->
> /var/tmp/etilqs_dy4Trta7FQrYQT8 (deleted)
>
> $ fg
> sqlite3 /dev/null
> .q
>
> $ TMPDIR=/dev/shm/ sqlite3 /dev/null
> SQLite version 3.6.10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create temp table foo (bar int) ;
> sqlite>
> [1]+ Stopped TMPDIR=/dev/shm/ sqlite3 /dev/null
>
> $ ls -l /proc/$(jobs -p %1)/fd
> total 0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 0 -> /dev/pts/0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 1 -> /dev/pts/0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 2 -> /dev/pts/0
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 3 -> /dev/null
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 4 ->
> /dev/shm/etilqs_MOz4R9xoAMB1i6U (deleted)
> lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 5 ->
> /dev/shm/etilqs_IXXkIEReSwGcwL5 (deleted)
>
> $ fg
> TMPDIR=/dev/shm/ sqlite3 /dev/null
> .q
>
> Regards,
> - Robert
> ___
> 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] temp directory?
When I was digging around trying to figure out why things wern't working
the way I thought they should I came across a function in the sqlite
code called getTempname. In certain situations(i.e. the
temp_store_directory is not set), this function will get a tempname
based on an environment variable.
It seems as though there are different implementations of getTempname
depending on OS.
in one implenetation we have the following :
if( sqlite3_temp_directory ){
zTempPath = sqlite3_temp_directory;
}else{
if( DosScanEnv( (PSZ)"TEMP", ) ){
if( DosScanEnv( (PSZ)"TMP", ) ){
if( DosScanEnv( (PSZ)"TMPDIR", ) ){
in the other we have :
azDirs[0] = sqlite3_temp_directory;
if (NULL == azDirs[1]) {
azDirs[1] = getenv("TMPDIR");
}
So it seems as if TMPDIR will work in two different OSes.
Robert Citek wrote:
> On Thu, Jan 7, 2010 at 10:42 AM, Jay A. Kreibich wrote:
>> On Thu, Jan 07, 2010 at 10:35:21AM -0500, Robert Citek scratched on the wall:
>>> You mention a temp environment variable. I've googled through the
>>> sqlite.org site and haven't found any mention of an environment
>>> variable. What environment variable can I set to change the default
>>> value for the temporary directory?
>>
>> http://sqlite.org/pragma.html#pragma_temp_store_directory
>>
>> It is a PRAGMA, not an env var. Also see "PRAGMA temp_store".
>
> Yes, I was aware of the pragma. I was hoping for an environment
> variable so that I don't have to write pragmas in my code.
>
> Regards,
> - Robert
> ___
> 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] temp directory?
I am unsure of the syntax, as I don't use the sqlite API directly. I
would say that seems correct as long as "'.$_SERVER['TMP'].'" resolves
to a directory in which the user under which the application is running,
has read and write access.
Artur Reilin wrote:
>
>> You can try setting a temp or tmp or tempdir environment variable on the
>> server, however, as I said in the previous post, I am not sure if it
>> will work.
>>
>> Probably the safest and most robust way would be to have your code call
>> the pragma temp_store_directory directly after opening the database.
>> How your code knows what directory to call the pragma with could be done
>> with an environment variable or whatever you prefer.
>>
>
> $db = @sqlite_open('../www/zero.sqlite');
> sqlite_exec('pragma temp_store=1; pragma
> temp_store_directory="'.$_SERVER['TMP'].'"',$db);
>
> like this?
> ___
> 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] temp directory?
You can try setting a temp or tmp or tempdir environment variable on the server, however, as I said in the previous post, I am not sure if it will work. Probably the safest and most robust way would be to have your code call the pragma temp_store_directory directly after opening the database. How your code knows what directory to call the pragma with could be done with an environment variable or whatever you prefer. Artur Reilin wrote: > I collect some things now: > > My hoster: > > pdo_sqlitePDO Driver for SQLite 3.x enabled > PECL Module version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.4 2008/12/31 > 11:17:42 sebastian Exp $ > SQLite Library3.3.8 > > SQLiteSQLite support enabled > PECL Module version 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.12 2008/12/31 > 11:17:44 sebastian Exp $ > SQLite Library2.8.17 > SQLite Encoding UTF-8 > > > My home server: > > pdo_sqlitePDO Driver for SQLite 3.x enabled > SQLite Library3.6.20 > > SQLiteSQLite support enabled > PECL Module version 2.0-dev $Id: sqlite.c,v 1.166.2.13.2.9.2.22 > 2009/06/25 09:38:04 johannes Exp $ > SQLite Library2.8.17 > SQLite Encoding iso8859 > > sqlite3SQLite3 supportenabled > SQLite3 module version0.7-dev > SQLite Library3.6.16 > > --- > > Seems that there are missing the sqlite3 library, but i use 2.8.17, > because the sqlite 3 version don't work on both systems. > > Okay, should i ask my hoster if they there is an way, to set the default > path to tmp dir from the server? This should normaly so, or? > > with best wishes > > Artur > > --- >> You could also set the temp_store pragma such that *most* temp files are >> created in memory. I say most, because even though you set temp_store >> to memory, there is still one temporary file(statement journal) which >> will still need to use the temp_store_directory. >> >> I suppose another possibility is to set the temp environment variable. >> >> I have just noticed that your version of sqlite is way old... so I'm >> very unsure if anything I've suggested is of any use. I know the >> environment variable did not work in 3.5.9, but does in 3.6 and up. >> >> Artur Reilin wrote: >>> I can, but doesn't there exists an another way? >>> >>> I believe you can specify the directory in which temporary files will be created. This is done with pragma temp_store_directory. I think you can also set this in compilation. Artur Reilin wrote: > >> >> On 19 Nov 2009, at 8:05am, Artur Reilin wrote: >> >>> My host have sqlite support vor sqlite 2.8.17 and i can use it. But >>> i >>> have >>> to give chMod 0777 to the directory where i use sqlite. Not only on >>> the >>> directory, that contains the sqlite database. let me show it: >>> >>> /index.php (uses sqlite database) >>> /data/data.sqlite (sqlite database) >>> >>> If i give chMod 0777 to /data/ and the files in this folder, it >>> wouldn't >>> work and get me an "malformed database - cannot create temp tables". >>> >>> I need to gibe chMod to the directory which contains the index.php >>> file. >>> That's a big security risk and i don't want to give chMod to the >>> main >>> directory. >>> >>> Is this normal and can i change this? >> >> You do not actually need 0777. Your problem is that the user which >> is >> creating and using the database is the user that runs Apache, not >> yourself. So find out which user Apache runs under. This might be >> perhaps www or _www or _apache. Make sure that that user has the >> rights >> over this directory. Then Apache (running the .php script) which >> have >> enough access to use the database file. >> >> Simon. > > The user which runs php (or has the highest rights) is called nobody. > The > support said me, that i need to set the directory to this user, but it > also don't work. I get the same error as before. I can try it again, > but > the last time it didn't work. > > I also wondering why my oop version of my script didn't work, but the > prozedural one does. They have pdo drivers like mine xampp server, but > they don't have the sqlite3 drivers. Perhaps this also happens because > of > the malformed database error... > > With best wishes > > Artur Reilin > sqlite.yuedream.de >>> >>> Artur Reilin >>> sqlite.yuedream.de > > > > Artur Reilin > sqlite.yuedream.de > ___ > 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] temp directory?
You could also set the temp_store pragma such that *most* temp files are created in memory. I say most, because even though you set temp_store to memory, there is still one temporary file(statement journal) which will still need to use the temp_store_directory. I suppose another possibility is to set the temp environment variable. I have just noticed that your version of sqlite is way old... so I'm very unsure if anything I've suggested is of any use. I know the environment variable did not work in 3.5.9, but does in 3.6 and up. Artur Reilin wrote: > I can, but doesn't there exists an another way? > > >> I believe you can specify the directory in which temporary files will be >> created. This is done with pragma temp_store_directory. I think you >> can also set this in compilation. >> >> Artur Reilin wrote: >>> On 19 Nov 2009, at 8:05am, Artur Reilin wrote: > My host have sqlite support vor sqlite 2.8.17 and i can use it. But i > have > to give chMod 0777 to the directory where i use sqlite. Not only on > the > directory, that contains the sqlite database. let me show it: > > /index.php (uses sqlite database) > /data/data.sqlite (sqlite database) > > If i give chMod 0777 to /data/ and the files in this folder, it > wouldn't > work and get me an "malformed database - cannot create temp tables". > > I need to gibe chMod to the directory which contains the index.php > file. > That's a big security risk and i don't want to give chMod to the main > directory. > > Is this normal and can i change this? You do not actually need 0777. Your problem is that the user which is creating and using the database is the user that runs Apache, not yourself. So find out which user Apache runs under. This might be perhaps www or _www or _apache. Make sure that that user has the rights over this directory. Then Apache (running the .php script) which have enough access to use the database file. Simon. >>> >>> The user which runs php (or has the highest rights) is called nobody. >>> The >>> support said me, that i need to set the directory to this user, but it >>> also don't work. I get the same error as before. I can try it again, but >>> the last time it didn't work. >>> >>> I also wondering why my oop version of my script didn't work, but the >>> prozedural one does. They have pdo drivers like mine xampp server, but >>> they don't have the sqlite3 drivers. Perhaps this also happens because >>> of >>> the malformed database error... >>> >>> With best wishes >>> >>> Artur Reilin >>> sqlite.yuedream.de > > Artur Reilin > sqlite.yuedream.de > ___ > 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] temp directory?
I believe you can specify the directory in which temporary files will be created. This is done with pragma temp_store_directory. I think you can also set this in compilation. Artur Reilin wrote: > >> >> On 19 Nov 2009, at 8:05am, Artur Reilin wrote: >> >>> My host have sqlite support vor sqlite 2.8.17 and i can use it. But i >>> have >>> to give chMod 0777 to the directory where i use sqlite. Not only on the >>> directory, that contains the sqlite database. let me show it: >>> >>> /index.php (uses sqlite database) >>> /data/data.sqlite (sqlite database) >>> >>> If i give chMod 0777 to /data/ and the files in this folder, it wouldn't >>> work and get me an "malformed database - cannot create temp tables". >>> >>> I need to gibe chMod to the directory which contains the index.php file. >>> That's a big security risk and i don't want to give chMod to the main >>> directory. >>> >>> Is this normal and can i change this? >> >> You do not actually need 0777. Your problem is that the user which is >> creating and using the database is the user that runs Apache, not >> yourself. So find out which user Apache runs under. This might be >> perhaps www or _www or _apache. Make sure that that user has the rights >> over this directory. Then Apache (running the .php script) which have >> enough access to use the database file. >> >> Simon. > > The user which runs php (or has the highest rights) is called nobody. The > support said me, that i need to set the directory to this user, but it > also don't work. I get the same error as before. I can try it again, but > the last time it didn't work. > > I also wondering why my oop version of my script didn't work, but the > prozedural one does. They have pdo drivers like mine xampp server, but > they don't have the sqlite3 drivers. Perhaps this also happens because of > the malformed database error... > > With best wishes > > Artur Reilin > sqlite.yuedream.de > ___ > 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
[sqlite] Database triggers
Given the trigger creation statement : CREATE TRIGGER triggername DELETE ON tablename BEGIN statements; END When does it happen ? BEFORE or AFTER ? I've read http://sqlite.org/lang_createtrigger.html, but it is unclear to me. If I had to guess, I would say the trigger is a BEFORE trigger, but I'd rather not guess, so I'm asking here. Thanks, Kris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEMP_STORE not working ?(3.5.9)
After code reading, I have discovered that the culprit is the statement journal. Regardless of TEMP_STORE = MEMORY or not, the statement journal, which is treated differently than a regular journal file, is placed in a temporary file. This effectively kills the usefulness and purpose of the TEMP_STORE directive. Kris Groves wrote: > Hi, > > Just want to bump this, I really need to get to the bottom of this. > > Thanks for any info, > Kris. > > Kris Groves wrote: > >> Hi, >> From what I understand : >> - default behavior is to use files for temporary stuff. >> - the directory that will be used for these temporary files can be >> defined via pragma (temp_store_directory). If the pragma is not used, >> it will default to the first hardcoded directory (linux), in the order >> that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. >> >> So, in the environment I am running in, either those directories do not >> exist, or are not writable to the user under which the process is >> running. The result being an "error 14: unable to open database file" >> as soon as temporary files are needed. >> >> After a little digging I discover SQLITE_TEMP_STORE compilation flag. >> So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, >> figuring that the temp files will now reside in memory, and need no >> writing into a directory. However, the problem remains. >> >> When I look through the code, there is no instance of SQLITE_TEMP_STORE, >> only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. >> >> Then I add a path that I know is accessible to the user under which the >> process runs, to the azDirs array in the unixGetTempname function. >> Voila.. working now.. >> >> I've retested with default TEMP_STORE and TEMP_STORE compiled in a >> 3(memory only). And regardless of the setting, it only works if there >> is a readable/writable directory... >> >> I would think that if TEMP_STORE=3, then no directory is required ? Is >> this a bug, or am I misunderstanding something ? >> >> Thanks, >> Kris. >> >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating procedures in sqlite
Perhaps this helps ? http://sqlite.org/c3ref/create_function.html Jyoti Seth wrote: > Hi All, > > > > SQLite lacks built-in support for stored procedures. We need to create it in > our application. Please guide me any alternative method to achieve this > functionality. > > > > Thanks, > > Jyoti > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Kris Groves Project Manager / Software Engineer mm-lab GmbH Phone: +49 7154 827 323 Stammheimer Str. 10 Fax:+49 7154 827 350 D-70806 Kornwestheim kris.gro...@mmlab.de www.mmlab.de Domicile of Company: Kornwestheim, Germany District Court - Court of Registration Stuttgart HRB 207257 Managing Directors: Bernd Herrmann, Lothar Krank, Michael Meiser, Dr. Andreas Streit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEMP_STORE not working ?(3.5.9)
Hi, Just want to bump this, I really need to get to the bottom of this. Thanks for any info, Kris. Kris Groves wrote: > Hi, > From what I understand : > - default behavior is to use files for temporary stuff. > - the directory that will be used for these temporary files can be > defined via pragma (temp_store_directory). If the pragma is not used, > it will default to the first hardcoded directory (linux), in the order > that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. > > So, in the environment I am running in, either those directories do not > exist, or are not writable to the user under which the process is > running. The result being an "error 14: unable to open database file" > as soon as temporary files are needed. > > After a little digging I discover SQLITE_TEMP_STORE compilation flag. > So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, > figuring that the temp files will now reside in memory, and need no > writing into a directory. However, the problem remains. > > When I look through the code, there is no instance of SQLITE_TEMP_STORE, > only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. > > Then I add a path that I know is accessible to the user under which the > process runs, to the azDirs array in the unixGetTempname function. > Voila.. working now.. > > I've retested with default TEMP_STORE and TEMP_STORE compiled in a > 3(memory only). And regardless of the setting, it only works if there > is a readable/writable directory... > > I would think that if TEMP_STORE=3, then no directory is required ? Is > this a bug, or am I misunderstanding something ? > > Thanks, > Kris. > > ___ > 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] TEMP_STORE not working ?(3.5.9)
Additionally, I have just tried changing the TEMP_STORE define in the code to 3, (just in case the compiler options were not working to begin with), with the same results as already described. Kris Groves wrote: > Hi, > From what I understand : > - default behavior is to use files for temporary stuff. > - the directory that will be used for these temporary files can be > defined via pragma (temp_store_directory). If the pragma is not used, > it will default to the first hardcoded directory (linux), in the order > that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. > > So, in the environment I am running in, either those directories do not > exist, or are not writable to the user under which the process is > running. The result being an "error 14: unable to open database file" > as soon as temporary files are needed. > > After a little digging I discover SQLITE_TEMP_STORE compilation flag. > So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, > figuring that the temp files will now reside in memory, and need no > writing into a directory. However, the problem remains. > > When I look through the code, there is no instance of SQLITE_TEMP_STORE, > only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. > > Then I add a path that I know is accessible to the user under which the > process runs, to the azDirs array in the unixGetTempname function. > Voila.. working now.. > > I've retested with default TEMP_STORE and TEMP_STORE compiled in a > 3(memory only). And regardless of the setting, it only works if there > is a readable/writable directory... > > I would think that if TEMP_STORE=3, then no directory is required ? Is > this a bug, or am I misunderstanding something ? > > Thanks, > Kris. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Kris Groves Project Manager / Software Engineer mm-lab GmbH Phone: +49 7154 827 323 Stammheimer Str. 10 Fax:+49 7154 827 350 D-70806 Kornwestheim kris.gro...@mmlab.de www.mmlab.de Domicile of Company: Kornwestheim, Germany District Court - Court of Registration Stuttgart HRB 207257 Managing Directors: Bernd Herrmann, Lothar Krank, Michael Meiser, Dr. Andreas Streit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TEMP_STORE not working ?(3.5.9)
Hi, >From what I understand : - default behavior is to use files for temporary stuff. - the directory that will be used for these temporary files can be defined via pragma (temp_store_directory). If the pragma is not used, it will default to the first hardcoded directory (linux), in the order that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. So, in the environment I am running in, either those directories do not exist, or are not writable to the user under which the process is running. The result being an "error 14: unable to open database file" as soon as temporary files are needed. After a little digging I discover SQLITE_TEMP_STORE compilation flag. So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, figuring that the temp files will now reside in memory, and need no writing into a directory. However, the problem remains. When I look through the code, there is no instance of SQLITE_TEMP_STORE, only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. Then I add a path that I know is accessible to the user under which the process runs, to the azDirs array in the unixGetTempname function. Voila.. working now.. I've retested with default TEMP_STORE and TEMP_STORE compiled in a 3(memory only). And regardless of the setting, it only works if there is a readable/writable directory... I would think that if TEMP_STORE=3, then no directory is required ? Is this a bug, or am I misunderstanding something ? Thanks, Kris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aliasing and columns names
On Thursday 10 July 2008 15:17:50 D. Richard Hipp wrote: > On Jul 10, 2008, at 7:57 AM, Evgeni Alesinskyy wrote: > > Hi all, > > > > I develop application in C# that has to run with MySQL and SQLite. > > At some point I need to join 2 tables and bind them resp. dataset > > containing the union to ListBox. > > > > To do this I use aliasing for table names. My SQL-statement looks > > like this: > > SELECT distinct(m.name), wp.MaterialSN from WorkDefaultsMaterial wp, > > Material m WHERE m.ShortName = wp.MaterialSN AND wp.WorkSN='bla-bla' > > order by m.name > > > > If I use MySQL, then the column names are just names of columns in > > each table with out alias, e.g. MaterialSN. If I use SQLite, then > > the column names starts with alias, e.g. wp.MaterialSN. > > > > So that if I bind the dataset to ListBox, in case where I use > > SQLite, I get an error : > > > > "Cannot bind to the new display member. > > Parameter name: newDisplayMember" > > > > The reason for this error is following: > > > > the column name ist wp.MaterialSN. In C# , if one binds a field/ > > column the dot means that the part before it is a table name and > > part after column name. So c# looks for the table wp in dataset. > > This is however not present :-). > > > > If I use MySQL, my application works fine, as the column names do > > not contain alias. > > > > I can surely rewrite my SQL-statement to SELECT distinct(m.name) as > > name, wp.MaterialSN as MaterialSN ... to obtain column names in > > needed format. However in my opinion it is not a good solution to do > > it. > > > > Is there a possibility to configure SQLite so that aliases are > > omitted and only column names are used? > > > > The strange thing is, that my application worked already with > > SQLite. And now as I had to make some changes it become this strange > > behavior. > > > > Any idea why? > > SQLite does not (currently) make any promises about column names on > queries that omit the AS clause. If you use an AS clause, then the > column name is guaranteed to be the label to the right of AS. If you > omit the AS clause, then the column names might change from one > release of SQLite to the next. > > At some point in the future, it would be nice to document exactly how > column names are computed and freeze the implementation accordingly. > But we are not there yet. > > Please note that there is a subtle change in column naming that will > appear in version 3.6.0 - a change which I believe will make SQLite > behave more like MySQL and omit the table names in cases where they > are not needed. So version 3.6.0 might work for your situation above > without the use of AS. But for portability, it is best to use AS. > > D. Richard Hipp > [EMAIL PROTECTED] > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users I would like to point out that when selecting without distinct, sqlite returns the real tablename in a column name formatted as tablename.columnname, even if the tables are aliased in the select statement. With select distinct, the tablenames that are returned are the alias if specified in the statement. At least this is how the sqlite3_get_table function returns. Kris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
