On Mon, 31 Aug 2009, Mike Pfeiff wrote: > Previously I have asked about making an ODBC connection to MS Access to > bring in my own data. With the help of users in this community I have > been successful in that end ever. > > Below is the code that I used: > > nulldata 250 > setobs 12 1990:1 > open dsn=FCST_INPUTS --odbc > string sqlstr="SELECT Emplopy FROM Data_Table_to_GRETL WHERE > census_division="East North Central" > data Employ @sqlstr --odbc > setinfo MWh -n "in MWh" > > Now I have a simple string concatenation question to be used in a SQL > statement. > > I need to bring in two (2) data series from the same MS Access table > named Data_Table_to_GRETL. The two series are: (1) Employ; and (2) Pop > > Both of data series need to be from the same census_division (in the > Acess database I have data by year month for each of the 9 US census > divisions). In this example the census division is "East North > Central". > > I though that I could define a string named census_divicsion early in > the code and then concatenate the sqlstr for each "ODBC read" as > follows: > > string census_division="East North Central" > nulldata 250 > setobs 12 1990:1 > > # bring in Employment data > open dsn=FCST_INPUTS --odbc > string sqlstr="SELECT Employ FROM Data_Table_to_GRETL WHERE > census_division=" ~ census_division > data Employ @sqlstr --odbc > > # bring in Population data > open dsn=FCST_INPUTS -odbc > string sqlstr2="SELECT Pop FROM Data_Table_to_GRETL WHERE > census_division="~ census_division > data Pop sqlstr2 --odbc > > However, for some reason even thought the string appears to be > correct, it errors out: > > Error executing script: halting > >data Employ @sqlstr -odbc > > Any assistance providing the correct logic for concatenating the > sqlstr that would make it able to be read in SQL would be > greatly appreciated.
I don't have access to an ODBC connection on the machine where I'm writing this, but I suspect that the problem arises from the embedded spaces in the value of the "census_division" variable in your "WHERE" clause (that is, the value "East North Central"). You can ensure that the value with spaces is correctly wrapped in quotes by using gretl's sprintf command with backslash-escaped quotes, as in: <script> string cendiv = "East North Central" sprintf s "SELECT X FROM Tbl WHERE Cond=\"%s\"", cendiv printf "s = %s\n", s </script> However, I'm not sure offhand whether gretl's ODBC routines will handle this sort of input correctly. I'll have to test. Allin Cottrell
