Firebird: Hyphens in Fieldnames. lazarus / 16-Databases ---------------------------------------------------------------------- cov · 2013-04-11 06:31 My Firebird Database Tables have field names which are gathered from other documents, but over which I have no control. I have appended an underscore to the field names to make sure that keywords like 'UPDATE' and 'BY' are not used. Instead, the Fieldnames would be converted to 'UPDATE_' and 'BY_'. Which means that other Fieldnames like 'TITLE' and 'AUTHOR' are converted to 'TITLE_' and 'AUTHOR_'; not ideal, but I can live with it. My problem now is that Firebird rejects hyphens, so that the FieldnameS 'TITLE-1' AND 'TITLE-2' are rejected. I suppose I could always replace the hyphens with underscores, but are there any other Gotchas regarding illegal characters in SQL queries? ---------------------------------------------------------------------- BigChimp · 2013-04-11 06:48 Yes. You should alphanumeric characters, preferably uppercase, or underscores; don't start with a number or underscore (as you found out); max field length is 32. No spaces, percent signs, or any other non-alphanumeric/non-underscore character. Avoid reserved words (generally SQL words like insert,update,delete,commit,rollback,table,field...) You are dynamically adding fields which is often a sign of bad database design. Could you tell us a bit about the data structure/aim of the program? Perhaps things can be made much simpler. A solution could be to have a mapping table: ID integer not null primary key THEIRFIELDNAME varchar OURFIELDNAME varchar if the structure of the documents you are processing is the same but only the values differ. Also: you might be better off asking your questions on the Firebird mailing list - they're very helpful there. That said, I'm happy to help you as much as I can. ---------------------------------------------------------------------- exdatis · 2013-04-11 06:52 See: http://www.firebirdsql.org/en/reference-manuals/ http://www.firebirdfaq.org/ Regards ---------------------------------------------------------------------- BigChimp · 2013-04-11 06:59 @Exdatis: would you know the exact link for identifier naming? Had a look but couldn't find it quickly. Edit: wait, now that I think about it, most of those liimits might actually be specified by the SQL standards... only the table/object length limit of 32 bytes isn't. Never mind Edit2: as usual, PostgreSQL has good documentation: http://www.postgresql.org/docs/7.4/interactive/sql-syntax.html#SQL- SYNTAX-IDENTIFIERS ---------------------------------------------------------------------- exdatis · 2013-04-11 07:09 Excellent, thanks for the link, BigChimp, very usefull Regards ---------------------------------------------------------------------- cov · 2013-04-11 07:29 Hi Big Chimp. The Database takes it's information from an AutoCAD drawing titleblock. Depending on the Drawing Office, this can have all sorts of markers by which the drawing needs to be searchable and this will vary widely by Company, Discipline, Industry, etc. A company manufacturing car parts might have a particular bolt on the bill of materials for a part and may need to search all the parts which may contain that bolt. That's a bit of a radical example, but the point that I'm making is that I really don't want to restrict the fields, the number of fields or how they are named. I suppose a solution would be to label the fields sequentially and then have the first record contain the real Field name. ---------------------------------------------------------------------- BigChimp · 2013-04-11 08:15 Thanks for the explanation! Quote from: cov on April 11, 2013, 09:29:43 amI suppose a solution would be to label the fields sequentially and then have the first record contain the real Field name. No, no, NOOOOOOO! Putting metadata about a table in the "first record" (or any record, really) is a definite no-no in relational database design land. Rather have a different table that contains the translations (see above). However, why not store a list of marker names and a lookup value, call it Markers. Then have your data table with columns like MarkerValue (which obviously contains the value), and a foreign key reference to Markers (call it MarkerName). Just thinking out loud... and quickly. Corrections welcome. ---------------------------------------------------------------------- cov · 2013-04-11 08:29 Yes, but this means that the TDBGrid can't really be used. I suppose it's not a big deal, just convenient. ---------------------------------------------------------------------- BigChimp · 2013-04-11 09:00 I'd recommend to get the data model right first... It sounds to me like you are going to have n tables, one per drawing office... What if you want to do searches across drawing offices? E.g. how many bolts have been specified? What is the average size of widget x? Of course, the data model depends on a lot of things. I can't advise you on the entire data model unless I know the complete specs for the system - IOW take this advice as it comes. However, the things you describe (dynamic field names, adding metadata in the first record) ARE signs of a pathological database design... which may however be appropriate in special circumstances. Summary: get somebody who knows databases to look at your design before you have to work around db problems in code later. ---------------------------------------------------------------------- cov · 2013-04-11 11:57 I really like the idea of using a 'Markers' table to hold the Drawing attribute Tags. Is there any way of manipulating the TDBGrid headings to draw the Headings row from such a table? ---------------------------------------------------------------------- BigChimp · 2013-04-11 12:22 Probably, not a GUI guy really. Have a look here http://wiki.lazarus.freepascal.org/Grids_Reference_Page probably also many hits on the forum. ---------------------------------------------------------------------- exdatis · 2013-04-11 13:24 That's it! (Grids reference page):"NOTE: as of March 21st 2013 tsqlquery has no way to clear indexes, but as a work around you can set unidirectional:=true, then set it to false and this will clear the indexes. In order to reuse the tsqlquery component with another sql statement the indexes must be cleared after sorting or a exception will be raised when you open the tsqlquery with a different SQL statement." Thank you very much, BigChimp. Regards ---------------------------------------------------------------------- gsa · 2013-04-11 16:46 Quote from: cov on April 11, 2013, 08:31:29 am My problem now is that Firebird rejects hyphens, so that the FieldnameS 'TITLE-1' AND 'TITLE-2' are rejected. You can use hyphens in a field name but you have to quote the name with double quotes. Double quoting allows reserved words as identifiers, too. Sample: create table "Table" ("TITLE-1" varchar(20)); But using double quoted identifiers requires exactly the same notation in all statements: select "TITLE-1" from "Table"; Gerd ---------------------------------------------------------------------- cov · 2013-04-11 20:18 That's good to know, Gerd, thank you.