Posted by Bouwput in SWF Studio V3 on Feb 04 2011, 07:48 am

I'm creating an application wich preforms some bulk datatransformation to an Access database giving m,ultiple xls files as output.

I'm using ADO to connect to Access and retrieve the data wich is manipulated by ASIII and then stored in Excel format using ADO.

While using ADO's execSQL the data is correctly send over to the xls sheet and added to the sheet like I want it. Except for a apostrophe that is added to every single INSERT.

eg:
INSERT INTO table (one, two) VALUES ('one', 'two')
-> check excell sheet
one two
'one 'two

This is bugging me because I'm inserting numbers aswell wich gives you an error in excel (number stored as string) '787,09

Furthermore it does add an apostrophe to the INSERT TABLE. So when adding a sheet (table) to the excel the scheet tab get's named _NAME (I recan excel set's the ' to _ because it can't handle the ' in the name).

Any help would be very very much apriciated, have to get this done before upcomming monday.

cheers,
Ron


Posted by northcode in SWF Studio V3 on Feb 04 2011, 09:34 am

Does it fail if you just try to create a SQL statement in code and insert that or only when you're pulling data from the ADO and then inserting it into the XLS file? To help, we'll need to see the code that you're using to do the insert and a sample of the XLS file that you're tryimg to update.


Posted by Bouwput in SWF Studio V3 on Feb 04 2011, 10:03 am


private function insertRow($result:Object, $callback:Object, $error:Object){
   if ($result.success){
      if(_curRun<_runs){
         _curRun++
         ssDebug.trace("info-input = "+'INSERT INTO ['+_insertTab+'$] (Informatie) VALUES("'+_insertData[_curRun].inf+'")');
         isql = "INSERT INTO ["+_insertTab+"$] (Informatie) VALUES ('"+_insertData[_curRun].inf+"')";
         ssCore.ADO.setSQL({sql:isql}, {callback:insertRow});
         ssCore.ADO.open();
      }else{
         ssDebug.trace("done, close conn, dispatch done event to model")
         ssCore.ADO.close();
         super._model.dispatchEvent(new Event(ModelEvent.TABLE_FILLED));
      }
   }else{
      ssDebug.trace("error: " + $result.Error.code + ":" + $result.Error.description);
      ssDebug.trace("adoError: " + $result.adoError);
   }
}


Normaly you would use just one sql query to do multiple inputs on a table but since this did not seem to work I had the query callback to itself to run the query again and using two vars (_runs and _curRun) to keep track of progress.

["+_insertTab+"$] -> just a var as String containing the name of the sheet
'"+_insertData[_curRun].inf+"' An array holding objects, on each run get the next object and set the value for the insert.

All goes well, I use this often in the application without any problem ecept for the apostrophe thingy.

I tried to switch " and the ' around, I have traced the query, everything seems to be just fine, still it adds a '. Attached is an example output XLS.

Thx for looking into this!

attachments: Rek & Wisk 2010.zip  


Posted by Dan in SWF Studio V3 on Feb 08 2011, 12:29 pm

Correct me if I'm wrong, but the single quote you are seeing is only visible when editing a cell, right?

In Excel, or any common spreadsheet program, the single quote at the beginning of a number (or even text, actually), is an indicator to Excel that the contents are to be treated as text, regardless of the underlying nature of the content. That means that typing in 3.14 will be enterpreted as a number, whereas '3.14 will be treated as characters -- the quote will not be displayed.

All this is to say that the data you are inserting via the ADO plugin is being inserted as text. This is not easily controlled, as the Excel driver will favour inserting your values as text unless conditions are just right.

For example if you use the attached spreadsheet, and run the following code, you get a numeric value added to the "Number" column.


var strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ssGlobals.ssStartDir + "\\Book1.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\";";
ssDebug.trace(strConnect);
ssCore.ADO.setConnectString({connectString: strConnect});

var strSQL = "INSERT INTO [Sheet1$] ([Number], [Text]) VALUES (3.1415, \"Pi\")";
ssDebug.trace(strSQL);
ssCore.ADO.execSQL({sql: strSQL});


Things that can make it fail, or more specifically, insert text, include:
1) Not having previous records with a numeric value. If the columns are empty, Excel seems to default to adding the data as text.
2) Not indicating that the first row is a header. The "HRD=Yes" in the connection string controls that.
3) Telling the driver to treat intermixed data as text. In other words, "IMEX=1" in the connect string will most likely not yield the required results.
4) Quoting the numeric value in your INSERT statement. Though not always a factor, I find it best to not quote numbers, since that ultimately implies you want it to be a string.

I hope this information is useful in sorting out your problem.

As an aside, you'll notice I'm using the ADO.execSQL statement, rather that ADO.setSQL and ADO.open. I'm not sure why you're recursively calling your insertRows function, but you probably have a reason. Personally, regardless of the reason, I wouldn't implement it that way. It's considered "asking for trouble". YMMV.

attachments: Book1.xls.zip  


Posted by Bouwput in SWF Studio V3 on Feb 14 2011, 05:06 am


QUOTE: from Dan;52082
I hope this information is useful in sorting out your problem.

As an aside, you'll notice I'm using the ADO.execSQL statement, rather that ADO.setSQL and ADO.open. I'm not sure why you're recursively calling your insertRows function, but you probably have a reason. Personally, regardless of the reason, I wouldn't implement it that way. It's considered "asking for trouble". YMMV.


Hi Dan,

Thx for the info. I'm aware of the added ' to force excel to set the 'text' property to the celle regardless of the input data type. Using a sheet manualy to insert text however doesn't result in an added ' by default when inserting text data. I assumed ADO (or excel) would cover this when inserting with SQL (table is created by SQL aswell, setting cell property to text).

Setting the cell property to number and adding data as number without quotes solved the 'problem on the numbers.

The reason why I'm recursively calling the insert statement is because I have to insert a large number of rows. I tried to expand my SQL statement as you would on a database:

'INSERT INTO tablename ('field1', 'field2') VALUES ('one','two'), ('four', 'five'), ('n', 'n')";

but with no success. It just adds the first row and then stops returning a success to the callback function. Is there a way to insert multiple rows using a single sql statement?

I have no IMEX set on my ADO connectstring. Does it default to 0?

Cheers,
Ron


Posted by northcode in SWF Studio V3 on Feb 14 2011, 01:31 pm

I haven't tested this, but the SQL "statement" you pass to ExecSQL should be able to handle multiple statements, each one terminated by a semi-colon.


INSERT INTO tablename ('field1', 'field2') VALUES ('one','two');
INSERT INTO tablename ('field1', 'field2') VALUES ('four', 'five');
INSERT INTO tablename ('field1', 'field2') VALUES ('n', 'n');
The thing you lose in this case is the ability to determine if/when a particular insert statement has failed.


Posted by Bouwput in SWF Studio V3 on Feb 18 2011, 03:36 am


QUOTE: from northcode;52130
I haven't tested this, but the SQL "statement" you pass to ExecSQL should be able to handle multiple statements, each one terminated by a semi-colon.


INSERT INTO tablename ('field1', 'field2') VALUES ('one','two');
INSERT INTO tablename ('field1', 'field2') VALUES ('four', 'five');
INSERT INTO tablename ('field1', 'field2') VALUES ('n', 'n');
The thing you lose in this case is the ability to determine if/when a particular insert statement has failed.


Thank you Tim,

I''ll have a go @ it and report back. I've noticed that the result of a single INSERT query also false to give the correct response. It always returns resultObject.result==success regardless of the actual success.

But it will be easier to to the query in one run and have a SELECT query follow up to see if any data has been send. It's not 100% failsave but in this case it will do.

I'll be back with some testresults later today.

Cheers,
Ron


Posted by northcode in SWF Studio V3 on Feb 18 2011, 03:51 am


QUOTE:
''ll have a go @ it and report back. I've noticed that the result of a single INSERT query also false to give the correct response. It always returns resultObject.result==success regardless of the actual success.The success you get back just says that the ExecSQL command was called and did something, it has no idea whether the SQL statement completed successfully or not. Even if the SQL statement is malformed, the call to ExecSQL was still a "success". It only fails if the method that executes the SQL statement can't be called for some reason.


Posted by Bouwput in SWF Studio V3 on Feb 18 2011, 04:33 am

Hi Tim,

That doesn't work. I get an error:
IDispatch error #3092
(translated from dutch) Encountered characters after the end of the SQL-instruction

Seems the ; seperating the query's is threated like an invalid character.

Any suggestions?


Posted by northcode in SWF Studio V3 on Feb 18 2011, 12:30 pm

Okay, we'll look into this a bit more. I know we expect the result of execSQL to return something (like when you use it for a SELECT statement) so ADO might not like seeing multiple statements glued together but there HAS to be a way to do this.


Posted by Bouwput in SWF Studio V3 on Feb 27 2011, 12:10 pm


QUOTE: from northcode;52158
Okay, we'll look into this a bit more. I know we expect the result of execSQL to return something (like when you use it for a SELECT statement) so ADO might not like seeing multiple statements glued together but there HAS to be a way to do this.

Hi Tim,

Did you find the time to look into this? I passed my deadline on the project and delivered the app with the recursive ADO query. It does the job but I wouldn't mind doing a single query for miltiple rows in V2 of the app.

Cheers,
Ron


Posted by northcode in SWF Studio V3 on Feb 27 2011, 09:41 pm

We may have to update the version of ADO we're assuming as the baseline, it may be too old to support this functionality. We need to do more testing to verify this and then update the ADO plugin to handle it.