Re: [sqlite] Single quotes are causing misery
Well the [ ] aren't a .NET thing, SQLite supports them, using [ ] are a standard supported SQLite feature and they make you're life way, way, easier, see: http://www.sqlite.org/lang_keywords.html [keyword] A keyword enclosed in square brackets is always understood as an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility. While this might not be "standard SQL" using double quotes " as the special character is a very poor choice.
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
Darn, I was going to have 500,000 columns in my table. The, each column would be named like this: Record1, Record2, Record3, and so on up to Record500. Each column type was going to be varchar and I was going to store my first record in xml format in my Record1 column of the first row, and so on down the line. That way I could stored all 500,000 of my records in a 1 row table, What do you guys think?
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
I agree, it's like creating one object (C++, Java, C#) in an application with 40,000 methods, you might be using an object oriented language but you aren't using OO techniques. I would hone it down before SQLite developers exploit this and you're stuck supporting it, it could come back to haunt you later. > Off topic, but people creating tables with that number of columns > should really think about normalizing their data structures. > > -- Gerhard >
Re: [sqlite] Single quotes are causing misery
> What's the proper way to ensure that ' characters are properly quoted but > don't show up in the output? Honestly, we use the SQLite .NET managed driver and pass all data in via parameters, therefore we have no escape issues and more importantly no SQL injection woes, if you're taking data right off the string and passing it into SQLite I could probably enter some text that wipe out all of your data, consider parameters, they solve a whole bunch of problems.
RE: [sqlite] thoughts on a web-based front end to sqlite3 db?
For what it's worth, if your in a hurry, try our product, it's Free. Basically, you launch our product, connect to your database and we'll spit out fully functional VB.NET or C# data/business entities using your database meta-data as the input. These objects (classes) do mostly everything that you need to do in a typical application. The advice below is good, and I would add one thing. How long will it (the application) be around and how hard will it be to maintain. anyway, see http://www.mygenerationsoftware.com if you're interested. - Mike > Here's my tuppence worth. > > I come from an engineering background where you're taught that rigour is > the > most important feature of any development. I find that all the > interpreted > environments tend not to be that great in this regard. Also, I don't have > an axe to grind when it comes to the "anything but Microsoft" prejudice > that > a lot of non-MS programmers seem to have. So, I would say that you need > to > decide what it is you're actually developing and then select a tool that > is > going to achieve that result - here's some questions to ask yourself; > > a) Am I developing a web application or just providing some dynmic > content > > b) Can I fix the deployment platform (Windows/Linux/Solaris) > > c) How much time have I got to develop this > > d) What level/quality of service do I need to provide > > There are lots of other questions but these are good ones to try and > resolve > straight off. > > Most of the applications I am involved in writing are multi-developer, > corporate wide systems and as such, it's not practical to choose a toolset > that isn't supported by a good vendor and a large user base. This tends > to > narrow the choices down to commercial products like > .NET/Java/Delphi/C/C++/VB - by commerical I'm talking about the > Development > Environments (IDE). > > Over the years I've come to realise that programming is much the same in > any > modern environment - same symantics, different syntax. what really makes > the difference for me is the development environment, I'm at the age where > I > can no longer bear the thought of using notepad/emacs/vi/vim as my > development editor for the sake of spending a few quid on a proper tool > for > the job that has Intellisense, aut-formatting, syntax checking etc. > > With that in mind, your choice is further narrowed and my current absolute > favourite is Java Servlets with JDBC using JetBrains IntelliJ. Low cost, > professional, high performance, good CV fodder, beautiful IDE. > If your pockets are deeper and you're sticking with Windows then .NET and > ADO is quite frankly the best possible way to go. > > Hey, you wanted an opinion > > p.s. anyone using assembler in a web environment should be kept away from > sharp objects for their own safety!! > > > > > -Original Message- > From: Eli Burke [mailto:[EMAIL PROTECTED] > Sent: 07 March 2005 21:23 > To: sqlite-users@sqlite.org > Subject: [sqlite] thoughts on a web-based front end to sqlite3 db? > > > I've been working on a project using sqlite3 since last fall. At the time, > I knew that it would need a web-based front-end eventually. I have a very > small bit of experience with PHP, and I assumed that PHP would support > sqlite3 sooner or later. Well, it's later, and as far as I know, PHP > is still using the 2.x branch. > > So, I was wondering if any of the more opinionated among you would care > to suggest an interface language. It'll be on a Linux box, presumably > running apache although I'm open to alternatives. The app itself uses > sqlite3 for scheduling jobs and storing job data, so the web interface > only needs to be able to insert some data and do visualization > (pretty standard stuff I think). > > Ease of learning is a plus as I need to get something basic up and > running fairly fast. I've heard good things about Python in that respect. > Does anyone have alternative suggestions, or if you agree that Python Is > Good, would you suggest using APSW, pysqlite, or something else? > > Thanks, > Eli > > > >
[sqlite] ANN: MyGeneration offers FREE SQLite Architecture
MyGeneration is a very popular code generator and O/R Mapper capable of generating code for any language, including C#, VB.NET, Java, HTML and others. MyGeneration offers a FREE .NET architecture known as dOOdads which has now been implemented for SQLite version 2.x and 3.x. Here is an image with MyGeneration Browsing a SQLite database: http://download.mygenerationsoftware.com/SQLite.jpg MyGeneration will automatically generate fully functional SQLite C# and VB.NET code that can Insert, Delete, Modify and Query data from a SQLite table or view. It even automatically brings back your identity columns for you. The dOOdads home page is at: http://www.mygenerationsoftware.com/dOOdads/dOOdads.aspx An example of using a SQLite dOOdad in C# http://www.mygenerationsoftware.com/dOOdads/CSharp_MasterSample.aspx An example of using a SQLite dOOdad in VB.NET http://www.mygenerationsoftware.com/dOOdads/VBNet_MasterSample.aspx Mike Griffin MyGeneration Software http://www.mygenerationsoftware.com
[sqlite] SELECT syntax?
I have a strange problem, I can do this: UPDATE [Employees] SET [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] WHERE [EMAIL PROTECTED] But I can do this, SELECT [LastName],[FirstName] FROM [Employees] I have to remove the [] brackets on LastName and FirstName in order for it to work, are [] even allowed? I'm confused abit.
[sqlite] Database Version 2 or 3, can you query for it?
Is it possible to query the database to see if it's 2.x or 3.x, we need to know if the rowid's are 32 or 64 bit and we don't know what version we might be running on?
RE: [sqlite] Does SQLite.NET not support AUTOINCREMENT
Okay, I posted there, but can you tell me if this is correct SQL for the AUTOINCREMENT situation, SQLite3 didn't complain at all so I assumed it was okay? CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT, LastName TEXT, FirstName TEXT); > This question is probably better asked on the SQLite.NET sourceforge > forum. > By the way, I don't know the answer. > > Tim > >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] >> Sent: Monday, February 28, 2005 10:22 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] Does SQLite.NET not support AUTOINCREMENT >> >> I used this SQL: >> >> CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY >> AUTOINCREMENT, LastName TEXT, FirstName TEXT); >> >> and using SQLite3.exe did this >> >> SQLite3 employees.db >> .read create.sql >> .exit >> >> which created my database but no tools nor the SQLite.NET >> provider can read it, I get the error "malformed database >> schema near AUTOINCREMENT? >> >> >> >> >
[sqlite] Does SQLite.NET not support AUTOINCREMENT
I used this SQL: CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT, LastName TEXT, FirstName TEXT); and using SQLite3.exe did this SQLite3 employees.db .read create.sql .exit which created my database but no tools nor the SQLite.NET provider can read it, I get the error "malformed database schema near AUTOINCREMENT?
RE: [sqlite] Good Graphical Tool for 3.x
Thanx, I tried that would last night, unfortunately the trial version is so crippled its worthless. > > The SqlitePlus Database Manager is pretty good. Its very similar to > Microsoft > SQL Query Analyzer. Here is the URL: > > www.sqliteplus.com > > > Quoting Tim McDaniel <[EMAIL PROTECTED]>: > >> >> > -Original Message- >> > From: [EMAIL PROTECTED] >> > [mailto:[EMAIL PROTECTED] >> > Sent: Monday, February 28, 2005 12:43 PM >> > To: sqlite-users@sqlite.org >> > Subject: [sqlite] Good Graphical Tool for 3.x >> > >> > Is there a good enterprise manager like tool for SQLite 3.0? >> > I'm a windows guy and command line impaired, what I'm really >> > after is a database with a column in it that use the new >> > AUTOINCREMENT keyword, we're adding support for SQLite 3.x in >> > MyGeneration, we'll also be releasing an instance of our >> > dOOdads .NET architecture (C# and VB.NET) for SQLite in about a week. >> > >> > Anyway, can anybody help me ? Graphical Tool or db with >> > AUTOINCREMENT column in it. >> > >> > Mike Griffin >> > MyGeneration Software >> > http://www.mygenerationsoftware.com >> > >> >> Here's the best three that I found, though still not terribly >> exciting... >> >> http://www.dbtools.com.br/EN/index.php >> >> http://sqlite.org/contrib/download/sqlite3Explorer.zip?get=5 >> >> SQLiteCC (Can't find where I got it from, maybe Googling will turn it >> up) >> >> > > > > > > This message was sent using IMP, the Internet Messaging Program. > > >
RE: [sqlite] ticket 1147
> Metadata should be on-demand, and not automatically returned. As far as a > standard is concerned, OLEDB and ODBC do it differently and I'd have to > look it up. Here are the meta data standards: OLEDB Scheme Rowsets http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbschema_rowsets.asp SQL - 92 INFORMATION SCHEMA VIEWS http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp However, concerning the SQLite.NET provider I recommend putting your efforts into Schemas in ADO.NET 2.0 (We're already playing with them) http://msdn.microsoft.com/library/en-us/dnvs05/html/adonet2schemas.asp Since SQLite has no OLEDB driver forget the schema rowset, you could create pragma's that match the SQL-92 INFORMATION_SCHEMA Views. Mike Griffin MyGeneration Software http://www.mygenerationsoftware.com
[sqlite] Good Graphical Tool for 3.x
Is there a good enterprise manager like tool for SQLite 3.0? I'm a windows guy and command line impaired, what I'm really after is a database with a column in it that use the new AUTOINCREMENT keyword, we're adding support for SQLite 3.x in MyGeneration, we'll also be releasing an instance of our dOOdads .NET architecture (C# and VB.NET) for SQLite in about a week. Anyway, can anybody help me ? Graphical Tool or db with AUTOINCREMENT column in it. Mike Griffin MyGeneration Software http://www.mygenerationsoftware.com
RE: [sqlite] ticket 1147
Well, I can help here a little, having provided meta data for 11 different databases via our product MyGeneration. We do support SQLite too, including foreignkeys and all the good stuff. However, concerning columns contained results sets from say a select statement that's another story, you're lucky if there is any meta data all about them, and origination is unheard of. You can reverse engineer the language types and the DbType's however easilly enough, as far a computed columns and constant columns there isn't a database on the planet that will give you that concerning a result set, now from a table or a view that's a different story. >> -Original Message- >> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] >> Sent: Monday, February 28, 2005 10:30 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] ticket 1147 >> >> On Mon, 2005-02-28 at 08:48 -0700, Robert Simpson wrote: >> > 5. What we do with the schema information or how well we >> compute it is >> > irrelevant. >> > >> >> No. It is exceedingly relevant if you want any cooperation from >> me in addressing the issue. >> >> There seem to be a lot of people who are emphatic about knowing >> which column in which table a value in the result set originated >> from. This makes no sense to me. Why do they care? What do >> these people do with result set values that originate from >> expressions or which are constants? What about the result set >> of compound selects or of natural joins where the origin column >> is ambiguous? If knowing the original column is so important, >> what do people do with those cases? Disallow them? What do >> other database engines (PostgreSQL, Oracle, MySQL) do in the way >> of revealing the originating column for result set values? Do >> they have some mysterious API that I have never seen? >> >> And why do people care? Can nobody give me a use case where it >> is important to know what the originating column for a result >> set value is? > > 1. Calculated fields are not table bound, therefore no schema information > is available and the column is read-only for the sake of updateability. > > 2. For each column of type TK_COLUMN, schema information COULD be > extracted > if there was some mechanism in place. Compound selects and natural joins > in > SQLite do not generate columns of type TK_COLUMN -- those columns are > computed. Therefore see #1. > > Here are just a few things I can think of off the top of my head that I > cannot do right now for a resultset, but that I *can* do with additional > schema information: > > 1. I cannot determine which (if any) columns in a resultset are primary > or > foreign keys > 2. I cannot determine if a column is autoincrement > 3. I can't figure out if any indexes exist on a column > 4. I can't determine if the data came out of a view or a table, or > neither > one, so I can't tell if the data is theoretically updateable. > 5. I can't determine what database the query ran against (in the case of > ATTACH'd databases) > 6. I can't build UPDATE, INSERT or DELETE statements programmatically. > (Again, forget the ramifications how hard this may be -- the point is, you > can't even TRY because the schema information simply isn't available) > 7. I cannot diagram the query to show table usage or hierarchically > disassemble views to show table/view usage for a query. > > Robert > > >
[sqlite] MyGeneration is looking for Volunteers for our new .NET architecture
MyGeneration is a Code Generator / OR Mapper that supports SQLite (it's free). We are creating a new .NET architecture that will also support SQLite (among 10 other DBMS systems) if you are interested in becoming one of our SQLite adivsors and contributors see this site: http://www.entityspaces.net Thank you MyGeneration Software
