Import data - problems

[Bart Pietercil]

Hi Greg,

SP would be Stored Procedure and would look like this

I just pasted an example of an SP so you can see the structure. Try to  
understand it, you can I think use the same structure to loop over  
your data and insert the data in the table(s) you want

-------------
create or replace procedure cm_php_getCoacheeCourseCatalog()
begin
/*
never call without setting setConnectionVars with a userid
*/

	declare curscounter,looper llong;
	declare bool_has_access boolean;
	declare str_mycatalog STRING;
	DECLARE str_Stress STRING;
	DECLARE test LLONG;
	
	set curscounter = select count (distinct objptr_assignment) from  
mm_assignment_access WHERE objptr_assignment->objptr_assignment_status  
in
					  (SELECT RecID from tbl_typelistvalues where listvalue = 1 and  
objptr_typelist->typelistname = 'AssignmentStatusList');
	if curscounter > 0 then
		declare assignment_id llong;
		declare full_catalog_curs cursor for select distinct  
objptr_assignment from mm_assignment_access WHERE objptr_assignment- 
 >objptr_assignment_status in
					  (SELECT RecID from tbl_typelistvalues where listvalue = 1 and  
objptr_typelist->typelistname = 'AssignmentStatusList');
		set looper = 0;
		open full_catalog_curs;
		fetch first full_catalog_curs into assignment_id;
		while looper < curscounter do
			begin
			set looper = looper +1;
			set bool_has_access =  
cm_funct_determineAccessForAssignment(assignment_id);
			PRINT 'assignment_id = ' || assignment_id;
			if bool_has_access then
				set str_mycatalog = concat(str_mycatalog, ',', assignment_id);
			end if

			if looper < curscounter THEN
				fetch next full_catalog_curs into assignment_id;
			END IF
			exception
				when others then
					raise;
			end
		end while
	PRINT 'str_mycatalog before substr = ' || str_mycatalog;
	
	
	set str_mycatalog = select substr(str_mycatalog,2);

	
	
	PRINT 'Lenght of my_strcatalog = ' || LENGTH(str_mycatalog);
	PRINT str_mycatalog ||'that\'s it.';
	

	END if
	
	
	SET str_Stress = concat( 'select recid from tbl_assignments where  
RecID in (' , str_mycatalog);
	SET str_Stress = concat(str_Stress,  ')' );
	PRINT 'str_Stress = ' || str_Stress;
	EXECUTE str_Stress;

/*	if List_ItemCount(str_mycatalog,',') > 0 Then
		select recid from tbl_assignments where RecID in (str_mycatalog);
	else
		select null;
	end if
*/

exception
	when others then
		call cm_CoreErrorHandler('cm_php_getCoacheeCourseCatalog');
end


---------------

hth

Bart



On 26 Aug 2009, at 00:15, Greg Olson-Hyde wrote:

>
> On 25/08/2009, at 11:11 PM, Thorsten Hohage wrote:
>
>> As Bart already mentioned, if this is a serious task - and it seems
>> too - then *I* would import everything in varchar fields and then use
>> one / some SP(s) to move the data to the final destination. Perhaps
>> this strategy is easier then to deal with a lot of regex before
>> importing.
>>
>> Btw. many dumps, e.g. one generated withe the _loved_ FileMaker Pro
>> can have a lot of invisible chars in columns that are intentionally
>> not thought to be used with such chars ->>> many trouble. All this  
>> can
>> be avoided with such a two step import, too.
>
> Hi Thorsten,
>
> Possibly a stupid question from a potential newbie, but what is an SP?
>
> Is there documentation to show how to move the data? I was just going
> to change the field types after import - this seems like it is NOT a
> good idea :((
>
> My data started life in the 'loved' FMP, was moved to SQLite and now
> to Valentina. I spent a LOT of time cleaning the data after FMP.
>
> Thanks to all of you for your help on this (and other) issues.
>
> Cheers
>
> Greg Olson-Hyde
> Sydney Australia
>
> 2.8 GHz Quad-Core Mac Pro
> Mac OS X 10.5.8
> RB 2009 r2.1 Pro
> Valentina Studio Pro 4.2 trial
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina