#233 odbc_call interface doesn't support object names starting with uppercase

[Markus Schatten]

If a database table or attribute name starts with an uppercase character each query after using odbc_attach throws an error of the form:

ERR - ODBC: odbc_error(S1000,ERROR: column rel1.c does not exist at character 103;
Error while executing the query)

This is due to the fact that all attribute and relation names are converted to lowercase.

A solution would be to quote all attribute and relation names with double quotes. I have been digging into the code of odbc_call.P, and propose to change the code from line 1030 to line 1055 (definition of predicate query_atom) with the following:

query_atom(query([agg_query(Function,Select,From,Where,Group)],,),QueryList,Diff,Blist,Olist) :-
%% --- ugly rule here: aggregate function only in SELECT Part of query ----
!, query_atom(agg_query(Function,Select,From,Where,Group),QueryList,Diff,Blist,Olist).
query_atom(query(Select,From,Where),QueryList,Diff,Blist,Olist) :-
add_quotes( Select, SelectQ ),
add_quotes( From, FromQ ),
add_quotes( Where, WhereQ ),
clause_atom(' SELECT ',SelectQ,',',QueryList,X1,Blist,Tl2),
clause_atom(' FROM ',FromQ,',',X1,X2,Tl2,Tl1),
clause_atom(' WHERE ',WhereQ,' AND ',X2,Diff,Tl1,Olist).
query_atom(del_query(From,Where),QueryList,Diff,Blist,Olist) :-
add_quotes( From, FromQ ),
add_quotes( Where, WhereQ ),
clause_atom(' DELETE FROM ',FromQ,',',QueryList,X1,Blist,Tlist),
clause_atom(' WHERE ',WhereQ,' AND ',X1,Diff,Tlist,Olist).
query_atom(ins_query(From,Columns,Where),QueryList,Diff,Blist,Olist) :-
writeln( 'Columns' ),
writeln( Columns ),
add_quotes( From, FromQ ),
add_quotes( Columns, ColumnsQ ),
add_quotes( Where, WhereQ ),
clause_ins_atom(' INSERT INTO',FromQ,',',QueryList,X1,Blist,Tlist),
clause_ins_atom(' (',ColumnsQ,',',X1,X2,Tlist,Tlist1),
clause_ins_atom(') VALUES (',WhereQ,',',X2,X3,Tlist1,Olist),
column_atom_a(')',X3,Diff,,).
query_atom(agg_query(Function,Select,From,Where,Group),QueryList,Diff,Blist,Olist) :-
writeln( 'Function' ),
writeln( Function ),
add_quotes( Function, FunctionQ ),
add_quotes( Select, SelectQ ),
add_quotes( From, FromQ ),
add_quotes( Where, WhereQ ),
add_quotes( Group, GroupQ ),
clause_atom(' SELECT ',FunctionQ,SelectQ,',',QueryList,X1,Blist,Tl3),
clause_atom(' FROM ',FromQ,',',X1,X2,Tl3,Tl2),
clause_atom(' WHERE ',WhereQ,' AND',X2,X3,Tl2,Tl1),
clause_atom(' GROUP BY ',GroupQ,',',X3,Diff,Tl1,Olist).
query_atom(negated_existential_subquery(Select,From,Where),QueryList,Diff,Blist,Olist) :-
add_quotes( Select, SelectQ ),
add_quotes( From, FromQ ),
add_quotes( Where, WhereQ ),
column_atom_a(' NOT EXISTS(',QueryList,X1,,),
clause_atom(' SELECT ',SelectQ,',',X1,X2,Blist,Tl2),
clause_atom(' FROM ',FromQ,',',X2,X3,Tl2,Tl1),
clause_atom(' WHERE ',WhereQ,' AND ',X3,X4,Tl1,Olist),
column_atom_a(')',X4,Diff,,).

%% Add quotes to attribute and relation names in queries
:- import str_cat/3 from string.
add_quotes( [], [] ).
add_quotes( [ att( Rel, Att ) | Rest ], [ att( Rout, Aout ) | Rest1 ] ) :-
add_quote( Rel, Rout ),
add_quote( Att, Aout ),
add_quotes( Rest, Rest1 ).
add_quotes( [ rel( R1, R2 ) | Rest ], [ rel( R1out, R2 ) | Rest1 ] ) :-
add_quote( R1, R1out ),
add_quotes( Rest, Rest1 ).
add_quotes( [ comp( att( Rel1, Att1 ), Op, att( Rel2, Att2 ) ) | Rest ], [ comp( att( Rel1Out, Att1Out ), Op, att( Rel2Out, Att2Out ) ) | Rest1 ] ) :-
add_quote( Rel1, Rel1Out ),
add_quote( Att1, Att1Out ),
add_quote( Rel2, Rel2Out ),
add_quote( Att2, Att2Out ),
add_quotes( Rest, Rest1 ).
add_quotes( [ comp( att( Rel1, Att1 ), Op, K ) | Rest ], [ comp( att( Rel1Out, Att1Out ), Op, K ) | Rest1 ] ) :-
add_quote( Rel1, Rel1Out ),
add_quote( Att1, Att1Out ),
add_quotes( Rest, Rest1 ).
add_quotes( [ comp( K, Op, att( Rel2, Att2 ) ) | Rest ], [ comp( K, Op, att( Rel2Out, Att2Out ) ) | Rest1 ] ) :-
add_quote( Rel2, Rel2Out ),
add_quote( Att2, Att2Out ),
add_quotes( Rest, Rest1 ).

add_quote( In, Out ) :-
str_cat( '"', In, Aux ),
str_cat( Aux, '"', Out ).

I have tested the code with unixODBC and PostgreSQL as a backend database, but since double quotes are standard SQL it should work with any type of database.

[Markus Schatten]

Since HTML ate the formatting of the code, I am attaching a textual file.