Menu
▾
▴
binarycloud-dev
|
From: Alex B. <en...@tu...> - 2001-08-22 02:02:01
|
hi Andreas,
I've just checked in a revision to your sql file which has an oracle version
of the create table statement.
I did the insert, and checked it with sqlplus:
SQL> SELECT newsid, news FROM news
2 ;
NEWSID
----------
NEWS
----------------------------------------------------------------------------
----
1
Hey, Congratualtions. Querymanager is working. You are reading a database
record
from the query_example. See user.mod.query_example for details.
So it worked, the data is there.
I run the page and get:
Module:
NewsId News
which I assume means that there are no results to display.
I look below, and find:
QueryManager: Database setup complete
QueryManager: Added query with id 20fd8766d8ae63f7eb407ffe7617de6e to
querylist
QueryManager: About to execute: 20fd8766d8ae63f7eb407ffe7617de6e
QueryManager: Handle is: 1
QueryManager: Query is: SELECT newsid, news FROM news
QueryManager: Sucsessfully executed query
then:
= Variables
==========================================================================
QueryManager: Result columnames: Array
(
[NEWSID] => 0
[NEWS] => 1
)
...............................................................
QueryManager: Resultbuffer: Array
(
)
...............................................................
I'll look into this tomorrow, anywhere you can point me?
...
also, if anyone is running oracle and wants a nice r2 tablespace:
CREATE TABLESPACE r2data DATAFILE 'r2data.dbs' SIZE 50M AUTOEXTEND ON NEXT
100M MAXSIZE 200M;
CREATE USER r2 IDENTIFIED BY testpasswd DEFAULT TABLESPACE r2data TEMPORARY
TABLESPACE temp;
GRANT CONNECT, RESOURCE TO r2;
:)
_alex
|
|
From: Andreas A. <a.a...@th...> - 2001-08-22 10:47:08
|
Hi All,
I think now is the time to establish some standards how query results and
queries are handled in bc.
RESULT FETCHING:
There are portability problems using array field names as array keys for
data. Because different dbms truncate the column headers differently It
might lead to unpredicted results.
So we should use indexed array for the result retrieving only. I'm not sure
how this will fit with EntityManager (I briefly looked over the code and saw
that by doing queries the $headers switch is turned on) but if it really
needs that a mapping could be done in EntityManager not in QueryManager.
STORED QUERIES:
QueryManager does have the abiliy to load stored queries. This is used in
EntityManager to load queries related to entities. But there might be the
need to load user queries for one page call. My proposal is to use the query
load system on init, optionally of course. So we could have a syntax like
this
<database>
<mcrypt>true</mrypt>
..
<queries>
<query>user.db.qry.UserQueriesFoo</query>
<query>user.db.qry.UserQueryBar</query>
</queries>
On Init the file user.db.qry.UserQueriesFoo is importet and the queries are
added to the queryList. The user then does have the abiliy to execute his
querys directly: qm->DoQuery("QRY_USER_MYQUERYNAME").
The query file would contain:
<queries>
<query>
<name>QRY_USER_MYQUERY</name>
<query>SELECT FROM ? WHERE newsId = ?</query>
<argtypes>
<type>text</type>
<type>integer</type>
</argtypes>
</query>
<query>
another query
</query>
</queries>
Or something similar.
Comments?
|
|
From: Alex B. <en...@tu...> - 2001-08-22 17:45:53
|
> Hi All,
>
> I think now is the time to establish some standards how query results and
> queries are handled in bc.
>
> RESULT FETCHING:
> There are portability problems using array field names as array keys for
> data. Because different dbms truncate the column headers differently It
> might lead to unpredicted results.
> So we should use indexed array for the result retrieving only. I'm not sure
> how this will fit with EntityManager (I briefly looked over the code and saw
> that by doing queries the $headers switch is turned on) but if it really
> needs that a mapping could be done in EntityManager not in QueryManager.
I would almost prefer to standardize db field names rather than avoid using
array keys, which are helpful for coding and debugging.
> STORED QUERIES:
> QueryManager does have the abiliy to load stored queries. This is used in
> EntityManager to load queries related to entities. But there might be the
> need to load user queries for one page call. My proposal is to use the query
> load system on init, optionally of course. So we could have a syntax like
> this
>
> <database>
> <mcrypt>true</mrypt>
> ..
> <queries>
> <query>user.db.qry.UserQueriesFoo</query>
> <query>user.db.qry.UserQueryBar</query>
> </queries>
>
> On Init the file user.db.qry.UserQueriesFoo is importet and the queries are
> added to the queryList. The user then does have the abiliy to execute his
> querys directly: qm->DoQuery("QRY_USER_MYQUERYNAME").
Why here? (I.e. in init, so early) why shouldn't query loading be the
responsibility of the code that needs the query?
> The query file would contain:
>
> <queries>
> <query>
> <name>QRY_USER_MYQUERY</name>
> <query>SELECT FROM ? WHERE newsId = ?</query>
> <argtypes>
> <type>text</type>
> <type>integer</type>
> </argtypes>
> </query>
> <query>
> another query
> </query>
> </queries>
>
> Or something similar.
>
> Comments?
I like the format above, nice n' clean :)
_a
|
|
From: Andreas A. <a.a...@th...> - 2001-08-22 19:07:52
|
Hi Alex,
> > So we should use indexed array for the result retrieving only.
> > I'm not surehow this will fit with EntityManager (I briefly looked over
the
> > code and saw that by doing queries the $headers switch is turned on) but
if it > > really needs that a mapping could be done in EntityManager not in
> > QueryManager.
> I would almost prefer to standardize db field names rather than
> avoid using array keys, which are helpful for coding and debugging.
Thats another option if the portability is guaranteed (nothing is
truncated).
But what if the db system changes its behaviour in a new versions?
Everything has to be fixed. For debugging and code readability it's nice.
Another option to get around this at QueryManager level could be an
additional option field within the query statement e.g.:
$query = array(
'QUERY' =>
"SELECT VeryLongColumThatMightBeTruncated, ".
"anotherVeryLongColThatIsDefenetlyTruncated ".
"FROM tablewithverylongfieldnames WHERE foo = ?",
'ARGTYPES' => array('integer'),
'FIELDNAMES' => array('Name1','Nam2');
);
Respective for the XML complement. So QueryManager can loop over the indexed
arrays and generate an asoc array like:
$buffer[rownumber] = array(
"Name1" => Datafromverlongcolumn1,
"Name" => Datafromverylongcolumn2
);
So we do not have the truncation problem but maybe a bit less performance
and a bit more typing in this mode of operation.
Additionally we should do a naming convetion for tables/fields. Then we are
100%save ;-)
> > STORED QUERIES:
> > QueryManager does have the abiliy to load stored queries. This
> is used in
> > EntityManager to load queries related to entities. But there
> might be the
> > need to load user queries for one page call. My proposal is to
> use the query
> > load system on init, optionally of course. So we could have a
> syntax like
> > this
> >
> > <database>
> > <mcrypt>true</mrypt>
> > ..
> > <queries>
> > <query>user.db.qry.UserQueriesFoo</query>
> > <query>user.db.qry.UserQueryBar</query>
> > </queries>
> >
> > On Init the file user.db.qry.UserQueriesFoo is importet and the
> queries are
> > added to the queryList. The user then does have the abiliy to
> execute his
> > querys directly: qm->DoQuery("QRY_USER_MYQUERYNAME").
>
> Why here? (I.e. in init, so early) why shouldn't query loading be the
> responsibility of the code that needs the query?
Hmm. If I have a page and I want to load a set of queries that are used by
some or all modules of this page. I load the query-set at init time and the
modules do not have to care about query loading. On the other hand the
module could load the query passed by a parameter "user.mod.foo.Queries".
Hmmm....
[query xml format]
> I like the format above, nice n' clean :)
Jepp. I think I'll do some experiments with that. And also with the query
loading stuff.
Andi
|
|
From: Andreas A. <a.a...@th...> - 2001-08-22 19:24:32
|
Re, > Respective for the XML complement. So QueryManager can loop over > the indexed arrays and generate an asoc array like: > > $buffer[rownumber] = array( > "Name1" => Datafromverlongcolumn1, > "Name" => Datafromverylongcolumn2 > ); Forgot to mention this. Using this method makes fetching result metadata obsolete. That would reduce db load and saving time, I guess. But the main point is to get around truncation. Andi |
|
From: Alex B. <en...@tu...> - 2001-08-22 19:55:24
|
>> Respective for the XML complement. So QueryManager can loop over
>> the indexed arrays and generate an asoc array like:
>>
>> $buffer[rownumber] = array(
>> "Name1" => Datafromverlongcolumn1,
>> "Name" => Datafromverylongcolumn2
>> );
>
> Forgot to mention this. Using this method makes fetching result metadata
> obsolete. That would reduce db load and saving time, I guess.
>
> But the main point is to get around truncation.
Right, but isn't that a lot of work for not much benefit? And where to we
store the metadata, in the Entity definition?
<field>
<name>LongMixedCaseFieldNameWithNoStandard</name>
<short_name>LONGMIXED</short_name>
</field>
egh! :)
---
I rethought an earlier message, so:
-I don't want QueryManager to 'enforce' any field naming or other
standards. I don't want enforcement anywhere in the system.
-What I would like to enforce with scripts, etc is this:
:: For modules to be distributed with binarycloud or available from the
(upcoming) module repository, you must:
-Use field and table/entity names with less than 32 chars.
-Use CAPS for all field and table/entity names.
-Verify that your SQL & Schemas work with Oracle, MySQL, and
PostgreSQL.
Does that sounds reasonable to everyone?
_a
|
|
From: Alex B. <en...@tu...> - 2001-08-22 21:30:50
|
hmm, on second thought I'd rather require _lowercase_ field name that UPPERCASE just because I (and I think many others) find them to be easier to read. for example, in oracle both of these queries work: select newsid from news; select NEWSID from NEWS; _a >>> Respective for the XML complement. So QueryManager can loop over >>> the indexed arrays and generate an asoc array like: >>> >>> $buffer[rownumber] = array( >>> "Name1" => Datafromverlongcolumn1, >>> "Name" => Datafromverylongcolumn2 >>> ); >> >> Forgot to mention this. Using this method makes fetching result metadata >> obsolete. That would reduce db load and saving time, I guess. >> >> But the main point is to get around truncation. > > Right, but isn't that a lot of work for not much benefit? And where to we > store the metadata, in the Entity definition? > > <field> > <name>LongMixedCaseFieldNameWithNoStandard</name> > <short_name>LONGMIXED</short_name> > </field> > > egh! :) > > --- > > I rethought an earlier message, so: > > -I don't want QueryManager to 'enforce' any field naming or other > standards. I don't want enforcement anywhere in the system. > -What I would like to enforce with scripts, etc is this: > > :: For modules to be distributed with binarycloud or available from the > (upcoming) module repository, you must: > -Use field and table/entity names with less than 32 chars. > -Use CAPS for all field and table/entity names. > -Verify that your SQL & Schemas work with Oracle, MySQL, and > PostgreSQL. > > > Does that sounds reasonable to everyone? > > _a > > > > _______________________________________________ > binarycloud-dev mailing list > bin...@li... > http://lists.sourceforge.net/lists/listinfo/binarycloud-dev > |
|
From: Andreas A. <a.a...@th...> - 2001-08-23 10:30:20
|
Hi Alex, > hmm, on second thought I'd rather require _lowercase_ field name that > UPPERCASE I think thats much better uahh: SELECT NEWSID FROM NEWS hard to read > for example, in oracle both of these queries work: > select newsid from news; > select NEWSID from NEWS; does this work? SELECT newsid FROM news I like this to distinguish alias/table.field stuff from SQL STATEMENTS. Andi |
|
From: Alex B. <en...@tu...> - 2001-08-23 17:12:25
|
> > does this work? > > SELECT newsid FROM news > > I like this to distinguish alias/table.field stuff from SQL STATEMENTS. works fine. so, I think everything I said before, but with lowercase: :: For modules to be distributed with binarycloud or available from the > (upcoming) module repository, you must: > -Use field and table/entity names with less than 32 chars. > -Use lowercase for all field and table/entity names. > -Verify that your SQL & Schemas work with Oracle, MySQL, and > PostgreSQL. |
|
From: Andreas A. <a.a...@th...> - 2001-08-23 12:14:30
|
Hi Alex, > for example, in oracle both of these queries work: > select newsid from news; How are the column headers returned for this query also in lowercase? Or in uppercase? In MySQL they are returned as typed in the query. Andi |
|
From: Konstantin C. <Kon...@da...> - 2001-08-23 12:30:47
|
Andreas Aderhold wrote:
> > for example, in oracle both of these queries work:
> > select newsid from news;
>
> How are the column headers returned for this query also in lowercase? Or in
> uppercase? In MySQL they are returned as typed in the query.
>
Wouldn't it be better just to define column and table names as
case-insensitive and always uppercase/lowercase them in QueryManager before
doing a MetaBase query?
Then, the user will be free in choosing their naming convention.
Doesn't Oracle (or some other databases) really support '_'? In that case,
especially, allowing the user to mix upper and lower cases in the names would
be nice. E.g. "FurbeeId" is more readable than "furbeeid" or "FURBEEID".
Just my 2 pence :-)
--
* * Konstantin Chuguev Francis House
* * Application Engineer 112 Hills Road
* Tel: +44 1223 302992 Cambridge CB2 1PQ
D A N T E WWW: http://www.dante.net United Kingdom
|
|
From: Alex B. <en...@tu...> - 2001-08-23 17:19:11
|
> Andreas Aderhold wrote: > >>> for example, in oracle both of these queries work: >>> select newsid from news; >> >> How are the column headers returned for this query also in lowercase? Or in >> uppercase? In MySQL they are returned as typed in the query. >> > > Wouldn't it be better just to define column and table names as > case-insensitive and always uppercase/lowercase them in QueryManager before > doing a MetaBase query? 1) yes that is entirely possible 2) I don't like it because I think it's a hell of a lof more elegant to have 'name sync' between entities, entity paths, and sql. > Then, the user will be free in choosing their naming convention. > Doesn't Oracle (or some other databases) really support '_'? In that case, > especially, allowing the user to mix upper and lower cases in the names would > be nice. E.g. "FurbeeId" is more readable than "furbeeid" or "FURBEEID". > > Just my 2 pence :-) The above is opinion, not doctrine... does anyone else have an opinion on this one? _a > -- > * * Konstantin Chuguev Francis House > * * Application Engineer 112 Hills Road > * Tel: +44 1223 302992 Cambridge CB2 1PQ > D A N T E WWW: http://www.dante.net United Kingdom > > > > > _______________________________________________ > binarycloud-dev mailing list > bin...@li... > http://lists.sourceforge.net/lists/listinfo/binarycloud-dev > |
|
From: Andreas A. <a.a...@th...> - 2001-08-23 18:09:58
|
Hi, > > Wouldn't it be better just to define column and table names as > > case-insensitive and always uppercase/lowercase them in > > QueryManager before doing a MetaBase query? > 2) > I don't like it because I think it's a hell of a lof more elegant to have > 'name sync' between entities, entity paths, and sql. I'm with you. If we establish a standard like proposed, we must keep it throughout the system. It makes live lot easier. > > be nice. E.g. "FurbeeId" is more readable than "furbeeid" or "FURBEEID". > The above is opinion, not doctrine... does anyone else have an opinion on > this one? Personally I like the furbeeId, userId, furbeeName syntax. But with the compatibility issues and the complexity of the system in mind it might be really wired in large applications. Its easier to track the path from a entity to the database if everything is in sync. Additionally I prefer do add the strtolower() thing in buffer creation ( as originally done with strotupper() ). So users have the freedom to name the database fields mixed case (FurbeeId) but the system gets them as all lowercase (furbeeid). But for the repository modules it should stay as proposed. Note to the query syntax: As I said before I like to write queris with SQL statements UPPERCASE. I'd love to add this to the standard 'caus it make the queries MUCH more readable. So from my point of view I think the following is very acceptable: For modules to be distributed with binarycloud or available from the (upcoming) module repository, as well as for binarycloud core components you must: - Use field and table/entity names with less than 32 chars - Use lowercase for all database, field and table/entity names - Use CAPS for SQL command (SELECT, AS, FROM, DELETE...) - Verify that your SQL & Schemas work with Oracle, MySQL, and PostgreSQL. Andi |
|
From: Alex B. <en...@tu...> - 2001-08-23 18:44:25
|
>>> Wouldn't it be better just to define column and table names as >>> case-insensitive and always uppercase/lowercase them in >>> QueryManager before doing a MetaBase query? > >> 2) >> I don't like it because I think it's a hell of a lof more elegant to have >> 'name sync' between entities, entity paths, and sql. > > I'm with you. If we establish a standard like proposed, we must keep it > throughout the system. It makes live lot easier. Agreed. I don't want to put in any code that necessarily enforces the standard, but I want to document and adhere to it. >>> be nice. E.g. "FurbeeId" is more readable than "furbeeid" or "FURBEEID". >> The above is opinion, not doctrine... does anyone else have an opinion on >> this one? > Personally I like the furbeeId, userId, furbeeName syntax. But with the > compatibility issues and the complexity of the system in mind it might be > really wired in large applications. Its easier to track the path from a > entity to the database if everything is in sync. actually, I agree: I prefer MooId or mooId, etc. But I think if we have to go with 'single case' the best is lowercase field_name > Additionally I prefer do add the strtolower() thing in buffer creation ( as > originally done with strotupper() ). So users have the freedom to name the > database fields mixed case (FurbeeId) but the system gets them as all > lowercase (furbeeid). But for the repository modules it should stay as > proposed. hmm. that is enforcing policy, but I think I'm willing to break my statement above and say "yeah" :) > Note to the query syntax: As I said before I like to write queris with SQL > statements UPPERCASE. I'd love to add this to the standard 'caus it make the > queries MUCH more readable. yes, ditto. > So from my point of view I think the following is very acceptable: > > For modules to be distributed with binarycloud or available from the > (upcoming) module repository, as well as for binarycloud core components > you must: > - Use field and table/entity names with less than 32 chars > - Use lowercase for all database, field and table/entity names > - Use CAPS for SQL command (SELECT, AS, FROM, DELETE...) > - Verify that your SQL & Schemas work with Oracle, MySQL, and PostgreSQL. deal. anyone else have comments/etc? I'm preparing a significant revision on the binarycloud coding standards... this will make it in. best, _alex |
|
From: Alex B. <en...@tu...> - 2001-08-23 17:17:40
|
> Hi Alex, > >> for example, in oracle both of these queries work: >> select newsid from news; > > How are the column headers returned for this query also in lowercase? Or in > uppercase? In MySQL they are returned as typed in the query. it _looks_ like oracle returns them in uppercase, but I haven't checked QueryManager for strtoupper(). _a |
|
From: Andreas A. <a.a...@th...> - 2001-08-23 18:10:01
|
Hi Alex, > it _looks_ like oracle returns them in uppercase, but I haven't checked > QueryManager for strtoupper(). Ok if so, we should add strtolower() in process results. Andi |
|
From: Alex B. <en...@tu...> - 2001-08-23 18:47:25
|
agreed. -a > Hi Alex, > > >> it _looks_ like oracle returns them in uppercase, but I haven't checked >> QueryManager for strtoupper(). > > Ok if so, we should add strtolower() in process results. > > > Andi > > _______________________________________________ > binarycloud-dev mailing list > bin...@li... > http://lists.sourceforge.net/lists/listinfo/binarycloud-dev > |
|
From: Andreas A. <a.a...@th...> - 2001-08-23 10:30:18
|
Hi Alex, > Right, but isn't that a lot of work for not much benefit? And where to we > store the metadata, in the Entity definition? > > <field> > <name>LongMixedCaseFieldNameWithNoStandard</name> > <short_name>LONGMIXED</short_name> > </field> Hmm. That looks crappy. Ok, forget about this idea. > -I don't want QueryManager to 'enforce' any field naming or other > standards. I don't want enforcement anywhere in the system. > -What I would like to enforce with scripts, etc is this: > > :: For modules to be distributed with binarycloud or available from the > (upcoming) module repository, you must: > -Use field and table/entity names with less than 32 chars. > -Use CAPS for all field and table/entity names. > -Verify that your SQL & Schemas work with Oracle, MySQL, and > PostgreSQL. > Does that sounds reasonable to everyone? Yes. It does :) Andi |
|
From: Alex B. <en...@tu...> - 2001-08-22 19:34:25
|
> Hi Alex,
>
>>> So we should use indexed array for the result retrieving only.
>>> I'm not surehow this will fit with EntityManager (I briefly looked over
> the
>>> code and saw that by doing queries the $headers switch is turned on) but
> if it > > really needs that a mapping could be done in EntityManager not in
>>> QueryManager.
>> I would almost prefer to standardize db field names rather than
>> avoid using array keys, which are helpful for coding and debugging.
> Thats another option if the portability is guaranteed (nothing is
> truncated).
> But what if the db system changes its behaviour in a new versions?
> Everything has to be fixed. For debugging and code readability it's nice.
My point is that if we establish fairly restrictive standards about table
and field naming, we can get around those problems and ensure compatibility
between oracle, mySQL, and Postgres:
-table names * field names no longer than 32 chars
-all field and table names in CAPS
for example.
> Another option to get around this at QueryManager level could be an
> additional option field within the query statement e.g.:
>
> $query = array(
> 'QUERY' =>
> "SELECT VeryLongColumThatMightBeTruncated, ".
> "anotherVeryLongColThatIsDefenetlyTruncated ".
> "FROM tablewithverylongfieldnames WHERE foo = ?",
> 'ARGTYPES' => array('integer'),
> 'FIELDNAMES' => array('Name1','Nam2');
> );
>
> Respective for the XML complement. So QueryManager can loop over the indexed
> arrays and generate an asoc array like:
>
> $buffer[rownumber] = array(
> "Name1" => Datafromverlongcolumn1,
> "Name" => Datafromverylongcolumn2
> );
>
> So we do not have the truncation problem but maybe a bit less performance
> and a bit more typing in this mode of operation.
>
> Additionally we should do a naming convetion for tables/fields. Then we are
> 100%save ;-)
Re: the above, I'd prefer to have something that works with a standard,
instead of trying to build code that works with 'everything'.
I'm not saying QueryManager should process incoming queries (like
strtoupper) but I think we should standardize those names elsewhere in the
system.
_a
>
>>> STORED QUERIES:
>>> QueryManager does have the abiliy to load stored queries. This
>> is used in
>>> EntityManager to load queries related to entities. But there
>> might be the
>>> need to load user queries for one page call. My proposal is to
>> use the query
>>> load system on init, optionally of course. So we could have a
>> syntax like
>>> this
>>>
>>> <database>
>>> <mcrypt>true</mrypt>
>>> ..
>>> <queries>
>>> <query>user.db.qry.UserQueriesFoo</query>
>>> <query>user.db.qry.UserQueryBar</query>
>>> </queries>
>>>
>>> On Init the file user.db.qry.UserQueriesFoo is importet and the
>> queries are
>>> added to the queryList. The user then does have the abiliy to
>> execute his
>>> querys directly: qm->DoQuery("QRY_USER_MYQUERYNAME").
>>
>
>> Why here? (I.e. in init, so early) why shouldn't query loading be the
>> responsibility of the code that needs the query?
> Hmm. If I have a page and I want to load a set of queries that are used by
> some or all modules of this page. I load the query-set at init time and the
> modules do not have to care about query loading. On the other hand the
> module could load the query passed by a parameter "user.mod.foo.Queries".
> Hmmm....
>
> [query xml format]
>> I like the format above, nice n' clean :)
> Jepp. I think I'll do some experiments with that. And also with the query
> loading stuff.
>
> Andi
>
>
>
>
> _______________________________________________
> binarycloud-dev mailing list
> bin...@li...
> http://lists.sourceforge.net/lists/listinfo/binarycloud-dev
>
|
|
From: Andreas A. <a.a...@th...> - 2001-08-23 10:30:24
|
Hi Alex, > My point is that if we establish fairly restrictive standards about table > and field naming, we can get around those problems and ensure > compatibility between oracle, mySQL, and Postgres: Agreed if those conventions do really work with all Metabase supported dbms. Or at least with the most widely used ones. I would add Informix to the list above. > Re: the above, I'd prefer to have something that works with a standard, > instead of trying to build code that works with 'everything'. Ok, I see your point. I'm not a db expert an my experience with different dbms are limited. Would a standard proposed in the other mail work with most dbms without trouble? What does Manuel think? > I'm not saying QueryManager should process incoming queries (like > strtoupper) but I think we should standardize those names elsewhere in the > system. Ok, and everybody !must! strictly obey. I.e. writing a query with mysql SELECT foo FROM bar will return the columnheader "foo" even if the field ist named "FOO". So the queries must entirely be written UPPERCASE. Andi |
|
From: Andreas A. <a.a...@th...> - 2001-08-22 10:47:21
|
Hi Alex, > > So it worked, the data is there. > > I run the page and get: > > Module: > NewsId News > > which I assume means that there are no results to display. > > I look below, and find: > > > > > QueryManager: Database setup complete > QueryManager: Added query with id 20fd8766d8ae63f7eb407ffe7617de6e to > querylist > QueryManager: About to execute: 20fd8766d8ae63f7eb407ffe7617de6e > QueryManager: Handle is: 1 > QueryManager: Query is: SELECT newsid, news FROM news > QueryManager: Sucsessfully executed query > > > then: > > = Variables > ========================================================================== > > QueryManager: Result columnames: Array > ( > [NEWSID] => 0 > [NEWS] => 1 > ) > > ............................................................... > QueryManager: Resultbuffer: Array > ( > ) > > ............................................................... > > > I'll look into this tomorrow, anywhere you can point me? Hmm. I think thats a problem with the column naming. They are mixed case in MySQL and also retured in mixed case with the result meta data while they seem to be upper case in oracle. I'm not experienced with oracle so I'm not comfortable with the differences. But I guess thats the prob. Note/Assumtion: So the changes you made to the schmema will work with oracle but not with mysql. MySQL assumes that the column newsId in the SELECT statement represents exactly the fieldname "newsId" in the table. So SELECT newsid and SELECT newsId are acutually addressing two different fields in MySQL. But back to the prob: Try the following: Look at QueryManager line 636: and uncomment this line: $_buffer[$row][strtoupper($field)] = $value; and comment out the following: $_buffer[$row][$field] = $value; Then in the output method of the query_example the asoc array should be changed to macht the uppercase indices. Andi |
|
From: Alex B. <en...@tu...> - 2001-08-22 18:26:05
|
> Hmm. I think thats a problem with the column naming. They are mixed case in
> MySQL and also retured in mixed case with the result meta data while they
> seem to be upper case in oracle.
> I'm not experienced with oracle so I'm not comfortable with the differences.
> But I guess thats the prob.
Here's a desc on the table:
SQL> desc news
Name Null? Type
----------------------------------------- --------
----------------------------
NEWSID NUMBER(38)
NEWS VARCHAR2(255)
so yes, you seem to be right. :)
> Note/Assumtion:
> So the changes you made to the schmema will work with oracle but not with
> mysql. MySQL assumes that the column newsId in the SELECT statement
> represents exactly the fieldname "newsId" in the table. So SELECT newsid and
> SELECT newsId are acutually addressing two different fields in MySQL.
How so? i.e. if you create the table with all uppercase field names in
mySQL, they should all be compatible.
I'll work on getting QM to see the table + fields, then report back..
> But back to the prob: Try the following:
>
> Look at QueryManager line 636:
> and uncomment this line:
>
> $_buffer[$row][strtoupper($field)] = $value;
>
> and comment out the following:
>
> $_buffer[$row][$field] = $value;
>
> Then in the output method of the query_example the asoc array should be
> changed to macht the uppercase indices.
OK, here's what I did:
I made sure all my field references were upper-case:
CREATE TABLE NEWS (NEWSID int default NULL,NEWS varchar(255));
(text is an invalid datatype in oracle)
$query = array(
'QUERY' => 'SELECT NEWSID, NEWS FROM NEWS',
'ARGTYPES' => array('integer','text'),
and
$this->result[$row]['NEWSID'];
$this->result[$row]['NEWS'];
That worked.
I'm doing a sync now, can we try and get SQL that works with MySQL _and_
Oracle?
I'll shortly be able to test on both.
I've done a sync, you can update your sandbox.
_a
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X