Re: Visual Basic 6 + MySQL
At 04:39 PM 1/19/2007, Nuno Vaz Oliveira wrote: Hello! I'm thinking to develop a database interface for our company and I thought great when I discovered that Visual Basic 6 was able to interact with MySQL. That's because VB6 is very easy and PHP (even being fast, but its hard for me) gets slower because the source code for HTML pages needs to go from the server to the client and big pages must carry the data and the tags, images, css, formating, etc... However I'm not going to start a project without knowing that VB6+ODBC Driver is able to do all the needed operations to connect/read/write/insert/update a database. Additionaly I would like to know if I can manage users from VB and to backup the DB. If anyone can tell me something about it I appreciate. I would also like (Not Mandatory) to use a secure connection (SSL) but I don't know if this is possible using VB6/ODBC Driver. Note: The MySQL Database will be served from our company server. (Any links to tutorials/tips/recommendations/forums about this subject are also welcome) Thanks - Nuno Why VB6? This isn't an anti-Microsoft position, but that language has been left to die on the vine. So I'd think about .NET, or RealBASIC, or ... or You will have to decide. I trust you realize that in VB ( 6 or .NET) you are doing desktop development, not Internet development; so take a hard look at what can be done with a back end scripting language like PHP, JavaScript on the client side, and the use of AJAX. You might come close to what you need. Having said that, from the second through fourth paragraphs of your email, you do not really seem know what you are about. Why don't you build a trivial little application in VB working from a MySQL database and see if it does what you want it to do. Write a routine to stuff it full of pseudo data, using tables / indexes which will approximate the size of your envisioned application. As for relative difficulty, both PHP and VB are very easy languages to use, but are used in completely different contexts. If your thinking that VB is easy is based on Microsoft's demonstrations, believe me the real world does not work that way; there is much more back end plumbing involved. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Anybody in here think of http://www.vbmysql.com? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Hello Miles, At 04:39 PM 1/19/2007, Nuno Vaz Oliveira wrote: Why VB6? This isn't an anti-Microsoft position, but that language has been left to die on the vine. So I'd think about .NET, or RealBASIC, or ... or You will have to decide. Why VB6? That is an easy one. First, it's the one we have available on our office and tha boss ain't going to buy another. Either it is VB6 or VBA. Second, because I know how to work with it and even being left to die on the vine it still does what I need right? I'm working with VB6 for a long time and there is nothing that I've ever tried to do that VB6 wasn't able to handle. (I have a 20 years old car... Still 4 wheels, 2 doors, glass windows...) I trust you realize that in VB ( 6 or .NET) you are doing desktop development, not Internet development; so take a hard look at what can be done with a back end scripting language like PHP, JavaScript on the client side, and the use of AJAX. You might come close to what you need. About that, the only problems are: 1 - Programming an interface in XHTML is a lot harder than in any other programming language for desktop. 2 - The amount of data travelling between client and server is 10 times bigger than the real data. The rest is the XHTML tags, the CSS, the formatting, etc... This is a problem because we are serving the MySQL database and the WebServer also and our bandwith is very limited. I've started this web development with Apache+PHP+MySQL and I can already list the full clients information. Less than 2KBytes from the MySQL database and the client had to download a 44KBytes... Our upload is 256Kbits which means that a page with the name, age, address and a few more info from a client will take about 4~5 seconds to load... I've tried with VB6 this morning and it was instantaneous (I know it was 0.34seconds sinse the request to the data retrival)... It's a huge difference in time and I'm talking about a small page. Having said that, from the second through fourth paragraphs of your email, you do not really seem know what you are about. Why don't you build a trivial little application in VB working from a MySQL database and see if it does what you want it to do. Write a routine to stuff it full of pseudo data, using tables / indexes which will approximate the size of your envisioned application. As for relative difficulty, both PHP and VB are very easy languages to use, but are used in completely different contexts. That is your opinion and it can be considered to be correct. However I know exactly what I want to do and it's easier to ask someone who knows about it than to try it myself. I might do something wrong and assume that it is not doable or I might forget to try something really needed and later found out it is no possible. That's why I came to this list... It's full of nice people that give help and advice for free! I'm already working on the Web version and on the desktop version of this project but any contribution is appreciated. If your thinking that VB is easy is based on Microsoft's demonstrations, believe me the real world does not work that way; there is much more back end plumbing involved. I don't tink that VB is easy. I know it is!!! I'm working with VB6 for more than 10 years i think... At this point any new language is harder than VB. However I've been great with PHP. Started a few weeks ago and I'm going pretty good with the manual and the lists but... I've a long time with VB... Regards - Miles Thompson Thank you for replying :) - Nuno Vaz Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Hello John, Anybody in here think of http://www.vbmysql.com? The site is not working correctly :( I've found a lot of references to that site but the articles are all missing. Thanks anyway ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Nuno Vaz Oliveira wrote: Hello John, Anybody in here think of http://www.vbmysql.com? The site is not working correctly :( I've found a lot of references to that site but the articles are all missing. This article isn't missing: http://www.vbmysql.com/articles/vb6-mysql/accessing-mysql-blob-columns-using-visual-basic-6/ Nor is this one http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-1/ Nor this one: http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-7/ I guess if you could point out which articles are missing, we could help you then, but so far, all the articles seem to be there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
On Sunday 21 January 2007 08:17, Nuno Vaz Oliveira wrote: Hello Miles, At 04:39 PM 1/19/2007, Nuno Vaz Oliveira wrote: Why VB6? This isn't an anti-Microsoft position, but that language has been left to die on the vine. So I'd think about .NET, or RealBASIC, or ... or You will have to decide. Why VB6? That is an easy one. First, it's the one we have available on our office and tha boss ain't going to buy another. Just FYI: you can get the Express version of the VB.Net portion of Visual Studio for free: http://msdn.microsoft.com/vstudio/express/default.aspx That way, you're much more likely to have a fully supported app. I've not tried running VB6 apps on Vista. :) j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query skips one set of records
At 12:14 AM 1/19/2007, Dan Nelson wrote: In the last episode (Jan 18), Miles Thompson said: The query displayed below performs flawlessly, except for these two records: 7364 M0174000250510 Invoice 2006-12-13 2006-12-13 2006-12-31 7365 M01740 002506 5 Invoice 2006-12-13 2006-12-132006-12-31 Here's the table structure: member_idvarchar(6) member_sub_idvarchar(6) pay_method varchar(8) monthly_cost decimal(11,0) anniv_bill_date date dtCreateddate fetch_date date This query: SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ) ) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 12 for the next one. Instead it is returning $180. Not for me: mysql create table subinfo ( member_id varchar(6), member_sub_id varchar(6), pay_method varchar(8),monthly_cost decimal(11,0), anniv_bill_date date, dtCreated date, fetch_date date); Query OK, 0 rows affected (0.03 sec) mysql insert into subinfo values (7364,M01740,Invoice,10,2006-12-13,2006-12-13,2006-12-31), (7365,M01740,Invoice,5,2006-12-13,2006-12-13,2006-12-31); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), (SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) )) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id; +---+---++ | member_id | member_sub_id | Amount | +---+---++ | 7364 | M01740| 220.00 | | 7365 | M01740| 60.00 | +---+---++ 2 rows in set (0.10 sec) mysql select version(); +---+ | version() | +---+ | 3.23.58 | +---+ 1 row in set (0.00 sec) I get the same result on 5.1.14, too. Try selecting count(*) along with the other columns in your query and verify that another record isn't sneaking in and getting totalled up. -- Dan Nelson [EMAIL PROTECTED] Dan, I thought I had proofed the message carefully when I sent it - your results are correct, but I should have left off the column containing the 7364 and 7365. Those are auto incrementing primary keys. The grouping, and summing, is to be done on the MO1740 column. I upgraded to MySQL 4.1 and results remained the same. Thank you for looking at this. Regards - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
changing from mysql5 to mysql4
Hello ! I need of koha, a software for libraries. It works with mysql 4, but it's not compatible with mysql5. How can do this changement with a Debian Etch ? Must I only remove the one and install the other ? Thanks for help, Thibaud. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query skips one set of records
At 03:58 AM 1/19/2007, Felix Geerinckx wrote: [EMAIL PROTECTED] (Miles Thompson) wrote in news:[EMAIL PROTECTED]: This query: SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ) ) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 12 for the next one. Instead it is returning $180. The monthly_cost column is indeterminate (read meaningless) outside of an aggregate function, since you're not grouping on it. See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html -- felix Felix, Sorry, but I do not understand what you are trying to explain. I have looked at that page in the manual, but I'm no clearer on its meaning. What I understand my SQL statement to say, is for every member_id add up the monthly cost, subject to this condition: If the monthly cost = 10 then calculate monthly_cost *2 plus 200 and add it to the total for that member_id, otherwise calculate monthly_cost * 12 and add that to the total for the member_id. How does this differ from the second example given in the previous section, http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ? What would the point be on grouping on monthly_cost? Apart from that, it works just fine for all other member_id's. If you could bring a little more light I would appreciate it. Regards - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query skips one set of records
Miles Try it yourself with table tbl (id,cost) with rows (1,10),(1,50),(2,100): SELECT id, IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount FROM tbl GROUP BY id,cost; +--++ | id | Amount | +--++ |1 |320 | |2 | 1200 | +--++ SELECT id, IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount FROM tbl GROUP BY id,cost; +--++ | id | Amount | +--++ |1 |220 | |1 |600 | |2 | 1200 | +--++ Unless (i) there is exactly one unique value of cost per id, or (ii) the query groups by cost as well as id, retrieved cost values are indeterminate. PB - Miles Thompson wrote: At 03:58 AM 1/19/2007, Felix Geerinckx wrote: [EMAIL PROTECTED] (Miles Thompson) wrote in news:[EMAIL PROTECTED]: This query: SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ) ) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 12 for the next one. Instead it is returning $180. The monthly_cost column is indeterminate (read meaningless) outside of an aggregate function, since you're not grouping on it. See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html -- felix Felix, Sorry, but I do not understand what you are trying to explain. I have looked at that page in the manual, but I'm no clearer on its meaning. What I understand my SQL statement to say, is for every member_id add up the monthly cost, subject to this condition: If the monthly cost = 10 then calculate monthly_cost *2 plus 200 and add it to the total for that member_id, otherwise calculate monthly_cost * 12 and add that to the total for the member_id. How does this differ from the second example given in the previous section, http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ? What would the point be on grouping on monthly_cost? Apart from that, it works just fine for all other member_id's. If you could bring a little more light I would appreciate it. Regards - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing from mysql5 to mysql4
At 02:55 PM 1/21/2007, Thibaud Hulin wrote: Hello ! I need of koha, a software for libraries. It works with mysql 4, but it's not compatible with mysql5. How can do this changement with a Debian Etch ? Must I only remove the one and install the other ? Thanks for help, Thibaud. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I imagine there is a way to run both, but do ont know how. Check the mysql docs. I would not trust a backward revision. Use mysqldump to get all your data out, then remove 5 and install 4. Why or how is koha not compatible with version 5? Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query skips one set of records
Peter (and Felix), Thank you, and I learned something today. Now I have a script that needs adjusting. The amount certainly is indeterminate. The wonder is that we were able to run with this for so long. Regards - Miles At 03:57 PM 1/21/2007, Peter Brawley wrote: Miles Try it yourself with table tbl (id,cost) with rows (1,10),(1,50),(2,100): SELECT id, IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount FROM tbl GROUP BY id,cost; +--++ | id | Amount | +--++ |1 |320 | |2 | 1200 | +--++ SELECT id, IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount FROM tbl GROUP BY id,cost; +--++ | id | Amount | +--++ |1 |220 | |1 |600 | |2 | 1200 | +--++ Unless (i) there is exactly one unique value of cost per id, or (ii) the query groups by cost as well as id, retrieved cost values are indeterminate. PB - Miles Thompson wrote: At 03:58 AM 1/19/2007, Felix Geerinckx wrote: [EMAIL PROTECTED] (Miles Thompson) wrote in news:[EMAIL PROTECTED]: This query: SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ) ) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 12 for the next one. Instead it is returning $180. The monthly_cost column is indeterminate (read meaningless) outside of an aggregate function, since you're not grouping on it. See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html -- felix Felix, Sorry, but I do not understand what you are trying to explain. I have looked at that page in the manual, but I'm no clearer on its meaning. What I understand my SQL statement to say, is for every member_id add up the monthly cost, subject to this condition: If the monthly cost = 10 then calculate monthly_cost *2 plus 200 and add it to the total for that member_id, otherwise calculate monthly_cost * 12 and add that to the total for the member_id. How does this differ from the second example given in the previous section, http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ? What would the point be on grouping on monthly_cost? Apart from that, it works just fine for all other member_id's. If you could bring a little more light I would appreciate it. Regards - Miles -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing from mysql5 to mysql4
Thanks for your answer. The install of Koha fails after entering my password root for sql. This is the message error : CREATING DATABASE Creating the MySQL database for Koha... 0 ERROR 1064 (42000) at line 772: 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 'return char(4) default NULL, renewals tinyint(4) default NULL, timestamp tim' at line 9 However, I can't install mysql 4.1 after removing mysql 5.0. Indeed, if I want install the package mysql-client-4.1, synaptic wants to install mysql-client-5.0. Idem for mysql-server-4.1. And when I try to install mysql-common-4.1, I have a message says this package is virtual, furnished by mysql-common 5.0-30.3 So, I don't know how do that... Miles Thompson a écrit : At 02:55 PM 1/21/2007, Thibaud Hulin wrote: Hello ! I need of koha, a software for libraries. It works with mysql 4, but it's not compatible with mysql5. How can do this changement with a Debian Etch ? Must I only remove the one and install the other ? Thanks for help, Thibaud. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] I imagine there is a way to run both, but do ont know how. Check the mysql docs. I would not trust a backward revision. Use mysqldump to get all your data out, then remove 5 and install 4. Why or how is koha not compatible with version 5? Regards - Miles Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Nuno Vaz Oliveira wrote: Hello! I'm thinking to develop a database interface for our company and I thought great when I discovered that Visual Basic 6 was able to interact with MySQL. That's because VB6 is very easy and PHP (even being fast, but its hard for me) gets slower because the source code for HTML pages needs to go from the server to the client and big pages must carry the data and the tags, images, css, formating, etc... Try Gtk2-Perl. I've written some Perl modules that will automate a lot of what you need for data-aware forms. Check them out at: http://entropy.homelinux.org/axis/ It's a whole lot faster than VB, and also cross-platform ( tested heavily under Windows 2000 and Gentoo Linux, and initial development done on OS-X ). If you don't wanna use Perl, then try the PHP bindings for Gtk2 instead :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Hello John, All the links visible on the site are workink. I should have explained myself better... There are other articles that used to be there that are missing like the sshtunnel tutorial which used to be at: http://www.vbmysql.com/articles/sshtunnel.html and other links that teached to connect to MySQL from VB6 and a lot more on MySQL + VB6. The articles you provided (except the first one) are for VB.NET... For VB6 there is only: - Accessing MySQL BLOB - CursorTypes, LockTypes, and CursorLocations - Using MySQL, Visual Basic 6, and Crystal Reports 9 - VB/MySQL Datatypes Nothing about connections, and nothing about VB6 and MySQL only... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic 6 + MySQL
Just FYI: you can get the Express version of the VB.Net portion of Visual Studio for free: http://msdn.microsoft.com/vstudio/express/default.aspx That way, you're much more likely to have a fully supported app. I've not tried running VB6 apps on Vista. :) I wasn't able to find the EULA for the express products... And I didn't even know about free stuff from Microsoft. Anyway, can you tell me if the express editions are free for comertial use? I mean, this is not for a student work. It's for a company's use... Thanks - Nuno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to convert the the string row[i] to its real type.
Hi all: I use mysql C API , and I want generate JSON object from mysql recordset, the filed value of JSON should be real type , e.g. int, float, ect. but the value I got using mysql_fetch_row() is strings, I can get its type using field-type, and then convert the values. but it is not convinient to me, How to do it in a smart way? -- David
Include a stored procedure ( that returns a recordset ) in another query
Hi all. I'd like to be able to define a stored procedure that returns a recordset ( I've done this bit ), and then select from this in another query, eg: CREATE [EMAIL PROTECTED] PROCEDURE `sp_MyClients`( IN ACCMAN INT ) BEGIN select * from mirror_Clients where AccMan = ACCMAN; END ... and then: select C.FileNo, C.SubNo, L.AccountNo from ( sp_MyClients ( 192 ) ) C inner join TLocations T on C.ClientID = T.ClientID; I've also tried adding 'call' immediately before the 'sp_MyClients' bit. Either way, I get: ERROR 1064 (42000): 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 '( 192 ) ) C inner join TLocations T on C.ClientID = T.ClientID' at line 1 I assume it's not possible to do this then? I realise I can skip the stored procedure in this case and just add the where clause from the SP to the query, but in some other cases, the SP bit will be quite complex. Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query Question
If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. I'm guessing this could be done with nested queries, but this is me attempting to optimise a script from 3 queries to 1, so the gains by using nested queries would be negligible. I'm guessing that this isn't possible, but I thought asking some people that know more than myself couldn't hurt. Any assistance is appreciated. Thanks, Adam Bishop
FOUND_ROWS() returns wrong result
Hi, I've installed MySQL 5.0.27 on a LIVE environment while DEVELOPMENT runs a lower version, yes I know wrong thing to do. I've run into quite a few issues which are causing me lots of problems, the main one being that FOUND_ROWS() return a wrong result. The queries in questions are SELECT SQL_CALC_FOUND_ROWS PI.productInfoIdentity , PI.productInfoTypeIdentity , PI.productCategoryIdentity , PI.dateStamp , PI.documentIdentity , PIT.productInfoTypeTitle , PC.productCategoryTitle , D.documentTitle , D.documentIdentity , D.originalExtension , D.originalName , D.fileSize FROMtbl_productinfo PI INNER JOIN tbl_productinfotype PIT ON PI.productInfoTypeIdentity = PIT.productInfoTypeIdentity INNER JOIN tbl_l_productcategory PC ON PI.productCategoryIdentity = PC.productCategoryIdentity INNER JOIN tbl_document D ON PI.documentIdentity = D.documentIdentity INNER JOIN tbl_l_country C ON C.countryIdentity = PI.countryIdentity AND PI.countryIdentity = 1 ORDER BYPC.productCategoryTitle LIMIT 0, 10; Which returns nothing Second statement SELECT FOUND_ROWS() AS value; Returns 8, which is completely wrong. The numbers returned do not make any sense either, as I have others that should return 0, but return 6 or some other number. Any help would be much appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP
The second major problem I am running into after the upgrade is the following error, which did not occur on Development. Error Executing Database Query. Cannot convert value '-00-00 00:00:00' from column 4 to TIMESTAMP. The error occurred on line 8. MySQL version is: 5.0.27 Thanks in advance for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there C API function to convert the MYSQL_ROW items from string to Language specified type?
Hi all: Is there a C API function to convert the MYSQL_ROW items from string to Language specified type? I thought there should be one, but I can't find it. Could you give me some advice? Appreciate your help! -- David
Re: SQL Query Question
In the last episode (Jan 22), Adam Bishop said: If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. How about: SELECT * FROM mytable WHERE Age=MIN(age); The smallest and largest values for a column are alawys available via MIN() and MAX(). If you had wanted the 2nd smallest, or the top 3, then you would have needed a subquery. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Ah, that would work. Looks like I was making the problem too complex in my mind, thanks for your help. Adam Bishop -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: 22 January 2007 07:07 To: Adam Bishop Cc: mysql@lists.mysql.com Subject: Re: SQL Query Question In the last episode (Jan 22), Adam Bishop said: If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. How about: SELECT * FROM mytable WHERE Age=MIN(age); The smallest and largest values for a column are alawys available via MIN() and MAX(). If you had wanted the 2nd smallest, or the top 3, then you would have needed a subquery. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
