tberthel / jiql Goto Github PK

What steps will reproduce the problem?
1. Try to run the following sql -

select m.load_functions, m.to_arg_functions, m.access_callback, 
m.access_arguments, m.page_callback, m.page_arguments, m.title, 
m.title_callback, m.title_arguments, m.type, m.description, ml.*           from 
menu_links ml, menu_router m where m.path = ml.router_path and            
ml.menu_name = 'primary-links' and ml.plid in (0)           order by p1 asc, p2 
asc, p3 asc, p4 asc, p5 asc, p6 asc, p7 asc, p8 asc, p9 asc
What is the expected output? What do you see instead?


What version of the product are you using? On what operating system?

Latest jiql (Dec 15, 2010). Windows XP.

Please provide any additional information below.

Please email me for any other question.

For example Wordpress uses "ON DUPLICATE KEY UPDATE" in insert clauses which 
fails on jiql.

Example SQL query:

INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES 
('name', 'value', 'yes') ON DUPLICATE KEY UPDATE `option_name` = 
VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = 
VALUES(`autoload`)

What version of the product are you using? On what operating system?

Version from Dec 15 

What steps will reproduce the problem?
1. create table test (value1 char(255), value2 char(255)) 
2. insert into `test` (value1, value2) values ('1', 
'2')

What is the expected output? What do you see instead?

jiql claims that value2 is missing column value even thought column clearly has 
value. 

What version of the product are you using? On what operating system?

Version from Dec 15 

Please provide any additional information below.

If you run same query without line breaks it works fine.

What steps will reproduce the problem?
1. Enter the following sql -

select ml.link_path, ml.mlid, ml.router_path, ml.updated from menu_links ml 
where ml.updated = 1 or (router_path not in 
('node','rss.xml','batch','admin','logout','user','contacts','user/login','syste
m/files','taxonomy/autocomplete','admin/by-module','admin/by-task','admin/compac
t','filter/tips','node/add','comment/delete','comment/edit','admin/help','user/r
egister','user/password','user/autocomplete','admin/content','admin/reports','ad
min/build','admin/settings','node/%','admin/user','user/%','node/%/view','user/%
/view','admin/settings/actions','admin/user/rules','admin/reports/updates','admi
n/content/backup_migrate','admin/build/block','admin/content/comment','admin/con
tent/types','admin/settings/logging','admin/reports/dblog','admin/reports/status
','taxonomy/term/%','admin/build/path','admin/help/block','admin/help/color','ad
min/help/comment','admin/help/dblog','admin/help/filter','admin/help/help','admi
n/help/menu','admin/help/node','admin/help/path','admin/help/php','admin/help/sy
stem','admin/help/taxonomy','admin/help/update','admin/help/user','admin/setting
s/clean-urls','admin/content/node','admin/settings/date-time','node/%/delete','u
ser/%/delete','node/%/edit','admin/settings/error-reporting','admin/settings/fil
e-system','admin/settings/image-toolkit','admin/settings/filters','admin/build/m
enu','admin/build/modules','admin/settings/performance','admin/user/permissions'
,'admin/content/node-settings','admin/content/rss-publishing','comment/reply/%',
'node/%/revisions','admin/user/roles','admin/settings/site-information','admin/s
ettings/site-maintenance','admin/content/taxonomy','admin/build/themes','admin/r
eports/access-denied','admin/reports/page-not-found','admin/user/settings','admi
n/user/user','user/%/edit','admin/settings/admin','admin/build/block/list','admi
n/content/node/overview','admin/content/types/list','admin/build/path/list','adm
in/content/taxonomy/list','admin/user/rules/list','admin/user/user/list','admin/
settings/filters/list','admin/build/modules/list','admin/content/comment/new','u
ser/%/edit/account','admin/content/backup_migrate/export','admin/build/themes/se
lect','admin/build/menu/list','admin/build/themes/settings','admin/settings/acti
ons/manage','admin/settings/actions/orphan','admin/build/modules/uninstall','/co
ntacts/%/getvisa','admin/build/path/add','admin/settings/filters/add','admin/use
r/rules/add','admin/user/user/create','admin/content/comment/approval','admin/us
er/rules/check','admin/settings/clean-urls/check','admin/settings/actions/config
ure','admin/settings/date-time/lookup','admin/content/backup_migrate/delete','ad
min/build/path/edit','admin/user/roles/edit','admin/user/rules/edit','admin/repo
rts/updates/list','admin/reports/updates/check','admin/reports/status/php','admi
n/content/node-settings/rebuild','admin/reports/status/run-cron','admin/reports/
status/sql','admin/content/backup_migrate/files','admin/content/backup_migrate/r
estorefile','admin/build/block/add','admin/content/types/add','admin/build/menu/
add','admin/content/backup_migrate/schedule','admin/build/block/configure','admi
n/build/path/delete','admin/build/block/delete','admin/settings/filters/delete',
'admin/user/rules/delete','admin/reports/event/%','admin/content/taxonomy/%','ad
min/content/backup_migrate/restore','admin/build/menu/settings','admin/reports/u
pdates/settings','admin/settings/logging/dblog','admin/settings/filters/%','admi
n/build/menu-customize/%','admin/build/themes/settings/global','admin/content/ta
xonomy/%/list','admin/settings/filters/%/edit','admin/build/modules/list/confirm
','admin/build/menu-customize/%/list','admin/build/modules/uninstall/confirm','a
dmin/build/themes/settings/bluemarine','admin/build/themes/settings/bbtt','admin
/build/themes/settings/chameleon','admin/content/taxonomy/edit/term','admin/buil
d/themes/settings/garland','admin/build/block/list/js','admin/content/backup_mig
rate/files/manual','admin/build/themes/settings/marvin','admin/build/themes/sett
ings/minnelli','admin/build/themes/settings/pushbutton','admin/content/backup_mi
grate/files/scheduled','admin/build/themes/settings/zen','admin/build/themes/set
tings/zen_classic','admin/build/themes/settings/starterkit','admin/build/menu-cu
stomize/%/add','admin/build/block/list/bluemarine','admin/build/block/list/bbtt'
,'admin/build/block/list/chameleon','admin/settings/filters/%/configure','admin/
settings/actions/delete/%','admin/build/menu-customize/%/delete','admin/build/me
nu-customize/%/edit','admin/build/block/list/garland','admin/build/block/list/ma
rvin','admin/build/block/list/minnelli','admin/build/block/list/pushbutton','adm
in/settings/filters/%/order','user/reset/%/%/%','admin/build/block/list/zen','ad
min/build/block/list/zen_classic','admin/build/block/list/starterkit','admin/con
tent/taxonomy/add/vocabulary','node/%/revisions/%/view','node/%/revisions/%/dele
te','node/%/revisions/%/revert','admin/build/menu/item/%/delete','admin/build/me
nu/item/%/edit','admin/content/taxonomy/edit/vocabulary/%','admin/build/menu/ite
m/%/reset','admin/content/taxonomy/%/add/term') and external = 0 and customized 
= 1)

Another SQL with NOT -

select * from menu_links where router_path not in 
('node','rss.xml','batch','admin','logout','user','contacts','user/login','syste
m/files','taxonomy/autocomplete','admin/by-module','admin/by-task','admin/compac
t','filter/tips','node/add','comment/delete','comment/edit','admin/help','user/r
egister','user/password','user/autocomplete','admin/content','admin/reports','ad
min/build','admin/settings','node/%','admin/user','user/%','node/%/view','user/%
/view','admin/settings/actions','admin/user/rules','admin/reports/updates','admi
n/content/backup_migrate','admin/build/block','admin/content/comment','admin/con
tent/types','admin/settings/logging','admin/reports/dblog','admin/reports/status
','taxonomy/term/%','admin/build/path','admin/help/block','admin/help/color','ad
min/help/comment','admin/help/dblog','admin/help/filter','admin/help/help','admi
n/help/menu','admin/help/node','admin/help/path','admin/help/php','admin/help/sy
stem','admin/help/taxonomy','admin/help/update','admin/help/user','admin/setting
s/clean-urls','admin/content/node','admin/settings/date-time','node/%/delete','u
ser/%/delete','node/%/edit','admin/settings/error-reporting','admin/settings/fil
e-system','admin/settings/image-toolkit','admin/settings/filters','admin/build/m
enu','admin/build/modules','admin/settings/performance','admin/user/permissions'
,'admin/content/node-settings','admin/content/rss-publishing','comment/reply/%',
'node/%/revisions','admin/user/roles','admin/settings/site-information','admin/s
ettings/site-maintenance','admin/content/taxonomy','admin/build/themes','admin/r
eports/access-denied','admin/reports/page-not-found','admin/user/settings','admi
n/user/user','user/%/edit','admin/settings/admin','admin/build/block/list','admi
n/content/node/overview','admin/content/types/list','admin/build/path/list','adm
in/content/taxonomy/list','admin/user/rules/list','admin/user/user/list','admin/
settings/filters/list','admin/build/modules/list','admin/content/comment/new','u
ser/%/edit/account','admin/content/backup_migrate/export','admin/build/themes/se
lect','admin/build/menu/list','admin/build/themes/settings','admin/settings/acti
ons/manage','admin/settings/actions/orphan','admin/build/modules/uninstall','/co
ntacts/%/getvisa','admin/build/path/add','admin/settings/filters/add','admin/use
r/rules/add','admin/user/user/create','admin/content/comment/approval','admin/us
er/rules/check','admin/settings/clean-urls/check','admin/settings/actions/config
ure','admin/settings/date-time/lookup','admin/content/backup_migrate/delete','ad
min/build/path/edit','admin/user/roles/edit','admin/user/rules/edit','admin/repo
rts/updates/list','admin/reports/updates/check','admin/reports/status/php','admi
n/content/node-settings/rebuild','admin/reports/status/run-cron','admin/reports/
status/sql','admin/content/backup_migrate/files','admin/content/backup_migrate/r
estorefile','admin/build/block/add','admin/content/types/add','admin/build/menu/
add','admin/content/backup_migrate/schedule','admin/build/block/configure','admi
n/build/path/delete','admin/build/block/delete','admin/settings/filters/delete',
'admin/user/rules/delete','admin/reports/event/%','admin/content/taxonomy/%','ad
min/content/backup_migrate/restore','admin/build/menu/settings','admin/reports/u
pdates/settings','admin/settings/logging/dblog','admin/settings/filters/%','admi
n/build/menu-customize/%','admin/build/themes/settings/global','admin/content/ta
xonomy/%/list','admin/settings/filters/%/edit','admin/build/modules/list/confirm
','admin/build/menu-customize/%/list','admin/build/modules/uninstall/confirm','a
dmin/build/themes/settings/bluemarine','admin/build/themes/settings/bbtt','admin
/build/themes/settings/chameleon','admin/content/taxonomy/edit/term','admin/buil
d/themes/settings/garland','admin/build/block/list/js','admin/content/backup_mig
rate/files/manual','admin/build/themes/settings/marvin','admin/build/themes/sett
ings/minnelli','admin/build/themes/settings/pushbutton','admin/content/backup_mi
grate/files/scheduled','admin/build/themes/settings/zen','admin/build/themes/set
tings/zen_classic','admin/build/themes/settings/starterkit','admin/build/menu-cu
stomize/%/add','admin/build/block/list/bluemarine','admin/build/block/list/bbtt'
,'admin/build/block/list/chameleon','admin/settings/filters/%/configure','admin/
settings/actions/delete/%','admin/build/menu-customize/%/delete','admin/build/me
nu-customize/%/edit','admin/build/block/list/garland','admin/build/block/list/ma
rvin','admin/build/block/list/minnelli','admin/build/block/list/pushbutton','adm
in/settings/filters/%/order','user/reset/%/%/%','admin/build/block/list/zen','ad
min/build/block/list/zen_classic','admin/build/block/list/starterkit','admin/con
tent/taxonomy/add/vocabulary','node/%/revisions/%/view','node/%/revisions/%/dele
te','node/%/revisions/%/revert','admin/build/menu/item/%/delete','admin/build/me
nu/item/%/edit','admin/content/taxonomy/edit/vocabulary/%','admin/build/menu/ite
m/%/reset','admin/content/taxonomy/%/add/term') and external = 0 and updated = 
0 and customized = 0 order by depth desc

What is the expected output? What do you see instead?

Supposed to run without error. Exception seen -

menu_links Included Unknown column router_path 
NOT:[ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p4}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p3}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=plid}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p2}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p1}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=varchar(255), 
tablefield=module}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=varchar(255), tablefield=link_path}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=text, tablefield=options}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=varchar(255), 
tablefield=link_title}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=smallint(6), tablefield=external}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(11), tablefield=weight}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=smallint(6), 
tablefield=has_children}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=int(10), tablefield=mlid}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=varchar(255), tablefield=router_path}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=smallint(6), 
tablefield=expanded}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=varchar(32), tablefield=menu_name}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=smallint(6), 
tablefield=hidden}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=smallint(6), tablefield=updated}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p9}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p8}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), tablefield=p7}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=smallint(6), 
tablefield=customized}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=int(10), tablefield=p6}, ColumnInfo:{tablename=menu_links, 
tablefieldtype=smallint(6), tablefield=depth}, 
ColumnInfo:{tablename=menu_links, tablefieldtype=int(10), 
tablefield=p5}]:jiql.SQLParser 
table:menu_links;aliases:{m1=menu_links};values:{};selects:[*];selectAS:{};selec
tAS2:{};includealllist:[SQLCriteria:router_path 
NOTinnode','rss.xml','batch','admin','logout','user','contacts','user/login','sy
stem/files','taxonomy/autocomplete','admin/by-module','admin/by-task','admin/com
pact','filter/tips','node/add','comment/delete','comment/edit','admin/help','use
r/register','user/password','user/autocomplete','admin/content','admin/reports',
'admin/build','admin/settings','node/%','admin/user','user/%','node/%/view','use
r/%/view','admin/settings/actions','admin/user/rules','admin/reports/updates','a
dmin/content/backup_migrate','admin/build/block','admin/content/comment','admin/
content/types','admin/settings/logging','admin/reports/dblog','admin/reports/sta
tus','taxonomy/term/%','admin/build/path','admin/help/block','admin/help/color',
'admin/help/comment','admin/help/dblog','admin/help/filter','admin/help/help','a
dmin/help/menu','admin/help/node','admin/help/path','admin/help/php','admin/help
/system','admin/help/taxonomy','admin/help/update','admin/help/user','admin/sett
ings/clean-urls','admin/content/node','admin/settings/date-time','node/%/delete'
,'user/%/delete','node/%/edit','admin/settings/error-reporting','admin/settings/
file-system','admin/settings/image-toolkit','admin/settings/filters','admin/buil
d/menu','admin/build/modules','admin/settings/performance','admin/user/permissio
ns','admin/content/node-settings','admin/content/rss-publishing','comment/reply/
%','node/%/revisions','admin/user/roles','admin/settings/site-information','admi
n/settings/site-maintenance','admin/content/taxonomy','admin/build/themes','admi
n/reports/access-denied','admin/reports/page-not-found','admin/user/settings','a
dmin/user/user','user/%/edit','admin/settings/admin','admin/build/block/list','a
dmin/content/node/overview','admin/content/types/list','admin/build/path/list','
admin/content/taxonomy/list','admin/user/rules/list','admin/user/user/list','adm
in/settings/filters/list','admin/build/modules/list','admin/content/comment/new'
,'user/%/edit/account','admin/content/backup_migrate/export','admin/build/themes
/select','admin/build/menu/list','admin/build/themes/settings','admin/settings/a
ctions/manage','admin/settings/actions/orphan','admin/build/modules/uninstall','
/contacts/%/getvisa','admin/build/path/add','admin/settings/filters/add','admin/
user/rules/add','admin/user/user/create','admin/content/comment/approval','admin
/user/rules/check','admin/settings/clean-urls/check','admin/settings/actions/con
figure','admin/settings/date-time/lookup','admin/content/backup_migrate/delete',
'admin/build/path/edit','admin/user/roles/edit','admin/user/rules/edit','admin/r
eports/updates/list','admin/reports/updates/check','admin/reports/status/php','a
dmin/content/node-settings/rebuild','admin/reports/status/run-cron','admin/repor
ts/status/sql','admin/content/backup_migrate/files','admin/content/backup_migrat
e/restorefile','admin/build/block/add','admin/content/types/add','admin/build/me
nu/add','admin/content/backup_migrate/schedule','admin/build/block/configure','a
dmin/build/path/delete','admin/build/block/delete','admin/settings/filters/delet
e','admin/user/rules/delete','admin/reports/event/%','admin/content/taxonomy/%',
'admin/content/backup_migrate/restore','admin/build/menu/settings','admin/report
s/updates/settings','admin/settings/logging/dblog','admin/settings/filters/%','a
dmin/build/menu-customize/%','admin/build/themes/settings/global','admin/content
/taxonomy/%/list','admin/settings/filters/%/edit','admin/build/modules/list/conf
irm','admin/build/menu-customize/%/list','admin/build/modules/uninstall/confirm'
,'admin/build/themes/settings/bluemarine','admin/build/themes/settings/bbtt','ad
min/build/themes/settings/chameleon','admin/content/taxonomy/edit/term','admin/b
uild/themes/settings/garland','admin/build/block/list/js','admin/content/backup_
migrate/files/manual','admin/build/themes/settings/marvin','admin/build/themes/s
ettings/minnelli','admin/build/themes/settings/pushbutton','admin/content/backup
_migrate/files/scheduled','admin/build/themes/settings/zen','admin/build/themes/
settings/zen_classic','admin/build/themes/settings/STARTERKIT','admin/build/menu
-customize/%/add','admin/build/block/list/bluemarine','admin/build/block/list/bb
tt','admin/build/block/list/chameleon','admin/settings/filters/%/configure','adm
in/settings/actions/delete/%','admin/build/menu-customize/%/delete','admin/build
/menu-customize/%/edit','admin/build/block/list/garland','admin/build/block/list
/marvin','admin/build/block/list/minnelli','admin/build/block/list/pushbutton','
admin/settings/filters/%/order','user/reset/%/%/%','admin/build/block/list/zen',
'admin/build/block/list/zen_classic','admin/build/block/list/STARTERKIT','admin/
content/taxonomy/add/vocabulary','node/%/revisions/%/view','node/%/revisions/%/d
elete','node/%/revisions/%/revert','admin/build/menu/item/%/delete','admin/build
/menu/item/%/edit','admin/content/taxonomy/edit/vocabulary/%','admin/build/menu/
item/%/reset','admin/content/taxonomy/%/add/term, SQLCriteria:external=0, 
SQLCriteria:updated=0, 
SQLCriteria:customized=0];eitheroralllist:[];getOriginalSelectList:[*];groupby:o
rg.jiql.db.GroupBy@414fad;union:jiql.Union 
tables:{};aliases:{};selects:;selectAS:;jincludealllist:[];jeitheroralllist:[];i
ncludealllist:;eitheroralllist:

What version of the product are you using? On what operating system?

Latest version of jiql as at Dec 15, 2010. Windows XP.

Please provide any additional information below.

Please email me for any other question.

I'm very happy to see such a pretty work supporting MySQL on GAE. When I
try migrating WordPress to GAE with jiql as its MySQL Database. I come
across an error of SQL query INSERT INTO multiple rows.


Jiql works very well with SQL queries like this:

INSERT INTO wp_options (option_name, option_value, autoload) VALUES
('siteurl', 'http://localhost:8888/wordpress', 'yes')


While report error with multiple rows in one INSERT INTO query like this:

INSERT INTO wp_options (option_name, option_value, autoload) VALUES
('siteurl', 'http://localhost:8888/wordpress', 'yes'), ('blogname', 'My
Blog', 'yes')


Jiql reports error :   Unknown column or missing quotes


In MySQL such queries are allowed and could save much time for inserting
many rows. So I expect jiql can support such queries.

- What steps will reproduce the problem?

1. Follow the example "10)Enter the following SQL statements to ALTER with 
FOREIGN KEYS tables" in "jiql.jsp"

2. I have these data in two tables:
[realm_user]
    realm_passphrase     realm_username
    tigres   ruser1
    tigres   ruser2
[realm_userrole]
    realm_rolename   realm_user
    role2    ruser1
    role2    ruser2
    role1    ruser1

3. And get wrong result when execute SQL "select t1.*,t2.* from realm_userrole 
t1 , realm_user t2 where t1.realm_user=t2.realm_username and 
realm_user='ruser1'":

    t1.realm_user   t1.realm_rolename   t2.realm_username   t2.realm_passphrase
    ruser1  role2   ruser1  tigres
    ruser1  role2   ruser1  tigres


- What is the expected output? What do you see instead?

Expected result should be:

    t1.realm_user   t1.realm_rolename   t2.realm_username   t2.realm_passphrase
    ruser1  role1   ruser1  tigres
    ruser1  role2   ruser1  tigres

What steps will reproduce the problem?
1. try and create a table with foreign key constraint pointing to another
table.

What is the expected output? What do you see instead?
- I get the following exception:
Caused by: CONSTRAINT --> RATED Invalid Field Type on Table RATED2CATG
    at org.jiql.util.JGException.get(JGException.java:47)
    at org.jiql.util.JGException.get(JGException.java:55)
    at org.jiql.util.SQLParser.parseCreateTableParams(SQLParser.java:2226)
    at org.jiql.util.SQLParser.parse(SQLParser.java:1540)
    at org.jiql.util.SQLParser.setSQLParser(SQLParser.java:425)
    at org.jiql.util.SQLParser.<init>(SQLParser.java:408)
    at org.jiql.util.SQLParser.get(SQLParser.java:319)
    at org.jiql.jdbc.Statement.execute(Statement.java:130)
    at org.jiql.jdbc.Statement.executeUpdate(Statement.java:700)
    at
org.jiql.jdbc.jiqlPreparedStatement.executeUpdate(jiqlPreparedStatement.java:104
)
    at
simpleorm.sessionjdbc.SSessionJdbc.rawUpdateDBNoFlush(SSessionJdbc.java:724)

given the following SQL:

simpleorm.utils.SException$Jdbc: SQL: 
CREATE TABLE RATED2CATG (
    RATED_ID          VARCHAR(20) NOT NULL, 
    CATEGORY_ID       VARCHAR(20) NOT NULL, 
    MINIMUM           DOUBLE PRECISION NULL, 
    MAXIMUM           DOUBLE PRECISION NULL, 
    MEAN              DOUBLE PRECISION NULL, 
    NUM_RATINGS       INTEGER NULL, 
    PRIMARY KEY (RATED_ID, CATEGORY_ID),
    CONSTRAINT  RATED  FOREIGN KEY (RATED_ID) REFERENCES RATED (RATED_ID),
    CONSTRAINT  CATEGORY  FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY
(CATEGORY_ID))


What version of the product are you using? On what operating system?
>> Latest

Please provide any additional information below.
ISSUE 1>> I tried MANY permutations of the foreign key constraint,
including lengthening/shortening the key name, using quotes, combining
CONSTRAINT*FOREIGN KEY*REFERENCES onto 1 line (it was scattered before),
simply nothing works!!!

ISSUE 2>> I'd love to debug your source code, but I cannot get it to
compile. First of all, there are strange SunJCE errors, and I'm not sure
how to set up such security... perhaps someone could try and load the
source into Eclipse (or any other IDE) and see what they run into and post
some solutions (i.e. links to jars, etc)

ISSUE 3>> I already got around the other CREATE table issue discussed by
other people and is currently open by inserting a space between the table
name and the bracket, as suggested by one of the readers... thank you!

What steps will reproduce the problem?
1. Enter into jiql.jsp
2. Type " create  table  testable( name  varchar(18) ,countf int,yesno 
varchar(90) )"
3. (Note there is no space between the table name and the open parenthesis)

What is the expected output? Table created.

What do you see instead? SQL FAILED testable( Invalid Create Table name

What version of the product are you using? Last one downloaded yesterday
from jiql site.

On what operating system? Windows XP. (Java 1.6)


Please provide any additional information below.

Table Structure:
Test2
------
id --> varchar
name --> varchar

Query:
INSERT INTO test2(id,name) VALUES (?,?)

Java Code:
//prepare statement from query "INSERT INTO test2(id,name) VALUES (?,?)"
preparedStatement.setObject(index, value, jdbcType);

Where:
index --> 1
value --> user123
jdbcType --> 12

Exception:
java.sql.SQLException: Unknown column or missing quotes id
    at org.jiql.jdbc.Statement.execute(Statement.java:113)
    at org.jiql.db.InsertData.load(InsertData.java:153)
    at org.jiql.db.InsertData.execute(InsertData.java:94)
    at org.jiql.db.InsertData.execute(InsertData.java:56)
    at org.jiql.jdbc.Statement.execute(Statement.java:156)
    at org.jiql.jdbc.Statement.executeUpdate(Statement.java:700)
    at org.jiql.jdbc.jiqlPreparedStatement.executeUpdate(jiqlPreparedStatement.java:104)

I have a table structure defined as below;

Test2
------
id
name

Test3
------
id
name

Test4
------
id1
id2

When i try to join 2 tables:
select * from test2 test2 inner join test4 test4 on test2.id = test4.id1 --> 
this is ok

But when i try to add third table to the query:
select * from test2 test2 inner join test4 test4 on test2.id = test4.id1 inner 
join test3 test3 on test3.id = test4.id2 --> this is not working

UPDATE table SET NAME="stuff, stuff2, and such" WHERE KEY= "x"

Commas in values should be allowed.

WordPress 3.1 uses following sql in installation:

DELETE FROM wp_options WHERE option_name REGEXP '^rss_[0-9a-f]{32}(_ts)?$'

This causes jiql to crash with following message:

ST.invoke ERROR: null:public static com.caucho.quercus.env.Value 
com.caucho.quercus.lib.db.MysqlModule.mysql_query(com.caucho.quercus.env.Env,com
.caucho.quercus.env.StringValue,com.caucho.quercus.lib.db.Mysqli)->:Env[]:DELETE
 FROM wp_options WHERE option_name REGEXP 
'^rss_[0-9a-f]{32}(_ts)?$':Mysqli[ConnectionEntry[ds=jiqlDataSource[activeCount=
-5, autoCommit=true, closed=false, driverClass=org.jiql.jdbc.Driver, 
loginTimeout=60, maxCount=200, minCount=1, password=random, readOnly=false, 
url=jdbc:jiql://local/, useCount=5, user=user], user=null]]

[ERROR]: java.lang.NullPointerException
    at org.jiql.util.SQLParser.parse(SQLParser.java:1372)
    at org.jiql.util.SQLParser.setSQLParser(SQLParser.java:414)
    at org.jiql.util.SQLParser.<init>(SQLParser.java:401)
    at org.jiql.util.SQLParser.get(SQLParser.java:314)
    at org.jiql.jdbc.Statement.execute(Statement.java:111)
    at com.caucho.quercus.lib.db.JdbcConnectionResource.realQuery(JdbcConnectionResource.java:637)
    at com.caucho.quercus.lib.db.Mysqli.realQuery(Mysqli.java:863)
    at com.caucho.quercus.lib.db.Mysqli.query(Mysqli.java:779)
    at com.caucho.quercus.lib.db.MysqlModule.mysql_query(MysqlModule.java:593)
    at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:613)
    at com.google.appengine.tools.development.agent.runtime.Runtime.invoke(Runtime.java:100)
    at com.caucho.quercus.module.StaticFunction.invoke(StaticFunction.java:135)
    at com.caucho.quercus.env.JavaInvoker.callMethod(JavaInvoker.java:670)
    at com.caucho.quercus.env.JavaInvoker.call(JavaInvoker.java:605)
    at com.caucho.quercus.env.AbstractJavaMethod.callCopy(AbstractJavaMethod.java:138)
    at com.caucho.quercus.expr.FunctionExpr.evalImpl(FunctionExpr.java:180)
    at com.caucho.quercus.expr.FunctionExpr.evalCopy(FunctionExpr.java:150)
    at com.caucho.quercus.expr.SuppressErrorExpr.evalCopy(SuppressErrorExpr.java:145)
    at com.caucho.quercus.expr.AssignExpr.eval(AssignExpr.java:86)
    at com.caucho.quercus.statement.ExprStatement.execute(ExprStatement.java:64)
    at com.caucho.quercus.statement.BlockStatement.execute(BlockStatement.java:105)
    at com.caucho.quercus.program.Function.callImpl(Function.java:381)
    at com.caucho.quercus.program.Function.call(Function.java:310)
    at com.caucho.quercus.function.AbstractFunction.callMethod(AbstractFunction.java:375)
    at com.caucho.quercus.env.ObjectExtValue.callMethod(ObjectExtValue.java:794)
    at com.caucho.quercus.expr.MethodCallExpr.eval(MethodCallExpr.java:104)
    at com.caucho.quercus.statement.ExprStatement.execute(ExprStatement.java:64)
    at com.caucho.quercus.statement.BlockStatement.execute(BlockStatement.java:105)
    at com.caucho.quercus.program.Function.callImpl(Function.java:381)
    at com.caucho.quercus.program.Function.call(Function.java:310)
    at com.caucho.quercus.expr.FunctionExpr.evalImpl(FunctionExpr.java:182)
    at com.caucho.quercus.expr.FunctionExpr.eval(FunctionExpr.java:126)
    at com.caucho.quercus.statement.ExprStatement.execute(ExprStatement.java:64)
    at com.caucho.quercus.statement.BlockStatement.execute(BlockStatement.java:105)
    at com.caucho.quercus.program.Function.callImpl(Function.java:381)
    at com.caucho.quercus.program.Function.callCopy(Function.java:315)
    at com.caucho.quercus.expr.FunctionExpr.evalImpl(FunctionExpr.java:180)
    at com.caucho.quercus.expr.FunctionExpr.evalCopy(FunctionExpr.java:150)
    at com.caucho.quercus.expr.AssignExpr.eval(AssignExpr.java:86)
    at com.caucho.quercus.statement.ExprStatement.execute(ExprStatement.java:64)
    at com.caucho.quercus.statement.BlockStatement.execute(BlockStatement.java:105)
    at com.caucho.quercus.statement.IfStatement.execute(IfStatement.java:81)
    at com.caucho.quercus.statement.BlockStatement.execute(BlockStatement.java:105)
    at com.caucho.quercus.statement.SwitchStatement.execute(SwitchStatement.java:99)
    at com.caucho.quercus.statement.BlockStatement.execute(BlockStatement.java:105)
    at com.caucho.quercus.program.QuercusProgram.execute(QuercusProgram.java:400)
    at com.caucho.quercus.page.InterpretedPage.execute(InterpretedPage.java:89)
    at com.caucho.quercus.env.Env.executePageTop(Env.java:3443)
    at com.caucho.quercus.env.Env.executeTop(Env.java:3389)
    at com.caucho.quercus.servlet.GoogleQuercusServlet.service(GoogleQuercusServlet.java:214)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
    at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:58)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:43)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:122)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
    at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
    at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
    at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
    at com.google.apphosting.utils.jetty.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:70)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:351)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.Server.handle(Server.java:326)
    at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
    at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)
    at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
    at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
    at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
    at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
    at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

What version of the product are you using? On what operating system?

Version from Dec 15 

When inserting data that contains some complex text JIQL parsing fails.

Example:

INSERT INTO transforminfo VALUES ('TestStore Insert 
CUSTOMIZER_TRANSFORM_INFO_NAME','TestStore','views.business.context.modules.stor
efront.customizer.template.InsertCustomizerValidationView','','template/
genericRootTemplateObjectConfig.xml','<?xml version="1.0" encoding="UTF-8" 
standalone="no"?><!--
Copyright (c) 2002=2004 All Binary.
--><OBJECTCONFIG_NAME 
OBJECTCONFIG_NAME="GenericRootTransformInfoObjectConfig">

   <TRANSFORM_COMPONENTS_NAME>

      <COMPONENT_NAME TRANSFORM_INFO_NAME="$STOREFRONT_NAME 
ROOT_TRANSFORM_INFO_NAME">
      </COMPONENT_NAME>
      <COMPONENT_NAME TRANSFORM_INFO_NAME="$STOREFRONT_NAME 
GENERATOR_TRANSFORM_INFO_NAME">
      </COMPONENT_NAME>
      <COMPONENT_NAME TRANSFORM_INFO_NAME="$STOREFRONT_NAME 
TRANSFORM_COMPONENTS_PREVIEW ROOT_TRANSFORM_INFO_NAME">
      </COMPONENT_NAME>
      <COMPONENT_NAME TRANSFORM_INFO_NAME="$STOREFRONT_NAME 
TRANSFORM_COMPONENTS_PREVIEW GENERATOR_TRANSFORM_INFO_NAME">
      </COMPONENT_NAME>
      <COMPONENT_NAME TRANSFORM_INFO_NAME="$STOREFRONT_NAME 
TRANSFORM_COMPONENTS_SMALL_PREVIEW ROOT_TRANSFORM_INFO_NAME">
      </COMPONENT_NAME>
      <COMPONENT_NAME TRANSFORM_INFO_NAME="$STOREFRONT_NAME 
TRANSFORM_COMPONENTS_SMALL_PREVIEW GENERATOR_TRANSFORM_INFO_NAME">
      </COMPONENT_NAME>

   </TRANSFORM_COMPONENTS_NAME>

</OBJECTCONFIG_NAME>','','','','','1269841940425','1269841940425')

I have found to issues:

1.  What I have found is that the end is missing for some inserts. That is 
the last single quote  and closing parens ') are gone from the end.

2.  jiql_replace_comma is not removed after the XML data column in the 
insert statement.

Note you could increase performance considerably by not doing so much 
string concatenation.

What steps will reproduce the problem?

1. Create HibernateConfig with property of type boolean
2. Try to persist data

Please provide any additional information below.

HibernateConfig Input:

<property name="_available" column="Available" type="boolean" access="field"/>

Output:

Available --> bit Invalid Field Type on Table rs_Participant
- Unsuccessful: create table rs_Participant (ParticipantID varchar(255) not null
, Description text, Notes text, Available bit, LastName varchar(255), FirstName
varchar(255), UserID varchar(255) unique, pword varchar(255), Administrator bit,
 primary key (ParticipantID))