The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
 All Forums
 Origin Forum for Programming
 LabTalk Forum
 Creating temporary table via dbImport

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!

Screensize:
UserName:
Password:
Anti-Spam Code:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkUpload FileInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
Check here to subscribe to this topic.
   

T O P I C    R E V I E W
Karsten8 Posted - 05/09/2023 : 03:04:44 AM
Origin Ver. and Service Release: 2022b 9.9.5.171
Operating System: Windows Server 2012

Dear Origin-team,

I use dbImport to get data from SQL database. This works without problems. But now I have to create a temporary table at the database before I import the data. Thus I know that the server connection is ok, the SQL-import works and if I test the SQL-sequence to create the temporary table directly on the server software he creates the table without problems.


This sequence I want to use:
string SPECTRALtemp$="CREATE TABLE ##temp_spectral_data (wavelength int, intensity int)";
dbEdit change conn:=dbSource$ sql:=SPECTRALtemp$;
dbImport;

The following error message occurs:
(800a0e78) ADODB.Recordset: Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen. C:\Windows\HELP\ADO270.CHM
... The process is not allowed for a closed object.

From web search I found that a closed connection could be a problem but I do not see the point why or where this should occur.

Thanks for your help and best regards,
Karsten8.
2   L A T E S T    R E P L I E S    (Newest First)
Karsten8 Posted - 05/16/2023 : 01:36:26 AM
Dear Cpyang,

thanks for your answer. The ## are needed to generate a temporary table and no permanent table.
I switched to python and via SQLalchemy I could generate a ##temporary table without problems.
Perhaps the ADODB driver has problems with ##temporary tables.

This topic can be closed.

Best regards,
Karsten8.
cpyang Posted - 05/09/2023 : 10:40:39 AM
I tried in SQLite and it complained about the two ##, so I did


string SPECTRALtemp$="CREATE TABLE temp_spectral_data (wavelength int, intensity int)";
dbEdit change conn:=dbSource$ sql:=SPECTRALtemp$;
dbImport;


and I see the temp_spectral_data table created.

BTW, we improved the db support in LT in 2023b, so code can look cleaner where error code can return, so the above will look like


wks.db.conn$="Driver={SQLite3 ODBC Driver};Database=C:\Users\dev\Downloads\hawaii.sqlite";
wks.db.sql$="SELECT name FROM sqlite_master WHERE type='table' AND name='temp_spectral_data'";
string strErr$;
if(wks.db.test(1, strErr$) != 1) {
	//strErr$=;
	type "need to add table";
	wks.db.sql$="CREATE TABLE temp_spectral_data (wavelength int, intensity int)";
	wks.db.run();
}
else
	type "table existed already";


The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000