Another ..Error[8]

D

DanW

Guest
Having problems with the: Display ALL

I have 16 tables total.

15 tables - allow for choices that feed into (select single now, on 3.0 beta) 1 table.

So , 15 Support tables, providing some choices into another table named "Systems".

So, I'm working in "Systems"

I'm trying to provide a selection of hardware for field "hw_platform) -

Primary key table: Hardware
Primary key field: id ( which it is in the table) ...
Linked fields: hardware_type

Now, on an new insert or search, the selections ( from Hardware.hardware_type) are properly displayed.

If I choose to "Show all" I get the (way) below error...



If I change to:
Primary key field: <leave blank>
Primary key table: Hardware
Linked fields: hardware_type

The results for SHOW ALL are good, but my selections do not show up as choices on an Insert.


Finally, If I change to:

Primary key field: hardware_type
Primary key table: Hardware
Linked fields: hardware_type

I alsoe end up the below errors....


Your SQL query (for debugging purpose): SELECT `Systems`.`name`, `Systems`.`core_ip`, `Systems`.`hw_platform`, `Systems`.`function`,
`Systems`.`region`, `Systems`.`status`, `Systems`.`notification`, `Systems`.`serial_number`, `Systems`.`asset_tag`, `Systems`.`searchflag`, `Systems`.`business_segment`, `Systems`.`projects`, `Systems`.`admin_rule_pri`, `Systems`.`admin_rule_sec`, `Systems`.`admin_pots_pri`, `Systems`.`admin_pots_sec`, `Systems`.`admin_plandesign_pri`, `Systems`.`address`, `Systems`.`room`, `Systems`.`location_other`, `Systems`.`city`, `Systems`.`state`, `Systems`.`local_contact_name`, `Systems`.`local_contact_phone`, `Systems`.`local_contact_pager`, `Systems`.`interface_info`, `Systems`.`disk_info`, `Systems`.`hardware_info`, `Systems`.`os_info`, `Systems`.`software_info`, `Systems`.`businesspartners`, `Systems`.`notes`, `Systems`.`add_date`, `Systems`.`last_update`, `Systems`.`fw_id`, `Systems`.`checkpoint_info`, `Systems`.`gauntlet_info`, `Systems`.`gated_info`, `Systems`.`ospatch_info`, `Systems`.`route_info`, `Systems`.`arp_info`, `Systems`.`firewall_ver`, `Systems`.`os_ver`, `Hardware`.`hardware_type` FROM Systems LEFT JOIN `Hardware` ON `Systems`.`hw_platform` = `Hardware`.`hardware_type` ORDER BY Systems.name ASC LIMIT 0 , 600


437 records found
[08] Error: during query execution.
SELECT `hardware_type` FROM `Hardware` WHERE `hardware_type` = SUN_E250
MySQL server said: Unknown column 'SUN_E250' in 'where clause'



Where AM I Caching the "SUN_E250"??

I do not see it in the select statement.
If I change the name SUN_E250, I still get an error. IE: Change Name to "BOSCO"


Also, this is not a column. It is an entry in a column.





Your SQL query (for debugging purpose): SELECT `Systems`.`name`, `Systems`.`core_ip`, `Systems`.`hw_platform`, `Systems`.`function`, `Systems`.`region`, `Systems`.`status`, `Systems`.`notification`, `Systems`.`serial_number`, `Systems`.`asset_tag`, `Systems`.`searchflag`, `Systems`.`business_segment`, `Systems`.`projects`, `Systems`.`admin_rule_pri`, `Systems`.`admin_rule_sec`, `Systems`.`admin_pots_pri`, `Systems`.`admin_pots_sec`, `Systems`.`admin_plandesign_pri`, `Systems`.`address`, `Systems`.`room`, `Systems`.`location_other`, `Systems`.`city`, `Systems`.`state`, `Systems`.`local_contact_name`, `Systems`.`local_contact_phone`, `Systems`.`local_contact_pager`, `Systems`.`interface_info`, `Systems`.`disk_info`, `Systems`.`hardware_info`, `Systems`.`os_info`, `Systems`.`software_info`, `Systems`.`businesspartners`, `Systems`.`notes`, `Systems`.`add_date`, `Systems`.`last_update`, `Systems`.`fw_id`, `Systems`.`checkpoint_info`, `Systems`.`gauntlet_info`, `Systems`.`gated_info`, `Systems`.`ospatch_info`, `Systems`.`route_info`, `Systems`.`arp_info`, `Systems`.`firewall_ver`, `Systems`.`os_ver`, `Hardware`.`hardware_type` FROM Systems LEFT JOIN `Hardware` ON `Systems`.`hw_platform` = `Hardware`.`hardware_type` ORDER BY Systems.name ASC LIMIT 0 , 600


437 records found
[08] Error: during query execution.
SELECT `hardware_type` FROM `Hardware` WHERE `hardware_type` = BOSCO
MySQL server

 
E

Eugenio

Guest
DanW wrote:

>
> Having problems with the: Display ALL
>
> I have 16 tables total.
>
> 15 tables - allow for choices that feed into (select single
> now, on 3.0 beta) 1 table.
>
> So , 15 Support tables, providing some choices into another
> table named "Systems".

Please post here the structure of your system and hardware tables, otherwise it's difficult to understand the problem.



(Latest version of DaDaBIK when this message was posted: 3.0 beta)
 
D

DanW

Guest
#
# Table structure for table `Systems`
#

CREATE TABLE `Systems` (
`name` varchar(20) NOT NULL default '',
`core_ip` varchar(15) NOT NULL default '0.0.0.0',
`hw_platform` varchar(20) default 'sun',
`function` varchar(20) NOT NULL default 'firewall',
`region` varchar(25) NOT NULL default '',
`status` varchar(30) NOT NULL default 'production',
`notification` varchar(25) NOT NULL default '',
`serial_number` varchar(20) default NULL,
`asset_tag` varchar(25) default NULL,
`searchflag` varchar(30) NOT NULL default '',
`business_segment` text,
`projects` text,
`admin_rule_pri` varchar(40) NOT NULL default 'Unknown',
`admin_rule_sec` varchar(40) NOT NULL default 'Unknown',
`admin_pots_pri` varchar(40) NOT NULL default 'Unknown',
`admin_pots_sec` varchar(40) NOT NULL default 'Unknown',
`admin_plandesign_pri` varchar(40) NOT NULL default 'Unknown',
`address` varchar(40) NOT NULL default 'Unknown',
`room` varchar(40) NOT NULL default 'Unknown',
`location_other` text,
`city` varchar(25) NOT NULL default '',
`state` char(2) NOT NULL default '',
`local_contact_name` varchar(40) NOT NULL default 'Unknown',
`local_contact_phone` varchar(20) NOT NULL default 'Unknown',
`local_contact_pager` varchar(20) NOT NULL default 'Unknown',
`interface_info` text,
`disk_info` text,
`hardware_info` text,
`os_info` text,
`software_info` text,
`businesspartners` text,
`notes` text,
`add_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_update` datetime default NULL,
`fw_id` int(11) NOT NULL auto_increment,
`checkpoint_info` text,
`gauntlet_info` text,
`gated_info` text,
`ospatch_info` mediumtext,
`route_info` mediumtext,
`arp_info` text,
`firewall_ver` varchar(80) NOT NULL default 'Unknown',
`os_ver` varchar(60) NOT NULL default 'Unknown',
PRIMARY KEY (`fw_id`,`name`),
KEY `name` (`name`)
) TYPE=MyISAM;




#
# Table structure for table `Hardware`
#

CREATE TABLE `Hardware` (
`id` int(11) NOT NULL auto_increment,
`hardware_type` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=31 ;

 
E

Eugenio

Guest
DanW wrote:

>
> #
> # Table structure for table `Systems`
> #
>

[....]
The correct one is:
Primary key table: Hardware
Primary key field: id
Linked fields: hardware_type
as you wrote in the first message, please post the error message you get only for this configuration.



(Latest version of DaDaBIK when this message was posted: 3.0 beta)
 
D

Debbie Sontag

Guest
I'm getting the same errors. I had an existing access database that was sent to me to have "converted" into a web database. I converted the data and imported it into a database table. Then I proceeded to create additional tables for the select fields like I had done on a previous installation of Dadabik (v2.x). When linking the sub-table to the main table, I did so using the data column as the select column because there was already data entered into the fields on the main table. Long story short - it worked like a charm in older version. As a side note, I have to have the data pulled into the main table by way of the actual data column not the record/id column because users will be exporting the data to CSV to create reports/charts from it.

I tried to do the same thing in v3 and am having a real problem getting the link between the two tables to work. I should also mention that I have several fields in my main table that are expected to link to another table (all different subs).

Here's the way I want to link to sub-table data:
Primary key field: Application
Primary key table: applications
Linked fields: Application
Order by: record (this is the primary key in the sub-table)

Here's the error code for above:
[08] Error: during query execution.
SELECT Application FROM applications WHERE Application = Headend
MySQL server said: Unknown column 'Headend' in 'where clause'


One other thing ... If I try to use two fields from a table as an insert into the main table, I get this:
Primary key field: Severity_Level
Primary key table: severity_levels
Linked fields: Severity_Level
Order by: record~Severity_Level

[08] Error: during query execution.
SELECT record, Severity_Level FROM severity_levels WHERE Severity_Level =
MySQL server said: You have an error in your SQL syntax near '' at line 1


Sorry for the amount of info here, but the more info the better chance of solving the problem.

Thanks!
Debbie
 
D

DanW

Guest
The correct one is:
Primary key table: Hardware
Primary key field: id
Linked fields: hardware_type
as you wrote in the first message, please post the error message you get only for this configuration.

Here is the error-
( The hardware shows up on an insert) but the error results NOW on a "show all"


[08] Error: during query execution.
SELECT `hardware_type` FROM `Hardware` WHERE `id` = Sun E250
MySQL server said: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'E250' at line 1


DaDaBIK 3.0 Beta
Your SQL query (for debugging purpose): SELECT `Systems`.`name`, `Systems`.`core_ip`, `Systems`.`hw_platform`, `Systems`.`function`, `Systems`.`region`, `Systems`.`status`, `Systems`.`notification`, `Systems`.`serial_number`, `Systems`.`asset_tag`, `Systems`.`searchflag`, `Systems`.`business_segment`, `Systems`.`projects`, `Systems`.`admin_rule_pri`, `Systems`.`admin_rule_sec`, `Systems`.`admin_pots_pri`, `Systems`.`admin_pots_sec`, `Systems`.`admin_plandesign_pri`, `Systems`.`address`, `Systems`.`room`, `Systems`.`location_other`, `Systems`.`city`, `Systems`.`state`, `Systems`.`local_contact_name`, `Systems`.`local_contact_phone`, `Systems`.`local_contact_pager`, `Systems`.`interface_info`, `Systems`.`disk_info`, `Systems`.`hardware_info`, `Systems`.`os_info`, `Systems`.`software_info`, `Systems`.`businesspartners`, `Systems`.`notes`, `Systems`.`add_date`, `Systems`.`last_update`, `Systems`.`fw_id`, `Systems`.`checkpoint_info`, `Systems`.`gauntlet_info`, `Systems`.`gated_info`, `Systems`.`ospatch_info`, `Systems`.`route_info`, `Systems`.`arp_info`, `Systems`.`firewall_ver`, `Systems`.`os_ver`, `Hardware`.`hardware_type` FROM Systems LEFT JOIN `Hardware` ON `Systems`.`hw_platform` = `Hardware`.`id` ORDER BY Systems.name ASC LIMIT 0 , 600
 
D

DanW

Guest
The primary-foreign key feature is now complete: it is possible to link a field of a table (foreign key) to a number of fields of another table (containing the primary key). The produced HTML select menu will have the linked field values as "options" and the primary key values as "values". This allow to use a normalized db design.


Eugenio: Please Explain...

If I set it one way, the Search All option works, but it breaks the drop down selections. If i set it up another way , the display all breaks, and the drop down selection works.

I think the problem is down around the LEFT JOIN & walking through all the array's & keys


Your SQL query (for debugging purpose): SELECT `Systems`.`name`, `Systems`.`core_ip`, `Systems`.`hw_platform`, `Systems`.`function`, `Systems`.`region`, `Systems`.`status`, `Systems`.`notification`, `Systems`.`serial_number`, `Systems`.`asset_tag`, `Systems`.`searchflag`, `Systems`.`business_segment`, `Systems`.`projects`, `Systems`.`admin_rule_pri`, `Systems`.`admin_rule_sec`, `Systems`.`admin_pots_pri`, `Systems`.`admin_pots_sec`, `Systems`.`admin_plandesign_pri`, `Systems`.`address`, `Systems`.`room`, `Systems`.`location_other`, `Systems`.`city`, `Systems`.`state`, `Systems`.`local_contact_name`, `Systems`.`local_contact_phone`, `Systems`.`local_contact_pager`, `Systems`.`interface_info`, `Systems`.`disk_info`, `Systems`.`hardware_info`, `Systems`.`os_info`, `Systems`.`software_info`, `Systems`.`businesspartners`, `Systems`.`notes`, `Systems`.`add_date`, `Systems`.`last_update`, `Systems`.`fw_id`, `Systems`.`checkpoint_info`, `Systems`.`gauntlet_info`, `Systems`.`gated_info`, `Systems`.`ospatch_info`, `Systems`.`route_info`, `Systems`.`arp_info`, `Systems`.`firewall_ver`, `Systems`.`os_ver`, `Hardware`.`hardware_type` FROM Systems LEFT JOIN `Hardware` ON `Systems`.`hw_platform` = `Hardware`.`id` ORDER BY Systems.name ASC LIMIT 0 , 600



437 records found
[08] Error: during query execution.
SELECT `hardware_type` FROM `Hardware` WHERE `id` = Sun E250
MySQL server said: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'E250' at line 1


Again, Hardware.id is an auto-index primary key field - it can NEVER have that value.



The above error is from a SHOW ALL and since I'm requesting a SHOW ALL from another table, I don't have a clue on why its's even picking this up.


I have approx. 15 other tables that feed into my main table, and have all the primary keys ( on the main table ) disabled until this is fixed.

Can you help?

dan
 
D

Debbie Sontag

Guest
adding post to have email replies sent to me. still having same problem as posted above.
 
J

Jon

Guest
Just wanted to add that I'm getting the exact same error as DanW. Insert works but show all and search don't. If I cut and paste the SQL query into a mysql prompt I get the correct results. It's that 2nd query (the one with the error[8]) that seems messed up. Where it is trying to get the id that corresponds to the hardware type. If it helps I can post my table structure and error message but it is exactly the same as DanW.
 
J

Jon

Guest
I think I may know what the problem is. Say I have 2 tables. one call machines with id (primary key, autoincrement), ser_number, mach_type. and another called location with id ( primary key, autoincrement), room_num, ser_num. I can't just link the 2 serial numbers together with:

Primary key table: machines
Primary key field: id
Linked fields: ser_number

because it tries to compare ser_number to id. What we may have to do is make ser_number the primary key in the table machines and then ser_num in location referance the machine.ser_number field.

See section 3.6.6 of the mysql manual. (Using foreign keys). In that example it would be:
Primary key table: person
Primary key field: id
Linked fields: owner

This is just an idea. I haven't tried it yet.
 
D

DanW

Guest
According to the Changelog
New features and feature changes

The primary-foreign key feature is now complete:

it is possible to link a field of a table (foreign key) to a number of fields of another table (containing the primary key).

The produced HTML select menu will have the linked field values as "options" and the primary key values as "values". This allow to use a normalized db design.


I think I need to rethink the way I've been populating databases.

after reading/re-reading the above statement and with what you wrote ( and looking at someone else's PERL scripts for use in DB operations) - I'm asking the following question:


What get's populated into the "final" or "main" table - when using selections?

A value or the actual "option" ?


dan
 
R

Rafael Alvarado

Guest
Thats IT !!!

Thank you Jon
----------------------------------

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);

see you have 2 tables, the person table will hold names of people

the shirt table will have a pull down (select) on the other field which is just a smallint referenced to person(id)

I used this same code and used Dadabik with it and it worked you can insert as well as show all records and search.

in Dadabik I went to the admin section and went to the SHIRT table, went to owner and entered the following fields:

Primary key field: id
Primary key table: person
Linked fields: name

ran it and worked like a charm.

My problem was that I had the field owner lets say as a CHAR(60) field, this appearantly wont work, you have to make it an INT and reference it to the table and ID in this case person(id)

hope this helps, I know a few of us were really stuck with this problem

thanks again Jon for pointing me to the right direction with this issue

thx

 
D

danW

Guest
You are correct - that works - Thanks ever so much.


However, How do I get all my original Data back into the "column" that once was a VARCHAR and now is an INT?

AM I going to have do some SED's and replace all the "Character Data" with the corresponding "ID"?
 
C

Chris O'Haver

Guest
I have a similar issue... but i am storing the ID in my table, not the display value...

SELECT `title` FROM `article` WHERE `id` =
MySQL server said: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

The error is that there is no value for id...

My guess is that the value need to be quoted... so an empty / null id would result in the query....

SELECT `title` FROM `article` WHERE `id` = ""

... which would not break (assuming the code handles an empty return).
 
C

Chris O'Haver

Guest
i have temporarily hacked this into working...

I added the following ugly hack to the top of execute_db
 
D

DanW

Guest
I went back to 2.2.1 - I have too many entries in my main table that I need to edit and put the corresponding "id"(integer) values linking them to the other tables..

... Then... I have other tables that are linked also...

Perhaps some other day...
 
E

Eugenio

Guest
DanW wrote:

>
> I went back to 2.2.1 - I have too many entries in my main
> table that I need to edit and put the corresponding
> "id"(integer) values linking them to the other tables..
>
> ... Then... I have other tables that are linked also...
>
> Perhaps some other day...

You can do this operation with a simple PHP script that read all your records and fill the ID field according to the main table.



(Latest version of DaDaBIK when this message was posted: 3.0 beta)
 
E

Eugenio

Guest
DanW wrote:

[....]

> What get's populated into the "final" or "main" table - when
> using selections?
>
> A value or the actual "option" ?

A value, the ID, which is different from the description displayed.



(Latest version of DaDaBIK when this message was posted: 3.0 beta)
 
E

Eugenio

Guest
DanW wrote:

>
> I went back to 2.2.1 - I have too many entries in my main
> table that I need to edit and put the corresponding
> "id"(integer) values linking them to the other tables..
>
> ... Then... I have other tables that are linked also...
>
> Perhaps some other day...

So DanW,
do you still have any problem with the foreign key feature? (apart from the fact you have to re-design your db).

In the next few week I want to release the final version 3.0 and I want to be sure that the foreign key feature hasn't any bug.



(Latest version of DaDaBIK when this message was posted: 3.0 beta)
 
E

Eugenio

Guest
Chris O'Haver wrote:

> i have temporarily hacked this into working...
>
> I added the following ugly hack to the top of execute_db

Could you describe better your problem? (with db structure, error messages....).



(Latest version of DaDaBIK when this message was posted: 3.0 beta)
 
Top