Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Fields of the same name in diff tables
Fields of the same name in diff tables [message #21685] Mon, 01 June 2009 02:43 Go to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
I am confused about how to do this:

TABLE_(HIVE_ACCESSORY)
    INT_ (ID) PRIMARY_KEY AUTO_INCREMENT
    STRING_ (NAME, 128) NOT_NULL
END_TABLE

TABLE_(HIVE)
    INT_ (ID) PRIMARY_KEY AUTO_INCREMENT
    STRING_ (NAME, 128) NOT NULL
    BLOB_ (DESCRIPTION)
END_TABLE


This results in compilation errors of duplicate ID and NAME values. I saw elsewhere in the forum that you should use INT_ on first use and INT on subsequent (of the same name). However, when I do that my HIVE table has only the field DESCRIPTION. It does not contain ID or NAME.

Any thoughts on how to accomplish my task? Do I have to create unique field names for all table entries?

Jeremy
Re: Fields of the same name in diff tables [message #21688 is a reply to message #21685] Mon, 01 June 2009 05:27 Go to previous messageGo to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
Hm, It seems to be only when I have more attributes, such as:

TABLE_(ABC)
    STRING_ (NAME, 80) NOT_NULL UNIQUE
END_TABLE

TABLE_(DEF)
    STRING (NAME, 80) NOT_NULL UNIQUE
END_TABLE


DEF will not have the NAME column. If I remove the NOT_NULL UNIQUE from each, then both will have the NAME column.

Jeremy
Re: Fields of the same name in diff tables [message #21690 is a reply to message #21688] Mon, 01 June 2009 08:41 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
jeremy_c wrote on Sun, 31 May 2009 23:27

Hm, It seems to be only when I have more attributes, such as:

TABLE_(ABC)
    STRING_ (NAME, 80) NOT_NULL UNIQUE
END_TABLE

TABLE_(DEF)
    STRING (NAME, 80) NOT_NULL UNIQUE
END_TABLE


DEF will not have the NAME column. If I remove the NOT_NULL UNIQUE from each, then both will have the NAME column.

Jeremy



Well, this rather looks like problem in generated script.

What DB is it?

Put SetTrace for your session and then check the .log file (Alt+L in theide).

Mirek
Re: Fields of the same name in diff tables [message #21706 is a reply to message #21690] Mon, 01 June 2009 16:23 Go to previous messageGo to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
It is Sqlite3. Here is the line causing the problem:

    STRING_ (NAME, 80) NOT_NULL UNIQUE


Here is the log line that shows what is going on behind the scenes:

ERROR Cannot add a NOT NULL column with default value NULL(0): Preparing: alter table SUPPLIER add
NAME text not null

Basically, I do not want it to be NULL and I want no default. I would like it to be required to be supplied by the application/user.

Jeremy
Re: Fields of the same name in diff tables [message #21708 is a reply to message #21706] Mon, 01 June 2009 17:34 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
I guess addind SQLDEFAULT would help here.

Mirek
Re: Fields of the same name in diff tables [message #21712 is a reply to message #21708] Mon, 01 June 2009 20:34 Go to previous messageGo to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
What would I default it to if I don't want a default? Simply what I want (in SQL) is:

CREATE TABLE ABC (
    NAME varchar(80) NOT NULL
);


Jeremy
Re: Fields of the same name in diff tables [message #21713 is a reply to message #21712] Mon, 01 June 2009 23:09 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
jeremy_c wrote on Mon, 01 June 2009 14:34

What would I default it to if I don't want a default? Simply what I want (in SQL) is:

CREATE TABLE ABC (
    NAME varchar(80) NOT NULL
);


Jeremy


I am not sure, I believe that the real reason is that choosen DB does not allow that.. You can easily test by issuing the command manually.

Mirek

[Updated on: Mon, 01 June 2009 23:09]

Report message to a moderator

Re: Fields of the same name in diff tables [message #21714 is a reply to message #21713] Mon, 01 June 2009 23:17 Go to previous messageGo to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
SQLite, PostgreSQL and MySQL all support the syntax given. What it does not support is:

CREATE TABLE ABC (
    NAME VARCHAR(80) NOT NULL DEFAULT NULL
);


Which seems to be what

TABLE_(ABC)
  STRING_  (NAME, 80) NOT_NULL
END_TABLE


is trying to do.

Jeremy

[Updated on: Mon, 01 June 2009 23:18]

Report message to a moderator

Re: Fields of the same name in diff tables [message #21716 is a reply to message #21714] Mon, 01 June 2009 23:30 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
jeremy_c wrote on Mon, 01 June 2009 17:17

SQLite, PostgreSQL and MySQL all support the syntax given. What it does not support is:

CREATE TABLE ABC (
    NAME VARCHAR(80) NOT NULL DEFAULT NULL
);


Which seems to be what

TABLE_(ABC)
  STRING_  (NAME, 80) NOT_NULL
END_TABLE


is trying to do.

Jeremy



OK, I will check Sqlite3 schema dialect.

Mirek
Re: Fields of the same name in diff tables [message #21717 is a reply to message #21716] Mon, 01 June 2009 23:36 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Well, the code generated seems to be:

create table ABCTEST (  NAME text not null )


and sqlite3 eats it without problems. Something else is going on...

Mirek
Re: Fields of the same name in diff tables [message #21718 is a reply to message #21717] Mon, 01 June 2009 23:58 Go to previous messageGo to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
Ok, here is the full definition:

TABLE_(SUPPLIER)
	INT_    (ID) PRIMARY_KEY AUTO_INCREMENT
	STRING_ (NAME, 80) NOT_NULL UNIQUE
	STRING_ (ADDRESS1, 128)
	STRING_ (ADDRESS2, 128)
	STRING_ (CITY, 128)
	STRING_ (STATE, 80)
	STRING_ (POSTAL_CODE, 24)
	STRING_ (PRIMARY_PHONE, 35)
	STRING_ (SECONDARY_PHONE, 35)
	STRING_ (EMAIL, 128)
	STRING_ (WEB_ADDRESS, 500)
	BLOB_   (DESCRIPTION)
END_TABLE


Here is the resulting SQL:

-- From S_BeekPRO.sql
create table SUPPLIER (
 ID integer primary key autoincrement,
 NAME text not null,
 ADDRESS1 text,
 ADDRESS2 text,
 CITY text,
 STATE text,
 POSTAL_CODE text,
 PRIMARY_PHONE text,
 SECONDARY_PHONE text,
 EMAIL text,
 WEB_ADDRESS text,
 DESCRIPTION blob
);

-- From A_BeekPRO.sql
create unique index IDX_SUPPLIER$NAME on SUPPLIER(NAME);


Here is the output from sqlite3 session:

C:\Develop\Tools\upp\out\MSC9.Debug.Debug_full.Gui>sqlite3 BeekPRO.db3
SQLite version 3.6.7
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema SUPPLIER
CREATE TABLE SUPPLIER (  ID integer primary key autoincrement , 
ADDRESS1 text, ADDRESS2 text, CITY text, STATE text, POSTAL_CODE 
text, PRIMARY_PHONE text, SECONDARY_PHONE text, EMAIL text, 
WEB_ADDRESS text, DESCRIPTION blob);


Here is the output from the log:

* C:\develop\tools\upp\out\MSC9.Debug.Debug_full.Gui\BeekPRO.exe 01.06.2009 17:52:23, user: jeremy

ERROR Cannot add a NOT NULL column with default value NULL(0): Preparing: alter table SUPPLIER add  NAME text not null
ERROR table SUPPLIER has no column named NAME(0): Preparing: create unique index IDX_SUPPLIER$NAME on SUPPLIER(NAME)


Jeremy

[Updated on: Tue, 02 June 2009 08:06]

Report message to a moderator

Re: Fields of the same name in diff tables [message #21719 is a reply to message #21718] Tue, 02 June 2009 07:36 Go to previous messageGo to next message
andrei_natanael is currently offline  andrei_natanael
Messages: 262
Registered: January 2009
Experienced Member
Hi, I remember a (short)discussion I had with Mirek some months ago about using sch files only for mapping DB fields in C++ code and let the programmer to create the DB structure in plain SQL because differences between SQL dialect implemented by different SQL "engines" in different ways... Now I think it's oblivious that sch MACROS doesn't cover good enough creation of tables(because difference between implementation).
It's simply to let the programmer to create tables with a custom SQL query, and let sch files be used only for identify(mapping) columns name in C++ code.
I think you may use a custom sql query(compatible with SQLite) to create tables in database.
Re: Fields of the same name in diff tables [message #21721 is a reply to message #21685] Tue, 02 June 2009 10:33 Go to previous messageGo to next message
mr_ped is currently offline  mr_ped
Messages: 825
Registered: November 2005
Location: Czech Republic - Praha
Experienced Contributor
While that sounds as good workaround, having working schema in C++ is very interesting too, because you will keep your versioning of DB definitions together with code versioning (if you use SVN/etc.).
If you do all your modifications directly to DB in some admin tool, you have no history documentation. (You can fight with it by doing export of DB schema and commit that file to SVN, but it feels like additional hassle to me, I prefer simple things, so having full DB generated from the info in C++ is IMHO nice feature).
(right now I have zero C++ DB projects, all my projects using some SQL are in php, but the wish for having the full schema generated trough source does apply there too)
Re: Fields of the same name in diff tables [message #21722 is a reply to message #21719] Tue, 02 June 2009 14:27 Go to previous messageGo to next message
jeremy_c is currently offline  jeremy_c
Messages: 175
Registered: August 2007
Location: Ohio, USA
Experienced Member
I like the idea of Ultimate++ generating table structures for me, I just think it needs a little more work and a little more documentation. Now, I am a database programmer by trade, so when it comes to an enterprise application, I doubt that UPP can ever keep up with what can be accomplished using the database, for instance, all sorts of trigger functions and other server side programming. For desktop applications w/simple databases, however, it's great.

Now, about managing SQL independent. What I do is I always have a directory structure such as:

project/docs
project/src
project/sql
project/sql/0001.sql
project/sql/0001_r.sql
project/sql/0002.sql
project/sql/0002_r.sql


0001.sql is the first version of my database schema. 0002.sql is the create/alter statements necessary to bring 0001.sql (current at the time) to my new version. So, if I were starting a new project, I could psql thedb < 0001.sql ... psql thedb < 0002.sql, etc... Now, 0001_r.sql and 0002_r.sql are "Revert" scripts which will undo the changes. I don't always make those but it's a good idea.

Jeremy
Re: Fields of the same name in diff tables [message #21723 is a reply to message #21718] Tue, 02 June 2009 14:47 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Well, this works:

Quote:


TABLE_(SUPPLIER)
INT_ (ID) PRIMARY_KEY AUTO_INCREMENT
STRING_ (NAME, 80) NOT_NULL SQLDEFAULT("")
STRING_ (ADDRESS1, 128)
STRING_ (ADDRESS2, 128)
STRING_ (CITY, 128)
STRING_ (STATE, 80)
STRING_ (POSTAL_CODE, 24)
STRING_ (PRIMARY_PHONE, 35)
STRING_ (SECONDARY_PHONE, 35)
STRING_ (EMAIL, 128)
STRING_ (WEB_ADDRESS, 500)
BLOB_ (DESCRIPTION)
END_TABLE



Hard to say what it dislikes.. I guess this is rather question to the sqlite3 forum...

Mirek
Re: Fields of the same name in diff tables [message #21724 is a reply to message #21722] Tue, 02 June 2009 14:50 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
jeremy_c wrote on Tue, 02 June 2009 08:27


Now, I am a database programmer by trade, so when it comes to an enterprise application, I doubt that UPP can ever keep up with what can be accomplished using the database, for instance, all sorts of trigger functions and other server side programming.



Obviously, triggers/views etc.. are beyond what you can achieve in .sch.

I usually put them into regular .sql files into the project, then use "DataFile" to load them and perform them when code starts in debug mode (SqlPerformScript).

Recent ide even has .sql highlighting Wink

Mirek
Re: Fields of the same name in diff tables [message #21725 is a reply to message #21719] Tue, 02 June 2009 14:52 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
andrei_natanael wrote on Tue, 02 June 2009 01:36

Hi, I remember a (short)discussion I had with Mirek some months ago about using sch files only for mapping DB fields in C++ code and let the programmer to create the DB structure in plain SQL because differences between SQL dialect implemented by different SQL "engines" in different ways... Now I think it's oblivious that sch MACROS doesn't cover good enough creation of tables(because difference between implementation).



It really is not that bad and it works fine. You just have to use it for what it is intended...

And, in fact, it is able to hide many DB engine differences for you. (YOu just cannot expect all of them to be hidden I guess Smile

Mirek
Re: Fields of the same name in diff tables [message #38500 is a reply to message #21725] Thu, 20 December 2012 19:20 Go to previous messageGo to next message
lectus is currently offline  lectus
Messages: 329
Registered: September 2006
Location: Brazil
Senior Member
Hi!

Sorry for ressurrecting this old thread.

But I noticed sch files really have trouble creating tables in Sqlite.

I had to add a create table command manually before everything. Before that I was getting an error.

Is that the expected behavior or am I missing something?
Re: Fields of the same name in diff tables [message #38552 is a reply to message #38500] Sun, 23 December 2012 10:17 Go to previous message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
lectus wrote on Thu, 20 December 2012 13:20

Hi!

Sorry for ressurrecting this old thread.

But I noticed sch files really have trouble creating tables in Sqlite.

I had to add a create table command manually before everything. Before that I was getting an error.

Is that the expected behavior or am I missing something?



Can you post a simple testcase, please?

Mirek
Previous Topic: Weird Date bevaviour in Sqlite
Next Topic: SqlLite3 crashes on Fetch()
Goto Forum:
  


Current Time: Fri Mar 29 05:47:48 CET 2024

Total time taken to generate the page: 0.01194 seconds