Re: [eclipse-clp-users] Dbi Query with more than 1 columns

Dear Kish,

  Thanks for the reply. Sorry for the confusion. When I use a query in Dbi with the following as SQL, where the select query will have more than one fields of type String(Corresponding to varchar in DB), eclipse exits abnormally. 

SQL = "select  c1.cityname, c1.cityString1, c1.cityString2 from city c1",

I am using the following environment

Windows 7 64 bit 
ECLiPSe ver 6.1 #192(x64_64_nt) using TkEclipse
MySQL version 5.6.22-winx64
MySQL and ECLiPSe are on the same machine. I am using Intel Core i7 laptop.

The SQL returns the right values when all the columns are of type integer. It will return the right values when first column is of type String and other columns are of type Int.


Thanks,
Shrirang
________________________________________
From: Kish Shen [kisshen_at_...5...]
Sent: 29 January 2015 16:48:52
To: Edgaonkar, Shrirang; eclipse-clp-users_at_lists.sourceforge.net
Subject: Re: [eclipse-clp-users] Dbi Query with more than 1 columns

Hi Shirang,

On 28/01/2015 08:53, Edgaonkar, Shrirang wrote:

> Check the following example which fails since tbl has two columns and first one is a Varchar(cityname).
> Eclipse tool crashes after execution of the script.

What exactly do you mean by "Eclipse tool crashes after execution of the
script"? I expect the program you show below to abort with a type error
when it is executed, when cursor_all_tuples/2 is called. Is this what
you are seeing? If not, please explain exactly what you mean by crash,
and more precise details on how the crash happened (which precise
version of ECLiPSe you are running, on what platform, etc.)


>
>   % SQL
>   SQL = "select  c1.cityname, c1.cityid from simulator.city c1",
>
>   % Template
>   Template = tbl(A, 1),

If your table's first column is a string type (VARCHAR), the type
specifier in the template should be a string, i.e. you should use a
template like

Template = tbl("london", 1)

For the template tbl(A, 1) you are using, the A -- a variable -- specify
that you expect the first column to be storing Prolog terms.
Such Prolog terms must have been inserted into the table from dbi, using
a prepared statement, because a special binary representation is passed
to MySQL by dbi.

>   session_sql_query(Session, Template, SQL, Cursor),
>
>   % Get Results
>   cursor_all_tuples(Cursor, Tuple),

When dbi reads this data back from SQL in a tuple, as you are doing
here, it expect the data for the first column to be in this special
binary format, which it will then convert back into a Prolog term.
If you did not write the data for this column using dbi as a general
Prolog term, the data will not be in the correct format, and
cursor_all_tuples/2 should throw a type error.


>
>   Template = tbl(A),   % Success

I am surprised that this would succeed. Please give more details on
exactly what you did here.

>   Template = tbl(0, 1),   % Success where 0 and 1 are integer fields(not displayed in the above script)

I am not sure what you are doing here exactly. Are you using the same
query and table, where the first column is of type VARCHAR? I would not
expect a string to be converted into an integer, unless the string
happens to be the characters representing a number (e.g. "12"), but
these are not city names...

> Template = tbl(0),   % Success

Again, what exactly are you doing here? The documentation suggest that
an error should be thrown if the template has a different arity from the
tuple.

Cheers,

Kish

______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.