[Gambas-user] SQL query for DB.Exec() question

[Caveat]

You just need to use backticks...

   Try myDB.Open
   If Error Then
     Message("Cannot Open database:" & Error.Text)
     Return False
   End If
   myDB.Exec("delete from test_table")
   myDB.Exec("insert into test_table (`Actual ID`) values('My actual ID')")
   res = myDB.Exec("select `Actual ID` from test_table")

   For Each resF In res.Fields
     Print resF.Name
   Next
   res.MoveFirst
   While res.Available
     Print res["Actual ID"]
     res.MoveNext
   Wend
   Return True
End

prints:

Actual ID
My actual ID

Alternatively, to be sure you get the backtick and not the single quote:

   myDB.Exec("delete from test_table")
   myDB.Exec("insert into test_table (" & Chr(96) & "Actual ID" & 
Chr(96) & ") values('My actual ID')")
   res = myDB.Exec("select " & Chr(96) & "Actual ID" & Chr(96) & " from 
test_table")

Kind regards,
Caveat

On 29/08/13 09:52, nando wrote:
> I see spaces in the field 'Initial IC' and 'Actual IC'.
> If you're using a space in the field name - DONT!!!
> Use _ underscore instead.
> Don't use a spaces in any names.
> You complicate life with them and make you want to pull your hair out.
>
> This should work
>
> myString = "SELECT IC.ID, Product.Description "
> myString.= "FROM IC, Product "
> myString.= "WHERE IC.ProductID = Product.ProductID "
> myString.= "ORDER BY IC.ID DESC"
> rData = ConMyData.Exec(myString)
>
>
> This is not correct:
> rData = ConMyData.Exec("SELECT &1 FROM IC", "'Actueel IC'")
>
> Should be:
> rData = ConMyData.Exec("SELECT `Actual IC` FROM IC")
>
> Again, PLEASE don't use spaces embedded in the field.
>
>
>
> ---------- Original Message -----------
> From: Willy Raets <willy at ...2734...>
> To: mailing list for gambas users <gambas-user at lists.sourceforge.net>
> Sent: Wed, 28 Aug 2013 22:18:52 +0200
> Subject: [Gambas-user] SQL query for DB.Exec() question
>
>> Hello all,
>>
>> I've been searching the Gambas mailing list archives and documentation
>> for a answer to a problem I have. Tried all kinds of suggestions but
>> haven't solved the problem yet.
>>
>> This is the situation simplified:
>>
>> A MySql database named MyData with a two tables named IC and Products.
>> Both tables have a field ProductID with a relation inbetween.
>>
>> Table IC has fields
>> ID		-> Primary Key, Indexed
>> ProductID	-> Indexed
>> Initial IC
>> Actual IC
>> ValueInitial
>> ValueActual
>>
>> Table Product has fields:
>> ProductID	-> Primary Key, Indexed
>> Description
>>
>> In a query I need to retrieve all fields in IC except ProductID and
>> Description in Products
>> I manage to get the query done except for the fields Initial IC and
>> Actual IC (because of the blanc in the field name).
>>
>> As I understand in MySql I need to query:
>> SELECT 'Actual IC' FROM IC to get Actual IC
>>
>> I have tried it like this in Gambas (rData being a Result and ConMyData
>> being the connection)
>>
>> rData = ConMyData.Exec("SELECT &1 FROM IC", "'Actueel IC'")
>>
>> But the syntax is not accepted by MySql
>>
>> To make the matter more complicated when querying more than one table
>> you need to add the table to the query syntax like this
>> SELECT IC.ID, Product.Description  FROM IC LEFT JOIN Product ON
>> IC.ProductID=Product.ProductID ORDER BY IC.ID DESC
>>
>> Or in Gambas:
>>
>> rData = ConMyData.Exec(SELECT IC.ID, Product.Description  FROM IC LEFT
>> JOIN Product ON IC.ProductID=Product.ProductID ORDER BY IC.ID DESC)
>>
>> This works perfect, but when I want to add field IC.Actual IC to that
>> query in a db.Exec() what is the proper format?
>> I tried with:
>> IC.'Actual IC'
>> 'IC'.'Actual IC'
>> 'IC.Actual IC'
>> They all give back syntax errors from MySql
>>
>> Any suggestions welcome
>>
>> -- 
>> Kind regards,
>>
>> Willy (aka gbWilly)
>>
>> http://gambasshowcase.org/
>> http://howtogambas.org
>> http://gambos.org
>>
>> ------------------------------------------------------------------------------
>> Learn the latest--Visual Studio 2012, SharePoint 2013, SQL 2012, more!
>> Discover the easy way to master current and previous Microsoft technologies
>> and advance your career. Get an incredible 1,500+ hours of step-by-step
>> tutorial videos with LearnDevNow. Subscribe today and save!
>> http://pubads.g.doubleclick.net/gampad/clk?id=58040911&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Gambas-user mailing list
>> Gambas-user at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gambas-user
> ------- End of Original Message -------
>
>
> ------------------------------------------------------------------------------
> Learn the latest--Visual Studio 2012, SharePoint 2013, SQL 2012, more!
> Discover the easy way to master current and previous Microsoft technologies
> and advance your career. Get an incredible 1,500+ hours of step-by-step
> tutorial videos with LearnDevNow. Subscribe today and save!
> http://pubads.g.doubleclick.net/gampad/clk?id=58040911&iu=/4140/ostg.clktrk
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
> .
>